MySQL中COUNT结合GROUP BY分组后如何使用AVG求平均值

实现思路 #

需要两层聚合完成需求，先做年-月维度的单量统计是必要步骤，因为计算平均值的基础是各年份同月份的订单总量样本，不需要关联其他表，单表即可完成：

1. 内层聚合：按商品、年份、月份分组，统计每一年每个商品在对应月份的总订单数
2. 外层聚合：基于内层结果，按商品、月份分组，对同商品同月份的各年单量求平均值，即可得到目标结果

可直接运行的SQL代码 #

SELECT
  item_number,
  month_num,
  AVG(monthly_count) AS avg_monthly_order_count
FROM (
  -- 内层：统计每个商品每年各月的订单总量
  SELECT
    item_number,
    MONTH(order_date) AS month_num,
    YEAR(order_date) AS year_num,
    COUNT(id) AS monthly_count
  FROM orders
  -- 按需添加过滤条件：比如只统计商品50就放开下一行注释
  -- WHERE item_number = 50
  -- 如果只统计4月的跨年份平均，再加 AND MONTH(order_date) = 4
  GROUP BY item_number, YEAR(order_date), MONTH(order_date)
) AS yearly_monthly_stats
GROUP BY item_number, month_num
ORDER BY item_number, month_num;

说明 #

• 针对你举的商品50的例子：内层统计会得到2020年4月单量40、2021年4月单量20两条记录，外层聚合到商品50、月份4时，AVG(monthly_count)会自动计算(40+20)/2=30，完全匹配预期结果。
• 统计单量时用COUNT(id)比COUNT(item_number)更稳妥，可以避免item_number字段为空时的统计误差。
• 你之前写的WHERE MONTH(order_date) = 4属于过滤条件，如果需要统计1-12月所有月份的平均值就去掉，只看单月再加即可。

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