SQL中如何正确填充分组内NULL值？LAG()函数使用疑问

需求与问题 #

需要将LOCAL_ID列的NULL值替换为对应GROUP_ID分组内的首个非NULL值（即每个分组中ORDER_IN_LOCAL_ID = 1的行的LOCAL_ID）。

当前使用LAG()函数的写法存在缺陷：

,CASE WHEN a.LOCAL_ID IS NULL THEN LAG(a.LOCAL_ID) OVER (ORDER BY b.GROUP_ID) ELSE a.LOCAL_ID END AS ANSWER

LAG()仅能获取上一行的值，当遇到连续多个NULL时，无法追溯到分组顶部的有效值，导致填充失败。

原始数据 #

UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1         808      1        808           1
2         NULL     1        909           2
3         676      7        676           1
4         674      8        674           1
5         352      9        352           1
6         NULL     9        134           2
7         232      11       232           1
8         NULL     11       431           2
9         NULL     11       323           3
10        NULL     11       567           4
11        800      98       800           1
11        NULL     98       786           2
11        NULL     98       345           3

期望结果 #

UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1         808      1        808           1
2         808      1        909           2
3         676      7        676           1
4         674      8        674           1
5         352      9        352           1
6         352      9        134           2
7         232      11       232           1
8         232      11       431           2
9         232      11       323           3
10        232      11       567           4
11        800      98       800           1
11        800      98       786           2
11        800      98       345           3

可行解决方案 #

方案1：使用FIRST_VALUE()窗口函数 #

FIRST_VALUE()可以在分组内按指定排序规则，取从分组开头到当前行的第一个非NULL值，完美适配需求：

SELECT
    UNIQUE_ID,
    FIRST_VALUE(LOCAL_ID) OVER (
        PARTITION BY GROUP_ID
        ORDER BY ORDER_IN_LOCAL_ID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS LOCAL_ID,
    GROUP_ID,
    LOCAL_STAY_ID,
    ORDER_IN_LOCAL_ID
FROM your_table_name;

• PARTITION BY GROUP_ID：按分组划分数据
• ORDER BY ORDER_IN_LOCAL_ID：保证分组内按顺序取首个有效值
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：指定窗口范围从分组第一行到当前行，确保始终取到分组顶部的有效值

方案2：使用分组聚合关联 #

先提取每个分组的有效值，再通过GROUP_ID关联回原表：

WITH group_valid_ids AS (
    SELECT
        GROUP_ID,
        LOCAL_ID AS valid_local_id
    FROM your_table_name
    WHERE ORDER_IN_LOCAL_ID = 1
)
SELECT
    t.UNIQUE_ID,
    COALESCE(t.LOCAL_ID, g.valid_local_id) AS LOCAL_ID,
    t.GROUP_ID,
    t.LOCAL_STAY_ID,
    t.ORDER_IN_LOCAL_ID
FROM your_table_name t
JOIN group_valid_ids g ON t.GROUP_ID = g.GROUP_ID;

这种方式逻辑直观，适合不支持复杂窗口函数的SQL环境。

方案3：使用MAX()窗口函数 #

由于每个分组内只有ORDER_IN_LOCAL_ID = 1的行有非NULL的LOCAL_ID，可以直接用分组内的MAX()（或MIN()）获取有效值：

SELECT
    UNIQUE_ID,
    MAX(LOCAL_ID) OVER (PARTITION BY GROUP_ID) AS LOCAL_ID,
    GROUP_ID,
    LOCAL_STAY_ID,
    ORDER_IN_LOCAL_ID
FROM your_table_name;

写法最简洁，前提是每个分组内仅有一个非NULL的LOCAL_ID（符合当前需求场景）。

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