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

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

 

WITH temp AS ( SELECT '199901' reg_dd, 100 a FROM dual UNION ALL SELECT '199902', 200 FROM dual UNION ALL SELECT '200001', 500 FROM dual UNION ALL SELECT '200002', 250 FROM dual UNION ALL SELECT '200002', 200 FROM dual UNION ALL SELECT '200003', 200 FROM dual UNION ALL SELECT '200301', 500 FROM dual ) SELECT gb , DECODE(gb, 3, 'TOTAL', (gb*200) ||'~'|| (gb*200+200)) gb_nm , DECODE(lv, 1, '합계', 2, '건수', 3, '비율') gb_nm2 , MIN(DECODE(yy, 2002, DECODE(lv, 1, s, 2, c, 3, r))) v_2002 , MIN(DECODE(yy, 2003, DECODE(lv, 1, s, 2, c, 3, r))) v_2003 , MIN(DECODE(yy, 2004, DECODE(lv, 1, s, 2, c, 3, r))) v_2004 , MIN(DECODE(yy, 2005, DECODE(lv, 1, s, 2, c, 3, r))) v_2005 , MIN(DECODE(yy, 2006, DECODE(lv, 1, s, 2, c, 3, r))) v_2006 , MIN(DECODE(yy, 2007, DECODE(lv, 1, s, 2, c, 3, r))) v_2007 , MIN(DECODE(yy, 2008, DECODE(lv, 1, s, 2, c, 3, r))) v_2008 , MIN(DECODE(yy, 2009, DECODE(lv, 1, s, 2, c, 3, r))) v_2009 , MIN(DECODE(yy, 2010, DECODE(lv, 1, s, 2, c, 3, r))) v_2010 , MIN(DECODE(yy, 2011, DECODE(lv, 1, s, 2, c, 3, r))) v_2011 FROM (SELECT a.gb , a.yy , SUM(s) s , SUM(c) c , TRUNC(RATIO_TO_REPORT(SUM(s)) OVER(PARTITION BY a.yy) * 200, 2) r FROM (SELECT MOD(LEVEL, 4) gb , 2001 + CEIL(LEVEL/4) yy FROM dual CONNECT BY LEVEL <= 10 * 4 ) a , (SELECT NVL(TRUNC(a / 200), 3) gb , SUBSTR(reg_dd, 1, 4) yy , SUM(a) s , COUNT(*) c FROM temp GROUP BY SUBSTR(reg_dd, 1, 4) , ROLLUP(TRUNC(a / 200)) ) b WHERE a.gb = b.gb(+) AND a.yy >= b.yy(+) GROUP BY a.gb, a.yy ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3) GROUP BY gb, lv ORDER BY gb, lv