Databricks中Window函数ignore nulls失效，求等效Spark SQL查询

等效Spark SQL语句 #

SELECT 
  ID,
  FIRST_VALUE(col1) IGNORE NULLS OVER (PARTITION BY ID ORDER BY hn) AS first_value,
  LAST_VALUE(col1) IGNORE NULLS OVER (PARTITION BY ID ORDER BY hn ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM your_table_name;

关键差异说明 #

1. IGNORE NULLS语法位置：Spark SQL要求IGNORE NULLS紧跟在窗口函数名之后（而非Snowflake那样放在函数参数后），这是语法兼容的核心调整。
2. LAST_VALUE的窗口范围：Spark中LAST_VALUE默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，只会取到当前行及之前的最后一个非NULL值。要匹配Snowflake中取整个分区最后一个非NULL值的行为，必须显式指定覆盖全分区的范围：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

执行上述语句后，输出结果将与Snowflake完全一致：

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