Catégories :: Syntaxe de requête

GROUP BY CUBE¶

GROUP BY CUBE est une extension de la clause GROUP BY, similaire à GROUP BY ROLLUP. Comme ROLLUP, CUBE produit des lignes agrégées à plusieurs niveaux. Cependant, alors que ROLLUP crée des agrégations qui suivent une hiérarchie naturelle (par exemple, la ville est rattachée à l’État, et l’État est rattaché au pays), CUBE crée des agrégations pour toutes les combinaisons possibles des colonnes spécifiées. Il s’agit à la fois des agrégations hiérarchiques que ROLLUP produirait et des lignes de « tabulation croisée » supplémentaires qui s’agrègent sur chaque dimension individuelle indépendamment.

CUBE peut être combiné avec d’autres expressions GROUP BY. Par exemple, vous pouvez écrire GROUP BY x, CUBE(y, z) pour grouper par colonne x en combinaison avec des agrégations de cubes sur y et z.

Un groupement CUBE équivaut à une série d’ensembles de regroupement et constitue essentiellement une spécification plus courte. Les éléments N d’une spécification CUBE correspondent à 2^N GROUPING SETS.

Voir aussi¶

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

Syntaxe¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] CUBE ( groupItem [ , groupItem [ , ... ] ] )
[ ... ]

Copy

Où :

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

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 autorise jusqu’à 7 éléments (équivalent à 128 ensembles de regroupement) dans chaque cube.

Exemples¶

Commencez par créer et charger une table contenant des informations sur les ventes d’une chaîne de magasins ayant des succursales dans différentes villes et différents états ou régions/territoires.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

Copy

Exécutez une requête de cube qui affiche les bénéfices par ville, état et total dans tous les états. L’exemple ci-dessous montre une requête qui a trois « niveaux » :

chaque ville.
chaque région.
Tous revenus confondus.

Cet exemple utilise ORDER BY state, city NULLS LAST pour s’assurer que le cumul de chaque état vient immédiatement après toutes les villes de cet état, et que le cumul final apparaît à la fin de la sortie.

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY CUBE (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | Miami   |     48 |
| NULL  | Orlando |     96 |
| NULL  | SF      |     13 |
| NULL  | SJ      |    218 |
| NULL  | NULL    |    375 |
+-------+---------+--------+

Copy

Certaines lignes du rollup contiennent des valeurs NULL. Par exemple, la dernière ligne de la table contient une valeur NULL pour la ville et une valeur NULL pour l’État, car les données concernent toutes les villes et tous les États, et non une ville et un État spécifiques.

La fonction utilitaire GROUPING peut aider à faire la distinction entre les valeurs NULL qui résultent de l’agrégation du cube par rapport aux valeurs NULL réelles dans les données.

GROUP BY CUBE et GROUP BY ROLLUP produisent une ligne pour chaque paire ville/État, et les deux clauses GROUP BY produisent également des lignes de cumul pour chaque État individuel et pour tous les États combinés. La différence entre les deux clauses GROUP BY est que GROUP BY CUBE produit également une ligne de sortie pour chaque nom de ville (Miami, SJ, etc.).

Faites attention lorsque vous utilisez GROUP BY CUBE sur des données hiérarchiques. Dans cet exemple, la ligne pour SJ contient des totaux pour la ville nommée SJ dans l’état de CA et pour la ville nommée SJ dans le territoire de PR, même si la seule relation entre ces villes est qu’elles portent le même nom. En général, utilisez GROUP BY ROLLUP pour analyser des données hiérarchiques, et GROUP BY CUBE pour analyser des données sur des axes indépendants.