데이터베이스 질문과 답변
connect by 로 년도 생성 후 자기 자신값과 누계값을 구하고 싶습니다.
0
6
2,175
by 짱구 [SQL Query] 년도생성 connect by 누계 [2017.01.23 11:51:27]
TABLE NAME : TA
PRIMARY KEY : id, id2, div, team, year
-----------------------------------------------
id id2 div tem year val val2
a a a a 2014 10 20
a a a a 2014 20 20
a a b b 2014 20 20
a b a a 2014 10 20
a b a a 2014 20 20
a b b b 2014 20 20
b a a a 2014 10 20
b a a a 2014 20 20
b a b b 2014 20 20
b b a a 2014 10 20
b b a a 2014 20 20
b b b b 2014 20 20
a a a a 2015 20 40
a a a a 2015 40 40
a a b b 2015 40 40
a b a a 2015 20 40
a b a a 2015 40 40
a b b b 2015 40 40
b a a a 2015 20 40
b a a a 2015 40 40
b a b b 2015 40 40
b b a a 2015 20 40
b b a a 2015 40 40
b b b b 2015 40 40
a a a a 2016 15 30
a a a a 2016 20 30
a a b b 2016 20 30
a b a a 2016 15 30
a b a a 2016 30 30
a b b b 2016 30 30
b a a a 2016 15 30
b a a a 2016 30 30
b a b b 2016 30 30
b b a a 2016 15 30
b b a a 2016 30 30
b b b b 2016 30 30
------------------------------------------------
SELECT TT.id, TT.id2, TT.year
, SUM(TT.cu.val) as cu_val
, MAX(TT.acc_val) as acc_val
FROM (SELECT TA.id
, TA.val+TA.val2 as cu_val
, SUM(TA.val+TA.val2) OVER(PARTITION BY TA.id2 ORDER BY TO_DATE(TB.year, 'yyyy) as acc_val
FROM TA
, (SELECT LEVEL as year
FROM DUAL
WHERE LEVEL >= 2016
CONNECT BY LEVEL <= 2030) TB) TT
GROUP BY TT.id, TT.id2, TT.year
하면
-----------------------------------------
id id2 year cu_val acc_val
a a 2014 110 110
a a 2015 220 330
a a 2016 145 475
a a 2017 0 475
a a 2018 0 475
a a 2019 0 475
....2030년까지
a b 2014 110 110 ....
이렇게 될줄 알았는데 이상한 값이 나오네요
-----------------------------------------
id id2 year cu_val acc_val
a a 2014 110 110
a a 2015 220 330
a a 2016 145 475
a a 2017 0 475
a a 2018 0 475
a a 2019 0 475
....2030년까지
a b 2014 110 110
a b 2015 220 330
......
이와 같은 값이 나오게 하려면 어떻게 하나요?
부탁드립니다.
감사합니다.
by 랑에1
[2017.01.23 12:43:40]
SELECT id, id2, year, cu_val, SUM(cu_val) OVER(PARTITION BY id, id2 ORDER BY year) AS acc_val FROM ( SELECT id, id2, year, SUM(val + val2) AS cu_val FROM TA GROUP BY id, id2, year ) TT는 왜넣으신 거예요?
by 짱구
[2017.01.23 13:22:25]
감사합니다.
근데 제가 구하고 싶은건 TA테이블에 있는 데이터 외로 없는 년도도 생성해서 누계를 구하고 싶습니다.
by 마농
[2017.01.23 13:25:03]
WITH ta AS ( SELECT 'a' id, 'a' id2, 'a' div, 'a' tem, '2014' year, 10 val, 20 val2 FROM dual UNION ALL SELECT 'a', 'a', 'a', 'a', '2014', 20, 20 FROM dual UNION ALL SELECT 'a', 'a', 'b', 'b', '2014', 20, 20 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2014', 10, 20 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2014', 20, 20 FROM dual UNION ALL SELECT 'a', 'b', 'b', 'b', '2014', 20, 20 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2014', 10, 20 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2014', 20, 20 FROM dual UNION ALL SELECT 'b', 'a', 'b', 'b', '2014', 20, 20 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2014', 10, 20 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2014', 20, 20 FROM dual UNION ALL SELECT 'b', 'b', 'b', 'b', '2014', 20, 20 FROM dual UNION ALL SELECT 'a', 'a', 'a', 'a', '2015', 20, 40 FROM dual UNION ALL SELECT 'a', 'a', 'a', 'a', '2015', 40, 40 FROM dual UNION ALL SELECT 'a', 'a', 'b', 'b', '2015', 40, 40 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2015', 20, 40 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2015', 40, 40 FROM dual UNION ALL SELECT 'a', 'b', 'b', 'b', '2015', 40, 40 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2015', 20, 40 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2015', 40, 40 FROM dual UNION ALL SELECT 'b', 'a', 'b', 'b', '2015', 40, 40 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2015', 20, 40 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2015', 40, 40 FROM dual UNION ALL SELECT 'b', 'b', 'b', 'b', '2015', 40, 40 FROM dual UNION ALL SELECT 'a', 'a', 'a', 'a', '2016', 15, 30 FROM dual UNION ALL SELECT 'a', 'a', 'a', 'a', '2016', 20, 30 FROM dual UNION ALL SELECT 'a', 'a', 'b', 'b', '2016', 20, 30 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2016', 15, 30 FROM dual UNION ALL SELECT 'a', 'b', 'a', 'a', '2016', 30, 30 FROM dual UNION ALL SELECT 'a', 'b', 'b', 'b', '2016', 30, 30 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2016', 15, 30 FROM dual UNION ALL SELECT 'b', 'a', 'a', 'a', '2016', 30, 30 FROM dual UNION ALL SELECT 'b', 'a', 'b', 'b', '2016', 30, 30 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2016', 15, 30 FROM dual UNION ALL SELECT 'b', 'b', 'a', 'a', '2016', 30, 30 FROM dual UNION ALL SELECT 'b', 'b', 'b', 'b', '2016', 30, 30 FROM dual ) SELECT b.id, b.id2 , a.year , NVL(b.cu_val, 0) cu_val , SUM(b.cu_val) OVER(PARTITION BY b.id, b.id2 ORDER BY a.year) acc_val FROM (SELECT TO_CHAR(2014 + LEVEL - 1) year FROM dual CONNECT BY LEVEL <= 2030 - 2014 + 1 ) a LEFT OUTER JOIN (SELECT id, id2, year , SUM(val + val2) cu_val FROM ta GROUP BY id, id2, year ORDER BY id, id2, year ) b PARTITION BY (b.id, b.id2) ON (a.year = b.year) ;
by 짱구
[2017.01.23 13:34:54]
정말 감사합니다.
LEFT OUTER JOIN에 PARTITION BY 를 쓸수 있군요.
항상 많은걸 배워갑니다.
by 마농
[2017.01.23 15:02:18]
SELECT * FROM (SELECT id, id2, year , SUM(val + val2) cu_val FROM ta GROUP BY id, id2, year ) b MODEL PARTITION BY (id, id2) DIMENSION BY (TO_NUMBER(year) year) MEASURES (cu_val, 0 acc_val) IGNORE NAV RULES AUTOMATIC ORDER ( cu_val[FOR year FROM 2014 TO 2030 INCREMENT 1] = cu_val[CV()] , acc_val[ANY] = acc_val[cv()-1] + cu_val[CV()] ) ;
by 짱구
[2017.01.23 17:23:30]
또 다른 방법도 있군요.
첫번째 일러주신 문장보다 좀 어렵네요 ^^
감사합니다.
다음엔 두번째 방법도 써보겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.