在BigQuery中实现90天移动平均及重复事件判定的最优SQL方案

针对你的需求，这里提供BigQuery中性能最优的SQL实现，同时完成90天移动平均计算和重复事件标记：

关键逻辑说明 #

• 日期格式转换：将样本中inc start的dd/mm/yyyy字符串转为BigQuery标准DATE类型，是后续滑动窗口计算的前提。
• 重复事件标记：以uid为分组依据（对应同一设备+事件类型），统计每条记录日期往前90天内的事件发生次数，超过2次则标记为Yes。
• 90天移动平均：提供两种常见场景的计算——全表每日事件数的移动平均、按uid分组的事件频率移动平均。

完整SQL代码 #

WITH formatted_data AS (
  SELECT
    incident_id,
    PARSE_DATE('%d/%m/%Y', `inc start`) AS incident_date,
    `inc description` AS incident_desc,
    element_name,
    uid
  FROM
    `your-project.your-dataset.target-table` -- 替换为你的实际表路径
)
SELECT
  *,
  -- 标记90天内同一uid出现次数超2次的记录
  CASE 
    WHEN COUNT(incident_id) OVER (
      PARTITION BY uid 
      ORDER BY incident_date 
      RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
    ) > 2 THEN 'Yes'
    ELSE 'No'
  END AS is_repeated,
  -- 全表每日事件数的90天移动平均
  AVG(daily_incident_count) OVER (
    ORDER BY incident_date 
    RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
  ) AS 90d_overall_daily_avg,
  -- 按uid分组的90天事件发生频率移动平均
  AVG(1) OVER (
    PARTITION BY uid 
    ORDER BY incident_date 
    RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
  ) AS 90d_uid_incident_avg
FROM (
  -- 预计算每日事件总数，用于全表移动平均
  SELECT
    *,
    COUNT(incident_id) OVER (PARTITION BY incident_date) AS daily_incident_count
  FROM formatted_data
)
ORDER BY incident_date, incident_id;

性能优势说明 #

BigQuery的窗口函数基于列存储引擎做了深度优化，使用RANGE BETWEEN INTERVAL定义滑动窗口，避免了传统自连接或子查询关联的低效操作，在大数据量场景下性能远高于其他实现方式。

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