Conjuntos de agrupamentos ou GROUPING SETS do Postgres – ROLLUP & CUBE

Na dica de hoje, você aprenderá sobre conjuntos de agrupamento e como usar a GROUPING SETS, cláusula PostgreSQL para gerar vários conjuntos de agrupamento em uma consulta.

Precisando de um servidor de banco de dados ou de gerenciamento e administração do seu servidor, entre em contato conosco, temos o prazer em ajudar. Acesse soloweb.com.br

O PostgreSQL é um dos melhores bancos de dados OLTP ( OLTP = processamento de transações online) do mundo. No entanto, ele pode fazer mais do que apenas OLTP. O PostgreSQL oferece muitos recursos adicionais relevantes para uma carga de trabalho mais no estilo OLAP. Um desses recursos é chamado de “GROUPING SETS”.

Antes de mergulharmos nos detalhes, vamos criar alguns dados de amostra que você pode carregar facilmente em seu banco de dados SQL:

-- Criação da tabela exemplo
CREATE TABLE vendas
(
    cidade	text,
    produto   	text,
    ano         int,
    qtd_venda   numeric
);
 
-- Carga de daods de exemplo
INSERT INTO vendas VALUES
    ('João Pessoa', 'Produto AAA', 2020, 12),
    ('João Pessoa', 'Produto AAA', 2021, 14),
    ('João Pessoa', 'Produto BBB', 2020, 54),
    ('João Pessoa', 'Produto BBB', 2021, 57),
    ('São Paulo', 'Produto AAA', 2020, 34),
    ('São Paulo', 'Produto AAA', 2021, 29),
    ('São Paulo', 'Produto BBB', 2020, 19),
    ('São Paulo', 'Produto BBB', 2021, 22),
    ('Rio de Janeiro', 'Produto AAA', 2020, 99),
    ('Rio de Janeiro', 'Produto AAA', 2021, 103),
    ('Rio de Janeiro', 'Produto BBB', 2020, 81),
    ('Rio de Janeiro', 'Produto BBB', 2021, 90)
;

Vamos deixa a estrutura da tabela e dados o mais simples possível para evitar problemas de compatibilidade de versões.

Vamos começar com uma agregação simples:

Não há muito o que dizer aqui, além do fato de que obteremos uma soma para cada grupo. No entanto, há um pouco de uma discussão filosófica acontecendo. “GROUP BY 1” significa basicamente “GROUP BY cidade”, que é o equivalente à primeira coluna da cláusula SELECT. Portanto “GROUP BY cidade” e “GROUP BY 1” são a mesma coisa:

Claro, isso também funciona com mais de uma coluna. No entanto, quero apontar outra coisa. Considere o seguinte exemplo:

A maioria das pessoas agrupa por uma coluna. Em alguns casos, pode fazer sentido agrupar por uma expressão. No meu caso, estamos formando grupos em tempo real (= um grupo para “SP” e outro para vendas fora de “SP”). Esse recurso geralmente é subestimado. No entanto, é útil em muitos cenários do mundo real. Lembre-se de que todas as coisas que você verá também funcionam com expressões, o que significa que um agrupamento mais flexível é possível.

GROUPING SETS / CONJUNTOS DE AGRUPAMENTO: Os blocos de construção básicos

GROUP BY transformará cada entrada distinta em uma coluna em um grupo. Às vezes, você pode querer fazer mais agrupamentos de uma só vez. Por que isso é necessário? Suponha que você esteja processando uma tabela de 10 TB. Claramente, a leitura desses dados costuma ser o fator limitante em termos de desempenho. Portanto, ler os dados uma vez e produzir mais resultados de uma só vez é atraente. Isso é exatamente o que você pode fazer com GROUP BY GROUP SETS. Suponha que queremos produzir dois resultados ao mesmo tempo:

GRUPO POR cidade
GRUPO POR produto

Veja como funciona:

Nesse caso, o PostgreSQL simplesmente anexa os resultados. As três primeiras linhas representam “GROUP BY cidade”. As próximas duas linhas contêm o resultado de “GROUP BY produto”. Logicamente, é o equivalente à seguinte consulta:

No entanto, a versão GROUPING SETS é muito mais eficiente porque só precisa ler os dados uma vez. 

ROLLUP: Adicionando a “linha de fundo”

Ao criar relatórios, muitas vezes você precisará do “resultado final” que resume o que foi mostrado na tabela. A maneira de fazer isso no SQL é usar “GROUP BY ROLLUP”:

O PostgreSQL injetará algumas linhas no resultado. Como você pode ver, “João Pessoa” retorna 3 e não apenas 2 linhas. A entrada “produto = NULL” foi adicionada por ROLLUP. Contém a soma de todas as vendas argentinas (116 + 27 = 137). Linhas adicionais são injetadas para ambos as outras cidade. Por fim, uma linha é adicionada para as vendas gerais em todas as cidade.

Freqüentemente, essas entradas NULL não são o que as pessoas desejam ver, portanto, pode fazer sentido substituí-las por algum outro tipo de entrada. A maneira de fazer isso é usar uma subselect que verifica a entrada NULL e faz a substituição. Veja como funciona:

Como você pode ver, todas as entradas NULL foram substituídas por “TOTAL”, que em muitos casos é a maneira mais desejável de exibir esses dados.

CUBE: Criando cubos de dados no PostgreSQL de forma eficiente

ROLLUP é útil se você quiser adicionar o “bottom line”. No entanto, muitas vezes você deseja ver todas as combinações de cidades e produtos. GROUP BY CUBE fará exatamente isso:

Neste caso, temos todas as combinações. Tecnicamente, é o mesmo que: GROUP BY cidade + GROUP BY produto + GROUP BY cidade_produto + GROUP BY (). Poderíamos fazer isso usando mais do que apenas uma instrução, mas fazê-lo de uma só vez é mais fácil – e muito mais eficiente.

Novamente, valores NULL foram adicionados para indicar vários níveis de agregação.

Conjuntos de agrupamento: planos de execução

Os conjuntos de agrupamento não apenas reescrevem a consulta para transformá-la em um UNION ALL – na verdade, há um código específico no mecanismo de banco de dados para executar essas agregações.

O que você verá é um “MixedAggregate” que é capaz de agregar em vários níveis ao mesmo tempo. Aqui está um exemplo:

Observar o MixedAggregate também revela quais agregações são executadas como parte do conjunto de agrupamento.

Em geral, os conjuntos de agrupamento são um recurso muito interessante que geralmente é desconhecido ou negligenciado. É altamente recomendável usar esse material incrível para acelerar suas agregações. É particularmente útil se você estiver lidando com um grande conjunto de dados.