BigQuery按user_id与相同created_at分组取最新updated_at行的实现

你现有代码仅按user_id单字段分组，只能取到每个用户全局最新的一条记录，不符合按相同created_at分组筛选的要求，仅需调整分组维度即可实现需求。

方案1：基于你原有ARRAY_AGG写法调整 #

仅需要在GROUP BY子句中追加created_at字段，按「用户ID + 创建时间」的组合分组即可：

SELECT
  agg.table.*
FROM (
  SELECT
    user_id,
    created_at,
    ARRAY_AGG(STRUCT(table)
    ORDER BY
      updated_at DESC)[SAFE_OFFSET(0)] agg
  FROM
    `dataset.my_table` table
  GROUP BY
    user_id, created_at)

方案2：窗口函数写法（可读性更高） #

也可以使用ROW_NUMBER窗口函数实现，逻辑更直观：

WITH ranked_records AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, created_at 
      ORDER BY updated_at DESC
    ) AS rn
  FROM `dataset.my_table`
)
SELECT * EXCEPT(rn)
FROM ranked_records
WHERE rn = 1

该方案先按user_id和created_at对数据分区，每个分区内按updated_at倒序编号，编号为1的就是该分组下最新的记录，最后过滤掉编号字段即可得到结果。

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