无法在MySQL中使用某些GROUP BY条件对AVG值进行ORDER BY

夏麟

我有一张表data_summaries。它具有这样的列如item_id INT(11)，user_grouping TEXT和value
DECIMAL(10,2)。
* j3 ?$ G4 Q+ O如果我尝试做一个查询组的结果通过user_grouping和他们的订单通过AVG的value，失败：
SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
ORDER BY avg_value
  Y1 w" Y* K! o2 H' ]: D+---------------+-----------+-----------+
6 a: K3 d0 s, L; `5 w: n" d& e5 q  w| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
# \1 H0 [6 g; X0 g2 e| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
| New York      | 51.500400 |   2575.02 |
" k$ X9 G, M+ R; N8 ^2 n| NULL          | 49.775627 |  18665.86 |
1 W2 z& Z2 _" {# \5 D: T+---------------+-----------+-----------+
该ORDER BY子句似乎确实在做 某些事情， 因为它确实改变了顺序：
SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
# h8 _8 P# `) N7 o) z1 tGROUP BY user_grouping
1 [, s# ^* S0 X: K- }7 `7 N/ ^+---------------+-----------+-----------+
5 N3 `% j8 a* z8 r- D) }8 {| user_grouping | avg_value | sum_value |
7 E/ p! D/ L. k7 X+---------------+-----------+-----------+
| NULL          | 49.775627 |  18665.86 |
+ g7 R* l+ Z6 G0 e3 N| New York      | 51.500400 |   2575.02 |
| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
+---------------+-----------+-----------+
在另一方面，通过订货SUM的value作品如预期：
4 [) J* X7 U( zSELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
0 P8 ?) o4 x2 Q! p8 @FROM data_summaries
GROUP BY user_grouping
1 @! Z; J# v8 |  I6 y  X2 M3 kORDER BY sum_value
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
4 E0 W' U$ G  H& Z9 @+---------------+-----------+-----------+
( ?% @9 N8 r8 O7 J7 Z3 \| New York      | 51.500400 |   2575.02 |
| NULL          | 49.775627 |  18665.86 |
+ K, g- u, n# x4 i+ k9 q$ v| London        | 50.609733 |  18978.65 |
| Paris         | 50.791733 |  19046.90 |
+---------------+-----------+-----------+
: I0 h: K, ?" ?6 D9 F如果我按分组item_id，则按AVG作品排序：
+ ~: h, K4 H' e( L1 q9 cSELECT item_id, AVG(value) AS avg_value, SUM(value) AS sum_value
, d2 }7 k9 @8 nFROM data_summaries
GROUP BY item_id
+---------+-----------+-----------+
| item_id | avg_value | sum_value |
+ a9 R6 x* c- m+---------+-----------+-----------+
  ]/ @9 ]% u4 j. P7 F|       4 | 49.318225 |  11392.51 |
|       1 | 49.737835 |  11489.44 |
# a# \: A  `0 O, z5 _|       2 | 50.420606 |  11647.16 |
|       6 | 51.024242 |  11786.60 |
4 Y5 z+ D+ `# D' ?|       5 | 51.456537 |  11886.46 |
. v: g7 \6 Z+ w) D( |+ B$ o8 v. i|       3 | 53.213000 |   1064.26 |
' _# m, c2 {" R7 n" v% f$ j+---------+-----------+-----------+
8 C) J. ?( x9 ^; J3 M我将如何更改第一个查询以使其按平均值排序？
               
2 ~% Y8 K0 `* Y' a: A& F解决方案:
+ |$ r% ^3 t& `/ P               

( K* ^+ D. z5 O( u
                那是一个MySQL错误，请参阅分组查询的意外顺序，其中涉及avg()与-text列组合在一起。它仍然在MySQL
5.7.15中打开。
0 g9 U( q2 L+ S+ e解决方法是，您可以将数据类型更改为例如varchar。如果不需要索引来加快速度，则转换也应该起作用：
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
8 `5 A3 Y/ O- [" n' u" `更新：
0 `, Z. y" D! z: y: `0 f# r* \该错误已在MySQL
5.7.17中修复：
  t6 U6 p3 D- V1 Q
- _% n5 A7 r0 G如果使用InnoDB临时表，则将查询分??组在基于BLOB的类型的列上，并根据AVG（），VAR_POP（）或STDDEV_POP（）聚合函数的结果进行排序，则返回的结果将以错误的顺序返回。（缺陷号22275357，错误＃79366）