mysql - 无法在 MySQL 中使用具有特定 GROUP BY 条件的 AVG 值进行 ORDER BY

Question

我有一个表data_summaries。它有 item_id INT(11)、user_grouping TEXT 和 value DECIMAL(10,2) 等列。

如果我尝试进行查询，按 user_grouping 对结果进行分组，并按 value 的 AVG 对结果进行排序，那么失败:

SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
ORDER BY avg_value
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
| New York      | 51.500400 |   2575.02 |
| NULL          | 49.775627 |  18665.86 |
+---------------+-----------+-----------+

ORDER BY 子句似乎确实在做某事，因为它确实改变了顺序:

SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| NULL          | 49.775627 |  18665.86 |
| New York      | 51.500400 |   2575.02 |
| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
+---------------+-----------+-----------+

另一方面，按 value 的 SUM 排序按预期工作:

SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
ORDER BY sum_value
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| New York      | 51.500400 |   2575.02 |
| NULL          | 49.775627 |  18665.86 |
| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
+---------------+-----------+-----------+

如果我改为按 item_id 分组，则可以按 AVG 排序:

SELECT item_id, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY item_id
+---------+-----------+-----------+
| item_id | avg_value | sum_value |
+---------+-----------+-----------+
|       4 | 49.318225 |  11392.51 |
|       1 | 49.737835 |  11489.44 |
|       2 | 50.420606 |  11647.16 |
|       6 | 51.024242 |  11786.60 |
|       5 | 51.456537 |  11886.46 |
|       3 | 53.213000 |   1064.26 |
+---------+-----------+-----------+

我需要如何更改第一个查询以使其按平均值排序？

Answer 1

这是一个 MySQL 错误，请参阅 Unexpected order for grouped query ，这涉及 avg() 与按 text 列分组的组合。它在 MySQL 5.7.15 中仍然打开。

作为解决方法，您可以将数据类型更改为例如可变字符。如果您不需要索引来加快速度，转换也应该有效:

SELECT cast(user_grouping as char(200)), AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY cast(user_grouping as char(200))
ORDER BY avg_value

更新:

错误已在 MySQL 5.7.17 中修复:

Queries that were grouped on a column of a BLOB-based type, and that were ordered on the result of the AVG(), VAR_POP(), or STDDEV_POP() aggregate function, returned results in the wrong order if InnoDB temporary tables were used. (Bug #22275357, Bug #79366)