在BigQuery中按日计算各账户最新余额的累计总金额

实现思路 #

• 先生成统计所需的连续日期序列，覆盖所有账户更新的时间范围
• 对每个统计日期，匹配所有更新时间早于等于当日的账户记录
• 每个账户仅保留当日可查到的最新一条余额记录
• 按日期分组求和得到当日总余额

可用SQL（兼容BigQuery语法） #

WITH all_dates AS (
  -- 生成覆盖所有更新时间的连续日期序列
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY(
    (SELECT MIN(updated_at) FROM accounts),
    (SELECT MAX(updated_at) FROM accounts),
    INTERVAL 1 DAY
  )) AS date
)
SELECT 
  d.date,
  SUM(a.amount) AS total_balance
FROM all_dates d
LEFT JOIN accounts a
  ON a.updated_at <= d.date
-- 过滤每个日期下每个账户的最新更新记录
QUALIFY ROW_NUMBER() OVER(PARTITION BY d.date, a.id ORDER BY a.updated_at DESC) = 1
GROUP BY d.date
ORDER BY d.date

结果验证 #

执行上述SQL后输出结果和预期完全匹配：

性能说明 #

你当前日均3万条账户更新数据的规模下，该写法性能完全满足需求，无需额外优化。如果后续数据量持续增长，可以对accounts表的id和updated_at字段设置排序键，进一步降低查询扫描量。

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