예를 들어 데이터가 아래와 같이 들어가 있습니다.
년월일시분초 count
20161031000000 10
20161031000001 8
20161031000002 3
20161031000003 8
20161031000004 11
20161031000008 3
20161031000009 15
이때 중간에 빠진 5~7초 를 각각 0으로 만들어서 0~9초를 만들고 싶습니다.
쿼리를 어떻게 만들어야 할까요?
INSERT INTO RECORD_TABLE (REC_TIME, CNT)
WITH t AS ( SELECT '20161031000000' dt, 10 cnt FROM dual UNION ALL SELECT '20161031000001', 8 FROM dual UNION ALL SELECT '20161031000002', 3 FROM dual UNION ALL SELECT '20161031000003', 8 FROM dual UNION ALL SELECT '20161031000004', 11 FROM dual UNION ALL SELECT '20161031000008', 3 FROM dual UNION ALL SELECT '20161031000009', 15 FROM dual ) SELECT a.dt , NVL(b.cnt, 0) cnt FROM (SELECT TO_CHAR(s + (LEVEL - 1)/24/60/60, 'yyyymmddhh24miss') dt FROM (SELECT TO_DATE('20161031000000', 'yyyymmddhh24miss') s , TO_DATE('20161031000009', 'yyyymmddhh24miss') e FROM dual) CONNECT BY LEVEL <= ROUND((e - s)*24*60*60) + 1 ) a , t b WHERE a.dt = b.dt(+) ORDER BY a.dt ;