如何用SQL从两张表生成连续日期区间并合并状态变化记录

需要从两张表中识别关联时段，生成二者的序列组合，每当状态发生变化时生成新记录。

第一张表（first_table）数据 #

81080,"segment_name1","2024-04-01 02:05:53.549000000","2024-04-05 08:05:05.000000000"
81080,"segment_name2","2024-04-05 08:05:06.828000000","2024-04-08 08:06:46.000000000"
81080,"segment_name3","2024-04-08 08:06:47.406000000","2024-04-10 08:05:07.000000000"
81080,"segment_name2","2024-04-10 08:05:08.085000000","2024-04-23 14:05:34.000000000"
81080,"segment_name4","2024-04-23 14:05:35.308000000","2024-04-24 08:05:56.000000000"
81080,"segment_name5","2024-04-24 08:05:57.618000000","2024-12-31 23:59:59.000000000"

第二张表（second_table）数据 #

81080,  "promo1", "2024-04-01 07:00:00","2024-04-02 07:00:00"
81080,  "promo1", "2024-04-02 07:00:00","2024-04-03 07:00:00"
81080,  "promo1", "2024-04-03 07:00:00","2024-04-04 07:00:00"
81080,  "promo1", "2024-04-04 07:00:00","2024-04-05 07:00:00"
81080,  "promo1", "2024-04-05 07:00:00","2024-04-06 07:00:00"
81080,  "promo1", "2024-04-06 13:30:00","2024-04-07 07:00:00"
81080,  "promo1", "2024-04-07 07:00:00","2024-04-09 07:00:00"
81080,  "promo1", "2024-04-08 07:00:00","2024-04-09 07:00:00"
81080,  "promo2", "2024-04-08 07:00:00","2024-07-09 07:00:00"
81080,  "promo1", "2024-04-09 07:00:00","2024-04-10 07:00:00"

期望结果表 #

81080,  "segment_name1",[],"2024-04-01 02:05:53.549" ,"2024-04-01 07:00:00"
81080,  "segment_name1",[promo1] ,"2024-04-01 07:00:00" ,"2024-04-05 08:05:06.828"
81080,  "segment_name2",[promo1],"2024-04-05 08:05:06.828","2024-04-06 07:00:00"
81080,  "segment_name2",[],"2024-04-06 07:00:00","2024-04-06 13:30:00"
81080,  "segment_name2",[promo1],"2024-04-06 13:30:00","2024-04-08 07:00:00"
81080,  "segment_name2",[promo1,promo2],"2024-04-08 07:00:00","2024-04-08 08:06:46.000"
81080,  "segment_name3",[promo1,promo2],"2024-04-08 08:06:47.000","2024-04-10 07:00:00"
81080,  "segment_name3",[promo2],"2024-04-10 07:00:00","2024-04-10 08:05:07.000"
81080,  "segment_name2",[promo2],"2024-04-10 08:05:08.000","2024-04-23 14:05:34.000"
81080,  "segment_name4",[promo2],"2024-04-23 14:05:35.308","2024-04-24 08:05:56.000"
81080,  "segment_name5",[promo2],"2024-04-24 08:05:57.618","2024-12-31 23:59:59.000"

已尝试的SQL #

SELECT * 
FROM first_table a 
LEFT JOIN second_table b 
ON a.segment_id = b.segment_id
AND a.start_time <= b.end_date
AND a.end_time >= b.start_date

完整解决方案 #

要实现需求，核心是先提取所有时间分界点，然后在每个时间区间内匹配对应的segment和活跃的promo列表，具体SQL如下：

WITH all_times AS (
    -- 收集所有可能的状态变化时间点
    SELECT start_time AS time_point FROM first_table
    UNION
    SELECT end_time AS time_point FROM first_table
    UNION
    SELECT start_time AS time_point FROM second_table
    UNION
    SELECT end_time AS time_point FROM second_table
),
time_intervals AS (
    -- 生成无重叠的连续时间区间
    SELECT 
        t1.time_point AS interval_start,
        t2.time_point AS interval_end
    FROM all_times t1
    JOIN all_times t2 ON t2.time_point > t1.time_point
    WHERE NOT EXISTS (
        SELECT 1 FROM all_times t3 
        WHERE t3.time_point > t1.time_point AND t3.time_point < t2.time_point
    )
)
-- 匹配每个区间的segment和活跃promo
SELECT 
    a.segment_id,
    a.segment_name,
    COALESCE(JSON_ARRAYAGG(DISTINCT b.promo_name), '[]') AS active_promos,
    i.interval_start,
    i.interval_end
FROM time_intervals i
JOIN first_table a ON i.interval_start >= a.start_time AND i.interval_end <= a.end_time
LEFT JOIN second_table b ON i.interval_start < b.end_time AND i.interval_end > b.start_time AND a.segment_id = b.segment_id
GROUP BY a.segment_id, a.segment_name, i.interval_start, i.interval_end
ORDER BY i.interval_start;

关键说明 #

1. 时间区间生成：通过合并两张表的所有时间点，得到所有状态可能变化的分界点，再生成相邻时间点组成的无重叠区间，确保每个区间内segment和promo的状态稳定。
2. promo聚合：使用JSON_ARRAYAGG（MySQL语法）聚合当前区间内的活跃promo，空值时返回[]；若使用PostgreSQL，可替换为array_agg，并处理空数组的显示。
3. 区间匹配逻辑：确保每个时间区间完全落在某个segment的生效时段内，同时匹配该区间内正在运行的promo。

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