Categorias:

Sintaxe de consulta

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS é uma extensão poderosa da cláusula GROUP BY que calcula várias cláusulas por grupo em uma única instrução. O conjunto do grupo é um conjunto de colunas de dimensões.

GROUP BY GROUPING SETS é equivalente a UNION de duas ou mais operações GROUP BY no mesmo conjunto de resultados:

  • 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.

Sintaxe

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

Onde:

groupSet ::= { <column_alias> | <position> | <expr> }
Copy
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.

  • A saída normalmente contém alguns valores NULL. Como GROUP BY ROLLUP funde 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 UNION de um conjunto de funcionários agrupados por departamento com um conjunto agrupado por tempo de serviço, os membros do conjunto com a maior tempo de serviço não estão necessariamente todos no mesmo departamento, então o valor do nome_setor é definido como NULL. Os exemplos a seguir contêm NULLs por esse motivo.

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 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”.

Aqui estão os comandos para criar e carregar 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 utiliza GROUP BY GROUPING SETS:

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

Saída:

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 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);
Copy

Saída:

As primeiras 5 linhas são as mesmas que na consulta anterior.

A última linha pode ser confusa no início – por que existe uma linha com NULL em ambas as colunas? E se todos os valores são NULL, por que o COUNT(*) é igual a 3?

A resposta é que a NULL na coluna RADIO_LICENSE dessa linha ocorre porque três enfermeiras não têm nenhuma licença de rádio. (“SELECT DISTINCT RADIO_LICENSE FROM enfermeiras” agora retorna quatro valores distintos: “Técnico”, “Geral”, “Amador extra” e “NULL”).

A coluna NULL na coluna MEDICAL_LICENSES ocorre pela mesma razão que os valores NULL ocorrem nos resultados da consulta anterior: os enfermeiros contados nesta linha têm MEDICAL_LICENSES diferentes, portanto, nenhum valor (“RN” ou “LVN”) se aplica necessariamente a todas as enfermeiras contadas nesta linha.

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

Se desejar, você pode comparar esta saída com a saída de um GROUP BY sem a cláusula GROUPING SETS:

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license;
Copy

Saída:

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
+----------+-----------------+---------------+
Linguagem: Português