• Facile

Mis à jour le 21/11/2013

Un peu de stats avec GROUP BY ( 2 / 2 )

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Vous ne savez pas encore tout sur le clause GROUP BY ! A moi de vous le prouver avec ce chapitre qui, je l'espère, en intéressera plus d'un.

Au programme: Particularités de GROUP BY, la fonction GROUP_CONCAT, l'imbrication d'aggrégation et le problème très fréquent de la récupération de la dernière ligne.

Bonne lecture !

Fonctionnalités moins connues

Dans cette sous-partie, on va parler de fonctionnalités de la clause GROUP BY qui sont moins connues et spécifiques à MySQL pour la plupart.

GROUP BY et ORDER BY

Nous n'avons pas encore vraiment parlé de la clause ORDER BY, bien que vous l'ayez déjà abordée avec M@teo21.

Les plus perspicaces d'entre vous s'en sont peut-être déjà rendus compte: MySQL trie les résultats selon la colonne groupée. En fait, faire GROUP BY nom_de_colonne revient non seulement à grouper mais également à faire ORDER BY nom_de_colonne (je rappelle que ce comportement est spécifique à MySQL). Il est possible d'empêcher ce comportement par défaut en ajoutant la clause ORDER BY qui est prioritaire par rapport à GROUP BY.

Notez également qu'on peut ajouter les mots-clefs ASC et DESC après le nom d'une colonne spécifiée dans GROUP BY.

SELECT nws_auteur FROM t_news GROUP BY nws_auteur DESC;
On peut indiquer le sens de l'ordonnancement dans la clause GROUP BY

Sachez enfin que ce tri a un coût au niveau du temps d'exécution, faible par rapport au coût du GROUP BY lui-même mais présent. Si vous voulez éviter ce qu'on pourrait parfois appeler une perte de temps (dans certains cas le tri n'est pas nécessaire), vous pouvez spécifier ORDER BY NULL, ainsi MySQL ne triera pas les données.

Pour illustrer ceci je commence par updater la première entrée (nws_auteur = 'karamilo') afin de désordonner l'ordre établi par la clef primaire par défaut par MySQL, ensuite j'exécute le GROUP BY avec et sans ORDER BY NULL.

UPDATE t_news SET nws_auteur = 'karamilo' WHERE nws_id = 1;
SELECT nws_auteur FROM t_news GROUP BY nws_auteur ORDER BY NULL;
SELECT nws_auteur FROM t_news GROUP BY nws_auteur;
UPDATE t_news SET nws_auteur = 'Alexi Laiho' WHERE nws_id = 1;
Éviter l'ordonnancement automatique du GROUP BY

Utilisations d'expressions

Il est tout à fait possible d'utiliser des expressions à la place de noms (ou alias) de colonnes avec GROUP BY. Ainsi, les deux requêtes suivantes sont équivalentes:

SELECT WEEK(nws_date) AS semaine FROM t_news GROUP BY semaine;
SELECT WEEK(nws_date) AS semaine FROM t_news GROUP BY WEEK(nws_date);

Personnellement je n'ai jamais utilisé d'expressions directement dans le GROUP BY (Sans doute parce que ce n'est pas autorisé par la norme), mais peut-être serez-vous un jour confronté à un cas où vous n'aurez pas le choix ... ?

GROUP BY col1, col2

Vous avez appris à grouper selon une colonne, pourquoi ne pas faire de sous-groupes dans ces groupes ? Par exemple grouper les commentaires d'abord par id de news, puis par commentateur, et compter le nombre d'entrées pour chaque cas :p

C'est possible et même très simple en SQL, il suffit de mettre les noms des colonnes selon lesquelles grouper dans le GROUP BY, séparées par des virgules :) Exemple:

SELECT cmt_nwsid, cmt_auteur, COUNT(*) AS nb_cmts FROM t_commentaire GROUP BY cmt_nwsid, cmt_auteur;
Il est possible de grouper selon plusieurs colonnes, la table est alors divisée en sous-groupes

WITH ROLLUP

Les choses risquent bien de se compliquer un peu ici ...

Commençons par le plus simple: s'il n'y a qu'un nom de colonne dans le GROUP BY, ajouter WITH ROLLUP après GROUP BY col [ASC|DESC] aura pour effet d'ajouter une ligne avec pour valeur de col "NULL" et pour valeur de vos colonnes d'aggrégat la valeur maximale / minimale / somme / moyenne / compte de toutes les lignes.

Un exemple vaut mieux qu'un long discours n'est-ce pas ? :p En gros WITH ROLLUP ajoute une ligne qui agit sur l'ensemble de la table:

SELECT cmt_nwsid, COUNT(*) FROM t_commentaire GROUP BY cmt_nwsid WITH ROLLUP;
WITH ROLLUP ajoute une ligne qui contient le résultat de la fonction d'aggrégation sur le groupe 'supérieur'

Si vous voulez renommer ce vilain "NULL" en "Total" par exemple, il suffit d'ajouter un COALESCE sur cmt_nwsid :)

SELECT COALESCE(cmt_nwsid, 'Total') AS cmt_nwsid, COUNT(*) AS nb_cmts FROM t_commentaire GROUP BY cmt_nwsid WITH ROLLUP;
On peut renommer aisément l'identifiant de la ligne résultante du WITH ROLLUP

Passons aux choses sérieuses: s'il y a plusieurs noms de colonnes dans le GROUP BY, WITH ROLLUP ajoutera à la fin de chaque "sous-groupe" une ligne NULL qui contiendra l'aggrégat total de ce sous-groupe.

On reprend l'exemple avec les nombre de commentaires par auteur par news + WITH ROLLUP:

SELECT cmt_nwsid, cmt_auteur, COUNT(*) AS nb_cmts FROM t_commentaire GROUP BY cmt_nwsid, cmt_auteur WITH ROLLUP;
WITH ROLLUP avec plusieurs colonnes dans le GROUP BY

Eh mais il y a un bug dans ton machin, là ! Dans l'une des ligne le nom de l'auteur n'était pas spécifié, comment faire pour savoir quelle ligne représente le nombre de commentaires du visiteur anonyme ou le nombre de commentaires total pour la news ? (cmt_nwsid = 5)

En effet c'est assez gênant, et le fait de mettre un COALESCE sur la colonne change évidemment les deux lignes ...

SELECT cmt_nwsid, COALESCE(cmt_auteur, 'Total'), COUNT(*) AS nb_cmts FROM t_commentaire GROUP BY cmt_nwsid, cmt_auteur WITH ROLLUP;
Problème: Comment différencier les vraies données contenant un NULL de celles générées par WITH ROLLUP ?

Comment faire dès lors ?

La solution la plus simple consiste à utiliser ce qu'on appelle les sous-requêtes, que nous avons déjà rencontrées mais pas encore étudiées, nous ne le ferons pas encore maintenant. Je vous en parle juste pour pointer la solution à ce problème. La requête suivante fonctionnera:

SELECT cmt_nwsid, cmt_auteur, COUNT(*) AS nb_cmts
FROM (SELECT cmt_nwsid, COALESCE(cmt_auteur, 'inconnu') AS cmt_auteur FROM t_commentaire) t
GROUP BY cmt_nwsid, cmt_auteur WITH ROLLUP;
Il faut qu'à la base les données ne contiennent pas de NULL, on transforme donc les données au préalable dans une sous-requête

ONLY_FULL_GROUP_BY

Je ne sais pas si vous vous en rappelez, mais je vous avais parlé plus tôt du fait qu'il ne fallait jamais sélectionner une colonne qui n'était pas reprise dans le GROUP BY, que cela n'avait absolument aucun sens.

Rassurez-vous, je ne vais pas vous dire maintenant que je vous ai encore menti :p

Il faut juste savoir que, même si ça n'a pas de sens, MySQL autorise la récupération d'une colonne non reprise dans le GROUP BY, et prend alors la valeur d'une des lignes présentes dans le groupe. A priori la première, mais on n'en a pas la certitude ...

Oui tu nous as déjà dit tout ça ... Et alors ? On sait bien qu'il ne faut pas faire ça !

Oui vous le savez bien, mais parfois on sait que certaines valeurs sont uniques dans notre groupe, par exemple, imaginez qu'on fasse un GROUP BY sur la table t_news selon la colonne nws_id (Oui c'est totallement stupide, ça nous donnera des groupes d'une ligne à chaque fois, n'empêche que dans chaque groupe, on n'a qu'un seul auteur, un seul nombre de vues, etc ...).

Dans ce cas précis, est-il vraiment mal de vouloir récupérer une autre colonne que nws_id ?

Autre exemple: Dans notre table t_news, chaque auteur n'a posté de news que dans une seule catégorie. Dès lors, si on veut récupérer les auteurs et la catégorie dans laquelle ils ont posté, est-il bien nécessaire de mettre nws_catid dans le GROUP BY ?

Regardez les deux requêtes suivantes, la quelle est la plus pratique selon vous ? Et l'une d'elle est-elle plus illogique que l'autre ?

SELECT nws_catid, nws_auteur FROM t_news GROUP BY nws_auteur;
SELECT nws_catid, nws_auteur FROM t_news GROUP BY nws_catid, nws_auteur;

Les deux requêtes renvoient évidemment exactement le même résultat, vu que MySQL n'a pas de choix plus ou moins arbitraire à faire. Nous verrons lors du chapitre sur les jointures que grouper selon un id unique n'est pas si farfelu que ça en a l'air ... Ca arrive même relativement fréquemment.

Pourtant, si vous exécutez la première requête sur un autre SGBDR, il y a fort à parier qu'il ne l'exécutera pas et vous renverra une erreur. Comment dès lors éviter ce genre de "bêtise" ? A savoir demander à MySQL de ne pas exécuter une requête si elle tente de récupérer des colonnes non présentes dans la clause GROUP BY.

C'est possible, et assez facilement en modifiant une variable de configuration de MySQL. Voilà comment il faut faire:

SET @@sql_mode = CONCAT_WS(',', @@sql_mode, 'ONLY_FULL_GROUP_BY');

Si vous vous rappelez de la fonction CONCAT_WS et du chapitre sur les variables utilisateur, alors vous aurez compris que la commande ci-dessus affecte la variable @@sql_mode en lui ajoutant ONLY_FULL_GROUP_BY avec une virgule si nécessaire.

@@sql_mode supporte beaucoup d'autres valeurs, cette variable sert en fait à déterminer comment MySQL réagit.

Revenons à notre ONLY_FULL_GROUP_BY. Vous l'avez compris, il sert à empêcher toute forme de "comportement à risque" au niveau du GROUP BY.

Ainsi, en activant cette option et en retestant les deux requêtes du dessus, on obtient:

SELECT nws_catid, nws_auteur FROM t_news GROUP BY nws_auteur;
SET @@sql_mode = CONCAT_WS(',', @@sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT nws_catid, nws_auteur FROM t_news GROUP BY nws_auteur;
SELECT nws_catid, nws_auteur FROM t_news GROUP BY nws_auteur, nws_catid;
Eviter les erreurs avec ONLY_FULL_GROUP_BY

Si vous voulez en savoir plus sur la variable @@sql_mode, je vous laisse le lien vers la doc en ce qui la concerne, en français en plus ^^ Par ici

Je suis au regret de vous annoncer qu'il est probable que GROUP BY vous réserve encore quelques surprises ... Néanmoins vous en savez assez pour vous tirer d'affaire dans la plupart des problèmes que cette clause pourrait vous opposer :)

GROUP BY n'est pas forcément simple à appréhender, mais une fois que vous avez compris le truc, vous ne devriez plus avoir de soucis de ce côté là :p

A la prochaine !

Exemple de certificat de réussite
Exemple de certificat de réussite