Qual é a cláusula do SQL GROUPING SETS, e como você a utiliza?

Os GROUPING SETS são grupos, ou conjuntos, de colunas pelas quais as linhas podem ser agrupadas. Em vez de escrever várias consultas e combinar os resultados com um UNION, você pode simplesmente usar GROUPING SETS.

GROUPING SETS em SQL pode ser considerado uma extensão da cláusula GROUP BY. Ela permite que você defina múltiplos conjuntos de agrupamento na mesma consulta.

Vejamos sua sintaxe e como ela pode ser equivalente a uma GROUP BY com múltiplas cláusulas UNION ALL.

Sintaxe dos conjuntos de agrupamentos SQL

A sintaxe geral do GROUPING SETS é a seguinte:

SELECT
    aggregate_function(column_1)
    column_2,
    column_3,
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column_2, column_3),
        (column_2),
        (column_3),
        ()
);

Você pode ver como estamos nos agrupando pelos diferentes conjuntos.

Esta sintaxe é equivalente à seguinte consulta mais longa que usa GROUP BY com UNION ALL para combinar os resultados:

SELECT SUM(column_1), column_2, column_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

UNION ALL

SELECT SUM(column_1), NULL, column_3
FROM table_name
GROUP BY column_3

UNION ALL

SELECT SUM(column_1), NULL, NULL
FROM table_name

Se você usar o GROUP BY assim, você precisa de várias cláusulas UNION ALL para combinar os dados de diferentes fontes. UNION ALL também exige que todos os conjuntos de resultados tenham o mesmo número de colunas com tipos de dados compatíveis, portanto você precisa ajustar as consultas adicionando um valor NULL quando necessário.

Mesmo que a consulta funcione como você espera, ela tem dois problemas principais:

• Ela é longa e pouco administrável.
• Ela pode levar a um problema de desempenho, pois o SQL tem que escanear a tabela de vendas a cada vez.

A cláusula GROUPING SETS aborda estes problemas. Mas como ela afeta a saída em comparação com uma cláusula GROUP BY tradicional? É hora de olhar para um exemplo!

Exemplo de conjuntos de SQL GROUPING SETS

Precisamos de alguns exemplos de dados. Vamos criar uma tabela chamada payments que contém todos os pagamentos que nossa empresa recebeu em janeiro, fevereiro e março dos últimos quatro anos, de 2018 a 2021. A loja exata onde ocorreu o pagamento é assinalada pela coluna store_id.

Para criar esta tabela, execute a seguinte consulta:

CREATE TABLE payment (payment_amount decimal(8,2), payment_date date, store_id int);

INSERT INTO payment
VALUES
(1200.99, '2018-01-18', 1),
(189.23, '2018-02-15', 1),
(33.43, '2018-03-03', 3),
(7382.10, '2019-01-11', 2),
(382.92, '2019-02-18', 1),
(322.34, '2019-03-29', 2),
(2929.14, '2020-01-03', 2),
(499.02, '2020-02-19', 3),
(994.11, '2020-03-14', 1),
(394.93, '2021-01-22', 2),
(3332.23, '2021-02-23', 3),
(9499.49, '2021-03-10', 3),
(3002.43, '2018-02-25', 2),
(100.99, '2019-03-07', 1),
(211.65, '2020-02-02', 1),
(500.73, '2021-01-06', 3);

Você pode visualizar os dados usando esta simples cláusula SELECT:

SELECT * FROM payment ORDER BY payment_date;

A execução desta consulta produz o resultado:

Você pode ver que existem múltiplas entradas para algumas lojas. Imagine que estamos preparando um relatório e queremos ver um total para cada loja. A função agregada SUM() pode nos ajudar com isto. Também usaremos a cláusula GROUP BY para agrupar nossos resultados por ano e loja.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY YEAR(payment_date), store_id
ORDER BY YEAR(payment_date), store_id;

Os resultados são agregados por cada combinação única de ano e loja.

Entretanto, não podemos ver o total de pagamentos por ano: o total de pagamentos para 2018, 2019, 2020, ou 2021. Também não podemos ver os totais por loja, o que seria uma métrica útil de se ter. O uso de GROUPING SETS nos permite ver estes totais.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id;

Uau, nossos resultados mudaram drasticamente! Agora, vemos apenas os totais totais gerais de cada loja junto com os totais gerais de cada ano.

Para as colunas pelas quais as filas não estão agrupadas, você vê os valores NULL.

Lembre-se que você pode incluir múltiplas cláusulas GROUP BY em seu GROUPING SETS. Aplicando isto à nossa consulta, obtemos os seguintes resultados:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS
(
(YEAR(payment_date), store_id),
(YEAR(payment_date)),
(store_id)
)
ORDER BY YEAR(payment_date), store_id;

Antes de terminar este tutorial, devemos mencionar duas outras extensões do SQL GROUP BY que podem ser úteis para seu projeto ou cenário em particular: ROLLUP e CUBE. Estes tópicos são abordados em grande detalhe neste SQL Avançado learning track de LearnSQL.com.br que apresenta funções de janela, GROUP BY extensões, e consultas recursivas.

Exemplo SQL ROLLUP

Similar a GROUPING SETS, você pode usar a opção ROLLUP em uma única consulta para gerar vários conjuntos de agrupamentos.

ROLLUP assume uma hierarquia entre as colunas de entrada. Por exemplo, se as colunas de entrada forem:

GROUP BY ROLLUP(column_1,column_2)

a hierarquia para isso é column_1 > column_2, e ROLLUP gera os seguintes conjuntos de agrupamentos:

(column_1, column_2)
(column_1)
()

ROLLUP gera todos os conjuntos de agrupamento que fazem sentido nesta hierarquia. Ele gera uma linha subtotal toda vez que o valor de column_1 muda; esta é a hierarquia que fornecemos. Por esta razão, muitas vezes usamos ROLLUP para gerar subtotais e grandes totais em relatórios. A ordenação de suas colunas em ROLLUP é muito importante.

Vejamos uma consulta que usa ROLLUP:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

O total geral é mostrado na parte superior do resultado:

O resto do resultado é estruturado da seguinte forma. Primeiro, o total anual é mostrado:

Em seguida, os totais por loja por ano:

Como você pode ver, ROLLUP gera uma linha subtotal toda vez que o valor de Payment Year muda, já que esta é a hierarquia que fornecemos. Este exemplo mostra como ROLLUP pode ser útil para fins de relatórios.

Exemplo de CUBE SQL

Similar a ROLLUP, CUBE é uma extensão da cláusula GROUP BY. Ela permite gerar subtotais para todas as combinações das colunas de agrupamento especificadas na cláusula GROUP BY.

O CUBE é como combinar GROUPING SETS e ROLLUP. Ele mostra a saída detalhada de ambos.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

A principal diferença nesta saída do exemplo ROLLUP é que o total geral para cada loja também é mostrado aqui.

Além destas linhas, todas as linhas neste resultado são as mesmas que o resultado do ROLLUP.

Isto conclui nossa comparação de GROUPING SETS, ROLLUP, e CUBE! Você pode encontrar mais exemplos neste artigo sobre agrupamento, rolagem e cubagem de dados.

Agrupe seus dados de forma eficaz com o SQL GROUP BY Extensions

O domínio das extensões SQL GROUP BY será praticado. Opções como GROUPING SETS, ROLLUP, e CUBE permitem manipular os resultados de suas consultas de diferentes maneiras. Saber como usar essas extensões reduz efetivamente a necessidade de formatar manualmente seus dados antes de repassá-los aos interessados relevantes.

Para expandir ainda mais seus conhecimentos nesta área, considere este curso de extensão GROUP BY de LearnSQL.com.br que abrange GROUPING SETS, ROLLUP, e CUBE.