如何按ID统计日期区间中的唯一日期数

问题描述 #

现有一张预订表，数据如下：

id date_start   date_end
1  03.01.2022    03.02.2022
1  03.02.2022    03.03.2022
1  01.03.2022    10.03.2022
2  02.04.2022    04.04.2022
2  02.06.2022    02.07.2022
2  30.06.2022    04.07.2022

表中存在日期区间相交/相邻、不相交的情况，需要按ID统计这些日期区间内的唯一天数，预期结果为：ID1对应67天，ID2对应34天。

已知单独处理相交（取最大最小日期算差值）和不相交（各区间天数求和）的方法，但不知道如何在脚本中自动区分这两种情况。

解决方案 #

不需要手动区分区间是否相交，可通过区间合并的思路自动处理所有情况，以下是SQL实现示例（以PostgreSQL为例，其他数据库可调整日期转换函数）：

-- 第一步：转换日期格式并按ID、起始日期排序
WITH sorted_intervals AS (
    SELECT 
        id,
        TO_DATE(date_start, 'DD.MM.YYYY') AS start_date,
        TO_DATE(date_end, 'DD.MM.YYYY') AS end_date
    FROM bookings
    ORDER BY id, start_date
),
-- 第二步：为每个区间标记所属的合并组
grouped_intervals AS (
    SELECT 
        id,
        start_date,
        end_date,
        -- 若当前区间与前一个区间重叠/相邻，则归为同一组，否则新建组
        SUM(CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id ORDER BY start_date) AS group_id
    FROM sorted_intervals
),
-- 第三步：合并同组内的区间，取组内最大结束日期
merged_intervals AS (
    SELECT 
        id,
        start_date,
        MAX(end_date) OVER (PARTITION BY id, group_id ORDER BY start_date) AS merged_end
    FROM grouped_intervals
),
-- 第四步：去重得到每个ID的所有不重叠连续区间
unique_intervals AS (
    SELECT DISTINCT id, start_date, merged_end
    FROM merged_intervals
)
-- 最后计算每个ID的总唯一天数
SELECT 
    id,
    SUM(merged_end - start_date) AS unique_days
FROM unique_intervals
GROUP BY id;

逻辑说明 #

1. sorted_intervals：将原始的dd.mm.yyyy格式日期转换为数据库可识别的日期类型，并按ID和起始日期排序，确保区间按时间顺序处理。
2. grouped_intervals：使用LAG函数获取当前区间的前一个区间结束日期，判断当前区间是否与前一个区间重叠或相邻，通过累加标记为不同的合并组。
3. merged_intervals：在每个合并组内，取最大的结束日期，得到合并后的连续区间。
4. unique_intervals：去重后得到每个ID的所有不重叠连续区间。
5. 最后分组计算每个合并区间的天数差并求和，得到每个ID的唯一天数。

这个方法会自动处理所有相交、相邻、不相交的区间，无需手动区分场景，最终结果与预期一致。

内容的提问来源于stack exchange，提问作者user5764