PostgreSQL中获取SCD表另一分区的最后start_date与end_date值

原表数据 #

需求 #

为每条记录添加**另一分区（仅存在1和2两个分区）**的最后一条记录的start_date和end_date，最终结果如下：

期望结果 #

尝试过的代码 #

你之前用last_value的写法逻辑有误，导致未达预期：

, last_value (start_date) OVER (partition by partition = '1' order by start_date asc) as last_start_date_partition
, last_value (end_date) OVER (partition by partition = '1' order by end_date asc) as last_end_date_partition

问题：能否通过窗口函数加条件实现需求？ #

可以实现，下面提供几种可行方案：

方案1：通用聚合关联法（所有SQL引擎支持） #

先预计算两个分区各自的最后一条记录值，再关联回原表匹配对应分区的值：

WITH partition_last_vals AS (
    SELECT
        partition AS p,
        MAX(start_date) AS max_start,
        MAX(end_date) AS max_end
    FROM your_scd_table
    GROUP BY partition
)
SELECT
    t.start_date,
    t.end_date,
    t.partition,
    -- 根据当前分区匹配另一分区的最后值
    CASE 
        WHEN t.partition = 1 THEN (SELECT max_start FROM partition_last_vals WHERE p = 2)
        WHEN t.partition = 2 THEN (SELECT max_start FROM partition_last_vals WHERE p = 1)
        ELSE NULL
    END AS max_start_date,
    CASE 
        WHEN t.partition = 1 THEN (SELECT max_end FROM partition_last_vals WHERE p = 2)
        WHEN t.partition = 2 THEN (SELECT max_end FROM partition_last_vals WHERE p = 1)
        ELSE NULL
    END AS max_end_date
FROM your_scd_table t
ORDER BY t.start_date;

方案2：窗口函数+FILTER子句（支持的引擎：PostgreSQL等） #

如果你的SQL引擎支持窗口函数的FILTER子句，可以直接在窗口中过滤出另一分区的数据，再取最后值。注意要指定完整窗口范围，否则last_value只会取到当前行之前的数据：

SELECT
    start_date,
    end_date,
    partition,
    LAST_VALUE(start_date) OVER (
        ORDER BY start_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        FILTER (WHERE partition != t.partition)
    ) AS max_start_date,
    LAST_VALUE(end_date) OVER (
        ORDER BY start_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        FILTER (WHERE partition != t.partition)
    ) AS max_end_date
FROM your_scd_table t
ORDER BY start_date;

方案3：窗口函数+条件判断（替代FILTER的通用写法） #

如果不支持FILTER，可以用CASE在窗口函数中标记另一分区的数据，再取最后非空值：

SELECT
    start_date,
    end_date,
    partition,
    LAST_VALUE(CASE WHEN partition != t.partition THEN start_date END) OVER (
        ORDER BY start_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_start_date,
    LAST_VALUE(CASE WHEN partition != t.partition THEN end_date END) OVER (
        ORDER BY start_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_end_date
FROM your_scd_table t
ORDER BY start_date;

关键说明 #

• 你之前的partition by partition='1'逻辑错误：它会把数据分成“partition=1”和“partition≠1”两组，而不是按分区分组后取另一分区的值。
• 方案1是最通用的，逻辑清晰，适用于所有SQL引擎；方案2、3更简洁，但依赖引擎特性。
• 第一条记录（partition=1）对应的另一分区（2）还没有数据，所以返回null，完全符合你的期望结果。

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