Fonctions avec GROUP BY
<<<		 Options de GROUP BY GROUP BY avec les champs cachés
>>>
13.9 Fonctions et options à utiliser dans les clauses GROUP BY
13 Fonctions à utiliser dans les clauses SELECT et WHERE
 Manuel de Référence MySQL 4.1 : Version Française
. Fonctions avec GROUP BY
->Options de GROUP BY
. GROUP BY avec les champs cachés

13.9.2 Options de GROUP BY

Depuis MySQL 4.1.1, la clause GROUP BY permet l'utilisation de l'option WITH ROLLUP qui fait que des lignes supplémentaires seront ajoutées lors de regroupements. Ces lignes représentent des regroupements de haut niveau (ou des super-agrégats). ROLLUP vous permet de répondre simultanément à plusieurs niveaux d'analyse avec une seule requête. Il peut être utilisée, par exemple, pour supporter des opérations OLAP ( Online Analytical Processing ).

Voici une illustration. Supposons que vous ayez une table de ventes sales , avec des colonnes pour l'année year , le pays country , le produit product et le profit profit : 

CREATE TABLE sales
(
    year    INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit  INT
);
Le contenu de cette table peut être agrégé par année avec la clause GROUP BY : 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+
Cette requête affiche le profit par année, mais si vous voulez déterminer le profit total de toutes les années, vous devez ajouter ces valeurs vous-mêmes, ou faire une autre requête.

Ou alors, vous pouvez utiliser la clause ROLLUP , qui fournit les deux niveaux d'analyse dans la même requête. En ajoutant l'option WITH ROLLUP à la clause GROUP BY , la requête va produire une autre ligne, avec le grand total de toutes les années : 


mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+
La ligne du grand total est identifiée par la valeur NULL dans la colonne year .

ROLLUP a des effets plus complexes lorsqu'il y a plusieurs colonnes dans la clause GROUP BY . Dans ce cas, il a y un changement de valeur pour toutes sauf la dernière colonne de groupement, et la requête va produire les super-agrégats.

Par exemple, sans la clause ROLLUP , le résumé des ventes de la table sales basé sur l'année year , le pays country et le produit product peut ressembler à ceci : 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+
Le résultat indique les valeurs résumées pour chaque triplet année/pays/produit. Si nous ajoutons la clause ROLLUP , la requête produit plusieurs nouvelles lignes : 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+
Pour cette requête, ajouter ROLLUP fait que la requête ajoute les résumés de quatre niveaux d'analyse, et non pas un seul. Voici comment interpréter le résultat de la clause ROLLUP :
  • Après chaque jeu de ligne sur les produits, pour une année et un pays donnée, un résumé est ajouté, indiquant le total de tous les produits. Ces lignes voient leur colonne product contenir la valeur NULL .
  • Après chaque jeu de ligne couvrant une année particulière, une nouvelle ligne est ajoutée pour afficher le total de tous les pays et produits, pour cette année la. Ces lignes voient leurs colonnes country et products contenir NULL .
  • Finalement, suivant toutes les autres lignes, un résumé général est produit, avec le grand total de toutes les années, pays et produits. Cette ligne contient la valeur NULL pour toutes les colonnes year , country et products .

Autres considérations avec ROLLUP

Voici quelques comportements spécifiques de MySQL et son implémentation de ROLLUP :

Lorsque vous utilisez ROLLUP , vous ne pouvez pas utiliser de clause ORDER BY pour trier les résultats. En d'autres termes, ROLLUP et ORDER BY sont mutuellement exclusives. Toutefois, vous avec toujours le contrôle sur l'ordre de tri avec la clause GROUP BY . Vous pouvez utiliser explicitement les mots ASC et DESC avec les colonnes listées dans GROUP BY pour spécifier les ordres de tri des colonnes individuelles. Les lignes de résumés de ROLLUP apparaissent toujours après les lignes pour lesquelles ils sont calculés, quelque soit le tri.

La clause LIMIT peut être utilisée pour restreindre le nombre de lignes retournées au client. LIMIT s'applique après ROLLUP , et la limite s'appliquera aux lignes ajoutées par ROLLUP . Par exemple : 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+
Notez qu'utiliser LIMIT avec ROLLUP peut conduire à des résultats plus difficiles à interpréter, car vous avez moins de contexte pour comprendre les résumés.Les indicateurs NULL de chaque super-agrégat sont produits lorsque la ligne est envoyée au client. Le serveur recherche les colonnes citées dans la clause GROUP BY , en les prenant la plus à gauche, dont la valeur change. Toute colonne du jeu de résultat dont le nom ne correspond pas lexicalement à un de ces noms, verra sa valeur être NULL . Si vous spécifiez un groupement par numéro de colonne, le serveur identifiera aussi les colonnes qui devront recevoir NULL .

Comme les valeurs NULL des résumés sont placées dans le résultat aussi tard durant le traitement de la requête, nous ne pouvons pas les tester comme étant des valeurs NULL provenant de la requête elle-même. Par exemple, vous ne pourrez pas ajouter HAVING product IS NULL pour éliminer certains résumés qui ne vous intéressent pas.

D'un autre coté, les valeurs NULL apparaissent comme des valeurs NULL du coté du client, et peuvent être repérées en tant que telles par le client MySQL.

<< Options de GROUP BY >>
Fonctions avec GROUP BY Fonctions et options à utiliser dans les clauses GROUP BY GROUP BY avec les champs cachés