Conjuntos de agrupamento do PostgreSQL

Resumo: neste tutorial, você aprenderá sobre o conceito de conjuntos de agrupamento e como usar a cláusula do PostgreSQL GROUPING SETS para gerar múltiplos conjuntos de agrupamento em uma consulta.

Configurar uma tabela de exemplo

Deixamos começar criando uma nova tabela chamadasales para a demonstração.

Code language: SQL (Structured Query Language) (sql)

A tabela sales armazena o número de produtos vendidos por marca e segmento.

Introdução ao conjunto de agrupamentos do PostgreSQL

Um conjunto de agrupamentos é um conjunto de colunas pelo qual você agrupa usando a cláusula GROUP BY.

Um conjunto de agrupamentos é denotado por uma lista separada por vírgula de colunas colocadas entre parênteses:

Por exemplo, a consulta seguinte usa a cláusula GROUP BY para retornar o número de produtos vendidos por marca e segmento. Em outras palavras, define um conjunto de agrupamento da marca e segmento que é denotado por (brand, segement)

Code language: SQL (Structured Query Language) (sql)

A consulta seguinte encontra o número de produtos vendidos por uma marca. Define um conjunto de agrupamentos (brand):

Code language: SQL (Structured Query Language) (sql)

A consulta seguinte encontra o número de produtos vendidos por segmento. Define um conjunto de agrupamentos (segment):

Code language: SQL (Structured Query Language) (sql)

A consulta seguinte encontra o número de produtos vendidos para todas as marcas e segmentos. Ela define um conjunto de agrupamento vazio que é indicado por ().

Code language: SQL (Structured Query Language) (sql)

Se você quiser todos os conjuntos de agrupamento usando uma única consulta. Para conseguir isso, você pode usar o UNION ALL para combinar todas as consultas acima.

Porque UNION ALL requer que todos os conjuntos de resultados tenham o mesmo número de colunas com tipos de dados compatíveis, você precisa ajustar as consultas adicionando NULL à lista de seleção de cada uma como mostrado abaixo:

Code language: SQL (Structured Query Language) (sql)

Esta consulta gerou um único conjunto de resultados com os agregados para todos os conjuntos de agrupamento.

Even embora a consulta acima funcione como você esperava, ela tem dois problemas principais.

• Primeiro, ela é bastante longa.
• Segundo, ela tem um problema de performance porque o PostgreSQL tem que digitalizar a tabela sales separadamente para cada consulta.

Para torná-lo mais eficiente, o PostgreSQL fornece a cláusula GROUPING SETS que é a subcláusula da GROUP BY cláusula.

A GROUPING SETS permite que você defina múltiplos conjuntos de agrupamento na mesma consulta.

A sintaxe geral do GROUPING SETS é a seguinte:

Code language: SQL (Structured Query Language) (sql)

Nesta sintaxe, nós temos quatro conjuntos de agrupamento (c1,c2), (c1), (c2), e ().

Para aplicar esta sintaxe ao exemplo acima, você pode usar a cláusula GROUPING SETS em vez da cláusula UNION ALL como esta:

Code language: SQL (Structured Query Language) (sql)

Esta consulta é muito mais curta e mais legível. Além disso, o PostgreSQL otimizará o número de vezes que ele escaneia a tabela sales e não escaneia múltiplas vezes.

Função de agrupamento

A função GROUPING() aceita um argumento que pode ser um nome de coluna ou uma expressão:

A cláusula column_name ou expression deve combinar com a especificada na cláusula GROUP BY.

A função GROUPING() retorna o bit 0 se o argumento for um membro do conjunto de agrupamento atual e 1 caso contrário.

Veja o seguinte exemplo:

Code language: SQL (Structured Query Language) (sql)

Como mostrado na captura de tela, quando o valor no grouping_brand é 0, a coluna sum mostra o subtotal do brand.

Quando o valor no grouping_segment é zero, a coluna de soma mostra o subtotal do segment.

Você pode usar a função GROUPING() na cláusula HAVING para encontrar o subtotal de cada marca assim:

Neste tutorial, você aprendeu como usar o PostgreSQL GROUPING SETS para gerar múltiplos conjuntos de agrupamentos.