如何在BigQuery中用窗口函数计算用户日事件占比？

需求：计算指定日期内，每个用户的已交付事件占当日总事件的百分比，公式为 用户事件数 / 总事件数 * 100。

你尝试的查询语句：

SELECT 
  TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY) as date,
  `dataset.table`.user_id AS user_id,
  SAFE_DIVIDE(
    SUM(`dataset.table`.event_count),
    SUM(SUM(`dataset.table`.event_count)) OVER (PARTITION BY TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY))
  ) * 100 AS event_percentage,
  SUM(`dataset.table`.event_count) AS event_share
FROM `dataset.table`
WHERE TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY) = '2025-05-01'
GROUP BY TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY), 
         `dataset.table`.user_id;

运行时出现错误：

PARTITION BY expression references `scoping_data_model.aggregates`.event_time which is neither grouped nor aggregated

错误原因 #

在GROUP BY执行后，原始的event_time字段未被分组或聚合，无法在窗口函数的PARTITION BY中直接引用。窗口函数的分区逻辑需要基于分组后已确定的字段，而非原始表的未聚合字段。

解决方案 #

方案1：修正窗口函数的分区字段（无需子查询） #

直接使用GROUP BY中已定义的date别名作为分区依据，避免引用未聚合的原始字段：

SELECT 
  TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY) as date,
  `dataset.table`.user_id AS user_id,
  SAFE_DIVIDE(
    SUM(`dataset.table`.event_count),
    SUM(SUM(`dataset.table`.event_count)) OVER (PARTITION BY date)
  ) * 100 AS event_percentage,
  SUM(`dataset.table`.event_count) AS event_share
FROM `dataset.table`
WHERE TIMESTAMP_TRUNC(`dataset.table`.event_time, DAY) = '2025-05-01'
GROUP BY date, user_id;

这里PARTITION BY date使用了分组后生成的日期字段，符合BigQuery的语法规则，窗口函数会基于每日的总事件数计算占比。

方案2：使用子查询/CTE（逻辑更直观） #

先单独计算当日的总事件数，再通过关联查询匹配每个用户的事件数据：

WITH daily_total AS (
  SELECT 
    TIMESTAMP_TRUNC(event_time, DAY) as date,
    SUM(event_count) AS total_events
  FROM `dataset.table`
  WHERE TIMESTAMP_TRUNC(event_time, DAY) = '2025-05-01'
  GROUP BY date
)
SELECT 
  TIMESTAMP_TRUNC(t.event_time, DAY) as date,
  t.user_id,
  SAFE_DIVIDE(SUM(t.event_count), dt.total_events) * 100 AS event_percentage,
  SUM(t.event_count) AS event_share
FROM `dataset.table` t
JOIN daily_total dt ON TIMESTAMP_TRUNC(t.event_time, DAY) = dt.date
WHERE TIMESTAMP_TRUNC(t.event_time, DAY) = '2025-05-01'
GROUP BY date, user_id, dt.total_events;

这种方式将每日总事件数的计算与用户事件占比的计算分离，逻辑更清晰，适合后续需要扩展复杂统计规则的场景。

总结 #

不需要必须使用子查询，修正窗口函数的分区字段即可解决原始错误。两种方案各有优劣：方案1代码更简洁，方案2逻辑更直观，可根据实际需求选择。

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