从group by到窗口函数：PostgreSQL活动参与率统计实现问题

常见问题根因 #

• 原有group by写法存在计算逻辑偏差：sum(event_subscribed/event_participated) 是逐行计算单条记录的比率后再求和，不符合整体参与率的业务口径，正确的统计逻辑应为分组下总订阅数 / 总参与数，即 sum(event_subscribed) / sum(event_participated)
• 窗口函数返回行数过多是其固有特性导致：窗口函数不会像group by那样对分组行做聚合压缩，而是将计算结果附加到原表每一行上，同一source的所有行都会携带相同的统计值，直接查询会返回与原表行数一致的结果
• 计算数值错误基本源于窗口函数内的聚合逻辑仍沿用了逐行算比率的错误写法，未按整体口径先汇总分子、分母再计算比值

可直接复用的实现代码 #

场景1：保留原表明细，附加source维度的参与率 #

直接在查询中附加窗口计算列即可，每行都会对应该记录所属source的整体参与率：

SELECT 
  *,
  SUM(event_subscribed) OVER (PARTITION BY source) 
  / NULLIF(SUM(event_participated) OVER (PARTITION BY source), 0) AS engagement_rate
FROM events

场景2：与原group by逻辑一致，仅返回每个source一行的统计结果 #

通过DISTINCT去重过滤掉重复的窗口计算结果，即可得到与group by聚合结果完全一致的输出：

SELECT DISTINCT
  source,
  SUM(event_subscribed) OVER (PARTITION BY source) 
  / NULLIF(SUM(event_participated) OVER (PARTITION BY source), 0) AS "engagement rate"
FROM events

说明：NULLIF 用于规避某source的event_participated汇总值为0时触发的除零错误，与常规统计的容错逻辑匹配。如果仅需对比newsletter与Email两类来源，可在查询中增加过滤条件WHERE source IN ('newsletter', 'Email')，即可排除其他来源的统计干扰。

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