1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| WITH t AS (
SELECT DATE '2000-01-01' d, 12 h FROM dual UNION ALL
SELECT DATE '2000-01-01' d, 12 FROM dual UNION ALL
SELECT DATE '2000-01-02' d, 12 FROM dual UNION ALL
SELECT DATE '2000-01-03' d, 22 FROM dual UNION ALL
SELECT DATE '2000-01-03' d, 22 FROM dual UNION ALL
SELECT DATE '2000-01-03' d, 22 FROM dual UNION ALL
SELECT DATE '2000-01-04' d, 12 FROM dual UNION ALL
SELECT DATE '2000-01-04' d, 12 FROM dual UNION ALL
SELECT DATE '2000-01-04' d, 13 FROM dual)
SELECT d, h, TRUNC(d + h2 / 24) AS d3, TO_NUMBER(TO_CHAR(d + h2/24, 'HH24')) AS h3
FROM
(
SELECT d, h,
NVL(lag(h, 1) over (PARTITION BY d, h ORDER BY d, h)
+ ROW_NUMBER() over ( PARTITION BY d, h ORDER BY d, h) - 1,
h) AS h2
FROM t
ORDER BY d,h
) |
Partager