BigQuery中含缺失值的移动平均计算技术咨询

这问题我太熟了！当数据集里有缺失的时间点时，直接用窗口函数算移动平均会跳过这些缺失值，导致结果不符合预期——比如你例子里2017-04-01缺失，如果直接算3月移动平均，5月会直接用3、5月的数据，而不是把4月的缺失状态纳入计算。所以核心思路是先补全时间轴，再计算移动平均，下面给你一步步拆解方案：

步骤1：生成连续的月份序列（补全时间轴） #

首先要为每个id生成完整的月份范围，确保从最早到最晚的每个月份都存在。BigQuery的GENERATE_DATE_ARRAY函数可以轻松生成连续日期，结合UNNEST展开成行：

WITH dummy_data AS (
 SELECT '2017-01-01' as ref_month, 18 as value, 1 as id
 UNION ALL
 SELECT '2017-02-01' as ref_month, 20 as value, 1 as id
 UNION ALL
 SELECT '2017-03-01' as ref_month, 22 as value, 1 as id
 -- UNION ALL SELECT '2017-04-01' as ref_month, 28 as value, 1 as id
 UNION ALL
 SELECT '2017-05-01' as ref_month, 30 as value, 1 as id
 UNION ALL
 SELECT '2017-06-01' as ref_month, 37 as value, 1 as id
 UNION ALL
 SELECT '2017-07-01' as ref_month, 42 as value, 1 as id
),
date_spine AS (
 SELECT
   id,
   ref_month
 FROM (
   -- 先获取每个id的时间边界（最早和最晚月份）
   SELECT
     id,
     MIN(PARSE_DATE('%Y-%m-%d', ref_month)) AS min_month,
     MAX(PARSE_DATE('%Y-%m-%d', ref_month)) AS max_month
   FROM dummy_data
   GROUP BY id
 ),
 -- 生成该边界内的所有连续月份
 UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) AS ref_month
)

步骤2：补全缺失的数值 #

把生成的连续月份和原数据左关联，用COALESCE处理缺失的value——你可以根据业务需求把缺失值设为NULL（计算时自动忽略）或0：

,filled_data AS (
 SELECT
   ds.id,
   FORMAT_DATE('%Y-%m-%d', ds.ref_month) AS ref_month,
   -- 这里用NULL保留缺失状态，也可以改成COALESCE(dd.value, 0)设为0
   COALESCE(dd.value, NULL) AS value
 FROM date_spine ds
 LEFT JOIN dummy_data dd
   ON ds.id = dd.id
   AND ds.ref_month = PARSE_DATE('%Y-%m-%d', dd.ref_month)
)

步骤3：计算移动平均 #

现在数据是完整的时间序列了，用窗口函数AVG()计算移动平均即可。下面以3个月移动平均为例：

基于行数的移动平均（固定取最近3个月份） #

适合严格按月份计数的场景（我们已经补全了时间轴，所以就是最近3个连续月份）：

SELECT
 id,
 ref_month,
 value,
 -- 取当前月+前2个月的平均值，自动忽略NULL值
 AVG(value) OVER (
   PARTITION BY id
   ORDER BY ref_month
   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 ) AS moving_avg_3months
FROM filled_data
ORDER BY id, ref_month;

基于时间范围的移动平均（取过去3个月内的数据） #

如果你的需求是不管中间隔了几个月，只要是过去3个月内的记录都算（比如数据不是严格每月一条的场景），可以用时间范围窗口：

SELECT
 id,
 ref_month,
 value,
 -- 取当前月及过去3个月内的所有数据的平均值
 AVG(COALESCE(value, 0)) OVER (
   PARTITION BY id
   ORDER BY PARSE_DATE('%Y-%m-%d', ref_month)
   RANGE BETWEEN INTERVAL 3 MONTH PRECEDING AND CURRENT ROW
 ) AS moving_avg_3months_time_based
FROM filled_data
ORDER BY id, ref_month;

结果说明 #

运行完整SQL后，你会看到2017-04-01的value是NULL，对应的3个月移动平均会自动忽略该NULL值，计算为前两个月的平均值；如果希望把缺失值当成0计算，只需把AVG(value)改成AVG(COALESCE(value, 0))即可。

你可以根据自己的业务需求调整缺失值的处理方式和窗口范围~

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