PostgreSQL 生成含重叠日期范围的视图实现方案

这是个很常见的时间区间整合需求，咱们得先把所有表的日期边界都提取出来，生成没有重叠的连续区间，再把每个区间和原表做关联，匹配对应时间段各表的val值。下面是具体步骤和SQL实现：

第一步：先处理原表的缺失日期 #

你给出的原表数据里有些end_date是空值，我们默认这类数据是无结束日期的永久区间，用'9999-12-31'来补全（你可以根据实际数据库调整这个极大值）。

第二步：提取所有日期边界，生成拆分后的区间 #

我们需要把三个表的start_date和补全后的end_date全部收集起来，去重排序后，用相邻的日期生成连续的不重叠区间。

第三步：关联原表，匹配每个区间的对应值 #

用生成的区间去和每个原表做关联，判断区间是否落在原表记录的时间范围内，获取对应的val，最后整理成目标格式。

完整SQL代码（以PostgreSQL为例，其他数据库可稍作调整） #

WITH cleaned_tables AS (
    -- 补全各表缺失的end_date，处理成统一格式
    SELECT start_date, COALESCE(end_date, '9999-12-31') AS end_date, val AS val_table_1 FROM table1
    UNION ALL
    SELECT start_date, COALESCE(end_date, '9999-12-31') AS end_date, val AS val_table_2 FROM table2
    UNION ALL
    SELECT start_date, COALESCE(end_date, '9999-12-31') AS end_date, val AS val_table_3 FROM table3
),
date_boundaries AS (
    -- 提取所有日期边界并去重排序
    SELECT DISTINCT date_val FROM (
        SELECT start_date AS date_val FROM cleaned_tables
        UNION ALL
        SELECT end_date + INTERVAL '1 day' AS date_val FROM cleaned_tables -- 加一天是为了适配闭区间转换
    ) AS all_dates
    ORDER BY date_val
),
split_intervals AS (
    -- 生成连续的不重叠区间
    SELECT
        date_val AS start_date,
        LEAD(date_val) OVER (ORDER BY date_val) - INTERVAL '1 day' AS end_date
    FROM date_boundaries
    WHERE LEAD(date_val) OVER (ORDER BY date_val) IS NOT NULL -- 排除最后一个无后续的边界
)
-- 关联各表，获取每个区间的对应值
SELECT
    si.start_date,
    si.end_date,
    MAX(ct.val_table_1) AS val_table_1,
    MAX(ct.val_table_2) AS val_table_2,
    MAX(ct.val_table_3) AS val_table_3
FROM split_intervals si
LEFT JOIN cleaned_tables ct
    ON si.start_date <= ct.end_date
    AND si.end_date >= ct.start_date
GROUP BY si.start_date, si.end_date
ORDER BY si.start_date;

代码说明 #

1. cleaned_tables：补全所有表的缺失end_date，并给每个表的val重命名为目标字段名，方便后续关联。
2. date_boundaries：收集所有的起始和结束日期（结束日期加一天是为了把原表的闭区间[start, end]转换成左闭右开的逻辑，避免区间重叠）。
3. split_intervals：用LEAD()窗口函数把相邻的日期边界转换成连续的、无重叠的新区间。
4. 最后关联步骤：用左连接把每个拆分后的区间和原表关联，通过日期范围判断匹配关系；用MAX()聚合是因为每个区间在每个表中最多只有一个匹配值，聚合后就能得到对应的val，没有匹配的则返回null。

注意事项 #

• 如果你的数据库是MySQL，INTERVAL '1 day'要改成INTERVAL 1 DAY，部分日期函数可能需要稍作调整。
• 确保所有日期字段的格式统一（比如你给出的是DD-MM-YYYY，数据库里最好是标准日期类型，避免格式转换问题）。

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