• Facile

Mis à jour le 21/11/2013

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

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

Entrons enfin dans le vif du sujet. :)

Si vous avez juste suivi le cours de M@teo21, ce que vous allez apprendre dans les 2 prochains chapitres sera totalement nouveau pour vous, c'est pourquoi j'essaierai d'être aussi clair et concis que possible. :)

J'ai décidé de diviser ce point en 2 chapitres puisqu'il est assez conséquent. Ce premier chapitre sera consacré à mettre en place les bases des fonctions d'aggrégation et de la clause GROUP BY. Tandis que le second fouillera les fonctionnalités un peu plus avancées de toutes ces notions. :)

Les fonctions statistiques de MySQL

MySQL propose toute une série de fonctions qui permettent de faire des stats sur vos tables, en résumé... :)

Les fonctions courantes

Fonction

Explication

Exemple

Résultat

COUNT()

Nombre de lignes

SELECT COUNT(nws_id) FROM t_news;

6

MAX()

Renvoie la valeur maximale

SELECT MAX(nws_nb_vues) FROM t_news;

2154

MIN()

Renvoie la valeur minimale

SELECT MIN(nws_nb_vues) FROM t_news;

0

SUM()

Renvoie la somme des valeurs

SELECT SUM(nws_nb_vues) FROM t_news;

4407

AVG()

Renvoie la moyenne (Average) des valeurs

SELECT AVG(nws_nb_vues) FROM t_news;

734.5000

Les fonctions de linéarisation

Il existe également d'autres fonctions permettant de calculer la variance et l'écart-type d'une population ou d'un échantillon (pour ceux qui sont allergiques aux maths, je propose que vous évitiez d'essayer de comprendre ce paragraphe :D ).

Ce sera certainement utile à quelques uns d'entre vous, donc je vous mets ces fonctions au nombre de 4 si on ne compte pas les dérivés non-standards créés par MySQL (on se demande pourquoi d'ailleurs...).

Fonction

Explication

Exemple

Résultat

VAR_POP

Variance d'une population

SELECT VAR_POP(nws_nb_vues) FROM t_news;

608275.5833

STDDEV_POP

Ecart-type d'une population

SELECT STDDEV_POP(nws_nb_vues) FROM t_news;

779.9202

VAR_SAMP

Variance d'un échantillon

SELECT VAR_SAMP(nws_nb_vues) FROM t_news;

729930.7000

STDDEV_SAMP

Ecart-type d'un échantillon

SELECT STDDEV_SAMP(nws_nb_vues) FROM t_news;

854.3598

Évidemment, comme nous avons des données qui ne se prêtent pas vraiment à la linéarité, les variances et écarts-types sont absolument... anarchiques. :p Avec des vraies données vous aurez bien sûr des résultats plus raisonnables. :)

Pour ceux qui commencent à attraper des boutons, rassurez-vous, les maths c'est à peu près terminé. :D :p

Quelques notes par rapport à tout ça...

COUNT(DISTINCT champ)

Je vous ai présenté la fonction COUNT() sous sa forme la plus simple, il y a plusieurs subtilités relativement intéressantes. :)

A commencer par le fait d'ajouter DISTINCT avant le nom de la colonne, ce qui permet, vous vous en doutez certainement, de ne pas prendre en compte les doublons. :)

Un petit exemple :

SELECT COUNT(nws_auteur) AS nbr_tot, COUNT(DISTINCT nws_auteur) AS nbr_distinct FROM t_news;

Pour rappel on a 6 news écrites par Alexi, karamilo, karamilo, alexi, karamilo et visiteur.

Le résultat de notre requête :

COUNT avec le mot-clef DISTINCT

Autre possibilité qui, même si elle peut paraître évidente pour certains, ne l'est pas pour d'autres : on peut utiliser des fonctions à l'intérieur de COUNT. Par exemple avec un IF :

SELECT COUNT(DISTINCT IF(nws_auteur='karamilo', 'Alexi Laiho', nws_auteur)) FROM t_news;
Utilisation de COUNT avec une fonction

Notez enfin que la fonction COUNT() ne prend pas en compte la valeur NULL. Ainsi, la requête suivante renverra 3 :

SELECT COUNT(NULLIF(nws_auteur, 'karamilo')) FROM t_news;
COUNT ne prend pas en compte les valeurs NULL

COUNT et AVG ne prennent pas NULL en compte, mais bien les 0 !

C'est vrai également mais moins important pour les fonctions SUM, MIN et MAX.

On vient de voir de quoi il en retourne pour la fonction COUNT.

Pour la fonction AVG, c'est important également de le savoir puisque la valeur 0 influe très fortement une moyenne. Ainsi, si on veut la moyenne du nombre de vues par news sans compter les news où ce nombre vaut 0, la requête suivante ferait l'affaire :

SELECT AVG(NULLIF(nws_nb_vues, 0)) FROM t_news;
AVG et sa gestion des valeurs NULL

COUNT(*) pour compter toutes les entrées de la table d'un coup

SELECT COUNT(*) FROM t_news;

En général, l'étoile c'est mal, mais voici un des rare cas où... C'est bien. :p

En effet si vous utilisez COUNT(*) sur une table, MySQL ira chercher directement la valeur dans ses statistiques à lui et vous enverra ce nombre en un temps record : il ne devra pas parcourir toute la table. :)

Bien sûr, COUNT(DISTINCT *) ne rime à rien et vous renverra une erreur. ^^

Enfin, à moins que toute la ligne ne soit NULL, COUNT(*) prend cette ligne en compte (vous pouvez vérifier avec la table t_commentaire).

Hop ! Fin de la sous-partie. :)

C'était le plus simple ^^ (à expliquer du moins :p ). Normalement vous commencez à comprendre pourquoi j'ai décidé de diviser ce chapitre en 2 parties. :D

Grouper les données avec GROUP BY

Voici un concept relativement complexe à introduire et pourtant relativement fondamental et vraiment très puissant !

Pour la partie théorique, GROUP BY permet de grouper les données selon un certain critère, mais cela ne vous aide guère... C'est pourquoi je vous propose de voir celà avec un exemple. :)

Prenons l'éternelle table t_news, nous ne nous intéressons qu'aux champs nws_id, nws_catid, nws_auteur et nws_titre.

A l'intérieur de la table, il y a 6 éléments, composés entre autres des 4 champs précités :

La table dans son état initial

Intéressons-nous plus particulièrement au champ nws_auteur :

Le critère selon lequel nous allons grouper

On constate qu'il y a 3 auteurs différents : karamilo, Alexi Laiho et un visiteur en quête du savoir absolu. :p On pourrait donc imaginer (soyons fous !) diviser notre table en 3 groupes, comme ceci :

On divise la table en 3 groupes

Vous avez compris ce qu'on a fait ? Si oui vous avez compris ce que GROUP BY fait. :) C'est exactement la même chose. Il prend des données, regarde une certaine colonne et fait des groupes en fonction de cette colonne.

Voilà. :)

Euh... Tu rigoles de nous là ?? Tu nous avait dit que GROUP BY était très puissant !!

Tout d'abord je vous parle de GROUP BY sans arrêt mais vous ne savez toujours pas comment on l'intègre dans une requête SQL... Si l'on considère qu'on groupe selon la colonne nws_auteur, le GROUP BY se ferait comme ceci :

SELECT champs FROM t_news GROUP BY nws_auteur;

Ensuite, c'est vrai que tout seul, GROUP BY ne sert pas à grand chose... L'énorme utilité de GROUP BY, c'est qu'on peut faire des requêtes SELECT sur chaque groupe individuellement : il faut voir votre table non plus comme une table de 6 lignes mais comme une table de 3 groupes : SELECT agit sur un groupe, plus sur une ligne !!!

SELECT agit sur un groupe en entier

Je vous explique un peu ce que je veux dire :

Quand vous faites un SELECT normal, MySQL vous renvoie des infos ligne par ligne. En général il s'agit de données simples.

Par contre, quand vous faites un SELECT contenant une clause GROUP BY, MySQL vous renvoie des infos groupe par groupe ! Et ce concept est très important. En effet, ça veut dire par exemple que ça n'a plus de sens de demander le nws_id de ce groupe puisque ce groupe possède plusieurs champs nws_id différents !

Ainsi la requête suivante:

SELECT nws_id FROM t_news GROUP BY nws_auteur;

n'a aucun sens ! MySQL pourrait aussi bien nous renvoyer 2, 3 ou 5 !

Par contre, il est tout à fait sensé de récupérer la colonne selon laquelle on a effectué le groupage, à savoir dans ce cas-ci nws_auteur :

SELECT nws_auteur FROM t_news GROUP BY nws_auteur;
Récupérer la colonne référencée dans la clause GROUP BY a forcément du sens

Tiens c'est rigolo, ça fait le même qu'avec un DISTINCT. :p

N'oubliez jamais qu'avec GROUP BY, SELECT agit sur un ensemble de lignes et non plus sur une ligne précise !

Mais dans ce cas, à quoi ça sert ?? C'est quand même pas juste une façon loufoque de faire un SELECT DISTINCT ???

Je vous propose de lire la prochaine sous-partie qui vous éclairera sans doute sur ce point. :)

Les fonctions statistiques agissent sur des groupes !!

Et là... C'est la révélation, vous avez tout compris maintenant non ? :p

La principale utilité de GROUP BY, c'est de diviser une table en groupes, et ensuite, sur ces groupes, on peut appliquer les fonctions MIN(), MAX(), SUM(), COUNT(), AVG(), ... vues juste avant. :)

Ainsi, si on veut connaître le nombre total de vues pour chaque auteur, on peut faire :

SELECT nws_auteur, SUM(nws_nb_vues) AS nb_vues_tot FROM t_news GROUP BY nws_auteur;

Si on veut le nombre moyen de vues pour les news de chaque auteur, on fait :

SELECT nws_auteur, AVG(nws_nb_vues) AS nb_vues_tot FROM t_news GROUP BY nws_auteur;

Le résultat semble presque magique...

Des stats par groupe avec GROUP BY !

Comme ce concept est très important pour la suite, je vous propose de trouver les requêtes qui permettront de...

  • Compter le nombre de news par auteur ;

  • Trouver le nombre de vues le plus grand pour chaque auteur ;

  • Trouver la date de la news la plus récente de chaque auteur ;

  • Trouver la date de la première news de chaque auteur ;

  • Compter le nombre de news dans chaque catégorie ;

  • Compter le nombre de news qui ont été lues au moins une fois et celles qui n'ont jamais été lues (vous devez obtenir 5 et 1).

Les réponses :

SELECT nws_auteur, COUNT(nws_id) AS nb_news FROM t_news GROUP BY nws_auteur;
SELECT nws_auteur, MAX(nws_nb_vues) AS max_vues FROM t_news GROUP BY nws_auteur;
SELECT nws_auteur, MAX(nws_date) AS date_derniere_news FROM t_news GROUP BY nws_auteur;
SELECT nws_auteur, MIN(nws_date) AS date_premiere_news FROM t_news GROUP BY nws_auteur;
SELECT nws_catid, COUNT(nws_id) AS nb_news FROM t_news GROUP BY nws_catid;
SELECT IF(nws_nb_vues=0,'jamais lues','lues au moins une fois') AS est_lue, COUNT(nws_id) FROM t_news GROUP BY est_lue;

Voilà voilà, soyez sûrs de bien comprendre la base de la clause GROUP BY avant de passer au chapitre suivant, sinon vous risquez d'être perdu... Et au risque de me répéter, n'oubliez pas que tout ce que fait GROUP BY, c'est diviser la table en groupes sur lesquels SELECT va agir directement, et donc qu'on ne peut pas récupérer une autre colonne que celle qui forme le groupe dans ce groupe ...

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