날짜가 연속되면서 항목이 같으면 Sum하고 아니면 그냥 표시.. 0 4 883

by 동동동 [SQL Query] [2020.03.25 17:24:28]


이미지.PNG (71,362Bytes)

안녕하세요..

첨부한 이미지 처럼 날짜가 연속되면서 항목이 같으면 Sum하고 아니면 그냥 표시..

자료를 나오게 하려고 쿼리를 작성했는데요..

24일과 25일은 날짜가 연속되면서 CD와 QTY가 서로 같기 때문에 DY는 Sum하고 Prsc_Dy는 시작일자인 24일로 표시하고..

23일은 24, 25일과 날짜는 연속되지만 Qty가 다른것이 있어서 그냥 표시, 27일은 날짜가 연속되지 않기 때문에 그냥 표시..

 

더 좋은 방법이나 다른 방법이 있을지 궁금하여 글을 올립니다..

 

많은 관심과 지도 부탁드립니다..

 

WITH TMP AS (
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_11' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_12' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_13' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_14' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_15' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_16' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200323' PRSC_DY, '20200217I00015_20200323_17' CD, '1' QTY, '1' DY FROM DUAL UNION ALL

SELECT '20200324' PRSC_DY, '20200217I00015_20200323_11' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_12' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_13' CD, '3' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_14' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_15' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_16' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200324' PRSC_DY, '20200217I00015_20200323_17' CD, '1' QTY, '1' DY FROM DUAL UNION ALL

SELECT '20200325' PRSC_DY, '20200217I00015_20200323_11' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_12' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_13' CD, '3' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_14' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_15' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_16' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200325' PRSC_DY, '20200217I00015_20200323_17' CD, '1' QTY, '1' DY FROM DUAL UNION ALL

SELECT '20200327' PRSC_DY, '20200217I00015_20200323_11' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_12' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_13' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_14' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_15' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_16' CD, '1' QTY, '1' DY FROM DUAL UNION ALL
SELECT '20200327' PRSC_DY, '20200217I00015_20200323_17' CD, '1' QTY, '1' DY FROM DUAL 
)
SELECT MIN(PRSC_DY) PRSC_DY
     , CD
     , QTY
     , SUM(DY) DY
 FROM ( 
SELECT PRSC_DY
     , CD
     , QTY
     , DY
     , MIN(CTN_YN) OVER (PARTITION BY PRSC_DY) CTN_YN
     , MIN(QTY_YN) OVER (PARTITION BY PRSC_DY) QTY_YN
 FROM (
SELECT A.*
     , CASE WHEN (LAG(PRSC_DY, 1, PRSC_DY) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = PRSC_DY OR
                 (LAG(PRSC_DY, 1, PRSC_DY) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = TO_CHAR(TO_DATE(PRSC_DY, 'YYYYMMDD') -1, 'YYYYMMDD') THEN
                 1  -- 연속
            ELSE
                 0  -- 비연속
        END CTN_YN  -- 날짜 연속 여부
     , CASE WHEN (LEAD(PRSC_DY, 1, PRSC_DY) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = PRSC_DY OR
                 (LEAD(PRSC_DY, 1, PRSC_DY) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = TO_CHAR(TO_DATE(PRSC_DY, 'YYYYMMDD') +1, 'YYYYMMDD') THEN
                 
                 CASE WHEN (LEAD(CD, 1, CD) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = CD AND
                           (LEAD(QTY, 1, QTY) OVER (PARTITION BY CD ORDER BY CD, PRSC_DY)) = QTY THEN
                           1
                      ELSE
                           0 
                  END 
            ELSE
                 1
        END QTY_YN  -- QTY가 동일한지 여부 
  FROM TMP A
  )
)
GROUP BY CD, QTY, CTN_YN, QTY_YN
ORDER BY PRSC_DY, CD
 

 

by 마농 [2020.03.25 21:23:09]
WITH tmp AS
(
SELECT '20200323'   prsc_dy, '20200217I00015_20200323_11' cd, 1 qty, 1 dy FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_12', 1, 1 FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_13', 1, 1 FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_14', 1, 1 FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_15', 1, 1 FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_16', 1, 1 FROM dual
UNION ALL SELECT '20200323', '20200217I00015_20200323_17', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_11', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_12', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_13', 3, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_14', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_15', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_16', 1, 1 FROM dual
UNION ALL SELECT '20200324', '20200217I00015_20200323_17', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_11', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_12', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_13', 3, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_14', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_15', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_16', 1, 1 FROM dual
UNION ALL SELECT '20200325', '20200217I00015_20200323_17', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_11', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_12', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_13', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_14', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_15', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_16', 1, 1 FROM dual
UNION ALL SELECT '20200327', '20200217I00015_20200323_17', 1, 1 FROM dual
)
SELECT MIN(prsc_dy) prsc_dy
     , cd
     , qty
     , SUM(dy) dy
  FROM (SELECT prsc_dy, cd, qty, dy
             , x
             , TO_DATE(prsc_dy, 'yyyymmdd')
             - DENSE_RANK() OVER(PARTITION BY x ORDER BY prsc_dy) grp
          FROM (SELECT prsc_dy, cd, qty, dy
                     , LISTAGG(cd||'('||qty||')') WITHIN GROUP(ORDER BY cd) OVER(PARTITION BY prsc_dy) x
                  FROM tmp
                )
        )
 GROUP BY cd, qty, x, grp
 ORDER BY prsc_dy, cd
;

 


by 동동동 [2020.03.26 09:39:19]

마농님 답글 감사합니다..또 하나 배웠네요..^^


by 동동동 [2020.03.26 10:48:36]

마농님 그런데...LISTAGG 에서 4000자가 넘어가면 문제가 발생하지 않나요??


by 마농 [2020.03.26 10:59:12]

네. 4000 넘으면 오류 나죠.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입