必须出现在GROUP BY子句中或被用于聚合函数。

Question

我有一张表，叫做'makerar'，它长这样：

cname	wmname	avg
canada	zoro	2.0000000000000000
spain	luffy	1.00000000000000000000
spain	usopp	5.0000000000000000

我想要选择每个cname的最大平均值。

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

但是我会收到一个错误信息，

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

所以我这样做。

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

然而，这将不会得到预期的结果，并显示了下面的错误输出。

cname	wmname	max
canada	zoro	2.0000000000000000
spain	luffy	1.00000000000000000000
spain	usopp	5.0000000000000000

实际上应该显示如下结果：

cname	wmname	max
canada	zoro	2.0000000000000000
spain	usopp	5.0000000000000000

我该如何解决这个问题？

注意：此表格是从先前操作创建的视图。

- RandomGuy

---

相关链接：https://dev59.com/H2Ml5IYBdhLWcg3w3aI- - Craig Ringer

我不明白。为什么期望的是 wmname="usopp" 而不是例如 wmname="luffy"？ - AndreKR

@AndreKR 因为 (1, 5) 的最大值是5，而5与“usopp”相关联，而不是“luffy”。这是预期/期望的结果。 - RVS

如果(西班牙，路飞)的avg与(西班牙，乌索普)相同，那么你需要指定预期的行为是什么... 随机选择一个？使用其他列作为“决胜者”？ - Ricardo

Answer 1

是的，这是一个常见的聚合问题。在SQL3 (1999)之前，所选字段必须出现在GROUP BY子句中[*]。

为了解决这个问题，您必须在子查询中计算聚合，然后将其与自身连接以获取需要显示的其他列：

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

但是你也可以使用窗口函数，看起来更简单：

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

使用这种方法的唯一问题是它会显示所有记录（窗口函数不进行分组）。但是它将在每行中显示正确的国家 MAX（即在 cname 级别上取得最大值），因此由你决定:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

解决方案，可能不够优雅，但可以显示与最大值匹配的唯一(cname, wmname)元组:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;


 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

[*]: 有趣的是，尽管规范允许选择非分组字段，但主要引擎似乎并不真正喜欢它。Oracle和SQLServer根本不允许这样做。Mysql过去默认允许这样做，但现在自5.7版本以来，管理员需要在服务器配置中手动启用此选项（ONLY_FULL_GROUP_BY）才能支持此功能...

- Sebas

---

感谢您的语法是正确的，但是在 joining 时您必须比较 mx 和 avg 的值。 - RandomGuy

是的，您的语法是正确的，并且消除了重复项，但是在最后需要加上 m.avg=t.mx （在您编写 JOING 之后）才能获得预期的结果。 - RandomGuy

@Sebas，可以不用在MAX上进行连接来完成（请参考@ypercube的答案，我的答案中也有另一种解决方案），但不能像你那样做。请检查预期输出。 - zero323

@Sebas，您的解决方案只是添加了一列（每个cname的最大avg），但它并没有限制结果的行数（正如OP所需）。请参见问题中的“实际结果应该是”段落。 - ypercubeᵀᴹ

在MySQL 5.7中关闭ONLY_FULL_GROUP_BY并不会激活SQL标准指定的可以省略group by中列的方式（或使MySQL的行为类似于Postgres）。它只是恢复到旧的行为，即MySQL返回随机（=“不确定”）结果。 - user330315

Answer 2

在Postgres中，您还可以使用特殊的DISTINCT ON (expression)语法：

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

- ypercubeᵀᴹ

---

如果想要像求平均值那样对列进行排序，按照期望的方式它不能正常工作。 - amenzhinsky

当然。如果你不运行我发布的查询，你会得到不同的结果！这与“它不能按预期工作”不同... - ypercubeᵀᴹ

@Batfan 谢谢。请注意，虽然这种写法很酷、紧凑且易于编写，但对于这种查询来说，它通常不是最有效的方式。 - ypercubeᵀᴹ

它在Redshift中支持吗？ - Yubaraj

虽然我非常喜欢这种语法，但我在优化这些查询方面遇到了麻烦，很容易写出次优的查询。也许我只是漏掉了什么。 - Eric Walker

Answer 3

在 group by 查询中指定非分组和非聚合字段的问题是引擎无法知道应该返回哪条记录的字段。它是第一个还是最后一个？通常没有一条记录与聚合结果自然对应（min 和 max 是例外情况）。

不过，有一个解决方法：也将需要的字段聚合起来。

在Postgres中，可以这样实现：

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

注意这将创建一个按平均值排序的所有wnames数组，并返回第一个元素（postgres中的数组从1开始）。

- e-neko

---

好观点。虽然数据库可能可以进行外连接，将每行的非聚合字段链接到该行所贡献的聚合结果。我一直很好奇为什么他们没有这个选项。虽然我可能只是不知道这个选项 :) - Ben Simmons

这是一个不错的pgsql解决方案，但有没有mysql的等效物呢？我不喜欢多选的方式。 - Muhammad Dyas Yaskur

很棒的array_agg使用，我不知道你可以在参数中进行排序。 - GavinBelson

array_agg 是我正在寻找的，用于返回所有与 GROUP BY 匹配的项目。 - Akaisteph7

在这种情况下，“MAX(avg)”可以被替换为“avg”，因为它们在聚合之后排序后可能没有区别，对吧？ - Thomas Tempelmann

Answer 4

对我来说，这不是一个“常见的聚合问题”，而只是一个不正确的SQL查询。 “选择每个cname的最大平均值”的唯一正确答案是

SELECT cname, MAX(avg) FROM makerar GROUP BY cname;

结果将会是：

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

这个结果通常回答了问题“每个组的最佳结果是什么？”。 我们看到西班牙的最佳结果为5，加拿大的最佳结果为2。 这是真的，没有错误。 如果我们还需要显示wmname，就必须回答这个问题：“从结果集中选择的规则是什么？” 让我们稍微改变一下输入数据以澄清错误：

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

你期望从运行以下查询中获得哪个结果：SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;? 应该是spain+luffy还是spain+usopp？为什么？在查询中没有确定如何选择“更好的”wmname，如果有多个适用的话，所以结果也没有确定。这就是SQL解释器返回错误的原因 - 查询不正确。

换句话说，问题“在西班牙小组中谁最好？”没有正确答案。Luffy并不比Usopp更好，因为Usopp得分相同。

- ox160d05d

---

这个解决方案对我也起作用了。我有查询问题，因为我的ORM还包含了关联的主键，导致以下 不正确 的查询： SELECT cname，id，MAX(avg) FROM makerar GROUP BY cname;，这确实给出了这个误导性的错误。 - Roberto

Answer 5

SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

使用rank()窗口函数：

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

注意

两种方法都可以保留每个组的多个最大值。如果您只想要每个组的单个记录，即使有多个平均值等于最大值的记录，您也应该检查@ ypercube的答案。

- zero323

Answer 6

这似乎也可以正常工作

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )

- daintym0sh

Answer 7

最近我遇到了这个问题，试图使用 case when 计数时发现改变 which 和 count 语句的顺序可以解决这个问题：

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

不要使用连字符“-”在后面，否则会出现聚合函数中应该出现苹果和橙子的错误。

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter

- Rachel Windzberg

---

which语句是什么？ - Hillary Sanders