[table1]
[result1]
월별 데이터 [table1] 가 있을 때 [result1] 과 같이 월별 누계를 조회하는 쿼리 만드는 방법
[query1]
[query2] - ORACLE 분석용 함수 (버전 8.1.6 이상)를 이용하는 방법
WITH_T AS
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
[result1]
MONTH MM_TTL
---------- ----------
1 2
2 6
3 12
4 20
5 30
6 42
7 56
8 72
9 90
10 110
11 132
12 156
12 rows selected
---------- ----------
1 2
2 6
3 12
4 20
5 30
6 42
7 56
8 72
9 90
10 110
11 132
12 156
12 rows selected
월별 데이터 [table1] 가 있을 때 [result1] 과 같이 월별 누계를 조회하는 쿼리 만드는 방법
[query1]
WITH
WITH_T AS
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
SELECT W1.MM AS MONTH
, SUM(W2.V) AS MM_TTL
FROM WITH_T W1
, WITH_T W2
WHERE W1.MM >= W2.MM
GROUP BY W1.MM
ORDER BY W1.MM
;
WITH_T AS
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
SELECT W1.MM AS MONTH
, SUM(W2.V) AS MM_TTL
FROM WITH_T W1
, WITH_T W2
WHERE W1.MM >= W2.MM
GROUP BY W1.MM
ORDER BY W1.MM
;
[query2] - ORACLE 분석용 함수 (버전 8.1.6 이상)를 이용하는 방법
WITH
WITH_T AS
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
SELECT W1.MM AS MONTH
, SUM(W1.V) OVER (ORDER BY W1.MM ROWS UNBOUNDED PRECEDING) AS TTL_SUM
FROM WITH_T W1
ORDER BY W1.MM
;
WITH_T AS
(
SELECT 1 AS MM, 2 AS V FROM DUAL
UNION ALL SELECT 2 AS MM, 4 AS V FROM DUAL
UNION ALL SELECT 3 AS MM, 6 AS V FROM DUAL
UNION ALL SELECT 4 AS MM, 8 AS V FROM DUAL
UNION ALL SELECT 5 AS MM, 10 AS V FROM DUAL
UNION ALL SELECT 6 AS MM, 12 AS V FROM DUAL
UNION ALL SELECT 7 AS MM, 14 AS V FROM DUAL
UNION ALL SELECT 8 AS MM, 16 AS V FROM DUAL
UNION ALL SELECT 9 AS MM, 18 AS V FROM DUAL
UNION ALL SELECT 10 AS MM, 20 AS V FROM DUAL
UNION ALL SELECT 11 AS MM, 22 AS V FROM DUAL
UNION ALL SELECT 12 AS MM, 24 AS V FROM DUAL
)
SELECT W1.MM AS MONTH
, SUM(W1.V) OVER (ORDER BY W1.MM ROWS UNBOUNDED PRECEDING) AS TTL_SUM
FROM WITH_T W1
ORDER BY W1.MM
;
댓글을 달아 주세요