基于日期合并重叠事件并拆分非重叠时段的SQL解决方案求助

以下分别提供Spark SQL和SQL Server的实现代码，满足仅同起始周日的重叠事件合并、非重叠时段单独成行的需求。

Spark SQL 实现 #

假设输入表Input结构为：Event STRING, Start_Date DATE, End_Date DATE，代码如下：

WITH EventTimePoints AS (
    -- 提取同起始周日下所有事件的开始时间，以及结束时间+1（用于生成连续区间）
    SELECT 
        Start_Date AS Base_Start,
        Event,
        Start_Date AS TimePoint
    FROM Input
    UNION ALL
    SELECT 
        Start_Date AS Base_Start,
        Event,
        DATE_ADD(End_Date, 1) AS TimePoint
    FROM Input
),
OrderedTimePoints AS (
    -- 对每个起始周日的时间点去重并排序，生成序号
    SELECT 
        Base_Start,
        TimePoint,
        ROW_NUMBER() OVER (PARTITION BY Base_Start ORDER BY TimePoint) AS rn
    FROM (SELECT DISTINCT Base_Start, TimePoint FROM EventTimePoints) t
),
TimeIntervals AS (
    -- 用相邻时间点生成连续的事件区间
    SELECT 
        otp1.Base_Start,
        otp1.TimePoint AS Interval_Start,
        DATE_SUB(otp2.TimePoint, 1) AS Interval_End
    FROM OrderedTimePoints otp1
    JOIN OrderedTimePoints otp2 
        ON otp1.Base_Start = otp2.Base_Start 
        AND otp1.rn = otp2.rn - 1
    WHERE otp1.TimePoint < otp2.TimePoint
),
EventCoverage AS (
    -- 匹配每个区间对应的覆盖事件
    SELECT 
        ti.Base_Start,
        ti.Interval_Start,
        ti.Interval_End,
        etp.Event
    FROM TimeIntervals ti
    JOIN EventTimePoints etp 
        ON ti.Base_Start = etp.Base_Start
        AND etp.Start_Date <= ti.Interval_Start
        AND DATE_SUB(etp.TimePoint, 1) >= ti.Interval_End
)
-- 合并每个区间的事件，输出最终结果
SELECT 
    Base_Start AS Original_Start_Date,
    Interval_Start AS Period_Start,
    Interval_End AS Period_End,
    CONCAT_WS(',', COLLECT_SET(DISTINCT Event)) AS Merged_Events
FROM EventCoverage
GROUP BY Base_Start, Interval_Start, Interval_End
ORDER BY Base_Start, Interval_Start;

SQL Server 实现 #

适用于SQL Server 2017及以上版本（支持STRING_AGG函数），输入表结构同上：

WITH EventTimePoints AS (
    -- 提取同起始周日下所有事件的开始时间，以及结束时间+1（用于生成连续区间）
    SELECT 
        Start_Date AS Base_Start,
        Event,
        Start_Date AS TimePoint
    FROM Input
    UNION ALL
    SELECT 
        Start_Date AS Base_Start,
        Event,
        DATEADD(DAY, 1, End_Date) AS TimePoint
    FROM Input
),
OrderedTimePoints AS (
    -- 对每个起始周日的时间点去重并排序，生成序号
    SELECT 
        Base_Start,
        TimePoint,
        ROW_NUMBER() OVER (PARTITION BY Base_Start ORDER BY TimePoint) AS rn
    FROM (SELECT DISTINCT Base_Start, TimePoint FROM EventTimePoints) t
),
TimeIntervals AS (
    -- 用相邻时间点生成连续的事件区间
    SELECT 
        otp1.Base_Start,
        otp1.TimePoint AS Interval_Start,
        DATEADD(DAY, -1, otp2.TimePoint) AS Interval_End
    FROM OrderedTimePoints otp1
    JOIN OrderedTimePoints otp2 
        ON otp1.Base_Start = otp2.Base_Start 
        AND otp1.rn = otp2.rn - 1
    WHERE otp1.TimePoint < otp2.TimePoint
),
EventCoverage AS (
    -- 匹配每个区间对应的覆盖事件
    SELECT 
        ti.Base_Start,
        ti.Interval_Start,
        ti.Interval_End,
        etp.Event
    FROM TimeIntervals ti
    JOIN EventTimePoints etp 
        ON ti.Base_Start = etp.Base_Start
        AND etp.Start_Date <= ti.Interval_Start
        AND DATEADD(DAY, -1, etp.TimePoint) >= ti.Interval_End
)
-- 合并每个区间的事件，输出最终结果
SELECT 
    Base_Start AS Original_Start_Date,
    Interval_Start AS Period_Start,
    Interval_End AS Period_End,
    STRING_AGG(DISTINCT Event, ',') AS Merged_Events
FROM EventCoverage
GROUP BY Base_Start, Interval_Start, Interval_End
ORDER BY Base_Start, Interval_Start;

效果说明 #

以示例输入为例：

运行后输出结果：

完全符合需求：同起始周日的重叠事件合并，非重叠时段单独成行，不同起始周日的事件独立输出。

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