▦▦[tsohr] 저작권따위개나줘버려▦▦ :: 누적합 (Stacked Sum) SQL 구하는 세 가지 쑈

누적합 (Stacked Sum) SQL 구하는 세 가지 쑈

SQL/DB 일반 2009/01/05 20:54

이럴 땐 오라클이 좋긴 좋더라. 기능도 많고...
MSSQL용 쿼리는 ORACLE로 변환 가능함.
MSSQL 쿼리에 SUM() 은 ORDER BY 절을 붙일 수 없다.

---------------------------------------------------------------------------------------------------
-- MSSQL 누적합 1번 -- JOIN ( NESTED LOOP )
WITH TA ( IDX, VAL ) AS
(             SELECT 1 IDX,     11*1 VAL
    UNION ALL SELECT 2 IDX,    222*2 VAL
    UNION ALL SELECT 3 IDX,   3333*3 VAL
    UNION ALL SELECT 4 IDX,  44444*4 VAL
)
SELECT
    TA1.IDX
  , TA1.VAL
  , SUM ( TA2.VAL )               "STACKED_SUM_1"
FROM TA TA1, TA TA2
WHERE TA2.IDX <= TA1.IDX
GROUP BY TA1.IDX, TA1.VAL
ORDER BY TA1.IDX ASC

OUTPUT/PLAN 더보기

---------------------------------------------------------------------------------------------------
-- MSSQL 누적합 2번 -- SCALAR SUBQUERY
WITH TA ( IDX, VAL ) AS
(             SELECT 1 IDX,     11*1 VAL
    UNION ALL SELECT 2 IDX,    222*2 VAL
    UNION ALL SELECT 3 IDX,   3333*3 VAL
    UNION ALL SELECT 4 IDX,  44444*4 VAL
)
SELECT
    TA1.IDX
  , TA1.VAL
  , ( SELECT SUM ( TA3.VAL )     -- 아래 플랜에 이 색으로 칠해진 부분같이 해석됨.
      FROM TA TA3
      WHERE TA3.IDX <= TA1.IDX )  "STACKED_SUM_2"
FROM TA TA1
GROUP BY TA1.IDX, TA1.VAL
ORDER BY TA1.IDX ASC

OUTPUT/PLAN 더보기

/************* OUTPUT
IDX         VAL         STACKED_SUM_2
----------- ----------- -------------
1           11          11
2           444         455
3           9999        10454
4           177776      188230

************** PLAN
Rows  Executes  StmtText
----- --------- ----------------------------------------------------------------------------------------------------------------
4     1         WITH TA ( IDX, VAL ) AS (             SELECT 1 IDX,     11*1 VAL     UNION ALL SELECT 2 IDX,    222*2 VAL ......
0     0           |--Compute Scalar(DEFINE:([Expr1020]=[Expr1020]))
4     1                |--Nested Loops(Inner Join, OUTER REFERENCES:([Union1008]))
4     1                     |--Sort(ORDER BY:([Union1008] ASC))
4     1                     |    |--Constant Scan(VALUES:(((1),(11)),((2),(444)),((3),(9999)),((4),(177776))))
0     0                     |--Compute Scalar(DEFINE:([Expr1020]=CASE WHEN [Expr1028]=(0) THEN NULL ELSE [Expr1029] END))
4     4                          |--Stream Aggregate(DEFINE:([Expr1028]=Count(*), [Expr1029]=SUM([Union1019])))
10    4                               |--Concatenation
0     0                                    |--Compute Scalar(DEFINE:([Expr1011]=(11)))
4     4                                    |    |--Filter(WHERE:(STARTUP EXPR((1)<=[Union1008])))
4     4                                    |         |--Constant Scan
0     0                                    |--Compute Scalar(DEFINE:([Expr1013]=(444)))
3     4                                    |    |--Filter(WHERE:(STARTUP EXPR((2)<=[Union1008])))
3     3                                    |         |--Constant Scan
0     0                                    |--Compute Scalar(DEFINE:([Expr1015]=(9999)))
2     4                                    |    |--Filter(WHERE:(STARTUP EXPR((3)<=[Union1008])))
2     2                                    |         |--Constant Scan
0     0                                    |--Compute Scalar(DEFINE:([Expr1017]=(177776)))
1     4                                         |--Filter(WHERE:(STARTUP EXPR((4)<=[Union1008])))
1     1                                              |--Constant Scan
*/

---------------------------------------------------------------------------------------------------
-- ORACLE 누적합 ( 집계함수의 윈도우 옵션 )
WITH TA AS
(             SELECT 1 IDX,     11*1 VAL FROM DUAL
    UNION ALL SELECT 2 IDX,    222*2 VAL FROM DUAL
    UNION ALL SELECT 3 IDX,   3333*3 VAL FROM DUAL
    UNION ALL SELECT 4 IDX,  44444*4 VAL FROM DUAL
)
SELECT
    IDX
  , VAL
  , SUM ( VAL )
    OVER ( ORDER BY IDX ASC
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) "STACKED_SUM_3"
FROM TA
ORDER BY IDX ASC

OUTPUT/PLAN 더보기