BigQuery SQL实现按用户分组计算近5天收入滚动中位数

完全可以通过BigQuery SQL实现该需求，核心通过窗口函数定义滑动时间窗口，搭配中位数计算函数即可完成。

核心SQL代码 #

假设你的源表名为user_revenue，date字段为DATE类型，参考SQL如下：

SELECT
  date,
  user_id,
  revenue,
  -- 计算当前行所属用户近5天（含当天）收入的连续中位数
  PERCENTILE_CONT(0.5) OVER(
    PARTITION BY user_id
    ORDER BY UNIX_DATE(date)
    RANGE BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS median_last_5_days
FROM
  `user_revenue`

如果你需要的是离散中位数，把PERCENTILE_CONT替换为PERCENTILE_DISC即可。

逻辑说明 #

• 窗口分区PARTITION BY user_id：所有计算按用户独立分组，互不干扰
• 排序规则ORDER BY UNIX_DATE(date)：把日期转换为整数天戳，方便后续按天数定义窗口范围
• 窗口帧RANGE BETWEEN 4 PRECEDING AND CURRENT ROW：限定窗口范围为当前日期往前推4天到当前日期，刚好覆盖5天的时间区间，自动处理日期不连续的场景，比按行偏移的写法更符合业务逻辑
• 中位数函数：PERCENTILE_CONT(0.5)返回连续分布的中位数，计算结果可以是不在原数据中的中间值；PERCENTILE_DISC(0.5)返回离散分布的中位数，结果一定是原数据中存在的数值。

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