deve aparecer na cláusula GRUPO POR GRUPO ou ser usado em uma função agregada

Eu tenho uma mesa que se parece com este chamador 'makerar'

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

E eu quero seleccionar a avg máxima para cada nome.

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

mas eu vou ter um erro,

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;

então eu faço isto

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

No entanto, isto não dará os resultados pretendidos, e a saída incorreta abaixo é mostrada.

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

Os Resultados Reais devem ser

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

Como posso resolver este problema?

Nota: Esta tabela é uma VIEW criada a partir de uma operação anterior.

a_horse_with_no_name
Pergunta editada :em
Programação
sql group-by aggregate-functions postgresql-9.1
26º outubro 2013 в 1:50
13 vistas
Solução

Sim, este é um problema comum de agregação. Antes de SQL3 (1999), os campos selecionados devem aparecer na cláusula GROUP BY[*].

Para resolver esta questão, você deve calcular o agregado em uma subconsulta e então juntar-se a ela para obter as colunas adicionais que você'd precisa 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

Mas você também pode usar funções de janela, que parecem mais simples:

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

A única coisa com este método é que ele mostrará todos os registros (as funções de janela não agrupam). Mas ele mostrará o correto (ou seja, máximo no nível cname) MAX para o país em cada linha, então ele's depende de você:

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

A solução, indiscutivelmente menos elegante, para mostrar o único (cname, wmname) tuples que corresponde ao valor máximo, é:

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, mesmo que a especificação permita seleccionar campos não agrupados, os motores principais parecem não gostar muito. Oracle e SQLServer apenas don't permitem isso de forma alguma. Mysql costumava permitir isso por padrão, mas agora desde 5.7 o administrador precisa habilitar essa opção (ONLY_FULL_GROUP_BY) manualmente na configuração do servidor para que esse recurso seja suportado...

Sebas
Resposta editada :em
Comentários (7)

Em Postgres, você também pode utilizar a sintaxe especial DISTINCT ON (expressão):

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;
Comentários (8)
 
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;

Utilizando 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;

**Nota***

Qualquer um deles irá preservar múltiplos valores máximos por grupo. Se você quiser apenas um registro por grupo, mesmo que haja mais de um registro com avg igual a max você deve verificar @ypercube's resposta.

zero323
Resposta editada :em
Comentários (0)