如何基于时间戳删除BigQuery中ad_id重复的旧行，是否有更优实现

你当前的实现仅能处理同一个ad_id最多2条重复的场景，如果同一个ad_id存在3条及以上的重复旧行，仅会删除时间最小的1条，其余旧重复行无法被清理，且相关子查询+GROUP BY的写法在数据量大时执行效率较低，以下是两种优化方案：

方案1：DML DELETE写法（兼容原删除逻辑，支持多重复行场景） #

DELETE FROM `{table_full_name}` o
WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)
AND EXISTS (
  SELECT 1 FROM `{table_full_name}` i
  WHERE i.ad_id = o.ad_id
  AND i.timestamp > o.timestamp
)

该写法用半连接EXISTS判断当前行是否存在同ad_id的更新行，只要满足条件且时间早于2小时前就会被删除，不受重复行数量限制。BigQuery对这类半连接的执行优化更好，数据量越大性能提升越明显。

方案2：表替换写法（更适配BigQuery架构，成本更低速度更快） #

BigQuery为列式存储架构，DELETE这类DML操作会产生额外的修改日志和临时副本开销，对于大表的定期清理场景，推荐直接用CREATE OR REPLACE TABLE替换原表，资源开销更低。

你可以先执行以下查询验证清理结果是否符合预期：

SELECT * FROM `{table_full_name}`
QUALIFY 
  -- 每个ad_id的最新行永久保留
  ROW_NUMBER() OVER (PARTITION BY ad_id ORDER BY timestamp DESC) = 1
  -- 其余行如果时间在近2小时内也保留
  OR timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)

确认结果正确后执行替换操作即可：

CREATE OR REPLACE TABLE `{table_full_name}` AS
SELECT * FROM `{table_full_name}`
QUALIFY 
  ROW_NUMBER() OVER (PARTITION BY ad_id ORDER BY timestamp DESC) = 1
  OR timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE)

如果存在同一个ad_id的两行timestamp完全相同的场景，可以调整窗口函数的排序规则，比如加上value排序ORDER BY timestamp DESC, value DESC；如果需要保留所有同最新时间的行，把ROW_NUMBER()改成RANK()即可。

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