BigQuery无时间戳时删除col1重复数据保留最后插入行

可行解决方案 #

你可以利用你当前查询全表默认按插入时间从旧到新返回的特性，先给全表生成全局递增的行号（行号越大代表插入时间越晚），再按col1分组取每组行号最大的记录即可：

1. 查询去重后的结果

WITH all_rows_with_insert_order AS (
  -- 按默认查询顺序生成全局行号，映射插入先后顺序
  SELECT *, ROW_NUMBER() OVER () AS global_insert_rank
  FROM `你的项目ID.你的数据集名.你的表名`
),
ranked_by_col1 AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY global_insert_rank DESC) AS rn
  FROM all_rows_with_insert_order
)
SELECT col1, col2, col3
FROM ranked_by_col1
WHERE rn = 1;

2. 直接删除原表重复行（覆盖原表）

如果确认结果符合预期，可以直接用以下语句覆盖原表完成重复行删除：

CREATE OR REPLACE TABLE `你的项目ID.你的数据集名.你的表名` AS
WITH all_rows_with_insert_order AS (
  SELECT *, ROW_NUMBER() OVER () AS global_insert_rank
  FROM `你的项目ID.你的数据集名.你的表名`
),
ranked_by_col1 AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY global_insert_rank DESC) AS rn
  FROM all_rows_with_insert_order
)
SELECT col1, col2, col3
FROM ranked_by_col1
WHERE rn = 1;

注意事项 #

• 该方案仅适用于你的表未执行过存储重写操作的场景：如果你对表执行过UPDATE/DELETE/MERGE等DML操作、表优化、分区聚类调整、数据重加载等操作，会改变数据物理存储顺序，导致默认查询顺序与实际插入顺序不一致，此时该方案的结果会出现偏差。
• 长期使用建议新增默认值为CURRENT_TIMESTAMP()的插入时间字段，可完全规避存储顺序变动带来的问题，后续数据运维的稳定性更高。

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