안녕하세요
테이블에 이렇게 데이터가 들어가져 있습니다.
날짜 | 사용자 | 토탈 | 사용중 |
201802150002 | ABC | 10 | 2 |
201802150050 | ABC | 10 | 9 |
201802150200 | ABC | 10 | 7 |
201802150400 | ABC | 10 | 6 |
201802150517 | ABC | 10 | 0 |
201802150003 ABC 10 2
201802150006 ABC 10 2
201802150009 ABC 10 2
: : : :
201802150048 ABC 10 2
201802150051 ABC 10 9
: : : :
이거 쿼리로도 가능한지 알고 싶습니다. 혹시 아시는분 있으시면 힌트라도 주시면 감사하겠습니다.
WITH t AS ( SELECT '201802150002' dt, 'ABC' id, 10 tot, 2 use FROM dual UNION ALL SELECT '201802150050', 'ABC', 10, 9 FROM dual UNION ALL SELECT '201802150200', 'ABC', 10, 7 FROM dual UNION ALL SELECT '201802150400', 'ABC', 10, 6 FROM dual UNION ALL SELECT '201802150517', 'ABC', 10, 0 FROM dual ) SELECT id , dt , tot , use FROM (SELECT a.lv , b.id , a.dt , LAST_VALUE(b.tot) IGNORE NULLS OVER(PARTITION BY b.id ORDER BY a.dt) tot , LAST_VALUE(b.use) IGNORE NULLS OVER(PARTITION BY b.id ORDER BY a.dt) use FROM (-- 조회기간 분단위 생성 -- SELECT LEVEL lv , TO_CHAR(sdt + (LEVEL-1)/24/60, 'yyyymmddhh24mi') dt FROM (SELECT TO_DATE('20180215', 'yyyymmdd') sdt -- 시작일 , TO_DATE('20180215', 'yyyymmdd') edt -- 종료일 FROM dual ) CONNECT BY LEVEL <= (edt - sdt + 1) * 24*60 ) a LEFT OUTER JOIN t b PARTITION BY (b.id) ON a.dt = b.dt ) WHERE MOD(lv, 3) = 1 -- 3분 단위만 추출 ORDER BY id, dt ;