PostgreSQL中拆分重叠日期区间为相邻区间的查询求助

没问题，这个需求咱们可以通过提取关键日期点、生成连续小区间，再匹配对应有效value的方式来实现，下面是具体的解决方案：

实现思路 #

1. 收集所有关键日期节点：把每条记录的start_date和end_date都提取出来，按column_name分组后排序，这样就能得到所有需要拆分的时间节点。
2. 生成相邻非重叠区间：用窗口函数LAG()将每个日期和前一个日期配对，形成一系列连续的小区间。
3. 匹配对应value：对每个小区间，找到所有原表中能完全覆盖它的记录，优先选择区间长度最短的（也就是最具体的那条记录，比如例子里的value2）。

完整查询语句 #

假设你的表名为your_table，日期格式是DD-MM-YYYY，先把字符串日期转为date类型处理：

WITH all_dates AS (
    -- 提取并去重所有关键日期点，按分组和日期排序
    SELECT column_name, start_date::date AS date_point
    FROM your_table
    UNION
    SELECT column_name, end_date::date AS date_point
    FROM your_table
    ORDER BY column_name, date_point
),
date_ranges AS (
    -- 生成相邻的非重叠小区间
    SELECT 
        column_name,
        LAG(date_point) OVER (PARTITION BY column_name ORDER BY date_point) AS start_date,
        date_point AS end_date
    FROM all_dates
    WHERE LAG(date_point) OVER (PARTITION BY column_name ORDER BY date_point) IS NOT NULL
)
-- 为每个小区间匹配对应的value，优先选覆盖它的最短原区间
SELECT 
    dr.column_name,
    (SELECT t.value
     FROM your_table t
     WHERE t.column_name = dr.column_name
       AND t.start_date::date <= dr.start_date
       AND t.end_date::date >= dr.end_date
     ORDER BY (t.end_date::date - t.start_date::date) ASC
     LIMIT 1) AS value,
    dr.start_date,
    dr.end_date
FROM date_ranges dr
WHERE dr.start_date < dr.end_date; -- 过滤掉日期相同的无效区间

测试示例 #

如果你插入如下测试数据：

INSERT INTO your_table (column_name, value, start_date, end_date)
VALUES
('column1', 'value1', '03-09-2020', '26-09-2020'),
('column1', 'value2', '07-09-2020', '20-09-2020');

运行上面的查询后，就能得到你期望的输出结果：

注意事项 #

• 如果你的日期字段已经是date类型，可以去掉::date的转换。
• 要是同一个小区间有多个长度相同的覆盖区间，这个查询会返回排序后的第一条，你可以根据实际需求调整ORDER BY的条件（比如按value排序）。

内容的提问来源于stack exchange，提问作者Стефан Цоли</think_never_used_51bce0c785ca2f68081bfa7d91973934>