仅通过DML语句删除BigQuery中的重复记录

问题场景 #

在Google BigQuery生产环境中，无DDL（如CREATE TABLE、TRUNCATE）权限，需删除数据表prod.emp中的重复记录：

• 重复判定依据：id + fname组合
• 保留规则：每组中dt字段值最新的记录

原表数据：

id | fname  | age|  dt
1  | 'John' | 23 | '2024-10-16'
1  | 'John' | 31 | '2024-12-23'
1  | 'John' | 29 | '2025-02-08'
2  | 'Tony' | 25 | '2024-11-24'
2  | 'Tony' | 34 | '2025-01-06'
3  | 'Peter'| 42 | '2024-10-17'

期望结果：

id | fname  | age|  dt
1  | 'John' | 29 | '2025-02-08'
2  | 'Tony' | 34 | '2025-01-06'
3  | 'Peter'| 42 | '2024-10-17'

解决方案：单条DELETE语句实现 #

利用BigQuery支持在DML中使用窗口函数的特性，直接筛选出需要删除的重复记录：

DELETE FROM `prod.emp`
WHERE (id, fname, dt) IN (
  SELECT id, fname, dt
  FROM `prod.emp`
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id, fname ORDER BY dt DESC) > 1
)

逻辑说明 #

1. 子查询中通过ROW_NUMBER()窗口函数，按id和fname分组，每组内按dt降序排序
2. 标记出每组中行号大于1的记录（即非最新dt的重复项）
3. 外层DELETE语句删除这些标记出的记录，仅保留每组中行号为1的最新记录

安全验证建议 #

执行DELETE前，先通过SELECT语句预览将要删除的记录，确保逻辑正确：

SELECT *
FROM `prod.emp`
QUALIFY ROW_NUMBER() OVER (PARTITION BY id, fname ORDER BY dt DESC) > 1

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