如何使用DELETE语句删除BigQuery表中的指定重复行？

你的现有DELETE语句存在逻辑错误：它会把整个重复分组的所有行全部删除，而非保留一行。原因是子查询中生成的rn>1的行，只要主表行的分组键（Timestamp、column_1至column_4）与子查询行匹配，EXISTS条件就会成立，导致该分组下所有行（包括应该保留的rn=1的行）都被标记为删除。

正确的DELETE语句 #

由于表中存在全列重复的行（无唯一键区分），可以通过HASH函数匹配具体行，确保仅删除重复行、保留每组最新的一行：

DELETE FROM BIGQUERY_TABLE t
USING (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Timestamp, column_1, column_2, column_3, column_4
            ORDER BY Timestamp DESC
        ) AS rn
    FROM BIGQUERY_TABLE
    WHERE Timestamp > 1709424000000 AND Timestamp < 1709510400000
) d
WHERE t.Timestamp = d.Timestamp
  AND t.column_1 = d.column_1
  AND t.column_2 = d.column_2
  AND t.column_3 = d.column_3
  AND t.column_4 = d.column_4
  AND HASH(t) = HASH(d)
  AND d.rn > 1
AND t.Timestamp > 1709424000000 AND t.Timestamp < 1709510400000;

逻辑说明 #

1. USING子查询给每个重复分组的行分配行号：
   • PARTITION BY指定重复行的识别键（Timestamp+column_1至column_4）
   • ORDER BY Timestamp DESC确保保留每组中最新的一行（rn=1）
2. HASH(t) = HASH(d)用于精准匹配要删除的具体行（因为全列重复的行无法通过列值区分）
3. d.rn > 1指定仅删除重复的行，保留每组的第一行

验证方法 #

执行以下SELECT语句，可提前查看将要被删除的行，确认逻辑正确：

SELECT *
FROM BIGQUERY_TABLE t
USING (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Timestamp, column_1, column_2, column_3, column_4
            ORDER BY Timestamp DESC
        ) AS rn
    FROM BIGQUERY_TABLE
    WHERE Timestamp > 1709424000000 AND Timestamp < 1709510400000
) d
WHERE t.Timestamp = d.Timestamp
  AND t.column_1 = d.column_1
  AND t.column_2 = d.column_2
  AND t.column_3 = d.column_3
  AND t.column_4 = d.column_4
  AND HASH(t) = HASH(d)
  AND d.rn > 1
AND t.Timestamp > 1709424000000 AND t.Timestamp < 1709510400000;

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