Catégories :

Syntaxe de requête

GROUP BY CUBE

GROUP BY CUBE est une extension de la clause GROUP BY similaire à GROUP BY ROLLUP. En plus de produire toutes les lignes d’un GROUP BY ROLLUP, GROUP BY CUBE ajoute toutes les lignes de « tabulations croisées ». Les lignes de sous-totaux sont des lignes qui s’agrégent encore, dont les valeurs sont dérivées en calculant les mêmes fonctions d’agrégation que celles qui ont été utilisées pour produire les lignes groupées.

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 :

GROUP BY GROUPING SETS , GROUP BY ROLLUP

Syntaxe

SELECT ...
FROM ...
[ ... ]
GROUP BY CUBE ( groupCube [ , groupCube [ , ... ] ] )
[ ... ]
Copy

Où :

groupCube ::= { <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 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.

Les clauses 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 rollup 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.).

Soyez prudent en utilisant 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 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.

Langage: Français