Modificadores GROUP BY


No MariaDB, a cláusula GROUP BY permite um modificador WITH ROLLUP que faz com que uma linha extra seja adicionada à saida resumo. Estas linhas representam operações de resumo de nível mais alto (ou super agregadas). Assim, o ROLLUP permite que você responda questões em multiplos níveis de análise com uma única consulta. Ele pode ser usado, por exemplo, para fornecer suporte para operações OLAP (Online Analytical Processing - Processamento Analítico OnLine).

Como ilustração, suponha que uma tabela chamada sales tenha as colunas year, country, product e profit para registrar as vendas lucrativas: 

CREATE TABLE sales
(
 year INT NOT NULL,
 country VARCHAR(20) NOT NULL,
 product VARCHAR(32) NOT NULL,
 profit INT
);

O conteúdo da tabela pode ser resumido pode ano com um simples GROUP BY como este: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+-------------+

Esta saída mostra o lucro total para cada ano, mas se você também quiser determinar o lucro total somado em todos os anos, você deve adicionar os valores adicionais ou executar uma consulta adicional.

Ou você pode usar o ROLLUP, que fornece os dois níveis de análise com uma única consulta. Adicionando um modificador WITH ROLLUP a cláusula GROUP BY faz com que a consulta produza outra linha que mostra o total geral de todos os anos: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+

A linha de total super-agrupada é identificada pelo valor NULL na coluna year.

ROLLUP tem um efeito mais complexo quando há múltiplas colunas GROUP BY. Neste caso, cada vez que houver um break (alteração no valor) em qualquer agrupamento, com exceção da última coluna, a consulta produz um linha resumo super-agrupada extra.

Por exemplo, sem ROLLUP, um resumo na tabela sales baseada no year, country e product pode se parecer com isto: 

mysql> SELECT year, country, product, SUM(profit)
 -> FROM sales
 -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
+------+---------+------------+-------------+

A saída indica os valores resumidos apenas no nível year/country/product da análise. Quando ROLLUP é adicionado, a consulta produz diversas linhas extras: 

mysql> SELECT year, country, product, SUM(profit)
 -> FROM sales
 -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+

Para esta consulta, adicionar ROLLUP faz com que a saída inclua uma informação resumida nos qualtro níveis de análise, não só em um. Aqui está como interpretar a saída ROLLUP:

Outras Considerações ao Usar ROLLUP

O seguinte item lista alguns comportamentos específicaos para a implementação do ROLLUP no MySQL:

Quando você usa ROLLUP, você não pode usar uma cláusula ORDER BY para ordenar os resultados. (Em outras palavras, ROLLUP e ORDER BY são exclusivos mutualmente.) No entanto, você ainda tem algum controle sobre a ordem de ordenação. O GROUP BY no MariaDB ordena os resultados, e você pode usar as palavras chaves ASC e DESC explicitamente com colunas chamadas na lista GROUP BY para especificar a ordem de classificação para colunas individuais. (A linha resumo de nível mais alto adicionado por ROLLUP ainda aparece depois da linha para as quais elas são calculadas, considerando a ordenação.)

LIMIT pode ser usado para restringir o númerod e linhas retornadas para o cliente. LIMIT é aplicado depois do ROLLUP, assim o limite se aplica contra as linhas extras adicionadas por ROLLUP. Por exemplo: 

mysql> SELECT year, country, product, SUM(profit)
 -> FROM sales
 -> GROUP BY year, country, product WITH ROLLUP
 -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+-------------+

Note que usar LIMIT com ROLLUP pode produzir resultados mais difíceis de interpretar, porque você têm menos contexto para entender as linhas super agrupadas.

O indicador NULL em cada linha super-agrupadas são produzidas quando a linha é enviada para o cliente. O servidor olha por cada coluna chamada na cláusula GROUP BY seguindo aquela mais a esquerda que tem o valor alterado. Para qualquer coluna no resultado com o nome que é uma combinação léxica de qualquer daqueles nomes, seu valor é definido com NULL. (Se você especifica o agrupamento de colunas pelo número da coluna, o servidor identifica quais colunas definir com NULL pelo número.)

Como os valores NULL em linhas super agrupadas são colocadas dentro do resultado como um estágio posterior no processamento da consulta, você não pode testá-los com valores NULL dentro da própria consulta. Por exemplo, você não pode adicionar HAVING product IS NULL a consulta para eliminar da saída todas as linhas com exceção das agrupadas.

Por outro lado, o valor NULL aparece como NULL no lado do cliente e pode ser testado usando qualquer interface de programação do cliente MySQL.

Retornar