交叉日期
前段时间刷到字节的一道SQL面试题，题目如下：
已知表hall，表中3个字段：
hall_id:大厅id
start_date:开始日期
end_date:结束日期
每一个大厅可以有多个活动：
1.合并在同一个大厅举行的所有重叠的活动，如果两个活动至少有一天相同，那他们就是重叠的
2.统计每一个大厅开展的营销天数，日期如果有重叠需要去重。
解题思路：
- 日期重叠的问题，关键在于找到开始日期与结束日期。
- 查询开始日期没有落在其它开始日期与结束日期之间的，则为非重叠日期的开始日期。
- 查询结束日期没有落在其它开始日期与结束日期之间的，则为非重叠日期的结束日期。
- 因为开始日期与结束日期是成对出现，且结束日期均是>=开始日期的，则利用此规则，进行排序后，重新组对后，即为所得。
测试数据如下：
CREATE TABLE t_hall (
hall_id int,
start_date date,
end_date date
);
insert into t_hall(hall_id, start_date, end_date)
values (1,'2023-01-13','2023-01-14'),
(1,'2023-01-14','2023-01-17'),
(1,'2023-01-18','2023-01-25'),
(2,'2022-12-09','2022-12-23'),
(2,'2022-12-13','2022-12-17'),
(3,'2022-12-01','2023-01-30'),
(4,'2023-01-1','2023-01-12'),
(4,'2023-01-1','2023-01-17'),
(4,'2023-01-11','2023-01-25'),
(4,'2023-01-13','2023-01-23'),
(5,'2023-01-10','2023-01-22'),
(5,'2023-01-11','2023-01-17');
完整的解题SQL如下（PG）：
-- 查询开始日期没有落在其它开始日期与结束日期之间的，则为非重叠日期的开始日期
with cte_start as (
select distinct hall_id
, start_date
from t_hall a
where not exists ( select 1
from t_hall b
where a.hall_id = b.hall_id
and a.start_date>b.start_date
and a.start_date<=b.end_date )
),
-- 查询结束日期没有落在其它开始日期与结束日期之间的，则为非重叠日期的结束日期
cte_end as (
select distinct hall_id
, end_date
from t_hall a
where not exists ( select 1
from t_hall b
where a.hall_id = b.hall_id
and a.end_date>=b.start_date
and a.end_date<b.end_date )
),
-- 因为开始日期与结束日期是成对出现，且结束日期均是>=开始日期的，则利用此规则，进行排序后，重新组对后，即为所得
cte_start_rw as (
select hall_id
, start_date
, row_number() over (PARTITION BY hall_id order by start_date) as rownumber
from cte_start
),
cte_end_rw as (
select hall_id
, end_date
, row_number() over (PARTITION BY hall_id order by end_date) as rownumber
from cte_end
)
select srw.hall_id
, srw.start_date
, erw.end_date
from cte_start_rw srw
join cte_end_rw erw
on srw.hall_id = erw.hall_id
and srw.rownumber = erw.rownumber;
结果截图如下：
如有不妥或者不对之处，请大家批评指出，谢谢。