BigQuery实现用户当日无收入记录时填充前一日累计值

1. 首日用户批量复制到所有后续日期的实现 #

实现逻辑很简单：先分别取出全表所有不重复的日期、首日（即表中最小日期）出现的全部用户，通过笛卡尔积生成所有「日期+首日用户」的配对组合，再左关联原表匹配收入字段，匹配不到的记录收入自动为null，就能得到你需要的中间表。
参考代码如下，把占位符替换成你实际的表名即可：

WITH
-- 提取全表所有不重复日期
all_dates AS (
  SELECT DISTINCT date FROM 你的收入表名
),
-- 提取首日出现的所有用户
first_day_users AS (
  SELECT DISTINCT user_id
  FROM 你的收入表名
  WHERE date = (SELECT MIN(date) FROM 你的收入表名)
),
-- 生成日期+用户骨架，关联原表补全收入字段
mid_table AS (
  SELECT
    d.date,
    u.user_id,
    t.revenue
  FROM all_dates d
  -- 笛卡尔积完成所有日期和首日用户的配对
  CROSS JOIN first_day_users u
  LEFT JOIN 你的收入表名 t
    ON d.date = t.date AND u.user_id = t.user_id
)
SELECT * FROM mid_table

2. BigQuery更优实现方案 #

你最初写的LAST_VALUE写法有两个问题：

• 没有按user_id分区，会把不同用户的收入数据混在一起计算
• 没有指定排序规则和窗口范围，无法保证取到的是对应用户之前最近日期的累计收入

实际上不需要单独落地中间表，生成日期用户骨架后直接搭配正确的窗口函数，就能一步拿到最终结果。BigQuery对带IGNORE NULLS的窗口函数优化非常成熟，执行效率很高，完整代码如下：

WITH
all_dates AS (
  SELECT DISTINCT date FROM 你的收入表名
),
first_day_users AS (
  SELECT DISTINCT user_id
  FROM 你的收入表名
  WHERE date = (SELECT MIN(date) FROM 你的收入表名)
)
SELECT
  d.date,
  u.user_id,
  -- 按用户分区、日期升序，取当前行之前最近的非空收入值填充
  LAST_VALUE(t.revenue IGNORE NULLS) OVER (
    PARTITION BY u.user_id
    ORDER BY d.date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS revenue
FROM all_dates d
CROSS JOIN first_day_users u
LEFT JOIN 你的收入表名 t
  ON d.date = t.date AND u.user_id = t.user_id
ORDER BY d.date, u.user_id

这个方案所有计算在一次查询内完成，笛卡尔积的两个输入（去重后日期、首日用户）数据量都极小，整体执行成本非常低。如果后续需要扩展为保留所有出现过的用户（不止首日用户），只需要把first_day_users的CTE替换成全表去重用户即可，其余逻辑不需要改动。

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