Catégories :: Syntaxe de requête

GROUP BY GROUPING SETS¶

GROUP BY GROUPING SETS est une extension puissante de la clause GROUP BY qui calcule 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) équivaut à GROUP BY a UNION ALL GROUP BY b.

Syntaxe¶

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

Copy

Où :

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

Copy

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.
La sortie contient généralement des valeurs NULL. Comme GROUP BY ROLLUP fusionne les résultats de deux ou plusieurs ensembles de résultats, dont chacun a été regroupé selon des critères différents, certaines colonnes qui ont une seule valeur dans un ensemble de résultats peuvent avoir plusieurs valeurs correspondantes dans l’autre ensemble de résultats. Par exemple, si vous faites une UNION d’un ensemble d’employés regroupés par département avec un ensemble d’employés regroupé par ancienneté, les membres de l’ensemble ayant la plus grande ancienneté ne sont pas nécessairement tous dans le même département, donc la valeur de department_name est définie sur NULL. Les exemples suivants contiennent NULLs pour cette raison.

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 (RN ou « Registered Nurse ») et d’un permis supplémentaire 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 ».

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

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

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 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);
Copy
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 la valeur NULL dans la colonne RADIO_LICENSE de cette ligne se produit parce que trois infirmières n’ont pas de licence radio. (« SELECT DISTINCT RADIO_LICENSE FROM nurses » renvoie maintenant quatre valeurs distinctes : « Technician », « General », « 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 :

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

Copy

Sortie :

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