PostgreSQL：通过窗口函数将Obsolete的low_date设为同id Ext的upper_date

原始数据 #

id | type      | low_date   | upper_date
----+-----------+------------+------------
1  | Obsolete  | 2020-01-01 | null
1  | Ext       | 2019-01-01 | null
2  | Obsolete  | 2023-01-01 | null
2  | Ext       | 2018-01-01 | null

需求说明 #

将相同id下Obsolete类型的low_date值，赋值给同id下Ext类型的upper_date字段，Obsolete类型的upper_date保持为null。

实现SQL #

SELECT 
    id,
    type,
    low_date,
    CASE 
        WHEN type = 'Ext' 
        THEN MAX(low_date) FILTER (WHERE type = 'Obsolete') OVER (PARTITION BY id)
        ELSE upper_date
    END AS upper_date
FROM your_table_name
ORDER BY id, type DESC;

代码说明 #

• PARTITION BY id：按id分组，确保仅在相同id范围内匹配数据
• MAX(low_date) FILTER (WHERE type = 'Obsolete')：筛选出同id下Obsolete类型的low_date，因每个id仅一条Obsolete记录，MAX用于提取唯一值，也可替换为FIRST_VALUE(low_date)
• CASE条件：仅对Ext类型行赋值，其他类型保留原upper_date值

执行结果 #

id | type      | low_date   | upper_date
----+-----------+------------+------------
1  | Obsolete  | 2020-01-01 | null
1  | Ext       | 2019-01-01 | 2020-01-01
2  | Obsolete  | 2023-01-01 | null
2  | Ext       | 2018-01-01 | 2023-01-01

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