데이터베이스 질문과 답변
원본쿼리가
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.