Agrégation

• Aperçu
• Compter les relevés de température
• Solution
• Moyenne avec NULL
• Solution
• Que fait cette requête ?
• Solution
• Ordre lors de la concaténation
• Points clés

Aperçu

Enseignement : 10 min
Exercices : 10 min

Questions

• Comment puis-je calculer des sommes, des moyennes et d’autres valeurs récapitulatives ?

Objectifs

• Définir l’agrégation et donner des exemples de son utilisation.

• Ecrire des requêtes qui calculent des valeurs agrégées.

• Tracer l’exécution d’une requête qui effectue une agrégation.

• Expliquer comment les données manquantes sont traitées pendant l’agrégation.

Nous voulons maintenant calculer des plages et des moyennes pour nos données.Nous savons comment sélectionner toutes les dates de la table Visited :

SELECT dated FROM Visited;

-null-

mais pour les combiner, nous devons utiliser une fonction d’agrégation telle que min ou max.Chacune de ces fonctions prend un ensemble d’enregistrements en entrée, et produit un seul enregistrement en sortie :

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min et max ne sont que deux des fonctions d’agrégation intégrées à SQL.Trois autres sont avg,count, et sum :

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

.

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

Nous avons utilisé count(reading) ici,mais nous aurions tout aussi bien pu compter quantou n’importe quel autre champ de la table, ou même utiliser count(*), puisque la fonction ne se soucie pas des valeurs elles-mêmes, juste du nombre de valeurs.

SQL nous permet de faire plusieurs agrégations à la fois.Nous pouvons, par exemple, trouver la plage des mesures de salinité sensible:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Nous pouvons également combiner les résultats agrégés avec les résultats bruts, bien que le résultat puisse vous surprendre :

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Pourquoi le nom de Lake apparaît-il plutôt que celui de Roerich ou de Dyer ?La réponse est que lorsqu’il doit agréger un champ, mais qu’on ne lui dit pas comment le faire, le gestionnaire de base de données choisit une valeur réelle dans l’ensemble des entrées.Il peut utiliser la première traitée, la dernière, ou quelque chose d’entièrement différent.

Un autre fait important est que lorsqu’il n’y a pas de valeurs à agréger -par exemple, lorsqu’il n’y a pas de lignes satisfaisant la clause WHERE– le résultat de l’agrégation est « ne sait pas « plutôt que zéro ou une autre valeur arbitraire :

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

Une dernière caractéristique importante des fonctions d’agrégation est qu’elles sont incohérentes avec le reste du SQL d’une manière très utile.Si nous additionnons deux valeurs, et que l’une d’entre elles est nulle, le résultat est nul.Par extension,si nous utilisons sum pour additionner toutes les valeurs d’un ensemble, et que l’une de ces valeurs est nulle, le résultat devrait également être nul.Il est cependant beaucoup plus utile que les fonctions d’agrégation ignorent les valeurs nulles et ne combinent que celles qui ne le sont pas.Ce comportement nous permet d’écrire nos requêtes comme:

SELECT min(dated) FROM Visited;

au lieu de toujours devoir filtrer explicitement :

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Agréger tous les enregistrements à la fois n’a pas toujours de sens.Par exemple,supposons que nous soupçonnions qu’il y a un biais systématique dans nos données, et que les relevés de radiation de certains scientifiques sont plus élevés que d’autres.Nous savons que cela ne fonctionne pas :

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

parce que le gestionnaire de la base de données sélectionne un seul nom arbitraire de scientifique plutôt que d’agréger séparément pour chaque scientifique.Comme il n’y a que cinq scientifiques, nous pourrions écrire cinq requêtes de la forme :

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

mais ce serait fastidieux, et si jamais nous avions un ensemble de données avec cinquante ou cinq cents scientifiques, les chances que nous réussissions toutes ces requêtes sont faibles.

Ce que nous devons faire, c’est dire au gestionnaire de la base de données d’agréger les heures pour chaque scientifique séparémenten utilisant une clause GROUP BY :

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY fait exactement ce que son nom indique : il regroupe tous les enregistrements ayant la même valeur pour le champ spécifié afin que l’agrégation puisse traiter chaque lot séparément.Puisque tous les enregistrements de chaque lot ont la même valeur pour person, il n’est plus important que le gestionnaire de la base de données en choisisse une arbitraire pour l’afficher à côté des valeurs reading agrégées.

De même que nous pouvons trier par plusieurs critères à la fois, nous pouvons aussi grouper par plusieurs critères.Pour obtenir le relevé moyen par scientifique et par quantité mesurée, par exemple, nous ajoutons simplement un autre champ à la clause GROUP BY :

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Notez que nous avons ajouté quant à la liste des champs affichés,car les résultats n’auraient pas beaucoup de sens autrement.

Faisons un pas de plus et supprimons toutes les entrées où nous ne savons pas qui a effectué la mesure :

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

En regardant de plus près, cette requête :

1. sélectionne les enregistrements de la table Survey où le champ person n’est pas nul;

2. regroupe ces enregistrements en sous-ensembles de sorte que les valeurs person et quant de chaque sous-ensemble soient les mêmes ;

3. a classé ces sous-ensembles d’abord par person, puis dans chaque sous-groupe par quant ; et

4. a compté le nombre d’enregistrements dans chaque sous-ensemble, calculé la moyenne reading dans chacun, et choisi une valeur person et quant dans chacun (peu importe lesquelles, puisqu’elles sont toutes égales).

Compter les relevés de température

Combien de relevés de température Frank Pabodie a-t-il enregistrés, et quelle était leur valeur moyenne ?

Solution

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

compte(lecture)	avg(lecture)
2	-20.0

Moyenne avec NULL

La moyenne d’un ensemble de valeurs est la somme des valeurs divisée par le nombre de valeurs.Cela signifie-t-il que la fonction avg renvoie 2,0 ou 3,0quand on lui donne les valeurs 1,0, null, et 5,0?

Solution

La réponse est 3,0.NULL n’est pas une valeur ; c’est l’absence de valeur.En tant que telle, elle n’est pas incluse dans le calcul.

Vous pouvez le confirmer, en exécutant ce code :

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Que fait cette requête ?

Nous voulons calculer la différence entrechaque lecture individuelle de rayonnement et la moyenne de toutes les lectures de rayonnement.Nous écrivons la requête:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Que produit-elle réellement, et pouvez-vous penser à pourquoi ?

Solution

La requête produit une seule ligne de résultats alors que ce que nous voulons réellement est un résultat pour chacun des relevés.La fonction avg() ne produit qu’une seule valeur, et parce qu’elle est exécutée en premier, le tableau est réduit à une seule ligne.La valeur reading est simplement arbitraire.

Pour obtenir ce que nous voulons, il faudrait exécuter deux requêtes :

SELECT avg(reading) FROM Survey WHERE quant='rad';

Cela produit la valeur moyenne (6.5625), que nous pouvons ensuite insérer dans une deuxième requête:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Cela produit ce que nous voulons, mais nous pouvons combiner cela en une seule requête en utilisant des sous-requêtes.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

De cette façon, nous n’avons pas à exécuter deux requêtes.

En résumé, ce que nous avons fait est de remplacer avg(reading) par (SELECT avg(reading) FROM Survey WHERE quant='rad') dans la requête originale.

Ordre lors de la concaténation

La fonction group_concat(field, separator)concatène toutes les valeurs d’un champ en utilisant le caractère séparateur spécifié(ou ‘,’ si le séparateur n’est pas spécifié).Utilisez-la pour produire une liste d’une ligne de noms de scientifiques,comme:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Pouvez-vous trouver un moyen d’ordonner la liste par nom de famille ?

Points clés

• Utilisez les fonctions d’agrégation pour combiner plusieurs valeurs.

• Les fonctions d’agrégation ignorent les valeurs null.

• L’agrégation se produit après le filtrage.

• Utiliser GROUP BY pour combiner des sous-ensembles séparément.

• Si aucune fonction d’agrégation n’est spécifiée pour un champ, la requête peut renvoyer une valeur arbitraire pour ce champ.

.