Oracle - 순차적으로 누적하기9

[ Oracle - 순차적으로 누적하기9 ]

 

일별 월누적매출 구하기

실 데이터는 <표1-실데이터>처럼 존재하며, <표2-결과>와 같은 결과 데이터를 조회

 

<표1-실데이터>

기준일자	코드	금액
2009-01-01	A	100
2009-01-01	B	200
2009-01-02	A	150
2009-01-02	B	200
2009-01-02	C	100
2009-01-03	A	300
2009-01-03	B	100

<표2-결과>

기준일자	코드	금액	누적금액
2009-01-01	A	100	100
2009-01-01	B	200	200
2009-01-02	A	150	250
2009-01-02	B	200	400
2009-01-02	C	100	100
2009-01-03	A	300	550
2009-01-03	B	100	500
2009-01-03	C	100	100

 

 

WITH t AS ( SELECT '20090101' dt, 'A' cd, 100 amt FROM dual UNION ALL SELECT '20090101' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'A' , 150 FROM dual UNION ALL SELECT '20090102' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'C' , 100 FROM dual UNION ALL SELECT '20090103' , 'A' , 300 FROM dual UNION ALL SELECT '20090103' , 'B' , 100 FROM dual ) SELECT dt, cd, amt, sum_amt FROM ( SELECT a.dt , b.cd , c.amt , SUM(c.amt) OVER(PARTITION BY b.cd ORDER BY a.dt) sum_amt FROM ( SELECT DISTINCT dt FROM t ) a CROSS JOIN (SELECT DISTINCT cd FROM t) b LEFT OUTER JOIN t c ON a.dt = c.dt AND b.cd = c.cd ) WHERE sum_amt IS NOT NULL ORDER BY dt, cd