SQL使用COUNT聚合后如何按其他列同值分组计算平均值

实现这个需求的核心是两层聚合：第一层按商品+年份分组，筛选出目标月份（本场景为5月）的订单后统计每年的订单量；第二层按商品分组，对该商品所有年份的目标月份订单量取平均值，不需要提前单独执行COUNT步骤，单条SQL即可完成计算。

可用SQL代码 #

支持CTE语法的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）优先用可读性更高的写法：

WITH yearly_target_month_orders AS (
    SELECT
        item_number,
        YEAR(date) AS order_year,
        COUNT(id) AS yearly_order_count
    FROM orders
    WHERE MONTH(date) = 5 -- 替换此处数字即可切换要统计的目标月份
    GROUP BY item_number, YEAR(date)
)
SELECT
    item_number,
    AVG(yearly_order_count) AS avg_orders_in_target_month
FROM yearly_target_month_orders
GROUP BY item_number;

如果是不支持CTE的老版本数据库，用子查询写法效果完全一致：

SELECT
    item_number,
    AVG(yearly_order_count) AS avg_orders_in_target_month
FROM (
    SELECT
        item_number,
        COUNT(id) AS yearly_order_count
    FROM orders
    WHERE MONTH(date) = 5
    GROUP BY item_number, YEAR(date)
) AS yearly_stats
GROUP BY item_number;

原SQL的问题说明 #

你之前写的查询返回结果异常，是两个典型的分组查询错误导致的：

• SELECT *写法不符合分组查询规范：分组查询中，SELECT子句里的非聚合字段必须全部出现在GROUP BY后，你查询的id、完整date字段未加入分组规则，数据库会随机返回分组内某一行的取值，直接导致计数和年份匹配错误（比如你结果里item#51凭空出现2020年的统计值就是这个原因）。
• 只做了一层按「商品+年+月」的分组聚合，没有再按商品维度二次聚合计算平均值，自然拿不到最终的平均结果。

结果匹配验证 #

基于你给出的预期值和原始表数据，上述SQL返回结果完全符合要求：

• 商品#4：仅在2022年5月有1笔订单，平均值为1
• 商品#43：有5月订单的年份中每年订单数为2，平均值为2
• 商品#51：非5月的2019年8月订单会被WHERE条件过滤，剩下两个有5月订单的年份每年1单，平均值为1

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