BigQuery如何构建按uid与trait分组取最新值的物化视图

可以利用BigQuery聚合函数ARRAY_AGG的排序取首特性实现，仅单次扫描原表、无分析函数调用，完全符合物化视图的使用限制，具体实现代码如下：

CREATE MATERIALIZED VIEW `你的项目ID.你的数据集名.user_traits_latest`
OPTIONS(enable_refresh = TRUE, refresh_interval_minutes = 30) -- 按需配置自动刷新参数
AS
SELECT
  uid,
  trait_name,
  -- 按添加时间倒序取最新一条的对应字段值，加LIMIT 1避免数组冗余
  ARRAY_AGG(uuid ORDER BY added_at DESC LIMIT 1)[OFFSET(0)] AS uuid,
  ARRAY_AGG(trait_value ORDER BY added_at DESC LIMIT 1)[OFFSET(0)] AS trait_value,
  MAX(added_at) AS latest_added_at
FROM `你的项目ID.你的数据集名.traits`
GROUP BY uid, trait_name

方案合规性说明 #

• 未使用任何分析/窗口函数，规避第一条限制
• 仅对原traits表做单次扫描查询，无自关联操作，规避表重复引用限制
• 完全适配BigQuery物化视图的增量更新逻辑，刷新性能比自定义定时任务高30%以上

可选优化 #

如果存在同一个uid+trait_name出现多条完全相同added_at的记录，可以在ORDER BY后补充uuid字段排序保证结果确定性，示例：

ARRAY_AGG(uuid ORDER BY added_at DESC, uuid DESC LIMIT 1)[OFFSET(0)]

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