如何在BigQuery中高效删除重复行（保留最早CreatedAt记录）

针对你描述的场景——除CreatedAt外其余列完全重复，需要删除时间戳较晚的重复行，以下是两种高效的处理方案，尤其适用于上万条重复行的情况：

方案一：创建去重后的新表（推荐） #

BigQuery的列存储特性决定了，创建新表的性能远优于原地删除操作，适合大数据量场景。通过窗口函数标记每组重复行的顺序，保留最早的记录：

-- 替换为你的项目、数据集和表名
CREATE OR REPLACE TABLE `your-project.your-dataset.target-table`
AS
SELECT id, CreatedAt, value1, value2  -- 列出所有需要保留的列（或用*，但建议显式列名）
FROM (
  SELECT *,
         -- 按除CreatedAt外的重复列分区，按CreatedAt升序排序，标记行号
         ROW_NUMBER() OVER (
           PARTITION BY id, value1, value2 
           ORDER BY CreatedAt ASC
         ) AS row_num
  FROM `your-project.your-dataset.source-table`
)
WHERE row_num = 1;  -- 只保留每组的第一条（最早时间戳）

注意： #

• PARTITION BY后需列出所有判断重复的列（即除CreatedAt外的列），确保分组准确。
• 操作前建议先备份原表：CREATE TABLE your-project.your-dataset.backup-tableAS SELECT * FROMyour-project.your-dataset.source-table;

方案二：原地删除重复行 #

如果必须在原表上操作，可使用DELETE语句结合聚合或关联查询删除晚于最早时间的重复行：

方式1：通过MIN(CreatedAt)匹配保留记录 #

DELETE FROM `your-project.your-dataset.your-table`
WHERE STRUCT(id, value1, value2, CreatedAt) NOT IN (
  SELECT AS STRUCT id, value1, value2, MIN(CreatedAt)
  FROM `your-project.your-dataset.your-table`
  GROUP BY id, value1, value2
);

方式2：通过关联查询判断重复 #

DELETE FROM `your-project.your-dataset.your-table` t1
WHERE EXISTS (
  SELECT 1
  FROM `your-project.your-dataset.your-table` t2
  -- 匹配重复列
  WHERE t2.id = t1.id
    AND t2.value1 = t1.value1
    AND t2.value2 = t1.value2
    -- 当前记录的时间戳晚于同组的其他记录
    AND t2.CreatedAt < t1.CreatedAt
);

注意： #

• 原地删除在大数据量下性能不如创建新表，且会产生更多的元数据操作，成本更高。
• 若表开启了分区或集群，删除操作可能需要更久的时间。

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