안녕하세요..
첨부한 이미지 처럼 날짜가 연속되면서 항목이 같으면 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
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 ;