下面是一个数据片段:
ID Type Start End
1 sleep 01/01/2020 07:00 01/01/2020 12:00
1 sleep 01/01/2020 12:00 01/01/2020 16:30
1 sleep 01/01/2020 16:30 01/01/2020 20:30
2 sleep 01/04/2019 08:00 01/04/2019 10:30
2 sleep 01/04/2019 08:00 01/04/2019 12:00
2 sleep 01/04/2019 12:00 01/04/2019 14:00
2 sleep 01/04/2019 14:00 01/04/2019 18:00
3 sleep 02/24/2015 14:45 02/25/2015 07:05
3 sleep 02/25/2015 07:05 02/25/2015 14:05
我想应用以下规则:
ID Type Start End Rule
1 sleep 01/01/2020 07:00 01/01/2020 12:00 ok, no change
1 sleep 01/01/2020 12:00 01/01/2020 16:30 ok, no change
1 sleep 01/01/2020 16:30 01/01/2020 20:30 ok, no change
2 sleep 01/04/2019 08:00 01/04/2019 10:30 Ignore
2 sleep 01/04/2019 08:00 01/04/2019 12:00 select
2 sleep 01/04/2019 12:00 01/04/2019 14:00 ok, no change
2 sleep 01/04/2019 14:00 01/04/2019 18:00 ok, no change
3 sleep 02/24/2015 14:45 02/25/2015 07:05 split
3 sleep 02/25/2015 07:05 02/25/2015 14:05 ok, no change
最终的表应该是这样的:
ID Type Start End
1 sleep 01/01/2020 07:00 01/01/2020 12:00
1 sleep 01/01/2020 12:00 01/01/2020 16:30
1 sleep 01/01/2020 16:30 01/01/2020 20:30
2 sleep 01/04/2019 08:00 01/04/2019 12:00
2 sleep 01/04/2019 12:00 01/04/2019 14:00
2 sleep 01/04/2019 14:00 01/04/2019 18:00
3 sleep 02/24/2015 14:45 02/25/2015 07:05
3 sleep 02/24/2015 14:45 02/24/2015 23:59
3 sleep 02/24/2015 23:59 02/25/2015 07:05
3 sleep 02/25/2015 07:05 02/25/2015 14:05
我遇到了心理障碍，需要帮助才能进入决赛。
与您的示例数据:
WITH
tbl (ID, A_TYPE, STARTS, ENDS) AS
( SELECT 1, 'sleep', To_Date('2020-01-01 07:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 1, 'sleep', To_Date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2020-01-01 16:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 1, 'sleep', To_Date('2020-01-01 16:30:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2020-01-01 20:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 2, 'sleep', To_Date('2019-01-04 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2019-01-04 10:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 2, 'sleep', To_Date('2019-01-04 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2019-01-04 12:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 2, 'sleep', To_Date('2019-01-04 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2019-01-04 14:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 2, 'sleep', To_Date('2019-01-04 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2019-01-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 3, 'sleep', To_Date('2015-02-24 14:45:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2015-02-25 07:05:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
Select 3, 'sleep', To_Date('2015-02-25 07:05:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2015-02-25 14:05:00', 'yyyy-mm-dd hh24:mi:ss') From Dual
),
…创建一个状态为.....
行的cte(网格)grid AS
( Select ID, A_TYPE, STARTS, ENDS,
CASE WHEN TRUNC(STARTS) < TRUNC(ENDS) THEN 'SPLIT'
WHEN ENDS > LEAD(STARTS) OVER(Partition By ID Order By STARTS) THEN 'OVERLAP_0'
WHEN STARTS < LAG(ENDS) OVER(Partition By ID Order By STARTS) THEN 'OVERLAP_1'
WHEN ENDS = LEAD(STARTS) OVER(Partition By ID Order By STARTS) THEN 'OK_0'
WHEN STARTS = LAG(ENDS) OVER(Partition By ID Order By STARTS) THEN 'OK_1'
ELSE 'XXX' END "STATUS"
From tbl
Order By ID, STARTS, ENDS
)
…
SELECT ID, A_TYPE,
STARTS, To_Char(STARTS, 'hh24:mi:ss') "TIME_START",
CASE WHEN STATUS = 'SPLIT' THEN TRUNC(STARTS) + INTERVAL '23:59:59' HOUR TO SECOND ELSE ENDS END "ENDS",
To_Char(CASE WHEN STATUS = 'SPLIT' THEN TRUNC(STARTS) + INTERVAL '23:59:59' HOUR TO SECOND ELSE ENDS END, 'hh24:mi:ss') "TIME_END"
FROM grid
WHERE STATUS != 'OVERLAP_0'
UNION ALL
SELECT ID, A_TYPE,
CASE WHEN STATUS = 'SPLIT' THEN TRUNC(STARTS + 1) ELSE STARTS END,
To_Char(TRUNC(STARTS + 1), 'hh24:mi:ss'),
TRUNC(STARTS + 1), To_Char(ENDS, 'hh24:mi:ss')
FROM grid
WHERE STATUS = 'SPLIT'
ORDER BY ID, STARTS
…
ID A_TYPE STARTS TIME_START ENDS TIME_END
---------- ------ --------- ---------- --------- --------
1 sleep 01-JAN-20 07:00:00 01-JAN-20 12:00:00
1 sleep 01-JAN-20 12:00:00 01-JAN-20 16:30:00
1 sleep 01-JAN-20 16:30:00 01-JAN-20 20:30:00
2 sleep 04-JAN-19 08:00:00 04-JAN-19 12:00:00
2 sleep 04-JAN-19 12:00:00 04-JAN-19 14:00:00
2 sleep 04-JAN-19 14:00:00 04-JAN-19 18:00:00
3 sleep 24-FEB-15 14:45:00 24-FEB-15 23:59:59
3 sleep 25-FEB-15 00:00:00 25-FEB-15 07:05:00
3 sleep 25-FEB-15 07:05:00 25-FEB-15 14:05:00
注意:如果你有超过两个连续的日期开始直到结束，你应该调整代码来覆盖它。在这种情况下，这段代码不会产生预期的结果。
您可以使用LEAD分析函数过滤掉重叠的行，然后，为了分割日期边界上的日期范围，您可以使用递归查询:
WITH split_days (id, type, start_dt, day_end, end_dt) AS (
SELECT id,
type,
start_dt,
LEAST(TRUNC(start_dt) + INTERVAL '23:59:59' HOUR TO SECOND, end_dt),
end_dt
FROM (
SELECT t.*,
LEAD(start_dt)
OVER (PARTITION BY id ORDER BY start_dt, end_dt) AS next_start_dt,
LEAD(end_dt)
OVER (PARTITION BY id ORDER BY start_dt, end_dt) AS next_end_dt
FROM table_name t
)
WHERE next_start_dt IS NULL
OR next_end_dt IS NULL
OR end_dt <= next_start_dt
UNION ALL
SELECT id,
type,
day_end + INTERVAL '1' SECOND,
LEAST(day_end + INTERVAL '1' DAY, end_dt),
end_dt
FROM split_days
WHERE day_end < end_dt
)
SEARCH DEPTH FIRST BY id, start_dt SET order_id
SELECT id,
type,
start_dt,
day_end AS end_dt
FROM split_days;
对于样本数据:
CREATE TABLE table_name (ID, Type, Start_dt, End_dt) AS
SELECT 1, 'sleep', DATE '2020-01-01' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2020-01-01' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'sleep', DATE '2020-01-01' + INTERVAL '12:00:00' HOUR TO SECOND, DATE '2020-01-01' + INTERVAL '16:30:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'sleep', DATE '2020-01-01' + INTERVAL '16:30:00' HOUR TO SECOND, DATE '2020-01-01' + INTERVAL '20:30:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'sleep', DATE '2019-01-04' + INTERVAL '08:00:00' HOUR TO SECOND, DATE '2019-01-04' + INTERVAL '10:30:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'sleep', DATE '2019-01-04' + INTERVAL '08:00:00' HOUR TO SECOND, DATE '2019-01-04' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'sleep', DATE '2019-01-04' + INTERVAL '12:00:00' HOUR TO SECOND, DATE '2019-01-04' + INTERVAL '14:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'sleep', DATE '2019-01-04' + INTERVAL '14:00:00' HOUR TO SECOND, DATE '2019-01-04' + INTERVAL '18:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'sleep', DATE '2015-02-24' + INTERVAL '14:45:00' HOUR TO SECOND, DATE '2015-02-25' + INTERVAL '07:05:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'sleep', DATE '2015-02-25' + INTERVAL '07:05:00' HOUR TO SECOND, DATE '2015-02-25' + INTERVAL '14:05:00' HOUR TO SECOND FROM DUAL;
输出:
END_DT2020-01-01 12:00:00 2015-02-25 07:05:00 2015-02-25 07:05:002015-02-25 14:05:00