[Oracle] SUM OVER PARTITION BY (WHERE) (조건별 누계)

[Oracle] SUM OVER PARTITION BY (WHERE) (조건별 누계)

WITH TEMP_TABLE1 AS ( SELECT 'A' Title, 'P1' Gubun, 1 Seq, 1000 Qty FROM DUAL UNION ALL SELECT 'A' Title, 'P1' Gubun, 3 Seq, 3000 Qty FROM DUAL UNION ALL SELECT 'A' Title, 'P2' Gubun, 2 Seq, 2000 Qty FROM DUAL UNION ALL SELECT 'B' Title, 'P3' Gubun, 1 Seq, 2000 Qty FROM DUAL UNION ALL SELECT 'B' Title, 'P1' Gubun, 2 Seq, 1000 Qty FROM DUAL ), TEMP_TABLE2 AS ( SELECT 'A' Title, '서울' SubTitle FROM DUAL UNION ALL SELECT 'B' Title, '부산' SubTitle FROM DUAL ) SELECT A.Title,B.SubTitle,A.Gubun,A.Seq,A.Qty, SUM(A.Qty) OVER(PARTITION BY A.Title ORDER BY A.Title, A.Seq) Title_누계, SUM(A.Qty) OVER(PARTITION BY A.Title,A.Gubun ORDER BY A.Title, A.Seq) Title_Gubun_누계 FROM TEMP_TABLE1 A LEFT JOIN TEMP_TABLE2 B ON B.Title = A.Title ORDER BY A.Title, A.Seq;