我有一个表格,看起来像这样的caller '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。
是的,这是一个常见的聚合问题。在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
在Postgres中,你也可以使用特殊的DISTINCT ON (expression)
语法。
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
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';的答案。