必须出现在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我该如何解决这个问题？注意：此表是根据上一个操作创建的VIEW。

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

;

此方法唯一的作用是它将显示所有记录（窗口功能不分组）。但是它将在每行中显示正确的国家（即cname最高级别）MAX，由您决定：

 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在服务器配置中手动启用此选项（）以支持此功能...

Answer 2

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

SELECT DISTINCT ON (cname) 

    cname, wmname, avg

FROM 

    makerar 

ORDER BY 

    cname, avg DESC ;

Answer 3

在selects中指定非分组字段和非聚合字段的问题group by在于，在这种情况下，引擎无法知道应该返回哪个记录的字段。首先吗 最后吗？通常没有自然与汇总结果相对应的记录（min并且max是例外）。

但是，有一种解决方法：也将必填字段汇总在一起。在posgres中，这应该起作用：

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)

FROM makerar GROUP BY cname;

请注意，这会创建一个由wg排序的所有wname的数组，并返回第一个元素（postgres中的数组基于1）。