BigQuery中如何计算每位客户每日过去30天的滚动中位数

你的原代码存在两个核心问题导致性能低下：

• 窗口范围错误：使用unbounded preceding会取用户所有历史交易数据，而非过去30天，不符合需求。
• 数组处理低效：通过array_agg存储历史支出再unnest计算中位数，这种方式会产生大量中间数组，占用极高内存和计算资源，数据量大时必然变慢。

以下是针对BigQuery的高效解决方案，分精确计算和近似计算两种场景：

一、精确滚动30天中位数计算 #

直接利用PERCENTILE_CONT结合日期范围窗口，无需额外数组操作，BigQuery会对这类窗口计算做针对性优化：

SELECT
  user_id,
  date,
  -- 精确计算过去30天支出的中位数
  PERCENTILE_CONT(0.5) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_median_spend,
  -- 同步计算过去30天的平均支出（替代原代码的rolling_avg_spend）
  AVG(spend) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg_spend
FROM monthly_spend

关键说明： #

• RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW：精确限定窗口为当前日期及往前30天的所有数据，完全匹配需求。
• 避免了数组的存储和拆解操作，大幅降低中间数据量，减少资源消耗。

二、超大数据量下的近似中位数计算 #

如果数据量极大，精确计算仍有性能瓶颈，可以使用BigQuery提供的近似分位数函数APPROX_QUANTILES，它通过近似算法在保证误差可控的前提下，大幅提升计算速度：

SELECT
  user_id,
  date,
  -- 近似中位数（取第50个分位数，误差约1%以内）
  (APPROX_QUANTILES(spend, 100) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ))[OFFSET(50)] AS rolling_30d_approx_median_spend,
  AVG(spend) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg_spend
FROM monthly_spend

关键说明： #

• APPROX_QUANTILES(spend, 100)会返回包含101个元素的数组（0到100分位数），OFFSET(50)对应中位数。
• 该算法的时间和空间复杂度远低于精确计算，适合百万级以上的超大数据集，误差在大多数业务场景可接受。

三、按日聚合后的滚动计算（可选） #

如果需要先计算用户每日总支出，再基于每日总支出计算过去30天的中位数，可以先做日聚合：

WITH daily_spend AS (
  SELECT
    user_id,
    date,
    SUM(spend) AS daily_total_spend
  FROM monthly_spend
  GROUP BY user_id, date
)
SELECT
  user_id,
  date,
  PERCENTILE_CONT(0.5) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_median_daily_spend,
  AVG(daily_total_spend) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg_daily_spend
FROM daily_spend

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