无法在MySQL中使用某些GROUP BY条件对AVG值进行ORDER BY [英] Can not ORDER BY an AVG value with certain GROUP BY criteria in MySQL
问题描述
我有一张桌子data_summaries
.它具有诸如item_id INT(11)
,user_grouping TEXT
和value DECIMAL(10,2)
的列.
I have a table data_summaries
. It has such columns as item_id INT(11)
, user_grouping TEXT
and value DECIMAL(10,2)
.
如果我尝试进行按user_grouping
分组结果并按value
的AVG
排序结果的查询,将失败:
If I try to make a query that groups the results by user_grouping
and orders them by the AVG
of value
, that fails:
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
子句确实在做某事,因为它确实改变了顺序:
The ORDER BY
clause does seem to be doing something as it does change the order:
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
排序可以按预期工作:
On the other hand, ordering by the SUM
of value
works as expected:
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
进行排序是可行的:
If I group by item_id
instead, then ordering by the AVG
works:
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 |
+---------+-----------+-----------+
我该如何更改第一个查询以使其按平均值排序?
How would I need to change the first query to get it ordered by the average?
推荐答案
That is a MySQL bug, see Unexpected order for grouped query, that involves avg()
in combination with grouping by a text
-column. It is still open in MySQL 5.7.15.
作为一种解决方法,您可以将数据类型更改为例如varchar
.如果您不需要索引来加快速度,则投射也应该起作用:
As a workaround, you can change your datatype to e.g. varchar
. If you don't need indexes to speed it up, casting should work too:
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 :
在基于BLOB的类型的列上进行分组并根据AVG(),VAR_POP()或STDDEV_POP()聚合函数的结果进行排序的查询,如果InnoDB是临时的,则以错误的顺序返回结果表被使用. (缺陷号22275357,缺陷号79366)
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)
这篇关于无法在MySQL中使用某些GROUP BY条件对AVG值进行ORDER BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!