> Le Site du Zéro - Informatique

> Cours

> Tutoriels

> Bases de données

> Pour aller plus loin...

> SELECT: Une puissance négligée

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

> Lecture du tutoriel

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

Par

Shepard

Mise à jour : 29/06/2009

1 063 visites depuis 7 jours, dont 112 sur ce chapitre classé 122/786

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.

Retour en haut

Sommaire du chapitre :

Les fonctions statistiques de MySQL
Grouper les données avec GROUP BY
Les fonctions statistiques agissent sur des groupes !!
Q.C.M.

Retour en haut

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

Code : SQL

1	SELECT COUNT(nws_id) FROM t_news;

MAX()

Renvoie la valeur maximale

Code : SQL

1	SELECT MAX(nws_nb_vues) FROM t_news;

2154

MIN()

Renvoie la valeur minimale

Code : SQL

1	SELECT MIN(nws_nb_vues) FROM t_news;

SUM()

Renvoie la somme des valeurs

Code : SQL

1	SELECT SUM(nws_nb_vues) FROM t_news;

4407

AVG()

Renvoie la moyenne (Average) des valeurs

Code : SQL

1	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

).

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

Code : SQL

1	SELECT VAR_POP(nws_nb_vues) FROM t_news;

608275.5833

STDDEV_POP

Ecart-type d'une population

Code : SQL

1	SELECT STDDEV_POP(nws_nb_vues) FROM t_news;

779.9202

VAR_SAMP

Variance d'un échantillon

Code : SQL

1	SELECT VAR_SAMP(nws_nb_vues) FROM t_news;

729930.7000

STDDEV_SAMP

Ecart-type d'un échantillon

Code : SQL

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

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

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 :

Code : SQL

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

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 :

Code : SQL

1	SELECT COUNT(DISTINCT IF(nws_auteur='karamilo', 'Alexi Laiho', nws_auteur)) FROM t_news;

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

Code : SQL

1	SELECT COUNT(NULLIF(nws_auteur, 'karamilo')) FROM t_news;

NULLIF(a1, a2) renvoie NULL si a1 = a2, sinon elle renvoie a1.

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 :

Code : SQL

1	SELECT AVG(NULLIF(nws_nb_vues, 0)) FROM t_news;

AVG() est strictement la même chose que SUM() / COUNT(), même si COUNT() renvoie 0 (c'est-à-dire qu'il n'y a pas de ligne à traiter), le résultat sera 0, c'est une exception qui permet de ne pas devoir prévoir d'exception quand il n'y a aucune ligne dans la table traitée.

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

Code : SQL

1	SELECT COUNT(*) FROM t_news;

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

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

). Normalement vous commencez à comprendre pourquoi j'ai décidé de diviser ce chapitre en 2 parties.

Retour en haut

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 :

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.

On pourrait donc imaginer (soyons fous !) diviser notre table en 3 groupes, comme ceci :

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 :

Code : SQL

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

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:

Code : SQL

1	SELECT nws_id FROM t_news GROUP BY nws_auteur;

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

Si vous essayez d'exécuter cette requête, MySQL vous renverra l'un des trois, généralement le premier du groupe, mais ne prenez jamais le risque de faire ce genre de suppositions dans un script supposé fonctionnel !!

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 :

Code : SQL

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

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.

Retour en haut

Les fonctions statistiques agissent sur des groupes !!

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

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 :

Code : SQL

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

Code : SQL

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

Le résultat semble presque magique...

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

On peut utiliser les alias des colonnes calculées entre SELECT et FROM dans la clause GROUP BY.

Les réponses :

Secret (cliquez pour afficher)

Code : SQL

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;

Remarquez qu'ici je n'utilise pas COUNT(*) dont j'avais fait l'éloge tout à l'heure mais COUNT(nws_id). En effet COUNT(*) n'est efficace que sur une table entière, il devient (très) lourd lorsque vous travaillez sur des groupes.

Retour en haut

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

Retour en haut

Retour en haut

9 commentaires pour "Un peu de stats avec GROUP BY ( 1 / 2 )"

Note moyenne : 3.87 / 4 (31 votes)

Pseudo	Commentaire
Shepard	# Posté le 26/07/2007 à 17:08:04
SQL Beginner ... Groupe : Anciens Ville : Mouscron Pays : Belgique Études : Université de Mons-Hainaut	Merci à tous La suite arrive ! Pour Talus: Ok mais je le copie / colle ici après Pour Gaga971: J'hésitais à en parler ... A mon avis MySQL est prévu pour les petits bases de données, et en général elles ne requièrent pas de solutions de partitionnement, et puis après je me suis dit que ça pourrait être cool, sur un forum par exemple, de splitter les posts en catégories, ça permettrait des stats très rapides ( COUNT instantanés ) J'en parlerais mais après les UDF puisque le partitionnement dynamique c'est mieux que le partitionnement fixe Bonne journée à tous !
patouche	# Posté le 25/08/2007 à 16:02:25
Ville : Houilles Pays : France métropolitaine Études : ENSEA	Super !!! Il est vraiment génial ton tuto. Il est pas aussi vivant que ceux de matéo mais il est et restera un très bon tuto clair et bien illustré. Il permet de bien montrer comment ça fonctionne des jolies requêtes !! Je n'ai pas noter les précédents mais tu peux faire des copier/coller puisque apparament tu aimes ça . En note, euh ça se note un tuto comme ça qui sort de nul part et qui déchire tout ? bon aller 20.5/20 et c'est cadeau !!! Pensez aussi au petit bouton résolu farkess.com
Mikilo	# Posté le 13/09/2007 à 22:32:29
Lintury Under Construction...	Rien à dire, c'est long, mais sympa à lire -=[Mikilo]=-
steph580	# Posté le 06/02/2008 à 20:35:39
Hé hé hé ... Groupe : Bannis	20/20 ! Trés trés bon ! j'attender que sa ! un tuto sur le SQL ! Merci merci merci ! J'attent la suite avec impatience !
Jerry Wham	# Posté le 30/03/2008 à 17:30:42
1castorMangé,1arbreSauvé Ville : Bordeaux Pays : France métropolitaine	Excellent tuto. Il manquait vraiment au sdz. Il mériterait de figurer dans les tutos officiels, une fois fini... 20/20 minimum. Wham Besoin de vacances ? A y est... je l'ai !La doc c'est bon ! Mangez-en !