데이터베이스 질문과 답변

기간별 날짜별 합계를 구하는 쿼리 도움 요청 드립니다. 0 4 998

by 너구리~ [2016.11.11 13:50:48] 

-- 원본 데이터
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~~

by 개발뉴비 [2016.11.11 16:27:41] 
-- 해놓고 보니 너무 지저분하네요...
-- 다음분이 더 좋은 쿼리를 보여주실겁니다.

-- 원본 데이터
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
by 마농 [2016.11.11 17:43:43] 
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
;

 

by 마농 [2016.11.14 09:31:04] 
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
;

 

by 능글맞은 너구리 [2016.11.14 12:21:28]

감사합니다.

참고해서, 잘 사용하겠습니다. ^^

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