如何删除BQ表中同日期重复记录并保留最新条目？

需求：删除BigQuery表中各团队同日期的重复记录，仅保留该团队当天时间戳最新的条目。例如以下示例数据中，需删除时间戳为2021-06-04 10:26:34.762000和2021-06-04 16:23:51.029000的记录，保留2021-06-04 16:39:04.428000的记录。

示例数据：

teamId  jiraboardId createddate                 backlogstorycount   backlogbugcount backlogchorecount   backlogtotalcount   lastupdateduser lastupdateddate                 UUID                                    storyPoint      bugPoint    chorePoint  totalPoint  
1       150     2021-06-04 10:26:34.762000 UTC  16                      0               1               17                  Xcelerate       2021-06-04 10:26:34.762000 UTC  57926a8e-32a3-4ba0-832f-df62bde18b4a    52              0           3   55  
1       150     2021-06-04 16:23:51.029000 UTC  16                      0               1               17                  Xcelerate       2021-06-04 16:23:51.029000 UTC  07075027-6f53-4089-8a15-66dfe45d8250    52              0           3   55  
1       150     2021-06-04 16:39:04.428000 UTC  16                      0               1               17                  Xcelerate       2021-06-04 16:39:04.428000 UTC  7b5be9ef-2a24-4c9f-846b-2a203a63e734    52              0           3   55  

用户尝试的无效查询：

delete 
 FROM `aaa.bb.jira_backlog` bl 
 LEFT JOIN 
  ( SELECT MAX(`createddate`) as createddate,backlogstorycount,backlogbugcount,backlogchorecount,backlogtotalcount,
    storyPoint,bugPoint,chorePoint,totalPoint FROM `aaa.bb.jira_backlog` b
    GROUP BY backlogstorycount,backlogbugcount,backlogchorecount,backlogtotalcount,
    storyPoint,bugPoint,chorePoint,totalPoint
  ) A
    ON bl.createddate = A.createddate
   AND bl.backlogstorycount = A.backlogstorycount
   AND bl.backlogbugcount = A.backlogbugcount
   AND bl.backlogchorecount = A.backlogchorecount
   AND bl.backlogtotalcount = A.backlogtotalcount
   AND bl.storyPoint = A.storyPoint 
   AND bl.bugPoint = A.bugPoint 
   AND bl.chorePoint = A.chorePoint  
   AND bl.totalPoint = A.totalPoint 
 WHERE A.createddate IS NULL;

问题分析 #

原查询的核心错误在于分组维度错误：需求是按**团队（teamId）+ 日期（createddate的日期部分）**分组保留最新记录，但原查询是按统计字段（backlogstorycount等）分组，这会导致两种问题：

1. 不同团队/不同日期但统计字段相同的记录会被错误归为一组，误删有效数据；
2. 同团队同日期但统计字段有变化的记录会被分成多组，无法正确去重。

正确解决方案 #

方案1：使用窗口函数标记需保留的记录

通过ROW_NUMBER()窗口函数，按团队+日期分区，给每条记录排序，仅保留排名第一（最新时间戳）的记录：

DELETE FROM `aaa.bb.jira_backlog`
WHERE UUID NOT IN (
  SELECT UUID
  FROM (
    SELECT 
      UUID,
      ROW_NUMBER() OVER (
        PARTITION BY teamId, DATE(createddate) 
        ORDER BY createddate DESC
      ) AS rn
    FROM `aaa.bb.jira_backlog`
  ) t
  WHERE rn = 1
);

方案2：通过EXISTS子查询删除非最新记录

直接删除所有存在同团队、同日期且时间戳更晚的记录：

DELETE FROM `aaa.bb.jira_backlog` bl
WHERE EXISTS (
  SELECT 1
  FROM `aaa.bb.jira_backlog` b
  WHERE b.teamId = bl.teamId
    AND DATE(b.createddate) = DATE(bl.createddate)
    AND b.createddate > bl.createddate
);

两种方案都能实现需求，方案1更直观，适合需要明确标记保留记录的场景；方案2语法更简洁，性能在数据量不大时表现相当。

内容的提问来源于stack exchange，提问作者gcpdev-guy