데이터베이스 질문과 답변

날짜기간을 시간개수로 쪼갤수있을까요? 1 43

by 김용한 [SQL Query] [2017.11.23 16:36:54]

원본쿼리가

with t ( st, cnt, start_date, end_date) as
(
select 12,    2,    '20171120122000',    '20171120132000' from dual union all
select 15,    1,    '20171120151000',    '20171120152000' from dual
)
select * from t
;

이구요 결과는 아래와같이나와야하는데요 어렵네요 ㅠㅠ

LV ST DIFF_TIME START_TIME END_TIME
1 12 1 20171120122000 20171120125959
2 13 1 20171120130000 20171120132000
3 15 1 20171120151000 20171120152000

LV 은 LEVEL 이구

ST는 20171120122000 이면 12시 를 말하는거구요

원본데이터가 만약에 20171120122000 ~ 20171120132000 이면

START_TIME( 20171120122000 ) ~ END_TIME( 20171120125959 )  로 강제 짤라서 레코드개수를 늘려주는겁니다( 시간단위로 )

by 마농 [2017.11.23 20:53:08] 
WITH t(id, start_date, end_date) AS
(
SELECT 1, '20171120122000', '20171120132000' FROM dual UNION ALL
SELECT 2, '20171120151000', '20171120152000' FROM dual
)
SELECT id
     , start_date, end_date
     , lv
     , TO_CHAR(GREATEST(sdt, TRUNC(sdt, 'hh') + (lv - 1) / 24)    , 'yyyymmddhh24miss') sdt
     , TO_CHAR(LEAST(edt, TRUNC(sdt, 'hh') + lv / 24 - 1/24/60/60), 'yyyymmddhh24miss') edt
  FROM (SELECT id, start_date, end_date
             , TO_DATE(start_date, 'yyyymmddhh24miss') sdt
             , TO_DATE(  end_date, 'yyyymmddhh24miss') edt
          FROM t
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= (TRUNC(edt, 'hh') - TRUNC(sdt, 'hh')) * 24 + 1
 ORDER BY id, lv
;

 

by 김용한 [2017.11.24 10:57:32]

많은 도움돼었습니다 감사합니다

이해를위해서 제가 답변단겁니다..

1. GREATEST ( t.sdt, T_SDT ) => SDT

2. LEAST ( t.edt, T_EDT ) = > EDT

WITH t(id, start_date, end_date) AS
(
SELECT 1, '20171120122000', '20171120132000' FROM dual UNION ALL
SELECT 2, '20171120151000', '20171120152000' FROM dual
)
SELECT id
     , start_date, end_date
     , lv
     , TO_CHAR(TRUNC(GREATEST(sdt, TRUNC(sdt, 'hh') + (lv - 1) / 24), 'hh'),'HH24') qhour
     , TO_CHAR(GREATEST(sdt, TRUNC(sdt, 'hh') + (lv - 1) / 24)    , 'yyyymmddhh24miss') sdt
     , TO_CHAR(LEAST(edt, TRUNC(sdt, 'hh') + lv / 24 - 1/24/60/60), 'yyyymmddhh24miss') edt
     , TRUNC(sdt, 'hh') + (lv - 1) / 24 t_sdt
     , TRUNC(sdt, 'hh') + lv / 24 - 1/24/60/60 t_edt
  FROM (SELECT id, start_date, end_date
             , TO_DATE(start_date, 'yyyymmddhh24miss') sdt
             , TO_DATE(  end_date, 'yyyymmddhh24miss') edt
          FROM t
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)

 WHERE lv <= (TRUNC(edt, 'hh') - TRUNC(sdt, 'hh')) * 24 + 1
 ORDER BY id, lv
;