Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
GROUP BY dá a você 1 linha por grupo e nenhuma linha para o todo. WITH ROLLUP adiciona essa linha, e os subtotais intermediários quando você agrupa por várias colunas. Isso evita executar uma segunda consulta e somar os números por conta própria. Abra o cliente com mysql -u root -p sakila antes de começar.

Como WITH ROLLUP funciona

Substitua GROUPING_COLUMN pela coluna que define os grupos, TABLE_NAME pela tabela, e AGGREGATE_FUNCTION(COLUMN_NAME) pelo que calcular por grupo. WITH ROLLUP vai no final da cláusula GROUP BY. O servidor retorna os grupos normais, depois linhas extras em que uma coluna de agrupamento é posta de lado e seu agregado cobre tudo. Ele coloca NULL nas colunas que pôs de lado, e é assim que você identifica essas linhas.

Exemplos

  1. Adicione uma linha de total

6 linhas em vez de 5. A última tem NULL onde uma classificação estaria, e sua contagem é a tabela inteira.

  1. Subtotais também

Agrupe por 2 colunas e você obtém um subtotal por valor da primeira, depois o total geral.
Leia uma linha pelas colunas que estão NULL. G / NULL / 3 é o subtotal de G em todas as taxas. NULL / NULL / 10 é o total geral, que bate com os 10 filmes que duram 185 minutos. As linhas de rollup vêm logo depois dos grupos que elas resumem, em vez de se juntarem no final, então o resultado é lido de cima para baixo como um relatório.

Distinguindo um NULL de rollup de um NULL real

O NULL em uma linha de rollup significa “todos os valores desta coluna”. Um NULL nos dados significa “nenhum valor registrado”. Eles são impressos de forma idêntica, então em uma coluna que pode ser NULL você não consegue distinguir os 2 só de olhar. GROUPING() responde à pergunta diretamente. Ela retorna 1 quando a coluna foi posta de lado naquela linha e 0 caso contrário.
rating nunca é NULL no Sakila, então aqui as 2 perguntas têm a mesma resposta. Em uma coluna que de fato contém NULLs, testar rating IS NULL pegaria os dois tipos de linha e GROUPING(rating) = 1 pega apenas o rollup.

ORDER BY move o total, e muda a ordenação

Os exemplos acima não têm ORDER BY, e isso é proposital: WITH ROLLUP coloca cada linha de rollup logo abaixo dos grupos que ela resume, e ordenar joga esse arranjo fora.
2 coisas se moveram. O total geral foi ordenado para o topo, porque NULL vem antes de todo valor real na ordenação. E as classificações saíram em ordem alfabética, e não na ordem declarada do ENUM que GROUP BY mostrou, porque ordenar um resultado de rollup o ordena como texto. Então decida o que você quer. Deixe o ORDER BY de fora para um relatório que é lido como subtotais sob seus grupos, ou adicione-o e aceite que as linhas de rollup caem onde a ordenação as colocar.

Rotulando as linhas de rollup

Uma coluna de NULLs não é o que você quer em um relatório. COALESCE retorna seu primeiro argumento que não é NULL, então ele coloca uma palavra ali em vez disso.
Isso fica bem legível, e só é seguro aqui porque rating nunca é NULL nos dados. Em uma coluna que pode ser NULL, COALESCE rotularia lacunas reais como totais, então use GROUPING() no teste em vez disso.

Resumo

  • Adicione WITH ROLLUP ao final de GROUP BY para obter linhas de subtotal e de total.
  • Leia NULL em uma coluna de agrupamento como “todos os valores desta coluna”.
  • Espere um subtotal por valor de cada coluna anterior quando você agrupa por várias.
  • Use GROUPING(column) para distinguir uma linha de rollup de uma linha em que os dados são NULL.
  • Espere que ORDER BY mova as linhas de rollup e ordene um ENUM como texto.

Veja também