BigQuery中SQL如何实现表连接后仅保留最新行

核心思路是使用ROW_NUMBER()窗口函数按目标维度分组排序后取最新值，可在单查询内完成处理，额外开销极低，是该场景下性能最优的方案。

具体实现逻辑 #

• 分组维度设为tenancy_id + my_date，保证每个租户、每个统计日期单独排序
• 排序维度设为event_date倒序，同组内最新的事件日期排在最前
• 过滤仅保留每组排序为1的行，即可得到每个租户每个统计日期对应的最新事件记录
• 若不需要保留原冗余表数据，推荐直接用查询结果生成新表替代原表，比执行DML删除操作成本更低

示例代码 #

WITH ranked_expand_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY tenancy_id, my_date 
      ORDER BY event_date DESC
    ) AS row_rank
  FROM 你的展开后的表名 -- 此处替换为你的实际表名，也可以直接嵌套你原来的交叉连接查询逻辑，避免先落地再处理的IO开销
)
SELECT * EXCEPT(row_rank)
FROM ranked_expand_table
WHERE row_rank = 1

特殊场景调整 #

如果存在tenancy_id+my_date+event_date完全相同的重复行，可以把ROW_NUMBER()替换为RANK()，或者在排序规则中补充额外字段（比如事件写入时间戳）做更精确的排序区分。

如果确实需要直接删除原表中的冗余行，可使用以下DML语句：

DELETE FROM 你的展开后的表名 t
WHERE EXISTS (
  SELECT 1
  FROM 你的展开后的表名 t2
  WHERE t.tenancy_id = t2.tenancy_id 
    AND t.my_date = t2.my_date 
    AND t2.event_date > t.event_date
)

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