如何在BigQuery中向前填充用户余额以计算每日总余额

问题 #

我在BigQuery中有一个userbalances表，存储用户每日余额数据，但仅当用户发生交易时才会添加记录；若用户当日无交易，则无对应记录。我需要从用户首次交易日起向后填充余额（直至余额再次变更），以此按balance_day分组计算每日总余额。

原始数据表 #

预期输出 #

2023-02-01 5.65
2023-02-02 5.65
……
2023-02-13 9.19  -- 用户123余额5.65 + 用户456余额3.54
2023-02-14 16.64 -- 用户123余额7.45 + 用户456余额3.54
……

我的思路与代码 #

我的思路分为四个步骤：

• usd_value_balances_per_day CTE：获取每个用户每日的余额
• all_users CTE：获取用户首次交易产生余额的日期（即用户在余额表中的第一条记录）
• all_dates CTE：生成2022-08-11至今的所有日期，但这里可能有错误，和all_users连接后会重复日期
• results CTE：从用户首次交易日起向前填充余额

WITH usd_value_balances_per_day AS(
SELECT
user_id,
balance
balance_day
FROM
userbalances
),

all_users AS(
select min(balance_day) as min_date, user_id
from usd_value_balances_per_day
group by user_id
),


all_dates AS(
select *
from all_users, unnest(generate_date_array('2022-08-11', current_date())) as balance_day
where balance_day >= min_date
),


results AS(
select balance_day, user_id, LAST_VALUE(balance IGNORE NULLS) OVER (PARTITION BY user_id ORDER BY balance_day
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance
from all_dates
left join usd_value_balances_per_day using (user_id, balance_day)
order by balance_day
)

select balance_day, SUM(balance) as total_balance
from results
group by balance_day
order by balance_day

修正方案 #

你的思路方向正确，但存在几个细节问题需要调整：

1. 修复语法错误 #

原代码中usd_value_balances_per_day CTE里，balance和balance_day字段之间缺少逗号，会直接导致语法报错。

2. 优化日期生成逻辑 #

原all_dates的写法虽能生成目标日期，但可以更简洁；同时窗口函数的范围参数是默认值，可省略不写，不影响功能。

修正后的完整代码 #

WITH user_balances AS (
  SELECT 
    UserID AS user_id, 
    balance_day, 
    balance 
  FROM userbalances
),
user_first_dates AS (
  SELECT 
    user_id, 
    MIN(balance_day) AS first_trade_date 
  FROM user_balances 
  GROUP BY user_id
),
all_user_dates AS (
  SELECT 
    u.user_id, 
    d.date AS balance_day 
  FROM user_first_dates u
  CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(u.first_trade_date, CURRENT_DATE())) d(date)
),
filled_balances AS (
  SELECT 
    a.balance_day,
    a.user_id,
    LAST_VALUE(b.balance IGNORE NULLS) OVER (
      PARTITION BY a.user_id 
      ORDER BY a.balance_day
    ) AS filled_balance
  FROM all_user_dates a
  LEFT JOIN user_balances b 
    ON a.user_id = b.user_id AND a.balance_day = b.balance_day
)
SELECT 
  balance_day, 
  SUM(filled_balance) AS total_balance
FROM filled_balances
GROUP BY balance_day
ORDER BY balance_day;

代码说明 #

• user_balances：统一字段命名，避免原表UserID与后续user_id的命名不一致问题。
• user_first_dates：精准获取每个用户的首次交易日期，确保只生成用户有余额后的有效日期。
• all_user_dates：通过CROSS JOIN UNNEST为每个用户生成从首次交易日到当前日期的完整日期序列，避免无效日期的生成。
• filled_balances：利用LAST_VALUE(balance IGNORE NULLS)窗口函数，按用户分组、日期排序，自动填充无交易日期的余额值（取最近一次有记录的余额）。
• 最后按日期分组求和，得到每日所有用户的总余额。

结果验证 #

以示例数据为例，2023-02-13的总余额为5.65+3.54=9.19，2023-02-14为7.45+3.54=16.64，完全符合预期。

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