BigQuery中：ID重复时将foo列设为Null并保留单条记录

可以借助窗口函数统计每个ID的出现次数，再据此修改foo列并完成去重，具体SQL语句如下：

CREATE OR REPLACE TABLE `你的项目名.你的数据集名.新表名` AS
WITH id_count_cte AS (
  SELECT
    id,
    foo,
    bar,
    -- 统计每个ID的总出现次数
    COUNT(*) OVER (PARTITION BY id) AS id_total,
    -- 给每个ID的记录分配序号，用于后续取单条记录
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY foo) AS row_num
  FROM `你的项目名.你的数据集名.原表名`
)
SELECT
  id,
  -- 若ID重复则将foo设为null，否则保留原foo值
  CASE WHEN id_total > 1 THEN NULL ELSE foo END AS foo,
  bar
FROM id_count_cte
WHERE row_num = 1; -- 每个ID仅保留一条记录

关键说明： #

• COUNT(*) OVER (PARTITION BY id)：精准统计每个ID在表中的出现次数，以此判断是否为重复ID
• ROW_NUMBER() OVER (PARTITION BY id ORDER BY foo)：给同一ID下的记录排序，这里按foo排序是为了优先保留原本foo为null的记录（和示例结果匹配）；如果不需要特定保留顺序，可改为ORDER BY NULL
• 若同一ID下的bar列存在不同值，可调整ORDER BY后的字段，指定保留哪一条的bar值

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