MySQL中两种AVG()平均值计算方式结果不一致问题

我尝试用两种方式计算平均值，原本以为结果会完全一致，但MySQL返回了不同结果。以下是测试数据和查询语句：

测试表结构与数据 #

CREATE TABLE `test_avg` (
  `dt` varchar(10) NOT NULL,
  `field1` double NOT NULL,
  `field2` double NOT NULL,
  `field3` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_avg` (`dt`, `field1`, `field2`, `field3`) VALUES
('2022-10-31',  16.1379,    13.0809,    'A'),
('2022-10-31',  12.7579,    0.4458, 'A'),
('2022-10-31',  7.9206, 2.7775, 'A'),
('2022-10-31',  6.3764, 2.666,  'A'),
('2022-10-31',  5.3136, 1.6478, 'A'),
('2022-10-31',  4.5103, 88.178, 'A'),
('2022-10-31',  4.3547, 7.813,  'A'),
('2022-10-31',  4.3542, 3.5463, 'A'),
('2022-10-31',  3.0554, 7.3114, 'A'),
('2022-10-31',  26.3792,    2.2424, 'B'),
('2022-10-31',  9.6861, 28.5324,    'B'),
('2022-10-31',  9.1814, 6.8606, 'B'),
('2022-10-31',  8.0094, 6.2568, 'B'),
('2022-10-31',  7.5882, 548.5715,   'B'),
('2022-10-31',  7.5301, 3.7209, 'B'),
('2022-10-31',  7.4933, 1.3494, 'B'),
('2022-10-31',  7.4388, 22.8762,    'B'),
('2022-10-31',  7.1385, 19.9597,    'B'),
('2022-10-31',  7.1196, 19.8701,    'B');

查询语句1：直接按日期计算整体平均值 #

SELECT dt, AVG(field1), AVG(field2)
FROM test_avg
GROUP BY dt

查询语句2：先按日期+field3分组求平均，再对结果求平均 #

SELECT a.dt, AVG(a.avg1), AVG(a.avg2)
FROM
(SELECT dt, AVG(field1)AS avg1, AVG(field2)AS avg2, field3
FROM test_avg
GROUP BY dt, field3)a
GROUP BY a.dt

为什么这两种方式计算出的平均值结果不一致？

原因分析 #

这两种计算方式的本质逻辑完全不同，结果自然会有差异：

1. 查询语句1的计算逻辑
   直接对所有dt='2022-10-31'的记录计算平均值，公式为：

   • AVG(field1) = 所有field1的总和 / 总记录数（19条）
   • AVG(field2) = 所有field2的总和 / 总记录数（19条）

2. 查询语句2的计算逻辑
   第一步先按dt+field3分组：

   • 分组A有9条记录，计算出A组的avg1和avg2
   • 分组B有10条记录，计算出B组的avg1和avg2
     第二步对这两个分组的平均值再求平均，公式为：
   • AVG(a.avg1) = (A组avg1 + B组avg1) / 2
   • AVG(a.avg2) = (A组avg2 + B组avg2) / 2

这种计算方式给两个分组赋予了相同的权重（各占50%），但实际上两个分组的记录数不同（9 vs 10），和整体求平均的权重（按记录数占比）完全不一致，所以结果必然不同。

修正方案：加权平均 #

要让第二种方式得到和第一种一致的结果，需要使用加权平均，即根据每个分组的记录数计算权重：

SELECT a.dt, 
       SUM(a.avg1 * a.count) / SUM(a.count) AS weighted_avg1,
       SUM(a.avg2 * a.count) / SUM(a.count) AS weighted_avg2
FROM
(SELECT dt, 
        AVG(field1)AS avg1, 
        AVG(field2)AS avg2, 
        field3,
        COUNT(*) AS count
 FROM test_avg
 GROUP BY dt, field3)a
GROUP BY a.dt

该查询先计算每个分组的记录数，再用分组平均值乘以记录数求和，最后除以总记录数，得到和查询语句1完全一致的结果。

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