아이템 | 구분 | D1 | D2 | D3 | D4 | D5 | D6 | D7 | D8 |
A | A-1 | 5 | 4 | 3 | 2 | 3 | 5 | 6 | 3 |
A | A-2 | 2 | 3 | 5 | 3 | 4 | 3 | 5 | 2 |
A | SUM | 3 | 4 | 2 | 1 | -1 |
기본 A라는 아이템에 대하여 누적값을 구하고싶습니다
기본 테이블 구조는 위와 같은 구조인데
기본 테이블에서 나오는건 A-1, A-2까지가 나오고 SUM을 쿼리로 구현을 해야합니다.
SUM값을 표현을 해야하거든요
D1SUM : 5-2=3
D2SUM : (5+4)-(2+3)=4
D3SUM : (5+4+3)-(2+3+5)=2
D-4SUM:(5+4+3+2)-(2+3+5+3)=1
D-5SUM:(5+4+3+2+3)-(2+3+5+4+4)=-1
이런 식으로 SUM OVER를 아닌 다른 쿼리를 써야 하는데...
동일한 열에서는 가능한데.. 이게 행으로 넘어가면.. 어찌할 도리가 없어서 질문드립니다..
이 쿼리를 해결하려면 어떻게 진행을 해야 할까요..
조언 좀 부탁드립니다..
-- model 이나 다른 방법으로 간결하게 될 것 같긴한데 일단 올려봅니다. 더 좋은 방법은 다른 분들께서 올려주실 것 같네요 ^^ (rollup 부분 잘못되어 수정했습니다.) WITH t AS ( SELECT 'A' item, 'A-1' div, 5 d1, 4 d2, 3 d3, 2 d4, 3 d5, 5 d6, 6 d7, 3 d8 FROM DUAL UNION ALL SELECT 'A', 'A-2', 2, 3, 5, 3, 4, 3, 5, 2 FROM DUAL ) SELECT item, DECODE(gid, 1, 'SUM', div) div, d1, DECODE(gid, 1, d1 + d2, d2) d2, DECODE(gid, 1, d1 + d2 + d3, d3) d3, DECODE(gid, 1, d1 + d2 + d3 + d4, d4) d4, DECODE(gid, 1, d1 + d2 + d3 + d4 + d5, d5) d5, DECODE(gid, 1, d1 + d2 + d3 + d4 + d5 + d6, d6) d6, DECODE(gid, 1, d1 + d2 + d3 + d4 + d5 + d6 + d7, d7) d7, DECODE(gid, 1, d1 + d2 + d3 + d4 + d5 + d6 + d7 + d8, d8) d8 FROM ( SELECT item, div, GROUPING_ID(div) gid, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d1, d1)), SUM(d1)) d1, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d2, d2)), SUM(d2)) d2, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d3, d3)), SUM(d3)) d3, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d4, d4)), SUM(d4)) d4, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d5, d5)), SUM(d5)) d5, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d6, d6)), SUM(d6)) d6, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d7, d7)), SUM(d7)) d7, DECODE(GROUPING_ID(div), 1, SUM(DECODE(div, 'A-2', -d8, d8)), SUM(d8)) d8 FROM t GROUP BY item, ROLLUP(div) ) ORDER BY item, div
WITH t(item, gb, d1, d2, d3, d4, d5, d6, d7, d8) AS ( SELECT 'A', 'A-1', 5, 4, 3, 2, 3, 5, 6, 3 FROM dual UNION ALL SELECT 'A', 'A-2', 2, 3, 5, 3, 4, 3, 5, 2 FROM dual ) SELECT item , NVL(gb, 'SUM') gb , DECODE(GROUPING(gb), 0, SUM(d1), SUM(v1)) d1 , DECODE(GROUPING(gb), 0, SUM(d2), SUM(v2)) d2 , DECODE(GROUPING(gb), 0, SUM(d3), SUM(v3)) d3 , DECODE(GROUPING(gb), 0, SUM(d4), SUM(v4)) d4 , DECODE(GROUPING(gb), 0, SUM(d5), SUM(v5)) d5 , DECODE(GROUPING(gb), 0, SUM(d6), SUM(v6)) d6 , DECODE(GROUPING(gb), 0, SUM(d7), SUM(v7)) d7 , DECODE(GROUPING(gb), 0, SUM(d8), SUM(v8)) d8 FROM (SELECT item, gb , d1, d2, d3, d4, d5, d6, d7, d8 , DECODE(gb, 'A-1', 1, -1) * (d1 ) AS v1 , DECODE(gb, 'A-1', 1, -1) * (d1+d2 ) AS v2 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3 ) AS v3 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3+d4 ) AS v4 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3+d4+d5 ) AS v5 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3+d4+d5+d6 ) AS v6 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3+d4+d5+d6+d7 ) AS v7 , DECODE(gb, 'A-1', 1, -1) * (d1+d2+d3+d4+d5+d6+d7+d8) AS v8 FROM t WHERE item = 'A' ) GROUP BY item, ROLLUP(gb) ;
WITH t(item, gb, d1, d2, d3, d4, d5, d6, d7, d8) AS ( SELECT 'A', 'A-1', 5, 4, 3, 2, 3, 5, 6, 3 FROM dual UNION ALL SELECT 'A', 'A-2', 2, 3, 5, 3, 4, 3, 5, 2 FROM dual ) SELECT * FROM (SELECT item, gb2 , "A-1", "A-2" , SUM("A-1" - "A-2") OVER(ORDER BY gb2) "SUM" FROM (SELECT * FROM t WHERE item = 'A') UNPIVOT (d FOR gb2 IN (d1, d2, d3, d4, d5, d6, d7, d8)) PIVOT (MIN(d) FOR gb IN ('A-1' "A-1", 'A-2' "A-2")) ) UNPIVOT (d FOR gb IN ("A-1", "A-2", "SUM")) PIVOT (MIN(d) FOR gb2 IN ( 'D1' d1, 'D2' d2, 'D3' d3, 'D4' d4 , 'D5' d5, 'D6' d6, 'D7' d7, 'D8' d8 ) ) ;
WITH t(item, gb, d1, d2, d3, d4, d5, d6, d7, d8) AS ( SELECT 'A', 'A-1', 5, 4, 3, 2, 3, 5, 6, 3 FROM dual UNION ALL SELECT 'A', 'A-2', 2, 3, 5, 3, 4, 3, 5, 2 FROM dual ) SELECT * FROM t MODEL PARTITION BY (item) DIMENSION BY (gb) MEASURES (d1, d2, d3, d4, d5, d6, d7, d8) RULES ( d1['SUM'] = d1['A-1'] - d1['A-2'] , d2['SUM'] = d2['A-1'] - d2['A-2'] + d1['SUM'] , d3['SUM'] = d3['A-1'] - d3['A-2'] + d2['SUM'] , d4['SUM'] = d4['A-1'] - d4['A-2'] + d3['SUM'] , d5['SUM'] = d5['A-1'] - d5['A-2'] + d4['SUM'] , d6['SUM'] = d6['A-1'] - d6['A-2'] + d5['SUM'] , d7['SUM'] = d7['A-1'] - d7['A-2'] + d6['SUM'] , d8['SUM'] = d8['A-1'] - d8['A-2'] + d7['SUM'] ) ;