主页 / 问题 / 1600678
Accepted
Lobster25
Asked:2024-11-24 03:14:06 +0000 UTC2024-11-24 03:14:06 +0000 UTC

消除重叠时间段sql

  • 772

有 2 个表：t1 包含主要时间段，t2 - 其中需要排除的时间间隔。

CREATE TABLE t1 (
    start_date DATE NOT NULL,
    finish_date DATE NOT NULL
);

INSERT INTO t1 (start_date, finish_date)
VALUES ('2023-04-02', '2023-07-31');


CREATE TABLE t2 (
    exception_start_date DATE NOT NULL,
    exception_finish_date DATE NOT NULL
);

INSERT INTO t2 (exception_start_date, exception_finish_date)
VALUES 
('2023-04-15', '2023-05-01'),
--('2023-06-01', '2023-07-01');

从 t2 开始的时间间隔始终完全位于 t1 内，并且彼此不相交。它们的数量可以是无限的

如果只有一个异常，解决方案很简单，可能如下所示：

with cte as (
select *
from t1 join t2
on t1.start_date < t2.exception_start_date and t1.finish_date > t2.exception_finish_date
)
select start_date as start_date, dateadd(day, -1, exception_start_date) as finish_date
from cte
union all
select dateadd(day, 1, exception_finish_date) as start_dt, finish_date as finish_date
from cte

请告诉我如何实现相同的逻辑，但在一个时间间隔内无限数量的异常？

ps 任何 DBMS 的语法

sql

1 个回答

  1. Best Answer

    总的来说，问题解决了，写了这样的东西

    with internal_border_exception as
(
select *
from t1 join t2 on t1.start_date < t2.exception_start_date and t1.finish_date > t2.exception_finish_date
)
, final_cte as (
    -- обойдём все участки "смотря слева"
    select 
       
        case 
            when lag(exception_finish_date) over (partition by  start_date order by exception_start_date) is null then start_date
            else dateadd(day, 1, lag(exception_finish_date) over (partition by start_date order by exception_start_date)) 
        end as start_date,
        dateadd(day, -1, exception_start_date) as finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is not null
    union all
    -- обработаем "крайней правый" промежуток
    select 
        case 
            when lead(exception_start_date) over (partition by  start_date order by exception_start_date) is not null then null 
            else dateadd(day, 1, exception_finish_date) 
        end as start_date,
        case 
            when lead(exception_start_date) over (partition by start_date order by exception_start_date) is not null then null 
            else finish_date 
        end as finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is not null
    union all
    -- выведем интервалы, где не было внутренних исключений
    select 
        start_date, 
        finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is null
)

select  start_date, finish_date
from final_cte
where start_date is not null
order by start_date;
    • 0

相关问题