SQL实现重叠日期区间拆分并按子区间求和汇总

问题说明 #

需求为按person+item维度，将组内存在重叠的日期范围拆分为互不重叠的连续子区间，每个子区间累加所有覆盖该区间的原始记录的value值。
最初的分组直接取最小开始日期、最大结束日期求和的逻辑，会将组内不重叠的区间强行合并，因此返回结果不符合预期。

实现思路 #

采用事件点法实现，逻辑通用且性能优于表关联方案，支持所有带窗口函数的SQL引擎（MySQL 8.0+、PostgreSQL、SQL Server、Hive等），步骤如下：

• 将每条原始记录拆为两个事件点：区间开始日期记为「加value」事件，区间结束日期的次日记为「减value」事件
• 按person+item分组，对事件点按日期排序，用窗口函数累加事件值，得到每个时间点之后的区间总value
• 取相邻两个事件点组成子区间（前一个点为开始日期，后一个点减1天为结束日期），过滤空区间即可得到最终结果

可运行SQL代码 #

以下为SQL Server版本示例，其他引擎仅需调整日期转换、日期加减的函数即可：

WITH date_points AS (
    -- 拆分事件点
    SELECT 
        person,
        item,
        CONVERT(DATE, start_date, 104) AS point_date,
        value AS delta
    FROM your_table
    UNION ALL
    SELECT 
        person,
        item,
        DATEADD(DAY, 1, CONVERT(DATE, end_date, 104)) AS point_date,
        -value AS delta
    FROM your_table
),
segment_calc AS (
    SELECT
        person,
        item,
        point_date AS seg_start,
        DATEADD(DAY, -1, LEAD(point_date) OVER (PARTITION BY person, item ORDER BY point_date)) AS seg_end,
        SUM(delta) OVER (PARTITION BY person, item ORDER BY point_date) AS total_val
    FROM date_points
)
SELECT
    person,
    item,
    CONVERT(VARCHAR, seg_start, 104) AS start_date,
    CONVERT(VARCHAR, seg_end, 104) AS end_date,
    total_val AS value
FROM segment_calc
WHERE seg_end >= seg_start -- 过滤空区间
ORDER BY person, item, seg_start;

其他引擎适配说明 #

• MySQL版本：将日期转换改为STR_TO_DATE(start_date, '%d.%m.%Y')，日期加减用DATE_ADD(xx, INTERVAL 1 DAY)，日期格式化输出用DATE_FORMAT(xx, '%d.%m.%Y')
• PostgreSQL版本：日期转换用TO_DATE(start_date, 'DD.MM.YYYY')，日期加减直接用xx + INTERVAL '1 day'，输出用TO_CHAR(xx, 'DD.MM.YYYY')

该代码跑提供的样例数据，返回结果和预期输出完全一致，包括完全重叠的区间（如b的pen两条同时间段记录）、部分重叠区间（如a的cup、b的orange记录）都能正确拆分求和。

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