Resumo : neste tutorial, você aprenderá como usar a ROLLUPcláusula MySQL para gerar subtotais e totais gerais.
Configurando uma tabela de exemplo
A instrução a seguir cria uma nova tabela chamada salesque armazena os valores dos pedidos resumidos por linhas de produtos e anos. Os dados vêm das tabelas products, orderse orderDetailsno banco de dados de amostra .
CREATE TABLE sales
SELECT
productLine,
YEAR(orderDate) orderYear,
SUM(quantityOrdered * priceEach) orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A consulta a seguir retorna todas as linhas da salestabela:
SELECT * FROM sales;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------------+-----------+------------+
| productLine | orderYear | orderValue |
+------------------+-----------+------------+
| Classic Cars | 2003 | 1374832.22 |
| Classic Cars | 2004 | 1763136.73 |
| Classic Cars | 2005 | 715953.54 |
| Motorcycles | 2003 | 348909.24 |
| Motorcycles | 2004 | 527243.84 |
| Motorcycles | 2005 | 245273.04 |
| Planes | 2003 | 309784.20 |
| Planes | 2004 | 471971.46 |
| Planes | 2005 | 172881.88 |
| Ships | 2003 | 222182.08 |
| Ships | 2004 | 337326.10 |
| Ships | 2005 | 104490.16 |
| Trains | 2003 | 65822.05 |
| Trains | 2004 | 96285.53 |
| Trains | 2005 | 26425.34 |
| Trucks and Buses | 2003 | 376657.12 |
| Trucks and Buses | 2004 | 465390.00 |
| Trucks and Buses | 2005 | 182066.45 |
| Vintage Cars | 2003 | 619161.48 |
| Vintage Cars | 2004 | 854551.85 |
| Vintage Cars | 2005 | 323846.30 |
+------------------+-----------+------------+
21 rows in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Visão geral do ROLLUP do MySQL
Um conjunto de agrupamento é um conjunto de colunas nas quais você deseja agrupar. Por exemplo, a consulta a seguir cria um conjunto de agrupamento denotado por(productline)
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------------+-----------------+
| productline | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
+------------------+-----------------+
7 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )A consulta a seguir cria um conjunto de agrupamento vazio indicado por ():
SELECT
SUM(orderValue) totalOrderValue
FROM
sales;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-----------------+
| totalOrderValue |
+-----------------+
| 9604190.61 |
+-----------------+
1 row in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Se quiser gerar dois ou mais conjuntos de agrupamentos em uma consulta, você pode usar o UNION ALLoperador da seguinte maneira:
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline
UNION ALL
SELECT
NULL,
SUM(orderValue) totalOrderValue
FROM
sales;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está o resultado da consulta:
+------------------+-----------------+
| productline | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
| NULL | 9604190.61 |
+------------------+-----------------+
8 rows in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Como UNION ALLexige que todas as consultas tenham o mesmo número de colunas, adicionamos NULLa lista de seleção da segunda consulta para atender a esse requisito.
O NULLna productLinecoluna identifica a linha superagregada total.
Esta consulta pode gerar os valores totais do pedido por linhas de produto e também a linha do total geral. No entanto, tem dois problemas:
- A consulta é bastante demorada.
- O desempenho da consulta pode não ser bom, pois o mecanismo de banco de dados precisa executar internamente duas consultas separadas e combinar os conjuntos de resultados em um.
Para corrigir esses problemas, você pode usar a ROLLUPcláusula.
A ROLLUPcláusula é uma extensão da GROUP BYcláusula com a seguinte sintaxe:
SELECT
select_list
FROM
table_name
GROUP BY
c1, c2, c3 WITH ROLLUP;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O ROLLUPgera vários conjuntos de agrupamento com base nas colunas ou expressões especificadas na GROUP BYcláusula. Por exemplo:
SELECT
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline WITH ROLLUP;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está a saída:
+------------------+-----------------+
| productLine | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
| NULL | 9604190.61 |
+------------------+-----------------+
8 rows in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Conforme mostrado claramente na saída, a ROLLUPcláusula gera não apenas os subtotais, mas também o total geral dos valores do pedido.
Se você tiver mais de uma coluna especificada na GROUP BYcláusula, a ROLLUPcláusula assumirá uma hierarquia entre as colunas de entrada.
Por exemplo:
GROUP BY c1, c2, c3 WITH ROLLUPLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )O ROLLUPassume que existe a seguinte hierarquia:
c1 > c2 > c3Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ele gera os seguintes conjuntos de agrupamento:
(c1, c2, c3)
(c1, c2)
(c1)
()Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Se você tiver duas colunas especificadas na GROUP BYcláusula:
GROUP BY c1, c2 WITH ROLLUPLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )então ROLLUPgera os seguintes conjuntos de agrupamento:
(c1, c2)
(c1)
()Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Veja o seguinte exemplo de consulta:
SELECT
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está a saída:
+------------------+-----------+-----------------+
| productLine | orderYear | totalOrderValue |
+------------------+-----------+-----------------+
| Classic Cars | 2003 | 1374832.22 |
| Classic Cars | 2004 | 1763136.73 |
| Classic Cars | 2005 | 715953.54 |
| Classic Cars | NULL | 3853922.49 |
| Motorcycles | 2003 | 348909.24 |
| Motorcycles | 2004 | 527243.84 |
| Motorcycles | 2005 | 245273.04 |
| Motorcycles | NULL | 1121426.12 |
| Planes | 2003 | 309784.20 |
| Planes | 2004 | 471971.46 |
| Planes | 2005 | 172881.88 |
| Planes | NULL | 954637.54 |
| Ships | 2003 | 222182.08 |
| Ships | 2004 | 337326.10 |
| Ships | 2005 | 104490.16 |
| Ships | NULL | 663998.34 |
| Trains | 2003 | 65822.05 |
| Trains | 2004 | 96285.53 |
| Trains | 2005 | 26425.34 |
| Trains | NULL | 188532.92 |
| Trucks and Buses | 2003 | 376657.12 |
| Trucks and Buses | 2004 | 465390.00 |
| Trucks and Buses | 2005 | 182066.45 |
| Trucks and Buses | NULL | 1024113.57 |
| Vintage Cars | 2003 | 619161.48 |
| Vintage Cars | 2004 | 854551.85 |
| Vintage Cars | 2005 | 323846.30 |
| Vintage Cars | NULL | 1797559.63 |
| NULL | NULL | 9604190.61 |
+------------------+-----------+-----------------+
29 rows in set (0.00 sec)Linguagem de código: PHP ( php )O ROLLUPgera a linha de subtotal sempre que a linha de produto muda e o total geral no final do resultado.
A hierarquia neste caso é:
productLine > orderYearLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Se você inverter a hierarquia, por exemplo:
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-----------+------------------+-----------------+
| orderYear | productLine | totalOrderValue |
+-----------+------------------+-----------------+
| 2003 | Classic Cars | 1374832.22 |
| 2003 | Motorcycles | 348909.24 |
| 2003 | Planes | 309784.20 |
| 2003 | Ships | 222182.08 |
| 2003 | Trains | 65822.05 |
| 2003 | Trucks and Buses | 376657.12 |
| 2003 | Vintage Cars | 619161.48 |
| 2003 | NULL | 3317348.39 |
| 2004 | Classic Cars | 1763136.73 |
| 2004 | Motorcycles | 527243.84 |
| 2004 | Planes | 471971.46 |
| 2004 | Ships | 337326.10 |
| 2004 | Trains | 96285.53 |
| 2004 | Trucks and Buses | 465390.00 |
| 2004 | Vintage Cars | 854551.85 |
| 2004 | NULL | 4515905.51 |
| 2005 | Classic Cars | 715953.54 |
| 2005 | Motorcycles | 245273.04 |
| 2005 | Planes | 172881.88 |
| 2005 | Ships | 104490.16 |
| 2005 | Trains | 26425.34 |
| 2005 | Trucks and Buses | 182066.45 |
| 2005 | Vintage Cars | 323846.30 |
| 2005 | NULL | 1770936.71 |
| NULL | NULL | 9604190.61 |
+-----------+------------------+-----------------+
25 rows in set (0.00 sec)Linguagem de código: PHP ( php )O ROLLUPgera o subtotal sempre que o ano muda e o total geral no final do conjunto de resultados.
A hierarquia neste exemplo é:
orderYear > productLine
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A função GROUPING()
Para verificar se NULLo conjunto de resultados representa os subtotais ou totais gerais, você usa a GROUPING()função.
A GROUPING()função retorna 1 quando NULLocorre em uma linha agregada da ceia, caso contrário, retorna 0.
A GROUPING()função pode ser usada na lista de seleção, HAVINGcláusula e (a partir do MySQL 8.0.12) ORDER BY.
Considere a seguinte consulta:
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue,
GROUPING(orderYear),
GROUPING(productLine)
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-----------+------------------+-----------------+---------------------+-----------------------+
| orderYear | productLine | totalOrderValue | GROUPING(orderYear) | GROUPING(productLine) |
+-----------+------------------+-----------------+---------------------+-----------------------+
| 2003 | Classic Cars | 1374832.22 | 0 | 0 |
| 2003 | Motorcycles | 348909.24 | 0 | 0 |
| 2003 | Planes | 309784.20 | 0 | 0 |
| 2003 | Ships | 222182.08 | 0 | 0 |
| 2003 | Trains | 65822.05 | 0 | 0 |
| 2003 | Trucks and Buses | 376657.12 | 0 | 0 |
| 2003 | Vintage Cars | 619161.48 | 0 | 0 |
| 2003 | NULL | 3317348.39 | 0 | 1 |
| 2004 | Classic Cars | 1763136.73 | 0 | 0 |
| 2004 | Motorcycles | 527243.84 | 0 | 0 |
| 2004 | Planes | 471971.46 | 0 | 0 |
| 2004 | Ships | 337326.10 | 0 | 0 |
| 2004 | Trains | 96285.53 | 0 | 0 |
| 2004 | Trucks and Buses | 465390.00 | 0 | 0 |
| 2004 | Vintage Cars | 854551.85 | 0 | 0 |
| 2004 | NULL | 4515905.51 | 0 | 1 |
| 2005 | Classic Cars | 715953.54 | 0 | 0 |
| 2005 | Motorcycles | 245273.04 | 0 | 0 |
| 2005 | Planes | 172881.88 | 0 | 0 |
| 2005 | Ships | 104490.16 | 0 | 0 |
| 2005 | Trains | 26425.34 | 0 | 0 |
| 2005 | Trucks and Buses | 182066.45 | 0 | 0 |
| 2005 | Vintage Cars | 323846.30 | 0 | 0 |
| 2005 | NULL | 1770936.71 | 0 | 1 |
| NULL | NULL | 9604190.61 | 1 | 1 |
+-----------+------------------+-----------------+---------------------+-----------------------+
25 rows in set (0.00 sec)Linguagem de código: PHP ( php )Retorna GROUPING(orderYear)1 quando NULLa orderYearcoluna ocorre em uma linha superagregada, 0 caso contrário.
Da mesma forma, GROUPING(productLine)retorna 1 quando NULLna productLinecoluna ocorre em uma linha superagregada, 0 caso contrário.
Freqüentemente usamos GROUPING()funções para substituir rótulos significativos por valores superagregados, NULLem vez de exibi-los diretamente.
O exemplo a seguir mostra como combinar a IF()função com a função para substituir rótulos para os valores GROUPING()superagregados nas colunas e :NULLorderYearproductLine
SELECT
IF(GROUPING(orderYear),
'All Years',
orderYear) orderYear,
IF(GROUPING(productLine),
'All Product Lines',
productLine) productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear ,
productline
WITH ROLLUP;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A saída é:
+-----------+-------------------+-----------------+
| orderYear | productLine | totalOrderValue |
+-----------+-------------------+-----------------+
| 2003 | Classic Cars | 1374832.22 |
| 2003 | Motorcycles | 348909.24 |
| 2003 | Planes | 309784.20 |
| 2003 | Ships | 222182.08 |
| 2003 | Trains | 65822.05 |
| 2003 | Trucks and Buses | 376657.12 |
| 2003 | Vintage Cars | 619161.48 |
| 2003 | All Product Lines | 3317348.39 |
| 2004 | Classic Cars | 1763136.73 |
| 2004 | Motorcycles | 527243.84 |
| 2004 | Planes | 471971.46 |
| 2004 | Ships | 337326.10 |
| 2004 | Trains | 96285.53 |
| 2004 | Trucks and Buses | 465390.00 |
| 2004 | Vintage Cars | 854551.85 |
| 2004 | All Product Lines | 4515905.51 |
| 2005 | Classic Cars | 715953.54 |
| 2005 | Motorcycles | 245273.04 |
| 2005 | Planes | 172881.88 |
| 2005 | Ships | 104490.16 |
| 2005 | Trains | 26425.34 |
| 2005 | Trucks and Buses | 182066.45 |
| 2005 | Vintage Cars | 323846.30 |
| 2005 | All Product Lines | 1770936.71 |
| All Years | All Product Lines | 9604190.61 |
+-----------+-------------------+-----------------+Neste tutorial, você aprendeu como usar o MySQL ROLLUP()para gerar vários conjuntos de agrupamentos considerando uma hierarquia entre colunas especificadas na GROUP BYcláusula.
