Retornando registros no resultado da SQL com group by

Retornando registros no resultado da SQL com group by

MySQL + SQL Server


Em diversos casos, utilizamos GROUP BY com uma função de agregação, como MAX(), para calcular o valor dentro de cada grupo. Mas como fazer para recuperar toda linha, e não só a coluna calculada?

Considerando o esquema e os dados como demonstrados na imagem abaixo, descobrir a maior quantidade de vezes que uma música de cada banda foi executada é bastante simples:


SELECT id_artista
, MAX(plays) FROM musica GROUP BY id_artista
+------------+------------+
| id_artista | MAX(plays) |
+------------+------------+
|          4 |         28 |
|          8 |          9 |
|         15 |          4 |
|         16 |          6 |
+------------+------------+

O problema fica um pouco mais curioso quando queremos todo o registro de cada música, e não apenas o valor calculado de determinada coluna.

Intuitivamente, talvez alguém sugira incluir os outros campos na query, mas os resultados estarão incorretos:

SELECT id_artista, MAX(plays), nome FROM musica GROUP BY id_artista 
+------------+------------+--------------------------+
| id_artista | MAX(plays) | nome                     |
+------------+------------+--------------------------+
|          4 |         28 | Martyr of the Free World |
|          8 |          9 | Lord of the Flies        |
|         15 |          4 | Air Batucada             |
|         16 |          6 | Sunny Side Up            |
+------------+------------+--------------------------+

Observe que os valores não batem. Não é Martyr of the Free World que tem 28 execuções; nem Lord of the Flies que tem 9 execuções, e assim por diante. Os valores são imprevisíveis e não representam o registro com o valor mostrado na coluna do MAX(plays).

A solução pode ser dada de algumas formas diferentes no MySQL.

Subquery dependente

Uma solução simples é filtrar as músicas onde a coluna plays tenha o valor máximo de plays daquele artista, do método mais tradicional possível:

SELECT id_artista, nome, plays
FROM musica AS m1
WHERE plays
= ( SELECT MAX(plays)
                FROM musica AS m2
                WHERE m1
.id_artista = m2.id_artista )

E o resultado é obtido como esperado:

+------------+----------------+-------+
| id_artista | nome           | plays |
+------------+----------------+-------+
|          4 | Unleashed      |    28 |
|          8 | Running Free   |     9 |
|         15 | Focus on Sight |     4 |
|         16 | The World      |     6 |
+------------+----------------+-------+

Todavia, com um número grande de artistas a consulta ficará ineficiente, já que a subquery é processada para cada artista, retornando o MAX(plays) correspondente que então é utilizado para filtrar as músicas com esse número de execuções.

Tabela derivada

Para não ter que calcular a subquery para cada artista, podemos calcular todos os MAX(plays) em uma query na cláusula FROM e fazer um relacionamento com o resultado, como se fosse uma tabela normal:

SELECT m1.id_artista, m1.nome, m1.plays
FROM musica AS m1
JOIN
(
    SELECT id_artista
, MAX(plays) AS plays
    FROM musica
    GROUP BY id_artista
) AS derivada
        ON m1
.id_artista = derivada.id_artista
       AND m1
.plays = derivada.plays

O resultado é o mesmo, mas nesse caso a consulta é mais eficiente, pois o MySQL calcula todos os MAX(plays) em memória uma única vez e depois apenas relaciona os registros.

Otimizando no caso de MIN() e MAX()

O próprio manual do MySQL dá uma outra dica interessante quando o objetivo é pegar todo o registro com maior valor em determinada coluna.

A técnica utiliza LEFT JOIN na mesma tabela com critérios de ser o mesmo artista e ter o número de execuções maior do que o registro da tabela original.

Ao final, a consulta retorna apenas os registros que não foram relacionados (valor NULL no LEFT JOIN), que são os registros sem nenhum outro com número de execuções maior - ou seja, os que a coluna plays é na verdade MAX(plays) daquele artista.

SELECT m1.id_artista, m1.nome, m1.plays
FROM musica m1
LEFT JOIN musica m2
    ON m1
.id_artista = m2.id_artista
   AND m1
.plays < m2.plays
WHERE m2
.id IS NULL

Essa fica sendo, então, a forma mais eficiente de recuperar o registro com maior valor em determinada coluna, já que não é necessário nenhum tipo de subquery e nem mesmo GORUP BY. E se quiser o menor valor, basta trocar o sinal.

A dica não vale, entretanto, para outras funções de agregação, como AVG() para média. Neste caso não tem jeito senão com subquery.

  1. Nome: