我有一张看起来像这个来电者'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。
wmname="usopp"
而不是 wmname="luffy"
?
是的,这是一个常见的聚合问题。在 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
) 才能支持此功能...
在 Postgres 中,您还可以使用特殊的 DISTINCT ON (expression)
语法:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
BY cname
的顺序对结果集进行排序?
在 group by
选择中指定非分组和非聚合字段的问题是引擎无法知道在这种情况下它应该返回哪个记录的字段。是第一吗?是最后吗?通常没有与聚合结果自然对应的记录(min
和 max
是例外)。
但是,有一种解决方法:将必填字段也进行聚合。在 postgres 中,这应该有效:
SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;
请注意,这会创建一个包含所有 wname 的数组,按 avg 排序,并返回第一个元素(postgres 中的数组是从 1 开始的)。
array_agg
我不知道你可以在参数中正确排序
array_agg
是我要返回的所有与 GROUP BY
匹配的项目
对我来说,这不是一个“常见的聚合问题”,而是一个不正确的 SQL 查询。 “选择每个 cname 的最大平均值...”的唯一正确答案是
SELECT cname, MAX(avg) FROM makerar GROUP BY cname;
结果将是:
cname | MAX(avg)
--------+---------------------
canada | 2.0000000000000000
spain | 5.0000000000000000
这个结果通常回答了“每个组的最佳结果是什么?”这个问题。我们看到西班牙的最佳结果是 5,而加拿大的最佳结果是 2。这是真的,而且没有错误。如果我们还需要显示 wmname,我们必须回答这个问题:“从结果集中选择 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 解释器返回错误的原因——查询不正确。
换句话说,“谁是 spain
组中最好的?”这个问题没有正确答案。。路飞并不比乌索普强,因为乌索普的“分数”是一样的。
SELECT cname, id, MAX(avg) FROM makerar GROUP BY cname;
,它确实给出了这个误导性错误。
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()
window function:
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 的答案。
这似乎也有效
SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
FROM makerar m2
WHERE m1.cname = m2.cname
)
我最近在尝试使用 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
which
语句?
MAX
的情况下完成(参见@ypercube 的回答,我的回答中还有另一个解决方案)但不是你这样做的方式。检查预期输出。cname
的 MAXavg
),但它不限制结果的行(如 OP 所愿)。请参阅问题中的 Actual Results should be 段落。ONLY_FULL_GROUP_BY
不会激活 SQL 标准指定何时可以从group by
中省略列的方式(或使 MySQL 的行为类似于 Postgres)。它只是恢复到 MySQL 返回随机(=“不确定”)结果的旧行为。