如何用BigQuery SQL按日获取每个用户的最新余额（高效方案）

核心查询语句 #

针对900GB规模的大表，使用窗口函数ROW_NUMBER()实现高效的每日用户最新余额提取：

WITH daily_balance_ranked AS (
  SELECT
    timestamp,
    id,
    balance,
    -- 按用户+日期分区，区内按时间戳倒序排名，最新记录排第1
    ROW_NUMBER() OVER (
      PARTITION BY id, DATE(timestamp)
      ORDER BY timestamp DESC
    ) AS rn
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的源表路径
)
SELECT
  timestamp,
  id,
  balance
FROM
  daily_balance_ranked
WHERE
  rn = 1 -- 筛选每个用户每日的最新记录
ORDER BY
  DATE(timestamp), id;

逻辑说明 #

1. 分区规则：通过PARTITION BY id, DATE(timestamp)将数据按用户ID和日期拆分，确保只在同一用户的单日数据内进行排序
2. 排序规则：ORDER BY timestamp DESC让同一分区内最新的记录排在最前面，ROW_NUMBER()会给这条记录标记为rn=1
3. 结果筛选：通过WHERE rn=1提取每个用户每日的最新余额记录

大表优化建议 #

针对900GB的数据集，以下优化措施可以显著提升查询效率和扩展性：

• 分区表改造：将源表按DATE(timestamp)设置为分区表，BigQuery会自动跳过未涉及的分区，减少扫描数据量
• 聚类配置：对id字段添加聚类，让同一用户的数据在分区内物理聚集，降低窗口函数计算时的数据 shuffle 开销
• 字段裁剪：仅保留查询所需的timestamp、id、balance字段，避免冗余数据的加载和处理
• 查询缓存利用：开启BigQuery查询缓存，重复执行相同查询时直接复用缓存结果，节省计算资源
• 分批查询（可选）：处理全量历史数据时，可按日期范围（如按月）拆分查询，避免单次查询占用过多资源

示例验证 #

将你提供的示例数据代入查询后，输出结果与期望完全一致：

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