BigQuery使用ROW_NUMBER去重及保留最新时间戳记录的优化咨询

现有写法问题修正 #

你当前写的窗口函数逻辑存在疏漏：ROW_NUMBER() OVER (PARTITION BY ID) 没有指定排序规则，数据库返回的row_number=1的记录是随机的，无法保证拿到的是UPDATED_AT最新的条目，首先需要修正为：

SELECT * EXCEPT(row_number)
FROM (
  SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY ID ORDER BY UPDATED_AT DESC) AS row_number
  FROM employees 
)
WHERE row_number = 1

更高效的实现方案 #

可以根据你使用的数据库类型，选择性能更优、写法更简洁的方案：

• 方案1：QUALIFY 语法简化（支持数据库：BigQuery、Snowflake、Spark SQL、PostgreSQL 13+等）
  无需嵌套子查询，执行效率和修正后的窗口函数一致，但代码更简洁：

  SELECT *
  FROM employees
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY UPDATED_AT DESC) = 1
  

• 方案2：聚合关联查询（性能最优，适合有索引场景）
  如果你的employees表已经建了(ID, UPDATED_AT DESC)的联合索引，这个方案的性能会远高于窗口函数方案，因为不需要全表扫描后再分组排序，只需要先聚合拿到每个ID的最大更新时间，再关联回原表取全字段即可：

  SELECT e.*
  FROM employees e
  INNER JOIN (
    SELECT ID, MAX(UPDATED_AT) AS max_updated
    FROM employees
    GROUP BY ID
  ) t ON e.ID = t.ID AND e.UPDATED_AT = t.max_updated
  

  注意：如果同一个ID存在两条完全相同UPDATED_AT的记录，这个写法会返回两条重复ID的数据，如果需要强制去重，可以搭配DISTINCT或者加额外的排序规则处理。

• 方案3：MySQL 5.x等不支持窗口函数的版本兼容方案
  可以用关联子查询实现，效率和方案2接近：

  SELECT *
  FROM employees e
  WHERE UPDATED_AT = (
    SELECT MAX(UPDATED_AT)
    FROM employees
    WHERE ID = e.ID
  )
  

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