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.

If you are familiar with the concept of grouping sets, you can think of a ROLLUP grouping as equivalent to a series of grouping sets, but essentially a shorter specification. The N elements of a ROLLUP specification correspond to N+1 GROUPING SETS.

See also

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

  • As the query is aggregated at higher and higher levels, it shows NULL values in more columns of each row. This is appropriate. In the following example, for the aggregate at the state level, the city column is NULL; that’s because the value in the profit column does not correspond to one city. Similarly, in the final total, which aggregates data from all the states and all the cities, the revenue is not from one specific state or one specific city, so both the state and city columns in that row are NULL.

  • The query should list the « most significant level » first in the parentheses after the ROLLUP. For example, states contain cities, so if you are rolling up data across states and cities, the clause should be GROUP BY ROLLUP (state, city)

    If you reverse the order of the column names, you get a result that is probably not what you want. In the following example, if you reversed the order of city and state in the ROLLUP clause, the result would be incorrect, at least in part because both California and Puerto Rico have a city named San Jose (SJ), and you probably would not want to combine the revenue from the two different San Jose cities, except in the final total of all revenue. (An alternative way to avoid combining data from different cities with the same name is to create a unique ID for each city and use the ID rather than the name in the query.)

  • 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

Start by creating and loading a table with information about sales at a chain store that has branches in different cities and states/territories.

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

Run a rollup query that shows profit by city, state, and total across all states. The query produces three « levels » of aggregation:

  • chaque ville.

  • chaque région.

  • All revenue combined across all states.

The query uses ORDER BY state, city NULLS LAST to ensure that each state’s rollup comes immediately after all of the cities in that state, and that the final rollup appears at the end of the output.

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.