如何在BigQuery中仅删除特定ReportingDate的重复数据？

你的原SQL语句问题在于：子查询仅判断2023-09-15这个日期存在重复记录，但没有区分具体哪些行是重复项，导致所有该日期的记录都会被匹配到，最终会删除该日期的全部数据。

下面是两种正确的处理方式：

方式一：直接删除重复行（DML操作） #

通过窗口函数ROW_NUMBER()标记重复组内的行，仅保留每组的第一行，删除其他重复行：

DELETE FROM my_table
WHERE STRUCT(ReportingDate, Balance, Currency, Service) IN (
  SELECT STRUCT(ReportingDate, Balance, Currency, Service)
  FROM (
    SELECT 
      ReportingDate, Balance, Currency, Service,
      -- 按所有字段分组，给每组内的行编号
      ROW_NUMBER() OVER (PARTITION BY ReportingDate, Balance, Currency, Service ORDER BY ReportingDate) AS row_num
    FROM my_table
    WHERE ReportingDate = '2023-09-15'
  )
  WHERE row_num > 1
);

• PARTITION BY后的字段是判断重复的依据（这里因为是完全重复的记录，所以包含所有字段）
• row_num > 1表示只删除每组中除第一行外的重复项

方式二：创建新表替换原表（适合大表场景） #

如果你的表数据量较大，BigQuery的DML删除操作成本较高，可以用替换表的方式保留唯一记录：

CREATE OR REPLACE TABLE my_table
AS
SELECT * FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY ReportingDate, Balance, Currency, Service ORDER BY ReportingDate) AS row_num
  FROM my_table
)
-- 保留非目标日期的所有数据，以及目标日期每组的第一行
WHERE row_num = 1 OR ReportingDate != '2023-09-15';

如果全表只有2023-09-15存在重复，也可以用更简洁的写法：

CREATE OR REPLACE TABLE my_table
AS
SELECT DISTINCT * FROM my_table;

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