꿈꾸는 개발자, DBA 커뮤니티 구루비

connect by 로 년도 생성 후 자기 자신값과 누계값을 구하고 싶습니다.

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]

또 다른 방법도 있군요.

첫번째 일러주신 문장보다 좀 어렵네요 ^^

감사합니다.

다음엔 두번째 방법도 써보겠습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변