如何为同窗口所有记录获取一致的非空首尾值？

为什么会出现这个结果？ #

1. 第一行first_valid_value为空的原因 #

当窗口函数搭配ORDER BY使用时，默认的窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——也就是只包含分区起始行到当前行的记录。第一行的value本身是NULL，且这个范围内没有其他非空值，哪怕加了IGNORE NULLS，也找不到有效取值，所以返回NULL。

2. 第二行last_valid_value不是"Bad"的原因 #

同样是默认窗口范围的限制：第二行的窗口范围只包含前两行（当前行及之前的行），这个范围内最后一个非空值是"Good"；只有第三行的窗口范围才覆盖全部三行，所以能取到"Bad"。

实现预期结果的方法 #

要让窗口函数覆盖整个分区的所有记录，需要显式指定窗口范围为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这样不管当前行在哪个位置，都能获取到整个分区内的首个/最后一个非空值。

修改后的SQL查询如下：

WITH sample_data AS (
  SELECT 1 AS id, NULL AS value, CURRENT_TIMESTAMP() AS update_ts
  UNION ALL
  SELECT 1, "Good", TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  UNION ALL
  SELECT 1, "Bad", TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
)
SELECT
  id,
  value,
  FIRST_VALUE(value IGNORE NULLS) OVER (ids) AS first_valid_value,
  LAST_VALUE(value IGNORE NULLS) OVER (ids) AS last_valid_value,
  update_ts
FROM sample_data
WINDOW ids AS (
  PARTITION BY id 
  ORDER BY update_ts DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 显式指定全分区范围
)

执行后会得到你预期的结果：

另外，也可以用MAX()/MIN()结合窗口函数达到类似效果，但FIRST_VALUE/LAST_VALUE配合全窗口范围更贴合你的需求场景。

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