Categorias:

Sintaxe de consulta

GROUP BY GROUPING SETS

GROUPING SETS é uma extensão poderosa da cláusula GROUP BY que computa várias cláusulas GROUP BY em uma única instrução. O conjunto de agrupamento é um conjunto de colunas de dimensão.

As expressões GROUPING SETS podem ser combinadas com outras expressões GROUP BY, tornando este constructo uma parte integrada da cláusula GROUP BY em vez de um constructo separado. Por exemplo, você pode escrever GROUP BY x, GROUPING SETS(y, z) para agrupar por coluna x em combinação com agrupamentos separados em y e z.

Uma expressão GROUPING SETS é equivalente à união de duas ou mais operações GROUP BY no mesmo conjunto de resultados. Por exemplo:

  • GROUP BY GROUPING SETS((a)) é equivalente à operação do conjunto de agrupamento único GROUP BY a.

  • GROUP BY GROUPING SETS((a), (b)) é equivalente a GROUP BY a UNION ALL GROUP BY b.

Observe que GROUPING SETS(a, b) sem parênteses adicionais é logicamente equivalente a GROUPING SETS((a), (b)) porque ambos criam dois conjuntos de agrupamento separados, um para a coluna a e outro para a coluna b. Essa expressão é bem diferente de GROUPING SETS((a, b)), que cria um único conjunto de agrupamento que agrupa por ambas as colunas.

Sintaxe

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]
Copy

Onde:

groupItem ::= { <column_alias> | <position> | <expr> }

groupSet ::= groupItem | ( groupItem [ , groupItem [ , ... ] ] )
Copy

Parâmetros

column_alias

Alias de coluna que aparece na lista SELECT do bloco de consulta.

position

Posição de uma expressão na lista SELECT.

expr

Qualquer expressão em tabelas no escopo atual.

Notas de uso

  • O Snowflake permite até 128 conjuntos de agrupamento no mesmo bloco de consulta.

  • Variações de sintaxe com parênteses:

    • GROUPING SETS(a, b) é uma abreviação de GROUPING SETS((a), (b)). Ambos criam dois conjuntos de agrupamento separados: um que agrupa pela coluna a, e outro que agrupa pela coluna b.

    • GROUPING SETS((a, b)) cria um único conjunto de agrupamento que agrupa por ambas as colunas a e b (semelhante a GROUP BY a, b).

  • Você pode combinar colunas GROUP BY regulares com GROUPING SETS: GROUP BY x, GROUPING SETS(y, z) agrupa pela coluna x em combinação com agrupamentos separados em y e z.

  • A saída normalmente contém alguns valores NULL. Como GROUP BY GROUPING SETS mescla os resultados de dois ou mais conjuntos de resultados, cada um deles agrupado por critérios diferentes, algumas colunas que têm um único valor em um conjunto de resultados podem ter muitos valores correspondentes no outro conjunto de resultados. Por exemplo, se você fizer a união de um conjunto de funcionários agrupados por departamento com um conjunto agrupado por tempo de serviço, nem todos os membros do conjunto com o maior tempo de serviço estarão necessariamente no mesmo departamento, portanto o valor de department_name é definido como NULL. Os exemplos a seguir contêm valores NULL por esse motivo.

Consulte também

Exemplos

Estes exemplos utilizam uma tabela de informações sobre enfermeiras treinadas para auxiliar em desastres. Todas essas enfermeiras têm licença para atuar na profissão (por exemplo, uma RN tem licença como «enfermeira registrada»), e uma licença adicional (por exemplo, em uma especialidade relacionada a desastres, como busca e resgate, comunicação via rádio etc.). Este exemplo simplifica e utiliza apenas duas categorias de licenças:

  • Enfermagem: RN (Enfermeira registrada) e LVN (Enfermeira profissional licenciada).

  • Rádio amador (“ham”): as licenças de rádio ham incluem “Técnico”, “Geral” e “Amador extra”.

Os seguintes comandos criam e carregam a tabela:

CREATE or replace TABLE nurses (
  ID INTEGER,
  full_name VARCHAR,
  medical_license VARCHAR,   -- LVN, RN, etc.
  radio_license VARCHAR      -- Technician, General, Amateur Extra
  )
  ;

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (201, 'Thomas Leonard Vicente', 'LVN', 'Technician'),
    (202, 'Tamara Lolita VanZant', 'LVN', 'Technician'),
    (341, 'Georgeann Linda Vente', 'LVN', 'General'),
    (471, 'Andrea Renee Nouveau', 'RN', 'Amateur Extra')
    ;
Copy

Esta consulta usa GROUP BY GROUPING SETS:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

As duas primeiras linhas mostram a contagem de RNs e LVNs (dois tipos de licenças de enfermagem). Os valores NULL na coluna radio_license para essas duas linhas são deliberados; a consulta agrupou todos os LVNs juntos (e todos os RNs juntos) independentemente de sua licença de rádio, de modo que os resultados não podem mostrar um valor na coluna radio_license para cada linha que necessariamente se aplica a todos os LVNs ou RNs agrupados naquela linha.

As três linhas seguintes mostram o número de enfermeiras com cada tipo de licença de rádio ham (“Técnico”, “Geral” e “Amador extra”). O valor NULL para medical_license em cada uma dessas três fileiras é deliberado porque nenhuma licença médica única se aplica necessariamente a todos os membros de cada uma dessas linhas.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

O exemplo a seguir demonstra a diferença entre agrupar por colunas separadamente e agrupar por colunas em conjunto. A consulta agrupa pela combinação de ambos medical_license e radio_license:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS ((medical_license, radio_license))
  ORDER BY 3 DESC NULLS FIRST;
Copy

Esta consulta produz linhas em que cada combinação de medical_license e radio_license aparece com a respectiva contagem. Ao contrário do exemplo anterior, não há valores NULL na saída porque a consulta agrupa por ambas as colunas juntas em vez de criar agrupamentos separados para cada coluna.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

O próximo exemplo mostra o que acontece quando algumas colunas contêm valores NULL. Comece acrescentando três novas enfermeiras que ainda não possuem licenças de rádio ham.

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (101, 'Lily Vine', 'LVN', NULL),
    (102, 'Larry Vancouver', 'LVN', NULL),
    (172, 'Rhonda Nova', 'RN', NULL)
    ;
Copy

Em seguida, faça a mesma consulta que antes:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

Por que agora há uma linha que tem NULL em ambas as colunas? E se todos os valores são NULL, por que o resultado de COUNT(*) é igual a 3?

A resposta é que NULL na coluna radio_license dessa linha ocorre porque três enfermeiras não têm nenhuma licença de rádio. (A consulta SELECT DISTINCT radio_license FROM nurses agora retorna quatro valores distintos: «Technician», «General», «Amateur Extra» e «NULL».)

O valor NULL na coluna medical_licenses ocorre pela mesma razão que os valores NULL nos resultados da consulta anterior: as enfermeiras contadas nessa linha têm licenças médicas diferentes, portanto um mesmo valor (RN ou LVN) não se aplica necessariamente a todas as enfermeiras contadas nessa linha.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

O exemplo a seguir demonstra a combinação das colunas GROUP BY regulares com GROUPING SETS. Esta consulta agrupa por medical_license, e dentro de cada grupo de licenças médicas, cria agregações separadas para cada valor radio_license e para todas as licenças de rádio combinadas:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, GROUPING SETS (radio_license, ())
  ORDER BY 3 DESC NULLS FIRST;
Copy

Para cada licença médica (LVN e RN), a saída mostra:

  • Linhas agrupadas por cada valor radio_license específico (Technician, General, Amateur Extra ou NULL para aqueles sem licença de rádio)

  • Uma linha de resumo com NULL na coluna radio_license que representa todas as enfermeiras com licença médica, independentemente da licença de rádio

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

Você pode comparar essa saída com a saída de uma consulta GROUP BY sem a cláusula GROUPING SETS:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license
  ORDER BY 3 DESC NULLS FIRST;
Copy
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

Uso da função GROUPING

A função utilitária GROUPING ajuda a identificar qual nível de agregação produziu cada linha. Isso é especialmente útil para distinguir entre valores NULL que resultam da operação de agrupamento e valores NULL reais nos dados.

A função GROUPING retorna:

  • 0 para uma linha agrupada na coluna especificada

  • 1 para uma linha não agrupada na coluna especificada (em que NULL aparece devido à agregação)

Este exemplo adiciona funções GROUPING à consulta para esclarecer a saída:

SELECT
    COUNT(*),
    medical_license,
    radio_license,
    GROUPING(medical_license) AS grp_medical,
    GROUPING(radio_license) AS grp_radio
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license);
Copy

As colunas grp_medical e grp_radio mostram quais colunas foram usadas para agrupamento:

  • Linhas 1-2: agrupadas por medical_license (grp_medical=0), não por radio_license (grp_radio=1)

  • Linhas 3-6: agrupadas por radio_license (grp_radio=0), não por medical_license (grp_medical=1)

  • Linha 6: o valor NULL em radio_license são dados reais (grp_radio=0), enquanto NULL em medical_license é da agregação (grp_medical=1)

+----------+-----------------+---------------+-------------+-----------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE | GRP_MEDICAL | GRP_RADIO |
|----------+-----------------+---------------+-------------+-----------|
|        2 | RN              | NULL          |           0 |         1 |
|        5 | LVN             | NULL          |           0 |         1 |
|        2 | NULL            | Technician    |           1 |         0 |
|        1 | NULL            | General       |           1 |         0 |
|        3 | NULL            | NULL          |           1 |         0 |
|        1 | NULL            | Amateur Extra |           1 |         0 |
+----------+-----------------+---------------+-------------+-----------+