[Plan du site] Vous êtes ici --- > Le Site du Zér0 > Les tutoriels > Non-Officiels > Site Web > PHP > Base de données > Lecture du tutoriel

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

	Auteur : Shepard Créé : le 28/06/2007 16:14:08 Modifié : le 29/06/2007 18:25:52 Noter et commenter ce tutoriel Imprimer ce tutoriel

Vous vous apprêtez à lire un tutoriel rédigé par un membre de ce site. Malgré tout le soin que ce membre a pu apporter au tutoriel, nous ne pouvons pas garantir que les informations contenues sur cette page sont exactes à 100%. Merci de garder cela en tête lorsque vous lirez cette page ;o)

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.

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.

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

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.

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.

Q.C.M.

Que fait la fonction AVG ?

Elle sélectionne les mots qui ont l'avantage dans une colonne d'un groupe (qui peut être la table entière)
Elle effectue une recherche aveugle sur une colonne d'un groupe (idem)
Elle fait la moyenne des données d'une colonne d'un groupe (idem)

Quelles sont les différences entre COUNT(colonne) et COUNT(*) appliquées à une table entière ?

COUNT(colonne) est plus rapide et ne prend en compte que la colonne indiquée, si une valeur NULL s'y trouve, elle n'est pas prise en compte
COUNT(colonne) est moins rapide mais ne tient pas compte des valeurs NULL alors que COUNT(*) si.
COUNT(colonne) est moins rapide, ne prend en compte que la ligne concernée, ne comptera pas les NULL alors que COUNT(*) ne comptera pas la ligne que si elle est exclusivement composée de valeurs NULL.

Que fait GROUP BY ?

Il scinde une table en plusieurs groupes selon un critère qu'on lui donne (une colonne de la table ou un alias d'une colonne calculée)
Il scinde une table en groupes uniquement selon une colonne de la table.
Il groupe plusieurs tables en une seule et permet d'effectuer des requêtes multi-tabulaires.

Un SELECT sur un groupe formé par GROUP BY...

agit sur l'ensemble des données du groupe.
agit comme un SELECT normal mais sur un groupe, c'est-à-dire ligne par ligne.
ne diffère pas d'un SELECT simple.

Quelle est l'utilité de GROUP BY ?

On peut ensuite effectuer des requêtes sur plusieurs tables à la fois.
On peut exécuter des fonctions statistiques (MIN, MAX, SUM, ...) sur chaque groupe et non plus sur la table entière uniquement.
L'union fait la force, un groupe de données est toujours plus intéressant qu'une ligne.

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

Auteur : Shepard
Noter et commenter ce tutoriel
Imprimer ce tutoriel