PostgreSQL中重叠与非重叠时间区间拆分需求求助

针对你需要将时间记录拆分为重叠和非重叠时段，并合并对应type_id的需求，可以使用以下SQL语句实现：

假设你的数据表名为time_records，字段为start_date（日期类型）、end_date（日期类型）、type_id（文本类型）：

WITH all_dates AS (
    -- 收集所有关键拆分节点：所有记录的起始日期，以及结束日期的次日
    SELECT start_date AS date_point FROM time_records
    UNION
    SELECT end_date + INTERVAL '1 day' AS date_point FROM time_records
),
date_ranges AS (
    -- 生成连续的时间区间
    SELECT
        date_point AS period_start,
        LEAD(date_point) OVER (ORDER BY date_point) - INTERVAL '1 day' AS period_end
    FROM all_dates
)
-- 匹配每个区间对应的type_id并合并
SELECT
    period_start::DATE AS start_date,
    period_end::DATE AS end_date,
    STRING_AGG(DISTINCT type_id, ' , ') AS type_id
FROM date_ranges
JOIN time_records
    ON period_start <= time_records.end_date
    AND period_end >= time_records.start_date
WHERE period_end IS NOT NULL
GROUP BY period_start, period_end
ORDER BY period_start;

逻辑说明 #

1. 收集关键日期节点：通过all_dates公共表表达式（CTE），提取所有记录的起始日期和结束日期的次日，这些节点是拆分时间区间的关键边界。
2. 生成连续时段：利用LEAD()窗口函数将相邻的日期节点配对，生成连续的时间区间，确保每个区间要么完全重叠，要么完全不重叠。
3. 匹配并合并type_id：将生成的时段与原表关联，找出每个时段覆盖的所有记录，使用STRING_AGG()合并对应的type_id，并按时段起始日期排序输出。

测试结果 #

使用你提供的示例数据执行上述SQL后，将得到如下结果：

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