PostgreSQL按星期聚合订单计算平均下单频率SQL报错问题

报错根因 #

你遇到的ERROR: aggregate function calls cannot be nested错误，是因为PostgreSQL不支持同一查询层级内嵌套聚合函数：COUNT(DISTINCT customer_id)本身是按weekday分组计算的聚合结果，外层套AVG()相当于要求在已聚合的结果上再次做聚合，和当前GROUP BY weekday的计算粒度冲突，无法直接执行。

指标计算逻辑 #

按星期维度统计的客户平均下单频率，本质是对应星期分组下，平均每个独立客户产生的订单数。数学上这个值完全等价于「分组内总订单量 / 分组内去重独立客户数」，不需要嵌套聚合，直接用同分组的两个聚合值计算即可，性能远高于写子查询二次聚合。

可直接运行的正确SQL #

SELECT 
    weekday,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT customer_id) AS customers,
    ROUND(
        COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT customer_id), 0),
        2
    ) AS avg_order_freq
FROM orders
GROUP BY weekday
ORDER BY weekday;

代码说明 #

• COUNT(*)::NUMERIC做类型转换，避免PostgreSQL默认整数除法截断小数的问题
• NULLIF(COUNT(DISTINCT customer_id), 0)处理极端场景下的除零报错，若分组内无有效客户，该字段返回空值而非中断查询
• ROUND(...,2)将平均下单频率保留2位小数，可根据业务精度需求调整保留位数
• 最后加ORDER BY weekday保证返回的星期维度结果按顺序排列，可根据自己的weekday字段取值规则调整排序逻辑

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