Catégories :

Syntaxe de requête

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS est une extension puissante de la clause GROUP BY qui permet de calculer plusieurs clauses group-by en une seule instruction. L’ensemble groupe est un ensemble de colonnes de dimensions.

GROUP BY GROUPING SETS est équivalent à UNION de deux ou plusieurs opérations GROUP BY dans le même ensemble de résultats :

  • GROUP BY GROUPING SETS((a)) est équivalent à l’opération de regroupement unique GROUP BY a.

  • GROUP BY GROUPING SETS((a),(b)) est équivalent à GROUP BY a UNION ALL GROUP BY b.

Syntaxe

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

Où :

groupSet ::= { <column_alias> | <position> | <expr> }
alias_colonne

Alias de colonne figurant dans la liste SELECT du bloc de requête.

position

Position d’une expression dans la liste SELECT.

expr

Toute expression sur les tables du périmètre actuel.

Notes sur l’utilisation

  • Snowflake permet de regrouper jusqu’à 128 ensembles de regroupements dans le même bloc de requête.

  • The output typically contains some NULL values. Because GROUP BY ROLLUP merges the results of two or more result sets, each of which was grouped by different criteria, some columns that have a single value in one result set might have many corresponding values in the other result set. For example, if you do a UNION of a set of employees grouped by department with a set grouped by seniority, the members of the set with the greatest seniority are not necessarily all in the same department, so the value of department_name is set to NULL. The examples below include examples that contain NULLs for this reason.

Exemples

These examples use a table of information about nurses who are trained to assist in disasters. All of these nurses have a license as nurses (e.g. an RN has a license as a « Registered Nurse »), and an additional license in a disaster-related specialty, such as search and rescue, radio communications, etc. This example simplifies and uses just two categories of licenses:

  • Soins infirmiers : RN (Registered Nurse, ou infirmière autorisée) et LVN (Licensed Vocational Nurse, ou infirmière professionnelle agréée).

  • Radio amateur : les licences de radio amateur comprennent « Technicien », « Général » et « Amateur Extra ».

Voici les commandes pour créer et charger la table :

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

Cette requête utilise GROUP BY GROUPING SETS :

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

Sortie :

Les deux premières lignes indiquent le nombre de RNs et LVNs (deux types d’autorisation d’exercer l’activité d’infirmière). Les valeurs NULL dans la colonne RADIO_LICENSE pour ces deux lignes sont délibérées ; la requête a regroupé tous les LVNs ensemble (et tous les RNs ensemble) indépendamment de leur licence radio. Ainsi, les résultats ne peuvent pas afficher une valeur dans la colonne RADIO_LICENSE pour chaque ligne qui s’applique nécessairement à tous les LVNs ou RNs groupés dans cette ligne.

The next three rows show the number of nurses with each type of ham radio license (« Technician », « General », and « Amateur Extra »). The NULL value for MEDICAL_LICENSE in each of those three rows is deliberate because no single medical license necessarily applies to all members of each of those rows.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+===============+
|        3 |            LVN  | NULL          |
|        1 |            RN   | NULL          |
|        2 |            NULL | TECHNICIAN    |
|        1 |            NULL | GENERAL       |
|        1 |            NULL | AMATEUR EXTRA |
+----------+-----------------+---------------+

L’exemple suivant montre ce qui se passe lorsque certaines colonnes contiennent des valeurs NULL. Commencez par ajouter trois nouvelles infirmières qui n’ont pas encore de permis de radio amateur.

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

Exécutez ensuite la même requête qu’auparavant :

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

Sortie :

Les 5 premières lignes sont les mêmes que dans la requête précédente.

La dernière ligne pourrait être déroutante au début – pourquoi y a-t-il une ligne qui a NULL dans les deux colonnes ? Et si toutes les valeurs sont NULL, pourquoi le COUNT(*) est égal à 3 ?

La réponse est que le NULL dans la colonne RADIO_LICENSE de cette rangée se produit parce que trois infirmières n’ont pas de licence radio. (« SELECT DISTINCT RADIO_LICENSE FROM nurses » renvoie maintenant quatre valeurs distinctes : « Technicien », « Général », « Amateur Extra », et « NULL »).

La colonne NULL dans la colonne MEDICAL_LICENSES se produit pour la même raison que les valeurs NULL se produisent dans les résultats de la requête précédente : les infirmières comptées dans cette rangée ont des MEDICAL_LICENSES différents, donc aucune valeur (« RN » ou « LVN ») ne s’applique nécessairement à toutes les infirmières comptées dans cette rangée.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+================+
|        5 |            LVN  | NULL          |
|        2 |            RN   | NULL          |
|        2 |            NULL | TECHNICIAN    |
|        1 |            NULL | GENERAL       |
|        1 |            NULL | AMATEUR EXTRA |
|        3 |            NULL | NULL          |
+----------+-----------------+---------------+

Si vous le souhaitez, vous pouvez comparer cette sortie à celle d’un GROUP BY sans la clause GROUPING SETS :

... GROUP BY medical_license, radio_license;

Sortie :

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+===============+
|        2 |            LVN  | NULL          |
|        2 |            LVN  | TECHNICIAN    |
|        1 |            LVN  | GENERAL       |
|        1 |            RN   | AMATEUR EXTRA |
|        1 |            RN   | NULL          |
+----------+-----------------+---------------+
Revenir au début