Sql 正在编写查询.我需要将订阅的时间段分为第一个订阅中包含的另一个订阅之前和之后的时间段

稍后我会试着写得更好，甚至可能会稍微清理一下查询.

假设您希望保留所有Books个订阅.如果存在以某种方式重叠的Premium订阅，则需要将重叠的时段吸收到另一个订阅中，同时调整/创建非重叠(可能是两个).

两个这样的订阅可以通过四种不同的方式相交.也有可能Books的倍数可以落在一个Premium之下.查询的第一部分标识是否存在重叠、重叠的类型，并收集与前向和后向"链接"相关的数据.

第二部分生成对应于重叠的不同段之间的边界的一组四个日期.它还处理创建额外的行，其中Premium必须分成三部分(其中一个保留Books)，而Premium完全被Books包含，并且需要完全消失.

其余的逻辑只是驱动所有部件的组装.注意不要在发生"链接"的地方重复片段.

没有递归，只有一个连接，所以我认为这将是有效的.列user_id, start_date, end_date上的适当索引可能是有益的.

with data as (
    select
        s.user_id, s.subscription_type, s.start_date, s.end_date,
        b.start_date as b_start_date, b.end_date as b_end_date,
        case when b.subscription_type is not null
             then lag(b.end_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as pb_end_date,
        case when b.subscription_type is not null
             then lead(b.start_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as nb_start_date,
        case when s.start_date < b.start_date then 'p'
             when s.start_date > b.start_date then 'b' else '' end ||
        case when s.end_date   < b.end_date   then 'b'
             when s.end_date   > b.end_date   then 'p' else '' end as overlap
    from ordered_subscriptions s left outer join ordered_subscriptions as b
        on      s.subscription_type = 'Premium' and b.subscription_type = 'Books'
            and s.user_id = b.user_id
            and s.start_date < b.end_date and s.end_date > b.start_date
), data2 as (
    select
        user_id, subscription_type, overlap,
        case when pb_end_date is not null then 'Y' else 'N' end as linked,
        least(start_date, b_start_date) as d1,
        greatest(start_date, b_start_date) as d2,
        least(end_date, b_end_date) as d3,
        coalesce(nb_start_date, greatest(end_date, b_end_date)) as d4,
        seg_num
    from data left outer join lateral (
            select * from (values (1), (2)) as v(seg_num)
            where subscription_type = 'Premium' and (seg_num = 1 or overlap = 'pp')
        ) as s(seg_num) on 1 = 1
    where (subscription_type = 'Books' or overlap <> 'bb')
)
select
    user_id, subscription_type,
    case when seg_num is null then d1
         when seg_num = 1 then case when overlap = 'bp' then d3 else d1 end
         when seg_num = 2 then d3 end as start_date,
    case when seg_num is null then d3
         when seg_num = 1 then case when overlap = 'bp' then d4 else d2 end
         when seg_num = 2 then d4 end as end_date
from data2
where (linked = 'N' or seg_num = 2)
order by user_id, start_date, end_date;

https://dbfiddle.uk/chT-SLiC个

我最初认为使用外连接会比使用联合更干净，并生成更好的计划.实际上，尽管这两个查询基本相同，但实际上走另一条路更好.

https://dbfiddle.uk/raMy-wOL