Catégories :: Syntaxe de requête

GROUP BY GROUPING SETS¶

GROUPING SETS est une extension puissante de la clause GROUP BY qui calcule plusieurs clauses GROUP BY en une seule instruction. Un ensemble de regroupement est un ensemble de colonnes de dimensions.

Les expressions GROUPING SETS peuvent être combinées avec d’autres expressions GROUP BY, faisant de cette structure une partie intégrée de la clause GROUP BY plutôt qu’une structure distincte. Par exemple, vous pouvez écrire GROUP BY x, GROUPING SETS(y, z) pour grouper par colonne x en combinaison avec des groupements séparés sur y et z.

Une expression GROUPING SETS est équivalente à l’union de deux ou plusieurs GROUP BY opérations dans le même jeu de résultats. Par exemple :

GROUP BY GROUPING SETS((a)) est équivalent à l’opération de regroupement unique GROUP BY a.
GROUP BY GROUPING SETS((a), (b)) équivaut à GROUP BY a UNION ALL GROUP BY b.

Remarque : GROUPING SETS(a, b) sans parenthèses supplémentaires est logiquement équivalent à GROUPING SETS((a), (b)) car les deux créent deux ensembles de regroupements distincts, l’un pour la colonne a et l’autre pour la colonne b. Cette expression est différente de GROUPING SETS((a, b)), qui crée un ensemble de regroupement unique regroupant les deux colonnes.

Syntaxe¶

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

Copy

Où :

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

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

Copy

Paramètres¶

column_alias: 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.
Variations de syntaxe avec des parenthèses :
- GROUPING SETS(a, b) est l’abréviation de GROUPING SETS((a), (b)). Les deux créent deux ensembles de regroupement distincts : l’un qui regroupe par colonne a, et l’autre qui regroupe par colonne b.
- GROUPING SETS((a, b)) crée un ensemble de regroupement unique qui regroupe les deux colonnes a et b (similaire à GROUP BY a, b).
Vous pouvez combiner des colonnes GROUP BY classiques avec GROUPING SETS : GROUP BY x, GROUPING SETS(y, z) groupe par colonne x en combinaison avec des groupements distincts sur y et z.
La sortie contient généralement des valeurs NULL. Comme GROUP BY GROUPING SETS fusionne les résultats de deux ou plusieurs jeux de résultats, dont chacun a été regroupé selon des critères différents, certaines colonnes qui ont une seule valeur dans un jeu de résultats peuvent avoir plusieurs valeurs correspondantes dans l’autre jeu de résultats. Par exemple, si vous effectuez une union entre un ensemble d’employés regroupés par service et un ensemble regroupé par ancienneté, les membres de l’ensemble ayant la plus grande ancienneté ne sont pas nécessairement tous dans le même service, donc la valeur de department_name est définie sur NULL. Les exemples suivants contiennent les valeurs NULL pour cette raison.

Voir aussi :¶

GROUPING (Fonction utilitaire pour identifier le niveau de regroupement qui a produit chaque ligne)
GROUP BY ROLLUP
GROUP BY CUBE

Exemples¶

Ces exemples utilisent une table d’informations sur les infirmières qui sont formées pour apporter de l’aide en cas de catastrophe. Toutes ces infirmières sont titulaires d’une autorisation d’exercer en tant qu’infirmière (par exemple, RN est une « Registered Nurse »ou infirmière autorisée) et d’un permis supplémentaire (par exemple, dans une spécialité liée aux catastrophes, comme la recherche et le sauvetage, les communications radio, etc.). Cet exemple simplifie et utilise seulement deux catégories de licences :

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 « Technician », « General » et « Amateur Extra ».

Les commandes suivantes créent et chargent 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')
    ;

Copy

Cette requête utilise 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

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.

Les trois lignes suivantes indiquent le nombre d’infirmières avec chaque type de licence radio amateur (« Technician », « General » et « Amateur Extra »). La valeur NULL de medical_license dans chacune de ces trois lignes est délibérée, car aucun permis médical ne s’applique nécessairement à tous les membres de chacune de ces lignes.

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

L’exemple suivant illustre la différence entre le regroupement par colonnes distinctes et le regroupement par colonnes groupées. La requête regroupe la combinaison de medical_license et 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

Cette requête produit des lignes où chaque combinaison de medical_license et radio_license apparaît avec son nombre. Contrairement à l’exemple précédent, il n’y a pas de valeurs NULL dans la sortie, car la requête regroupe les deux colonnes ensemble plutôt que de créer des groupements distincts pour chaque colonne.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | 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)
    ;

Copy

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)
  ORDER BY 3 DESC NULLS FIRST;

Copy

Pourquoi y a-t-il maintenant une ligne qui a NULL dans les deux colonnes ? Et si toutes les valeurs sont NULL, pourquoi le COUNT(*) est égal à 3 ?

Réponse : le NULL dans la colonne radio_license de cette ligne se produit parce que trois infirmières n’ont pas de licence radio. (La requête SELECT DISTINCT radio_license FROM nurses renvoie maintenant quatre valeurs distinctes : « Technician », « General », « Amateur Extra » et « NULL ».)

La valeur 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 licences médicales différentes, 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 |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

L’exemple suivant illustre la combinaison de colonnes GROUP BY classiques avec GROUPING SETS. Cette requête est regroupée par medical_license, et au sein de chaque groupe de licences médicales, crée des agrégations distinctes pour chaque valeur radio_license et pour toutes les licences radio combinées :

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

Copy

Pour chaque licence médicale (LVN et RN), la sortie affiche :

Lignes regroupées par valeur radio_license spécifique (« Technician », « General », « Amateur Extra » ou NULL pour les personnes sans licence radio)
Une ligne de résumé avec NULL dans la colonne radio_license représentant toutes les infirmières possédant cette licence médicale, quelle que soit leur licence radio

+----------+-----------------+---------------+
| 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 |
+----------+-----------------+---------------+

Vous pouvez comparer cette sortie à celle d’une requête GROUP BY sans la clause 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 |
+----------+-----------------+---------------+

Utilisation de la fonction GROUPING¶

La fonction utilitaire GROUPING permet d’identifier le niveau d’agrégation qui a produit chaque ligne. Ceci est particulièrement utile pour faire la distinction entre les valeurs NULL qui résultent de l’opération de regroupement par rapport aux valeurs NULL réelles dans les données.

La fonction GROUPING renvoie :

0 pour une ligne groupée dans la colonne spécifiée
1 pour une ligne qui n’est pas groupée dans la colonne spécifiée (où NULL apparaît en raison de l’agrégation)

Cet exemple ajoute les fonctions GROUPING à la requête pour clarifier la sortie :

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

Les colonnes grp_medical et grp_radio montrent quelles colonnes ont été utilisées pour le regroupement :

Lignes 1-2 : Regroupées par medical_license (grp_medical=0), et non par radio_license (grp_radio=1)
Lignes 3-6 : Regroupées par radio_license (grp_radio=0), et non par medical_license (grp_medical=1)
Ligne 6 : La valeur NULL dans radio_license est une donnée réelle (grp_radio=0), tandis que NULL dans medical_license provient de l’agrégation (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 |
+----------+-----------------+---------------+-------------+-----------+