如何在Snowflake中让LAG()和LEAD()函数忽略多行NULL值

我有一个包含多用户的排名数据集，用户排名会随日期变化，但数据中存在NULL值（空值）替代已知排名的情况。需求是：

• 对ID#100：用最近的已知排名向前填充空值
• 对ID#200：用首次出现的已知排名向后填充空值

原始数据集：

期望结果：

之前尝试过LAG()和LEAD()函数，但它们无法连续填充空值，只会传递NULL，需要可行的解决方案。

核心思路是用窗口函数实现定向填充：对ID100取最近的非空排名做前向填充，对ID200取最早的非空排名做后向填充，以下分两种数据库场景给出实现方式。

方法1：支持IGNORE NULLS的数据库（PostgreSQL 11+、SQL Server、Oracle等） #

直接利用窗口函数的IGNORE NULLS参数跳过空值，精准获取需要的填充值：

SELECT 
    "ID#",
    日期,
    CASE 
        WHEN "ID#" = 100 THEN
            -- 给ID100做前向填充：抓取当前行之前最近的非空排名
            LAST_VALUE(排名 IGNORE NULLS) OVER (
                PARTITION BY "ID#" 
                ORDER BY 日期 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
        WHEN "ID#" = 200 THEN
            -- 给ID200做后向填充：抓取当前行之后最早出现的非空排名
            FIRST_VALUE(排名 IGNORE NULLS) OVER (
                PARTITION BY "ID#" 
                ORDER BY 日期 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            )
        ELSE 排名
    END AS 排名
FROM 你的表名
ORDER BY "ID#", 日期;

逻辑说明 #

• LAST_VALUE(排名 IGNORE NULLS)：在同一个ID的分组内，按日期从早到晚排序，只识别非空排名，取到当前行为止最后出现的那个值，刚好把中间的空值都填上最近的已知排名。
• FIRST_VALUE(排名 IGNORE NULLS)：同样按日期排序，但范围是从当前行到分组末尾，抓取第一个非空排名，这样ID200前面的空值就能被后面首次出现的8填充。

方法2：不支持IGNORE NULLS的数据库（比如MySQL 8.0以前） #

需要先给非空排名“划分组”，再用分组内的非空值统一填充：

WITH 标记分组 AS (
    SELECT 
        "ID#",
        日期,
        排名,
        -- ID100：每碰到一个非空排名，分组号+1，空值和前面的非空排名同组
        CASE WHEN "ID#" = 100 THEN
            SUM(CASE WHEN 排名 IS NOT NULL THEN 1 ELSE 0 END) OVER (
                PARTITION BY "ID#" ORDER BY 日期
            )
        -- ID200：倒序排序，碰到非空排名分组号+1，前面的空值和后面的非空排名同组
        WHEN "ID#" = 200 THEN
            SUM(CASE WHEN 排名 IS NOT NULL THEN 1 ELSE 0 END) OVER (
                PARTITION BY "ID#" ORDER BY 日期 DESC
            )
        END AS 分组标记
    FROM 你的表名
),
提取填充值 AS (
    SELECT 
        "ID#",
        分组标记,
        MAX(排名) AS 填充排名  -- 每组仅一个非空排名，MAX/MIN均可
    FROM 标记分组
    GROUP BY "ID#", 分组标记
)
SELECT 
    t."ID#",
    t.日期,
    COALESCE(t.排名, f.填充排名) AS 排名
FROM 标记分组 t
JOIN 提取填充值 f ON t."ID#" = f."ID#" AND t.分组标记 = f.分组标记
ORDER BY t."ID#", t.日期;

逻辑拆解 #

1. 标记分组：给ID100的每段连续空值贴上前面最近非空排名的分组号；给ID200的前置空值贴后面首次非空排名的分组号。
2. 提取填充值：每个分组对应一个唯一的非空填充值。
3. 填充空值：用COALESCE把原表的空值替换成对应分组的填充值。

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