Tengo una tabla que se parece a esta llamada 'makerar'
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Y quiero seleccionar la media máxima de cada nombre de la cn.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
pero me da error,
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;
así que hago esto
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
sin embargo, esto no dará los resultados previstos, y la salida incorrecta se muestra a continuación.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Los resultados reales deberían ser
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
¿Cómo puedo solucionar este problema?
Nota: Esta tabla es una VISTA creada a partir de una operación anterior.
Sí, se trata de un problema de agregación común. Antes de SQL3 (1999), los campos seleccionados deben aparecer en la cláusula GROUP BY
.
Para solucionar este problema, debe calcular el agregado en una subconsulta y luego unirlo consigo mismo para obtener las columnas adicionales que necesita mostrar:
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
Pero también se pueden utilizar funciones de ventana, que parecen más sencillas:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
Lo único que ocurre con este método es que mostrará todos los registros (las funciones de ventana no agrupan). Sin embargo, mostrará el valor correcto (es decir, el máximo a nivel de "nombre") del país en cada fila, así que depende de usted:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
La solución, posiblemente menos elegante, para mostrar las únicas tuplas (cname, wmname)
que coincidan con el valor máximo, es:
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
[*]: Curiosamente, aunque la especificación permite seleccionar campos no agrupados, a los principales motores parece no gustarles. Oracle y SQLServer no lo permiten en absoluto. Mysql solía permitirlo por defecto, pero ahora desde la versión 5.7 el administrador necesita habilitar esta opción (ONLY_FULL_GROUP_BY
) manualmente en la configuración del servidor para que esta característica sea soportada...
En Postgres, también puede utilizar la sintaxis especial DISTINCT ON (expresión)
:
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;
Usando rank()
función de ventana:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Nota
Cualquiera de los dos conservará múltiples valores máximos por grupo. Si quieres un solo registro por grupo aunque haya más de un registro con avg igual a max debes consultar la respuesta de @ypercube's.