Google BigQuery计算周日均事件数报错及结果异常求助

问题分析 #

1. 初始代码报错Aggregations of aggregations are not allowed：直接嵌套聚合函数AVG(COUNT(*))违反BigQuery规则，同一层级SELECT不支持聚合函数嵌套，必须通过子查询或CTE分步处理。
2. 修改后的代码结果不符合预期：子查询仅按星期几（day）分组，导致每个星期几只生成一条总事件数记录，对单个值求平均自然与原数值一致——核心问题是未先按具体日期统计每天的事件数，丢失了每个星期几对应的多个单日数据点。

正确实现代码 #

分两步完成需求：先按具体日期统计单日事件数并标记星期几，再按星期几分组计算这些单日数据的平均值：

WITH daily_events AS (
  -- 第一步：按具体日期统计单日事件数，提取对应星期几
  SELECT
    EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_timestamp)) AS day_of_week,
    COUNT(*) AS daily_count
  FROM `table`
  WHERE
    DATE(TIMESTAMP_MICROS(event_timestamp)) BETWEEN DATE('2023-05-01') AND DATE('2023-05-28')
  GROUP BY DATE(TIMESTAMP_MICROS(event_timestamp)), day_of_week
)
-- 第二步：按星期几分组，计算平均事件数
SELECT
  day_of_week,
  -- 可选：映射为中文星期名称，提升可读性
  CASE day_of_week
    WHEN 1 THEN '周日'
    WHEN 2 THEN '周一'
    WHEN 3 THEN '周二'
    WHEN 4 THEN '周三'
    WHEN 5 THEN '周四'
    WHEN 6 THEN '周五'
    WHEN 7 THEN '周六'
  END AS day_name,
  AVG(daily_count) AS average_events
FROM daily_events
GROUP BY day_of_week
ORDER BY day_of_week;

关键说明 #

• BigQuery中EXTRACT(DAYOFWEEK FROM timestamp)返回值为1（周日）到7（周六），若需以周一为一周起始，可替换为EXTRACT(ISOWEEKDAY FROM timestamp)（返回1=周一，7=周日）。
• WHERE条件用DATE()函数直接将时间戳转换为日期，比DATE_TRUNC更简洁，且能准确筛选目标日期范围。
• 第一步必须按具体日期分组，确保每个星期几对应多个单日事件数记录，这样后续的AVG()才能计算出真正的平均值。

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