Clause GROUP BY : La connaissez-vous bien ?

La base de données peut effectuer différents types de calculs : elle peut additionner et soustraire, multiplier et diviser, effectuer des calculs sur des dates, compter des lignes et additionner des valeurs de lignes, etc. Il peut également effectuer des statistiques assez sophistiquées. La clause GROUP BY est le moyen le plus simple de calculer des statistiques en SQL. Elle peut être assez difficile pour les débutants, mais elle est vraiment puissante. Examinons les détails de la clause GROUP BY, en commençant par les principes de base.

Les bases : comment fonctionne GROUP BY

Nous avons ici le tableau des médailles avec les médaillés de la Coupe du monde de saut à ski pour les quatre dernières saisons.

 country |         person        | season  | place
---------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2011-12 |   1
Austria  | Gregor Schlierenzauer | 2011-12 |   2
Austria  | Andreas Kofler        | 2011-12 |   3
Austria  | Gregor Schlierenzauer | 2012-13 |   1
Norway   | Anders Bardal         | 2012-13 |   2
Poland   | Kamil Stoch           | 2012-13 |   3
Poland   | Kamil Stoch           | 2013-14 |   1
Slovenia | Peter Prevc           | 2013-14 |   2
Germany  | Severin Freund        | 2013-14 |   3
Germany  | Severin Freund        | 2014-15 |   1
Slovenia | Peter Prevc           | 2014-15 |   2
Austria  | Stefan Kraft          | 2014-15 |   3

Je veux savoir combien de médailles a obtenu la Pologne :

SELECT count(*)
FROM medals
WHERE country = 'Poland';

Si je voulais connaître le nombre de médailles de l'Allemagne, je devrais lancer cette requête :

SELECT count(*)
FROM medals
WHERE country = 'Germany';

Si je voulais connaître le nombre de médailles pour chaque pays, je pourrais poser six requêtes similaires. Ou bien je peux utiliser un GROUP BY.

SELECT country, count(*)
FROM medals
GROUP BY country;

La clause GROUP BY vient juste après la clause WHERE dans une requête SQL. Ici, la clause WHERE est absente, elle se trouve donc juste après FROM.

Le résultat :

 country | count
---------+-------
Poland   |   2
Germany  |   2
Austria  |   4
Norway   |   2
Slovenia |   2

Avec la requête GROUP BY, la base de données divise les données en groupes. Les lignes ayant la même colonne GROUP BY (le pays dans l'exemple) sont placées dans un seul groupe. Donc, en utilisant notre exemple, les médaillés de Pologne sont placés dans un groupe, les médaillés d'Allemagne sont placés dans un autre groupe et ainsi de suite. Voici le regroupement que nous obtenons pour cette requête :

 country |        person         | season  | place
–--------+-----------------------+---------+-------
Poland   | Kamil Stoch           | 2012-13 |   3
         | Kamil Stoch           | 2013-14 |   1
–--------+-----------------------+---------+-------
Germany  | Severin Freund        | 2013-14 |   3
         | Severin Freund        | 2014-15 |   1
–--------+-----------------------+---------+-------
Austria  | Gregor Schlierenzauer | 2012-13 |   1
         | Stefan Kraft          | 2014-15 |   3
         | Gregor Schlierenzauer | 2011-12 |   2
         | Andreas Kofler        | 2011-12 |   3
–--------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2012-13 |   2
         | Anders Bardal         | 2011-12 |   1
–--------+-----------------------+---------+-------
Slovenia | Peter Prevc           | 2013-14 |   2
         | Peter Prevc           | 2014-15 |   2
–--------+-----------------------+---------+-------

Avec GROUP BY les agrégats (count, sum, avg, min, max, et autres) sont calculés pour chacun séparément. Dans l'exemple, la base de données compte le nombre de lignes dans chaque groupe.

Regroupement avec plusieurs colonnes

Vous pouvez regrouper des lignes en fonction de plusieurs colonnes.

Par exemple, si vous voulez savoir combien de médailles chaque pays a obtenu à chaque saison, votre requête ressemblerait à ceci :

SELECT country, season, count(*)
FROM medals
GROUP BY country, season;

Les lignes ayant le même pays et la même saison sont placées dans un seul groupe. Le regroupement ressemble à ceci :

 country | season  |        person         | place
–--------+---------+-----------------------+-------
Poland   | 2012-13 | Kamil Stoch           |   3
–--------+---------+-----------------------+-------
Poland   | 2013-14 | Kamil Stoch           |   1
–--------+---------+-----------------------+-------
...
–--------+---------+-----------------------+-------
Austria  | 2011-12 | Gregor Schlierenzauer |   2
         | 2011-12 | Andreas Kofler        |   3
–--------+---------+-----------------------+------

Le résultat final :

 country | season  | count
---------+---------+-------
 Poland  | 2012-13 |   1
 Austria | 2011-12 |   2
 ...
 Poland  | 2013-14 |   1

Valeurs NULL dans GROUP BY

Un rappel : Dans la condition WHERE, deux valeurs NULL ne sont pas considérées comme égales. Aussi étrange que cela puisse paraître, la requête

SELECT *
FROM medals
WHERE place = place;

sélectionnera toutes les lignes sauf celles qui ont une place NULL. Pour SQL, la valeur NULL signifie "Inconnu" et si elle est inconnue, SQL ne peut pas supposer qu'il sait avec certitude quel sera son résultat. (En particulier, il ne peut pas savoir avec certitude que le résultat est VRAI).

Avec GROUP BY, c'est différent. Les lignes avec des valeurs NULL sont toutes placées dans un seul groupe, et les agrégats sont calculés pour ce groupe, comme pour tout autre groupe. Cela fonctionne également pour les GROUP BY à colonnes multiples.

Pour cette table :

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
 United States | NULL    | Maria Jane   |   1000
 Germany       | Berlin  | Hans Schmitt |   2430
 United States | NULL    | Bill Noir    |   1000
 United States | Chicago | Rob Smith    |   3000
 NULL          | Warsaw  | Sophie Doe   |   2000
 Germany       | Berlin  | Jane Dahl    |   1500

la requête

SELECT country, city, sum(earnings)
FROM employees
GROUP BY country, city;

donne ces groupes :

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
               |         | Sophie Doe   |   2000
–--------------------------------------------------
 United States | NULL    | Maria Jane   |   1000
               |         | Bill Noir    |   1000
–--------------------------------------------------
 United States | Chicago | Rob Smith    |   3000
–--------------------------------------------------
 Germany       | Berlin  | Hans Schmitt |   2430
               |         | Jane Dahl    |   1500

et ce résultat

    country    |   city  |  sum
---------------+---------+-------
 NULL          | Warsaw  |  3000
 United States | NULL    |  2000
 United States | Chicago |  3000
 Germany       | Berlin  |  3930

Mythe : les colonnes sélectionnées doivent apparaître dans la clause GROUP BY ou sous une fonction d'agrégation ?

La sagesse commune dit que les colonnes sélectionnées dans une requête GROUP BY doivent apparaître soit dans la clause GROUP BY, soit sous une fonction d'agrégation. Cette requête est donc incorrecte :

SELECT user_account.id, email, count(*)
FROM user_account JOIN address
ON user_account.id = address.user_id
GROUP BY email;

La requête regroupe les résultats par email, mais elle sélectionne la colonne id, qui n'est pas dans la clause GROUP BY.

Cette sagesse était la règle dans la norme SQL92. C'est ainsi que de nombreuses bases de données mettent en œuvre le comportement GROUP BY aujourd'hui. Vous obtiendrez une erreur similaire à celle-ci :

ERROR:  column "user_account.id" must appear in the GROUP BY clause or be used 
in an aggregate function

D'où vient cette règle ? Prenons un exemple de données.

|       user_account      |   |         address         |
+----+--------------------+   +----+----------+---------+
| id |        Email       |   | id |   city   | user_id |
+----+--------------------+   +----+----------+---------+
| 1  | john@example.com   |   | 1  | London   | 1       |
| 2  | mary@example.co.uk |   | 2  | Brussels | 2       |
| 3  | john@example.com   |   | 3  | Cairo    | 3       |
|    |                    |   | 4  | Dublin   | 1       |

Nous regroupons les données par email

 
user_account.email |user_account.id |address.id|address.city|address.user_id|
-------------------+----------------+----------+------------+---------------+
john@example.com   | 1              | 1        | A          | 1             |
                   +----------------+----------+------------+---------------+
                   | 1              | 4        | D          | 1             |
                   +----------------+----------+------------+---------------+
                   | 3              | 3        | C          | 3             |
-------------------+----------------+----------+------------+---------------+
mary@example.com   | 2              | 2        | B          | 2             |

La base de données crée un groupe pour chaque email. Mais il y a plusieurs ids de user_account dans chaque groupe. La base de données ne sait pas quel identifiant elle doit renvoyer. La norme SQL veut que le résultat SQL soit déterministe, elle vous interdit donc d'exécuter une requête comme celle-ci.

La norme SQL99 a modifié la formulation de la règle. Elle dit maintenant que toute colonne qui apparaît sous SELECT doit apparaître sous la fonction d'agrégation ou être fonctionnellement dépendante des colonnes dans la clause GROUP BY. La règle ne prévoit plus que toutes les colonnes non agrégées soient répétées dans la clause GROUP BY.

Que signifie "colonnes fonctionnellement dépendantes dans la clause BY by" ? Cela signifie : si je fixe des valeurs pour les colonnes dans la clause GROUP BY, il ne doit y avoir qu'une seule valeur pour l'autre colonne. Par exemple, l'adresse électronique détermine la valeur du nom de son propriétaire. Mais il y a un hic : la base de données doit être au courant de cette dépendance. Dans le contexte des bases de données, la dépendance signifie soit des clés primaires, soit des clés uniques. Si je regroupe par une clé primaire, je sais alors que les autres colonnes de cette table ont des valeurs fixes.

Notre exemple initial n'est toujours pas valable selon la nouvelle règle. MAIS : si j'applique la contrainte unique sur la colonne email, la requête devient valide sous la nouvelle règle. Si la colonne email est unique dans la table user_account, alors la fixation de la valeur email détermine toutes les autres colonnes de la table user_account. Bien sûr, si j'ajoute la contrainte unique, les données de mon exemple sont également invalides. Je ne peux pas avoir deux lignes différentes avec le même email.

Les bases de données prennent-elles en charge la nouvelle règle GROUP BY ?

Certaines le font, d'autres non. La nouvelle règle figure dans la norme SQL99. MySQL depuis la version 5.7.4 supporte le nouveau comportement. Il en est de même pour Postgres à partir de la version 9.1. Postgres traite la colonne comme fonctionnellement dépendante des colonnes groupées si un sous-ensemble des colonnes groupées est une clé primaire de la table d'où provient la colonne.

Pour autant que je sache, Oracle et SQL Server s'en tiennent toujours à l'ancienne version.

Faut-il utiliser la nouvelle version ou l'ancienne version de la règle dans vos requêtes ? En pratique, cette modification ne change pas grand-chose. Les deux règles garantissent que chaque fois que vous sélectionnez une colonne non agrégée dans une requête GROUP BY, sa valeur est sans ambiguïté dans chaque groupe. L'ancienne règle vous oblige à ajouter cette colonne dans la clause GROUP BY, mais ce GROUP BY ne change pas la sémantique de la requête. Le résultat est le même, vous devez juste taper un peu plus avec la nouvelle règle.

En général, il est préférable de s'en tenir à l'ancienne règle. Vos requêtes s'exécuteront sur la plupart des bases de données. Mais il est bon de savoir que vous n'êtes pas obligé de le faire.

Extensions de GROUP BY

SQL-99 a ajouté ROLLUP, CUBE et GROUPING SETS comme options pour les instructions SELECT.

GROUPER PAR ROLLUP

La syntaxe de ROLLUP est la suivante

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY ROLLUP (<group-by columns>);

L'utilisation de ROLLUP (a,b,c) génère des clauses GROUP BY : (a, b, c), (a, b), (a) et une ligne pour une agrégation de toutes les lignes sélectionnées. Cela équivaut à quatre requêtes SELECT avec diverses clauses GROUP BY.

Pour cette table

department | year | sales
–----------+--------------
 IT        | 2012 |  25000
 IT        | 2013 |  26000
 Retail    | 2012 |  35000
 Retail    | 2013 |  15000
 IT        | 2014 |  18000

et cette requête

SELECT department, year, sum(sales)
FROM sales
GROUP BY ROLLUP (department, year);

nous obtenons le résultat :

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000 <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000 <-  group by department
 NULL       | NULL |  119000 <-  group by (), i.e. all rows selected

Les lignes supplémentaires sont parfois appelées super-agrégats.

ROLLUP est supporté par SQL Server, Oracle, DB2.

Dans MySQL, vous pouvez utiliser la syntaxe WITH ROLLUP :

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY a,b,c WITH ROLLUP;

PostgreSQL ne supporte pas ROLLUP.

GROUPER PAR CUBE

La syntaxe de CUBE est

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY CUBE (a, b, c);

Elle fonctionne de manière similaire à ROLLUP mais génère toutes les combinaisons possibles de colonnes : (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c) et une ligne pour toutes les lignes sélectionnées.

La requête

SELECT department, year, sum(sales)
FROM sales
GROUP BY CUBE (department, year);

rendra ce résultat :

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000  <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000  <-  group by department
 NULL       | NULL |  119000  <-  group by ()
 NULL       | 2014 |   18000 |
 NULL       | 2012 |   60000 | <= three new rows added by CUBE
 NULL       | 2013 |   41000 |    

CUBE est supporté par SQL Server et Oracle, et DB2. MySQL et Postgres ne le supportent pas.

GROUPAGE PAR GROUPAGE D'ENSEMBLES

GROUPING SETS fonctionne de manière similaire, mais il permet de spécifier quelles combinaisons de colonnes doivent être utilisées dans le résultat. Les ensembles de regroupement doivent être séparés par des virgules. S'il y a plus d'une colonne dans un ensemble de regroupement, cet ensemble de regroupement doit être mis entre parenthèses. Des parenthèses vides signifient l'enregistrement général avec des agrégats pour l'ensemble du groupe.

Exemple de requête :

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY GROUPING SETS ((a, b), c, ());

GROUPING SETS est supporté par SQL Server et Oracle, et DB2. MySQL et Postgres ne le supportent pas.