PostgreSQL：如何用CASE语句填充同列NULL值为最近/后续非空值？

问题分析 #

你遇到的重复行问题，根源是t1和t2关联时未添加严格的日期过滤条件，导致两表产生笛卡尔积（每个NULL记录与t2中所有非空level记录匹配），最终生成重复行，和min()函数无关。

正确实现方法 #

PostgreSQL 11+支持带IGNORE NULLS参数的窗口函数，可直接实现前向填充；对于前置无值的场景，结合反向后向填充覆盖，最终用COALESCE合并结果即可满足需求。

完整SQL代码 #

WITH ordered_data AS (
    SELECT 
        date,
        id,
        "group",
        level,
        -- 前向填充：取当前行之前最近的非空level
        LAST_VALUE(level) OVER (
            PARTITION BY id, "group" 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS forward_fill,
        -- 后向填充：取当前行之后最早的非空level
        FIRST_VALUE(level) OVER (
            PARTITION BY id, "group" 
            ORDER BY date 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS backward_fill
    FROM t1
    WHERE date BETWEEN '2022-01-01' AND '2023-12-01'
)
SELECT 
    date,
    id,
    "group",
    -- 优先用前向填充，无值则用后向填充
    COALESCE(forward_fill, backward_fill) AS level
FROM ordered_data
ORDER BY id, "group", date;

代码说明 #

1. ordered_data CTE：
   • 按id和group分组，按date排序
   • LAST_VALUE(level) ... IGNORE NULLS：从分组起始到当前行，忽略NULL值，取最后一个非空level（即最近的前置非空值）
   • FIRST_VALUE(level) ... IGNORE NULLS：从当前行到分组末尾，忽略NULL值，取第一个非空level（即最早的后置非空值）
2. 最终查询：
   • 用COALESCE优先选取前向填充值，若前置无有效值（如首条记录为NULL），则自动取后向填充值，完全匹配你的需求。

兼容低版本PostgreSQL（低于11） #

若无法使用IGNORE NULLS，可通过子查询+窗口函数模拟：

WITH filled_data AS (
    SELECT 
        date,
        id,
        "group",
        level,
        -- 标记每个非空level的前向区间
        SUM(CASE WHEN level IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id, "group" ORDER BY date
        ) AS forward_group,
        -- 标记每个非空level的后向区间
        SUM(CASE WHEN level IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id, "group" ORDER BY date DESC
        ) AS backward_group
    FROM t1
    WHERE date BETWEEN '2022-01-01' AND '2023-12-01'
),
forward_fill AS (
    SELECT 
        id, "group", forward_group,
        MAX(level) AS filled_level
    FROM filled_data
    GROUP BY id, "group", forward_group
),
backward_fill AS (
    SELECT 
        id, "group", backward_group,
        MIN(level) AS filled_level
    FROM filled_data
    GROUP BY id, "group", backward_group
)
SELECT 
    fd.date,
    fd.id,
    fd."group",
    COALESCE(ff.filled_level, bf.filled_level) AS level
FROM filled_data fd
LEFT JOIN forward_fill ff 
    ON fd.id = ff.id AND fd."group" = ff."group" AND fd.forward_group = ff.forward_group
LEFT JOIN backward_fill bf 
    ON fd.id = bf.id AND fd."group" = bf."group" AND fd.backward_group = bf.backward_group
ORDER BY fd.id, fd."group", fd.date;

原方案问题排查 #

你提到的t3生成重复行，是因为关联t1和t2时未添加日期过滤条件（比如t2.date <= t1.date用于前向填充，t2.date >= t1.date用于后向填充），也未做聚合处理，导致每个t1的NULL行与t2中同id-group的所有非空行关联。比如错误写法可能是：

-- 错误示例：无日期过滤的关联
SELECT t1.*, t2.level AS insert_level
FROM t1
LEFT JOIN t2 ON t1.id = t2.id AND t1."group" = t2."group"
WHERE t1.level IS NULL

修复思路需添加日期过滤+聚合，比如前向填充的正确关联写法：

-- 修复后的前向填充关联
SELECT 
    t1.date,
    t1.id,
    t1."group",
    MAX(t2.level) AS insert_level -- 取最近的非空值（对应最大的date）
FROM t1
LEFT JOIN t2 
    ON t1.id = t2.id 
    AND t1."group" = t2."group"
    AND t2.date <= t1.date
WHERE t1.level IS NULL
GROUP BY t1.date, t1.id, t1."group"

但这种方法需要分别处理前向、后向再合并，效率和简洁性远不如窗口函数方案。

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