데이터베이스 질문과 답변
select '20180101' as dt, 0 as t from dual
union all
select '20180105' as dt, 5 as t from dual
union all
select '20180106' as dt, 6 as t from dual
union all
select '20180108' as dt, 8 as t from dual
union all
select '20180110' as dt, 10 as t from dual
;
| dt | t |
| 20180101 | 0 |
| 20180102 | 5 |
| 20180103 | 5 |
| 20180104 | 5 |
| 20180105 | 5 |
| 20180106 | 6 |
| 20180107 | 8 |
| 20180108 | 8 |
| 20180109 | 10 |
| 20180110 | 10 |
위처럼 데이터 에서 위와 같은 결과를 얻고 싶습니다.
날짜인데~ 중간에 빠져있는 날짜가 있는데... 빠진 날짜도 포함되서 결과값으로 보이고,
그 날짜의 값이 있는 전날의 dt 데이터를 가져올수 있으면 될것 같습니다.
어떻게 쿼리를 구성하면 좋을까요/?
by 우리집아찌
[2018.03.31 10:37:58]
WITH T AS (
select '20180101' as dt, 0 as t from dual
union all
select '20180105' as dt, 5 as t from dual
union all
select '20180106' as dt, 6 as t from dual
union all
select '20180108' as dt, 8 as t from dual
union all
select '20180110' as dt, 10 as t from dual
), T2 AS (
SELECT MIN_DT - 1 + LEVEL AS DT
FROM ( SELECT MIN(TO_DATE(DT,'YYYYMMDD')) MIN_DT
, MAX(TO_DATE(DT,'YYYYMMDD')) MAX_DT
FROM T )
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1
)
SELECT B.DT
, T
, LAST_VALUE(A.T IGNORE NULLS) OVER(ORDER BY B.DT DESC) T
FROM T A
, T2 B
WHERE A.DT(+) = B.DT
ORDER BY B.DT
by 마농
[2018.04.02 09:11:01]
WITH t AS
(
SELECT '20180101' dt, 0 t FROM dual
UNION ALL SELECT '20180105', 5 FROM dual
UNION ALL SELECT '20180106', 6 FROM dual
UNION ALL SELECT '20180108', 8 FROM dual
UNION ALL SELECT '20180110', 10 FROM dual
)
SELECT TO_CHAR(dt - lv + 1, 'yyyymmdd') dt
, t
FROM (SELECT dt, t
, NVL(dt - LAG(dt) OVER(ORDER BY dt), 1) cnt
FROM (SELECT TO_DATE(dt, 'yyyymmdd') dt, t FROM t)
)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 9)
WHERE lv <= cnt
ORDER BY dt
;
) 버튼을 클릭하여 작성 하시면 됩니다.