BigQuery通过rank()函数删除表内重复行的实现方案

• 存在语法错误：子查询中引用了未定义的表别名r，语句本身无法正常执行。
• 逻辑判断错误：EXISTS子句仅通过name字段关联，只要某个业务主键下存在非最新版本的记录，该主键对应的**所有行（包括需要保留的最新版本行）**都会匹配到删除条件，最终触发全表删除。
• 未利用分区特性：直接全表计算窗口函数，大表场景下扫描量过高，没有发挥分区表的裁剪优势。

针对200GB以上的分区表，优先选择分区级操作，避免全表扫描，远比重建全表的成本低。

方案1：增量DELETE（日常增量同步后首选） #

核心逻辑是仅删除同业务主键下存在更新版本的旧记录，搭配分区裁剪限制扫描范围，每次仅处理同步涉及的分区，扫描量极低：

-- 定义本次需要去重的分区日期，日常同步后可指定为当日/T-1日的分区
DECLARE target_partition DATE DEFAULT DATE('2022-07-10');

DELETE
FROM `project.dataset.sample` ori
WHERE
  -- 强制分区裁剪，仅扫描目标分区，大表场景必须添加，避免全表扫描
  DATE(ori.process_timestamp) = target_partition
  AND EXISTS (
    SELECT 1
    FROM `project.dataset.sample` newer
    WHERE
      newer.name = ori.name
      -- 同业务主键下存在时间戳更大的更新记录，说明当前行是冗余旧数据
      AND newer.process_timestamp > ori.process_timestamp
      AND DATE(newer.process_timestamp) = target_partition
  );

如果是首次执行全表去重，可先将所有业务主键对应的最新记录标识存入临时表，再执行删除，避免重复计算窗口函数：

-- 临时表存储所有需要保留的最新记录唯一标识
CREATE TEMP TABLE keep_latest AS
SELECT
  name,
  MAX(process_timestamp) AS latest_process_time
FROM `project.dataset.sample`
GROUP BY name;

-- 删除所有非最新版本的冗余记录
DELETE
FROM `project.dataset.sample` ori
WHERE NOT EXISTS (
  SELECT 1
  FROM keep_latest k
  WHERE
    ori.name = k.name
    AND ori.process_timestamp = k.latest_process_time
);

注意：如果业务上存在同主键、同process_timestamp的完全重复行，可使用ROW_NUMBER()窗口函数给重复行加唯一序号，仅保留序号为1的记录即可，避免RANK()导致的同排名多记录问题。

方案2：分区重写（单分区重复率高于50%时首选） #

如果单个分区内重复数据占比很高，用DELETE删除冗余数据的开销高于直接重写整个分区，此时可仅重写目标分区，不影响其他分区的存量数据：

-- 1. 先将目标分区去重后的数据存入临时表
CREATE TEMP TABLE dedup_data AS
SELECT name, process_timestamp, amount
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY process_timestamp DESC) AS rn
  FROM `project.dataset.sample`
  -- 仅扫描需要处理的目标分区
  WHERE DATE(process_timestamp) = '2022-07-10'
)
WHERE rn = 1;

-- 2. 清空目标分区的原有数据
DELETE FROM `project.dataset.sample`
WHERE DATE(process_timestamp) = '2022-07-10';

-- 3. 将去重后的数据写回目标分区
INSERT INTO `project.dataset.sample`
SELECT * FROM dedup_data;

以上两种方案都不会扫描全表数据，相比CREATE OR REPLACE全表重建的方式，计算成本可降低90%以上，适配TB级大表的去重需求。

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