BigQuery如何不使用CREATE OR REPLACE删除重复行仅留1条记录

以下方法均适配重复行数不固定（2-4条及以上）、所有重复行内容完全一致的场景，不需要依赖唯一主键字段。 #

方法1：临时表中转法（不改动原表结构/权限，适配所有规模表） #

官方推荐的CREATE OR REPLACE方案会重置原表的自定义权限、聚簇/分区属性，临时表中转可以完全规避这个问题：

1. 将去重后的全量唯一数据写入临时表，因为行内容完全一致，直接用DISTINCT即可，不需要窗口函数

CREATE OR REPLACE TABLE `项目ID.数据集.tmp_clean_data` AS
SELECT DISTINCT * FROM `项目ID.数据集.待去重目标表`;

2. 清空原表数据

TRUNCATE TABLE `项目ID.数据集.待去重目标表`;

3. 将临时表的干净数据回写原表，最后清理临时表

INSERT INTO `项目ID.数据集.待去重目标表`
SELECT * FROM `项目ID.数据集.tmp_clean_data`;

DROP TABLE IF EXISTS `项目ID.数据集.tmp_clean_data`;

方法2：MERGE直接删重（无需临时表，适配中小规模表） #

之前尝试CTE + ROW_NUMBER()没有生效，核心原因是排序逻辑错误：全字段重复场景下不需要用业务字段排序，用常量做排序锚点即可给重复行正确编号，配合MERGE语句直接删除多余重复行：

MERGE `项目ID.数据集.待去重目标表` t
USING (
  SELECT
    *,
    -- PARTITION BY 需覆盖表的所有字段，常量排序不依赖业务唯一键
    ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY (SELECT 1)) rn
  FROM `项目ID.数据集.待去重目标表`
) s
ON t.id = s.id AND t.name = s.name AND s.rn > 1
WHEN MATCHED THEN DELETE;

执行完成后每个重复分组只会保留1条有效记录。 #

方法3：分区表定向替换（成本最低，适配分区表场景） #

如果待去重表配置了分区规则，不需要全表扫描替换，仅针对存在重复数据的分区做去重重写即可，扫描成本可降低90%以上：

-- 替换前需保留原表的分区、聚簇配置
CREATE OR REPLACE TABLE `项目ID.数据集.待去重目标表`
PARTITION BY 分区字段名
CLUSTER BY 聚簇字段名 -- 无聚簇可省略
AS
SELECT DISTINCT * FROM `项目ID.数据集.待去重目标表`
WHERE 分区字段名 = '存在重复数据的分区值';

操作前校验提示：执行去重前先运行以下SQL确认重复量，避免误操作：

SELECT 
  COUNT(*) AS 总行数,
  COUNT(DISTINCT FORMAT('%t', t)) AS 去重后行数,
  COUNT(*) - COUNT(DISTINCT FORMAT('%t', t)) AS 待删除重复行数
FROM `项目ID.数据集.待去重目标表` t

待删除重复行数符合预期时再执行去重操作。

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