BigQuery无rowId时删除ts早于120分钟的重复ad_id旧行方案咨询

原方案存在的问题 #

• 语法问题：DELETE TOP不符合BigQuery的DELETE语法规范，BigQuery的DELETE语句不需要加TOP关键字
• 场景限制：仅支持每个ad_id最多2条重复的场景，若同一ad_id重复超过2次，只能删掉最旧的1条，无法清理所有历史重复旧行
• 性能问题：关联子查询的逐行匹配逻辑，在数据量较大时执行效率较低

更优雅的实现方案 #

使用BigQuery原生支持的窗口函数+QUALIFY语法实现，逻辑最简洁、适配场景更广、性能更优：

DELETE FROM `{table_full_name}`
WHERE 
  -- 满足时间早于当前120分钟的条件
  timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)
  -- 筛选出同ad_id下不是最新的所有旧重复行
  QUALIFY ROW_NUMBER() OVER(PARTITION BY ad_id ORDER BY timestamp DESC) > 1

兼容写法（不使用QUALIFY） #

如果对QUALIFY语法不熟悉，也可以用子查询实现相同逻辑：

DELETE FROM `{table_full_name}` t
WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)
AND EXISTS (
  SELECT 1 FROM (
    SELECT 
      ad_id,
      timestamp,
      ROW_NUMBER() OVER(PARTITION BY ad_id ORDER BY timestamp DESC) AS rn
    FROM `{table_full_name}`
  ) i
  WHERE i.ad_id = t.ad_id AND i.timestamp = t.timestamp AND i.rn > 1
)

方案优势 #

• 适配任意重复次数：不管同一个ad_id下有多少条重复记录，都会自动保留最新的1条，删除所有更早的旧重复行
• 性能更优：BigQuery对窗口函数有深度优化，批量计算的效率远高于逐行关联的子查询
• 易维护：如果后续需要调整规则，比如保留同ad_id下最新的2条记录，只需要把>1改成>2即可，修改成本极低
• 语法标准：完全符合BigQuery的语法规范，没有兼容问题

操作提示 #

执行DELETE前建议先运行查询确认待删除数据是否符合预期，避免误删：

SELECT * FROM `{table_full_name}`
WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)
QUALIFY ROW_NUMBER() OVER(PARTITION BY ad_id ORDER BY timestamp DESC) > 1

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