如何在BigQuery的LAG导航函数中忽略NULL值，获取最近非空值？

嘿，这个问题我碰到过好多次了——BigQuery的LAG()函数确实没办法直接跳过NULL值，但咱们换个思路，用LAST_VALUE()搭配IGNORE NULLS参数就能完美解决你要的「获取最近非NULL值」的需求！

LAG()是按固定行偏移取值，不管目标行是不是NULL；而LAST_VALUE()结合IGNORE NULLS可以自动忽略窗口内的空值，只追踪到当前行之前最后一个非空的position值，完全符合你的需求。

修改后的查询代码 #

WITH table1 AS (
 SELECT TIMESTAMP '2016-10-18 2:51:45' AS ts, 'AB4' AS packId, FALSE AS stat, 'x' AS position
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 2:54:11', 'AB4', FALSE, 'x'
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 3:01:17', 'AB4', FALSE, 'x'
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 3:05:42', 'AB4', TRUE, NULL
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 3:06:24', 'AB4', FALSE, 'x'
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 3:06:36', 'AB4', TRUE, 'y'
 UNION ALL
 SELECT TIMESTAMP '2016-10-18 3:07:41', 'AB4', FALSE, 'x'
)
SELECT 
 ts, 
 packId, 
 stat, 
 position,
 -- 获取当前行之前最近的非NULL position
 LAST_VALUE(position IGNORE NULLS) OVER (
   PARTITION BY packId 
   ORDER BY ts 
   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
 ) AS previous_position1,
 -- 获取当前行之前的第二个最近非NULL position（如果需要的话）
 NTH_VALUE(position IGNORE NULLS, 2) OVER (
   PARTITION BY packId 
   ORDER BY ts 
   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
 ) AS previous_position2
FROM table1

关键参数说明 #

• IGNORE NULLS：这是核心！添加这个参数后，函数会自动跳过窗口内所有NULL值，只考虑非空的position数据。
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：限定窗口范围是从当前分组的第一行，到当前行的前一行——这样就不会把当前行的position算进去，和原来LAG()的逻辑完全对齐。

效果验证 #

针对你示例中的NULL行（2016-10-18 3:05:42这条），previous_position1会自动取上一个非空值x，而不是返回NULL；后续的行也会一直追踪最近的非空position，哪怕中间隔了多行NULL。

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