Catégories :: Syntaxe de requête

GROUP BY ROLLUP¶

GROUP BY ROLLUP est une extension de la clause GROUP BY qui produit des lignes agrégées à plusieurs niveaux d’une hiérarchie (en plus des lignes groupées détaillées). Par exemple, si vous regroupez par ville et État, ROLLUP produit des agrégations pour chaque combinaison ville/État, chaque total d’État, et un grand total pour tous les États. Ces agrégations sont calculées en utilisant les mêmes fonctions d’agrégation que celles spécifiées dans la clause SELECT.

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

Vous pouvez considérer le rollup comme la génération de plusieurs ensembles de résultats, dont chacun (après le premier) est l’agrégat de l’ensemble de résultats précédent. Ainsi, par exemple, si vous possédez une chaîne de magasins de détail, vous voudrez peut-être voir le bénéfice pour :

chaque magasin.
chaque ville (les grandes villes peuvent avoir plusieurs magasins).
chaque région.
tout (tous les magasins dans tous les régions).

Vous pouvez créer des rapports distincts pour obtenir cette information, mais il est plus efficace de numériser les données une seule fois.

Si vous maîtrisez le concept des ensembles de groupement, vous pouvez considérer un groupement ROLLUP comme équivalent à une série d’ensembles de groupement, à la différence qu’il s’agit essentiellement d’une spécification plus courte. Les éléments N d’une spécification ROLLUP correspondent à N+1 GROUPING SETS.

Voir aussi¶

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

Syntaxe¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] ROLLUP ( 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¶

Comme la requête est agrégée à des niveaux de plus en plus élevés, elle affiche des valeurs NULL dans plus de colonnes de chaque ligne. C’est tout à fait approprié. Dans l’exemple suivant, pour l’agrégat au niveau de l’État, la colonne city est NULL. Cela est dû au fait que la valeur dans la colonne profit ne correspond pas à une ville. De même, dans le total final, qui regroupe les données de tous les États et de toutes les villes, les recettes ne proviennent pas d’un État ou d’une ville en particulier, de sorte que les deux colonnes``state`` et city dans cette ligne sont NULL.
La requête doit énumérer le « niveau le plus significatif » en premier entre parenthèses après ROLLUP. Par exemple, parce que les États contiennent des villes, si vous cumulez des données entre États et villes, la clause devrait être GROUP BY ROLLUP (state, city).

Si vous inversez l’ordre des noms des colonnes, vous obtiendrez un résultat qui n’est probablement pas ce que vous voulez. Dans l’exemple ci-dessous, si vous inversez l’ordre de la city et de l’state dans la clause ROLLUP, le résultat sera incorrect, du moins en partie, car la Californie et Porto Rico ont toutes deux une ville nommée San José (« SJ »), et vous ne voulez probablement pas combiner les revenus des deux villes San José, sauf dans le total final de tous les revenus. (Une autre façon d’éviter de combiner les données de différentes villes portant le même nom consiste à créer un ID unique pour chaque ville et à utiliser l’ID plutôt que le nom dans la requête).
La fonction utilitaire GROUPING peut aider à faire la distinction entre les valeurs NULL qui résultent de l’agrégation du rollup par rapport aux valeurs réelles NULL dans les données. GROUPING renvoie 0 pour une ligne groupée sur une colonne spécifiée et 1 pour une ligne où la colonne indique NULL en raison de l’agrégation.

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/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 rollup qui affiche les bénéfices par ville, par État, et par total dans tous les États. La requête produit trois « niveaux » d’agrégation :

chaque ville.
chaque région.
Tous les revenus confondus dans tous les États.

La requête 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 ROLLUP (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  | 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.