如何在BigQuery中基于乘数实现连续的半自动化用户量预测？

核心思路是利用窗口函数+累计乘积实现连续依赖的计算逻辑——以最后一个真实用户值为起点，对后续的乘数进行累计相乘，再乘以初始真实值得到每一分钟的预测结果。

步骤1：模拟场景数据 #

WITH sample_data AS (
  SELECT TIMESTAMP('2024-05-20 00:00:00') AS minute, 100 AS user_value, 1.1 AS multiplier UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:01:00'), 110, 1.2 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:02:00'), NULL, 1.05 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:03:00'), NULL, 0.98 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:04:00'), NULL, 1.02
)

步骤2：完整计算逻辑 #

WITH sample_data AS (
  SELECT TIMESTAMP('2024-05-20 00:00:00') AS minute, 100 AS user_value, 1.1 AS multiplier UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:01:00'), 110, 1.2 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:02:00'), NULL, 1.05 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:03:00'), NULL, 0.98 UNION ALL
  SELECT TIMESTAMP('2024-05-20 00:04:00'), NULL, 1.02
),
-- 按最后一个真实值分组，后续预测行归为同一组
grouped_data AS (
  SELECT
    minute,
    user_value,
    multiplier,
    -- COUNT忽略NULL，因此最后一个真实值之后的行group_id相同
    COUNT(user_value) OVER (ORDER BY minute) AS group_id
  FROM sample_data
),
-- 获取每个分组的初始真实值（组内最后一个非NULL的user_value）
group_initial_values AS (
  SELECT
    group_id,
    MAX(user_value) AS initial_value
  FROM grouped_data
  GROUP BY group_id
),
-- 计算累计乘积并生成预测值
final_calculation AS (
  SELECT
    g.minute,
    CASE
      WHEN g.user_value IS NOT NULL THEN g.user_value
      -- 用对数转换实现累计乘积：SUM(LN(multiplier))等价于所有乘数的乘积取对数，EXP还原为原乘积
      ELSE gi.initial_value * EXP(SUM(LN(g.multiplier)) OVER (PARTITION BY g.group_id ORDER BY g.minute))
    END AS predicted_user_value,
    g.multiplier
  FROM grouped_data g
  JOIN group_initial_values gi ON g.group_id = gi.group_id
)
SELECT * FROM final_calculation

关键说明 #

1. 分组逻辑：用COUNT(user_value) OVER (ORDER BY minute)生成分组ID，最后一个真实值之后的所有预测行将被归为同一组，确保累计计算的范围正确。
2. 累计乘积实现：BigQuery没有直接的窗口累计乘积函数，因此通过LN()取对数、SUM()求和、EXP()还原的方式间接实现连续相乘。
3. 边界处理：如果数据中存在乘数为0或负数的情况，需要提前过滤或处理，否则LN()会返回错误。

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