如何编写自定义函数(UDF)获取列的最新非空值？

完全可以通过BigQuery的自定义SQL函数（UDF）封装这段重复的ANY_VALUE逻辑，实现你想要的简化写法。

方案1：创建持久化UDF #

如果需要重复使用这个函数，可以创建一个持久化的UDF：

CREATE OR REPLACE FUNCTION `你的项目ID.你的数据集ID.latest_non_null`(target_col ANY TYPE, timestamp_col INT64)
RETURNS ANY TYPE
AS (
  ANY_VALUE(target_col HAVING MAX IF(target_col IS NOT NULL, timestamp_col, NULL))
);

替换其中的你的项目ID.你的数据集ID为实际的项目和数据集路径即可。

方案2：定义临时UDF（一次性使用） #

如果只是临时使用，不想创建持久化函数，可以在查询的WITH子句中定义临时UDF：

WITH
  FUNCTION latest_non_null(target_col ANY TYPE, timestamp_col INT64)
  RETURNS ANY TYPE
  AS (
    ANY_VALUE(target_col HAVING MAX IF(target_col IS NOT NULL, timestamp_col, NULL))
  ),
  foo AS (
    SELECT 1 AS a, "hello" AS b, NULL AS c, 10 AS some_timestamp
    UNION ALL
    SELECT 1, NULL, "world", 11
    UNION ALL
    SELECT 2, "hi", "!", 99
  )
SELECT
  a,
  latest_non_null(b, some_timestamp) AS latest_b,
  latest_non_null(c, some_timestamp) AS latest_c
FROM
  foo
GROUP BY
  a

逻辑说明 #

这个UDF的逻辑和你原代码完全一致：

• 仅当目标列（比如b或c）不为NULL时，才用对应的时间戳参与最大值计算
• 通过ANY_VALUE ... HAVING MAX获取该组内时间戳最大的那个非NULL值

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