PostgreSQL基于时间范围关联两张表的实现问题求助

针对你遇到的表关联问题，核心是先补全表2的有效时间区间，再通过拆分时间边界生成最小单元区间，最终关联两张表的属性。具体步骤如下：

1. 补全表2的有效期To字段 #

表2的记录有效期到下一条更大From值的记录为止，用窗口函数LEAD()可以获取每条记录的下一个From值，作为当前记录的To；最后一条记录没有后续记录，用'infinity'表示永久有效：

WITH processed_table2 AS (
    SELECT
        ID,
        "From" AS b_from,
        COALESCE(LEAD("From") OVER (PARTITION BY ID ORDER BY "From"), 'infinity'::timestamp) AS b_to,
        Letter
    FROM table2
)

2. 提取所有时间边界点 #

收集表1的From/To、表2处理后的b_from/b_to，这些点是分割时间区间的关键：

, all_time_points AS (
    SELECT ID, "From" AS time_point FROM table1
    UNION
    SELECT ID, "To" AS time_point FROM table1
    UNION
    SELECT ID, b_from AS time_point FROM processed_table2
    UNION
    SELECT ID, b_to AS time_point FROM processed_table2
)

3. 生成最小有效时间区间 #

按ID分组排序时间点，用LEAD()生成连续的时间区间，筛选出有效区间（起始时间小于结束时间）：

, time_intervals AS (
    SELECT
        ID,
        time_point AS interval_from,
        LEAD(time_point) OVER (PARTITION BY ID ORDER BY time_point) AS interval_to
    FROM all_time_points
    WHERE time_point IS NOT NULL
),
valid_intervals AS (
    SELECT * FROM time_intervals WHERE interval_from < interval_to
)

4. 关联两张表到最小区间 #

将每个最小时间区间分别关联表1和表2，确保区间完全落在原表的有效期内，从而获取对应属性：

SELECT
    vi.ID,
    vi.interval_from AS "From",
    vi.interval_to AS "To",
    t1.Color,
    t1.Shape,
    t2.Letter
FROM valid_intervals vi
LEFT JOIN table1 t1
    ON vi.ID = t1.ID
    AND t1."From" <= vi.interval_from
    AND vi.interval_to <= t1."To"
LEFT JOIN processed_table2 t2
    ON vi.ID = t2.ID
    AND t2.b_from <= vi.interval_from
    AND vi.interval_to <= t2.b_to
ORDER BY vi.ID, vi.interval_from;

关键说明 #

• 拆分最小时间区间可以避免原表区间重叠导致的属性匹配错误，每个区间只会对应一组表1和表2的属性。
• 如果表1的区间和表2的区间没有重叠，对应字段会显示NULL，你可以根据需求调整LEFT JOIN为INNER JOIN过滤无匹配的区间。

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