Google BigQuery中按最新ingestion_time获取各Element的首个非null值

针对你的需求，这里提供两种可靠的BigQuery SQL实现方案，都能精准获取每个Element对应的最新非null tmed值（关联最新的ingestion_time），如果某个Element的tmed全为null，则返回最新ingestion_time对应的null值。

方法一：使用ROW_NUMBER()窗口函数 #

通过分组排序标记优先级，直接筛选目标记录：

WITH ranked_records AS (
  SELECT
    Element,
    tmed,
    ingestion_time,
    ROW_NUMBER() OVER (
      PARTITION BY Element
      ORDER BY 
        CASE WHEN tmed IS NOT NULL THEN 0 ELSE 1 END,
        ingestion_time DESC
    ) AS rn
  FROM your_table_name -- 替换为你的实际表名
)
SELECT
  Element,
  tmed,
  ingestion_time
FROM ranked_records
WHERE rn = 1;

逻辑说明：

• 按Element分组后，先用CASE语句把tmed非null的记录优先级设为0（排在前面），null的设为1（排在后面）
• 再按ingestion_time降序排列，确保同优先级里最新的记录排在最前
• 最后筛选每个组里序号为1的记录，正好是符合要求的目标数据

方法二：使用LAST_VALUE()配合IGNORE NULLS #

利用窗口函数直接提取最新非null值，同时获取最新时间：

SELECT DISTINCT
  Element,
  LAST_VALUE(tmed IGNORE NULLS) OVER (
    PARTITION BY Element
    ORDER BY ingestion_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS tmed,
  MAX(ingestion_time) OVER (PARTITION BY Element) AS ingestion_time
FROM your_table_name -- 替换为你的实际表名

逻辑说明：

• LAST_VALUE(tmed IGNORE NULLS)会忽略null值，在每个Element分组内按时间顺序提取最后出现的非null tmed值；如果全为null，该函数会返回null
• MAX(ingestion_time)直接获取每个组的最新时间，确保时间字段符合要求
• 用DISTINCT去重，得到每个Element唯一的一条结果

关于ANY_VALUE的问题 #

ANY_VALUE本身是无序聚合函数，BigQuery不保证它会按时间顺序取值，所以无法依赖它获取最新的非null值，改用上述窗口函数方案可以明确控制排序逻辑，确保结果符合预期。

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