Snowflake SQL实现类似R tidyr fill向后填充的问题排查

需要在Snowflake SQL中实现类似R语言tidyr::fill的功能，用后续非空的TIMESTAMP2值填充对应行的空值。当前使用以下语句：

LAST_VALUE(TIMESTAMP2 IGNORE NULLS) OVER (PARTITION BY "Group" ORDER BY TIMESTAMP1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

但最后一行TIMESTAMP2为空的行被填充为6/7/23 3:21 AM，而预期应为NULL。

数据示例 #

问题原因 #

当前语句的逻辑是取当前行及之前所有行中最后一个非空的TIMESTAMP2值：窗口范围UNBOUNDED PRECEDING AND CURRENT ROW限定了只看当前行之前的记录，LAST_VALUE会提取这个范围内的最后一个有效值。对于最后一行，前面的第七行存在非空值6/7/23 3:21 AM，因此会被填充该值，但你的需求是用后续的非空值填充，当当前行之后没有非空值时，应返回NULL。

解决方案 #

要实现用后续非空值填充，需要调整窗口范围和聚合函数：

• 窗口范围改为CURRENT ROW TO UNBOUNDED FOLLOWING，包含当前行及之后所有行
• 使用FIRST_VALUE替代LAST_VALUE：按TIMESTAMP1升序排列时，后续行的时间更晚，FIRST_VALUE(TIMESTAMP2 IGNORE NULLS)会提取当前行之后第一个非空的TIMESTAMP2值

修正后的SQL语句：

FIRST_VALUE(TIMESTAMP2 IGNORE NULLS) OVER (PARTITION BY "Group" ORDER BY TIMESTAMP1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

逻辑验证 #

• 第一行：后续第一个非空值是自身的6/16/22 7:26 AM，符合预期
• 第二至第五行：后续第一个非空值是第六行的5/11/23 3:17 AM，符合预期
• 第六、第七行：自身有非空值，直接返回自身值，符合预期
• 第八行：后续没有行，无有效非空值，返回NULL，符合预期

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