如何在Google BigQuery中基于唯一标识符删除重复行？原SQL方法无效

我明白你原来用标准SQL的那套删重复流程在BigQuery里跑不通——毕竟BigQuery的分布式架构和传统关系型数据库差异很大，临时表的生命周期、DML操作的语法都有自己的规则。下面给你几个实用的方案，适配不同的表规模和场景：

方案1：替换整个表（适合小表/非分区表，最简便） #

BigQuery里直接删除单条重复行的效率不如直接生成一个去重后的新表替换原表，这种方式代码简洁，执行效率也高：

-- 先备份原表，避免操作失误丢失数据
CREATE TABLE `your-project.your-dataset.Origination_backup` 
AS SELECT * FROM `your-project.your-dataset.Origination`;

-- 生成去重后的表替换原表
CREATE OR REPLACE TABLE `your-project.your-dataset.Origination`
AS
SELECT * EXCEPT(row_num)
FROM (
  SELECT *,
    -- 按Unique_ID分组，给每组的行编序号
    ROW_NUMBER() OVER (
      PARTITION BY Unique_ID 
      ORDER BY (SELECT NULL) -- 如果不关心保留哪一行，用这个；如果要保留最新/最早的，换成你的时间字段，比如ORDER BY create_time DESC
    ) AS row_num
  FROM `your-project.your-dataset.Origination`
)
WHERE row_num = 1; -- 只保留每组的第一行，实现去重

如果需要保留特定版本的重复行（比如最新创建的），把ORDER BY (SELECT NULL)改成你的时间字段排序即可，比如ORDER BY update_time DESC。

方案2：增量删除重复行（适合大分区表，减少数据重写） #

如果你的表是分区表（比如按日期分区），替换整个表成本太高，可以只针对存在重复行的分区做增量删除，用MERGE语句实现：

-- 先备份目标分区（可选，但建议做）
CREATE TABLE `your-project.your-dataset.Origination_partition_backup`
AS SELECT * FROM `your-project.your-dataset.Origination`
WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2024-01-01') AND TIMESTAMP('2024-01-31'); -- 替换成你要处理的分区范围

-- 用MERGE删除重复行
WITH duplicated_rows AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY Unique_ID 
      ORDER BY (SELECT NULL) -- 同样，可替换为特定排序字段
    ) AS row_num
  FROM `your-project.your-dataset.Origination`
  -- 可选：只筛选出有重复的分区，缩小处理范围
  WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2024-01-01') AND TIMESTAMP('2024-01-31')
)
MERGE INTO `your-project.your-dataset.Origination` AS target
USING duplicated_rows AS source
ON target.Unique_ID = source.Unique_ID
   AND target._PARTITIONTIME = source._PARTITIONTIME -- 匹配分区键
   AND source.row_num > 1 -- 只匹配重复的行（序号大于1的）
WHEN MATCHED THEN DELETE; -- 删除这些重复行

如果你的分区键不是时间，而是其他字段（比如region），把_PARTITIONTIME换成你的分区字段即可。

为啥你原来的标准SQL流程在BigQuery不生效？ #

• 临时表机制不同：BigQuery里用SELECT ... INTO创建的是会话级临时表，会话结束后就会被销毁，而且跨语句引用的规则和传统数据库不一样；
• 多表关联DELETE不支持：BigQuery的DELETE语句只支持单表操作，不能像传统SQL那样直接关联多个表删除；
• 效率问题：即使你调整语法适配临时表，这种“导出重复行→删除原重复行→导回去重行”的流程在BigQuery里效率极低，尤其是大表，远不如直接生成去重表的方式高效。

内容的提问来源于stack exchange，提问作者I.m.rich