-- 원본 데이터 WITH TBL_TEMP AS ( SELECT 7 AS SEQ, 15618603 AS CONTENT_ID, '07512' AS CONTENT_CD, '20160616' AS START_DT, '20160630' AS END_DT, '201606' AS APPLY_DT, 45 AS RATE FROM DUAL UNION ALL SELECT 6, 15618603, '07512', '20160601', '20160615', '201606', 30 FROM DUAL UNION ALL SELECT 5, 15618603, '19101', '20160611', '20160630', '201606', 60 FROM DUAL UNION ALL SELECT 4, 15618603, '19101', '20160601', '20160610', '201606', 40 FROM DUAL UNION ALL SELECT 3, 15618604, '07512', '20160601', '20160630', '201606', 50 FROM DUAL UNION ALL SELECT 2, 15618604, '19101', '20160611', '20160630', '201606', 50 FROM DUAL UNION ALL SELECT 1, 15618604, '19101', '20160601', '20160610', '201606', 40 FROM DUAL ) SELECT * FROM TBL_TEMP; -- 결과 데이터 (SUM이 100이 넘는 CONTENT_ID) -- (20160616~20160630 기간에는 합이 105가 됨으로 100이 넘는 일수는 15일) WITH TBL_TEMP AS ( SELECT 15618603 AS CONTENT_ID, 15 AS DAYS FROM DUAL ) SELECT * FROM TBL_TEMP;
---------------------------------------------------------------------------------------------------------------------
조건1 : 해당월의 1일부터 말일까지 CONTENT별 RATE의 합이 100을 넘는 일자가 있는지 확인
조건2 : 기간이 달을 넘을 경우 범위는 START_DT기준으로 해당월의 말일까지 임.
조건3 : APPLY_DT는 START_DT의 YYYYMM 임.
조건4 : START_DT와 END_DT 기간이 모두 연속되지 않고, 중간이 빠지는 날도 존재(0으로 계산)
(예, 20160601~20160610, 20160615~20160630)
---------------------------------------------------------------------------------------------------------------------
기간에 1일 부터 말일 까지 CONTENT별 RATE의 합이 100을 넘는 일수가 1일이라도 있는 데이터의 CONTENT_ID와 DAYS(일수)를 추출하는 쿼리가 목적입니다.
고수님들의 많은 도움 기다립니다. please~~
-- 해놓고 보니 너무 지저분하네요... -- 다음분이 더 좋은 쿼리를 보여주실겁니다. -- 원본 데이터 WITH TBL_TEMP AS ( SELECT 7 AS SEQ, 15618603 AS CONTENT_ID, '07512' AS CONTENT_CD, '20160616' AS START_DT, '20160630' AS END_DT, '201606' AS APPLY_DT, 45 AS RATE FROM DUAL UNION ALL SELECT 6, 15618603, '07512', '20160601', '20160615', '201606', 30 FROM DUAL UNION ALL SELECT 5, 15618603, '19101', '20160611', '20160630', '201606', 60 FROM DUAL UNION ALL SELECT 4, 15618603, '19101', '20160601', '20160610', '201606', 40 FROM DUAL UNION ALL SELECT 3, 15618604, '07512', '20160601', '20160630', '201606', 50 FROM DUAL UNION ALL SELECT 2, 15618604, '19101', '20160610', '20160630', '201606', 50 FROM DUAL UNION ALL SELECT 1, 15618604, '19101', '20160601', '20160610', '201606', 40 FROM DUAL UNION ALL SELECT 8, 15618605, '19101', '20160711', '20160730', '201607', 50 FROM DUAL UNION ALL SELECT 9, 15618605, '19101', '20160701', '20160716', '201607', 60 FROM DUAL ) SELECT CONTENT_ID , COUNT(DDAY) DAYS FROM ( SELECT CONTENT_ID , DDAY , SUM(RATE) RATE FROM TBL_TEMP T1 , ( SELECT TO_CHAR(FDAY + LEVEL - 1, 'YYYYMMDD') DDAY FROM ( SELECT MIN(TO_DATE(APPLY_DT||01, 'YYYYMMDD')) FDAY , MAX(LAST_DAY(TO_DATE(APPLY_DT, 'YYYYMM'))) LDAY FROM TBL_TEMP ) CONNECT BY LEVEL <= LDAY - FDAY + 1 ) T2 WHERE T2.DDAY BETWEEN T1.START_DT AND END_DT GROUP BY CONTENT_ID, DDAY ) WHERE RATE >= 100 GROUP BY CONTENT_ID
WITH tbl_temp AS ( SELECT 7 seq, 15618603 content_id, '07512' content_cd , '20160616' start_dt, '20160630' end_dt, '201606' apply_dt, 45 rate FROM dual UNION ALL SELECT 6, 15618603, '07512', '20160601', '20160615', '201606', 30 FROM dual UNION ALL SELECT 5, 15618603, '19101', '20160611', '20160630', '201606', 60 FROM dual UNION ALL SELECT 4, 15618603, '19101', '20160601', '20160610', '201606', 40 FROM dual UNION ALL SELECT 3, 15618604, '07512', '20160601', '20160630', '201606', 50 FROM dual UNION ALL SELECT 2, 15618604, '19101', '20160611', '20160630', '201606', 50 FROM dual UNION ALL SELECT 1, 15618604, '19101', '20160601', '20160610', '201606', 40 FROM dual ) SELECT content_id , SUM(edt - sdt + 1) cnt FROM (SELECT content_id , dt sdt , LEAD(dt) OVER(PARTITION BY content_id ORDER BY dt) - 1 edt , SUM(SUM(rate)) OVER(PARTITION BY content_id ORDER BY dt) rate FROM (SELECT content_id , DECODE(s, 1, sdt, LEAST(edt, LAST_DAY(sdt)) + 1) dt , rate * s rate FROM (SELECT content_id , TO_DATE(start_dt, 'yyyymmdd') sdt , TO_DATE( end_dt, 'yyyymmdd') edt , rate FROM tbl_temp WHERE apply_dt = '201606' ) , (SELECT DECODE(LEVEL, 1, 1, -1) s FROM dual CONNECT BY LEVEL <= 2 ) ) GROUP BY content_id, dt ) WHERE rate > 100 GROUP BY content_id ;
SELECT content_id , COUNT(*) cnt FROM (SELECT content_id FROM (SELECT content_id , TO_DATE(start_dt, 'yyyymmdd') sdt , TO_DATE( end_dt, 'yyyymmdd') edt , rate FROM tbl_temp WHERE apply_dt = '201606' ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999) WHERE lv <= edt - sdt + 1 GROUP BY content_id, sdt + lv - 1 HAVING SUM(rate) > 100 ) GROUP BY content_id ;