在BigQuery中合并同ID行并忽略Null值，生成最终及历史结果

嘿，针对你这个合并同ID行、用非空值覆盖空值的需求，我给你两个精准的BigQuery方案，分别对应最终结果和历史版本快照：

一、获取最新合并状态 #

要拿到每个ID的最终合并结果，我们可以用LAST_VALUE窗口函数搭配*IGNORE NULLS*——这是核心，它能帮我们精准抓取每个列的最新非空值，再配合分组筛选出最新的时间行就行。

直接上SQL：

WITH ranked_data AS (
  SELECT
    id,
    col_1,
    col_2,
    updated,
    -- 按更新时间倒序排，让最新的行排在第一位
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated DESC) AS rn
  FROM
    `你的项目ID.你的数据集名称.你的表名称`
),
latest_values AS (
  SELECT
    id,
    -- 拉取col_1从最开始到当前的最新非空值
    LAST_VALUE(col_1 IGNORE NULLS) OVER (PARTITION BY id ORDER BY updated ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS col_1,
    -- 同理处理col_2
    LAST_VALUE(col_2 IGNORE NULLS) OVER (PARTITION BY id ORDER BY updated ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS col_2,
    updated
  FROM
    ranked_data
)
SELECT
  id,
  col_1,
  col_2,
  updated
FROM
  latest_values
WHERE
  rn = 1; -- 只保留最新时间对应的那条合并结果

执行后就能得到你想要的最终结果：

二、生成历史版本快照 #

如果需要每个时间点的合并状态（也就是到该日期为止的最新数据快照），只需要调整窗口函数的范围，让每一行都计算到当前行为止的最新非空值就行：

对应的SQL：

SELECT
  id,
  -- 计算到当前行为止，col_1的最新非空值
  LAST_VALUE(col_1 IGNORE NULLS) OVER (PARTITION BY id ORDER BY updated ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS col_1,
  -- 同理处理col_2
  LAST_VALUE(col_2 IGNORE NULLS) OVER (PARTITION BY id ORDER BY updated ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS col_2,
  updated
FROM
  `你的项目ID.你的数据集名称.你的表名称`
ORDER BY
  id, updated;

跑出来的结果就是每个日期的历史版本：

小提示 #

• 一定要确保updated列是可正确排序的日期类型，如果是字符串格式，记得用PARSE_DATE('%m/%d', updated)转成DATE类型再排序，避免出错
• 把SQL里的你的项目ID.你的数据集名称.你的表名称替换成你实际的项目、数据集和表名就可以直接用啦

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