[ORACLE] 시작종료 그룹핑

시작일 : P_FROM_DT
종료일 : P_TO_DT
INTERVAL : 1M, 2M, 1H,2H, 1D,2D, 1W,1M


WITH TBL_PARAMS AS
(
SELECT
    , SUBSTR(:P_FROM_DT,0,8)AS STD_DAY -- 시작일
    , TO_CHAR(TO_DATE( SUBSTR(:P_TO_DT, 0, 8) , 'YYYY-MM-DD')+1, 'YYYYMMDD') AS END_DAY -- 종료일
    , REGEXP_REPLACE(:P_INTERVAL, '[0-9]') AS INTERVAL_TY -- MI, H, D, W, M
    , (CASE WHEN SUBSTR(:P_INTERVAL, 0,2) = 'MI' THEN REGEXP_REPLACE(:P_INTERVAL, '[^0-9]') * 60
      ELSE TO_NUMBER(REGEXP_REPLACE(:P_INTERVAL, '[^0-9]'))
          END) AS INTERVAL_TIME -- 계산 시간
    FROM DUAL
)
, TBL_DATE_MIN_RANGE AS (
SELECT
   TO_DATE(TP.STD_DAY, 'YYYYMMDDHH24MISS') + (LEVEL)/24/60  AS NEW_DATE
    FROM TBL_PARAMS TP
    CONNECT BY LEVEL <= ROUND((TO_DATE(SUBSTR(TP.END_DAY, 0,12 ), 'YYYY-MM-DD HH24:MI') - TO_DATE(SUBSTR(TP.STD_DAY, 0,12 ), 'YYYY-MM-DD HH24:MI')) *24 *60, 2) +1  
)
, TBL_RAW_DATA AS (
SELECT TRUNC(TT.DATE_DTM, 'MI') AS DATE_DTM
      , TT.*
  FROM TB_TEMP TT
     , TBL_PARAMS TP
WHERE 1=1    
  AND TT.DATE_DTM >= TP.STD_DAY
  AND TT.DATE_DTM <= TP.END_DAY
)
, TBL_DATA AS (
SELECT TDMR.NEW_DATE
  , TRD.*
  FROM TBL_DATE_MIN_RANGE TDMR
, TBL_RAW_DATA TRD
WHERE 1=1    
  AND TDMR.NEW_DATE = TRD.DATE_DTM(+)
)
,TBL_DATA_GROUP AS (
    SELECT
CASE
WHEN TP.INTERVAL_TY = 'MI' THEN  -- 분 단위
TRUNC(TD.NEW_DATE) + FLOOR(EXTRACT(HOUR FROM CAST (TD.NEW_DATE AS TIMESTAMP)) * 60 + EXTRACT(MINUTE FROM CAST (TD.NEW_DATE AS TIMESTAMP))) * (TP.INTERVAL_TIME / (24 * 60 * 60))
WHEN TP.INTERVAL_TY = 'H' THEN   -- 시간 단위
TRUNC(TD.NEW_DATE) + FLOOR((EXTRACT(DAY FROM TD.NEW_DATE ) * 24 + EXTRACT(HOUR FROM CAST (TD.NEW_DATE AS TIMESTAMP) - TRUNC(TD.NEW_DATE))) / TP.INTERVAL_TIME ) * (TP.INTERVAL_TIME / 24)
WHEN TP.INTERVAL_TY = 'D' THEN   -- 일 단위
TRUNC(TD.NEW_DATE) - MOD(TO_NUMBER(TO_CHAR(TD.NEW_DATE, 'DDD')), TP.INTERVAL_TIME)
WHEN TP.INTERVAL_TY = 'W' THEN   -- 주 단위
TRUNC(TD.NEW_DATE, 'IW')
WHEN TP.INTERVAL_TY = 'M' THEN   -- 월 단위
TRUNC(TD.NEW_DATE, 'MM')
END AS INTERVAL_START
        , TD.TEST_TY
        , TD.SAMPLE1
    FROM
       TBL_DATA TD
     , TBL_PARAMS TP
)  
SELECT
      TO_CHAR(INTERVAL_START, 'YYYYMMDDHH24MISS') AS INTERVAL_START
, SAMPLE
FROM (
SELECT
INTERVAL_START
, AVG(CASE WHEN TEST_TY IN ('A', 'B') THEN SAMPLE1 ELSE NULL END) AS SAMPLE
  FROM TBL_DATA_GROUP
     GROUP BY INTERVAL_START
ORDER BY INTERVAL_START
)