Fonctions d'agrégation
Une agrégation est une opération qui permet de combiner plusieurs valeurs de données en une seule valeur résumée. Les opérations d'agrégation sont souvent utilisées en combinaison avec la clause group by pour regrouper les données selon des critères spécifiques avant de les résumer.
Compter
L'opérateur count Compte le nombre d'enregistrements dans un ensemble de données.
Exercice : Compter le nombre d'adhérents.
select count(*) from adherent
>> 2439
On peut compter sur n'importe quelle colonne, dans ce cas, pour ne pas avoir à choisir on utilise le caractère joker *.
Attention ! Si on compte sur une colonne seules les lignes avec une valeur non nulle seront comptées.
Exercice : Compter le nombre d'adhérent dont le codepostal est non null.
select count(codepostal) from adherent;
>> 2438
Exercice : Compter le nombre d'articles de la famille 02CHOU.
select count(*) from article
where famille_code = '02CHOU'
>> 29
distinct
En ajoutant le mot clé distinct l'opération s'effectue sur les valeurs uniques d'une ou de plusieurs colonnes.select codepostal,
count(case when genre = 1 then 1 end) as nb_hommes,
count(case when genre = 2 then 1 end) as nb_femmes
from adherent
group by codepostal;
Exercice : Afficher le nombre de codes postaux différents trouvés dans la table adhérent.
select count(distinct codepostal) from adherent
>> 11
[!WARNING] Attention ! Sans distinct,
count(codepostal)compte toutes les lignes de la colonne codepostal dont la valeur est non nulle. Dans notre cas pratiquement tous les adhérents (2438/2439).
Regroupement
la clause group by permet de choisir un ou plusieurs niveau de regroupement.
select codepostal, count(*)
from adherent
group by codepostal;
| codepostal | count |
|---|---|
| 88210 | 178 |
| 88650 | 195 |
| 88580 | 97 |
| 88430 | 131 |
| 88520 | 127 |
| 88110 | 210 |
| 88420 | 158 |
| 88480 | 245 |
| 88490 | 261 |
| 88100 | 650 |
| 88470 | 187 |
Alias de colonne
Il est possible de donner un nom spécifique à n'importe qu'elle colonne y compris (et surtout) les colonnes d'agrégations avec le mot clé as.
select codepostal, count(*) as nb_adherents
from adherent
group by codepostal;
Filtre
Il est également possible d'ajouter une condition where à une seule colonne uniquement. C’est souvent plus lisible et performant que de faire un CASE WHEN imbriqué. Cela permet de faire des agrégations différenciées par colonne.
Exercice : Afficher le nombre d'adhérent par code postal ET par genre de telle façon d'avoir UNE ligne par code postal et deux colonnes une pour les hommes et l'autre pour les femmes.
Avec la clause group by une ligne différente est générée par combinaison de colonnes.
select codepostal, genre, count(*) as nb_adherents
from adherent
group by codepostal, genre
| codepostal | genre | nb_adherents |
|---|---|---|
| 88580 | 2 | 51 |
| 88650 | 1 | 114 |
| 88430 | 2 | 54 |
| 88520 | 1 | 68 |
| 88110 | 1 | 103 |
| 88100 | 1 | 345 |
| 88110 | 2 | 107 |
| ... | ... | ... |
select codepostal,
count(*) filter (where genre = 1) as nb_hommes,
count(*) filter (where genre = 2) as nb_femmes
from adherent
group by codepostal
| codepostal | nb_hommes | nb_femmes |
|---|---|---|
| 88210 | 94 | 84 |
| 88650 | 114 | 81 |
| 88580 | 46 | 51 |
| 88430 | 77 | 54 |
| 88520 | 68 | 59 |
| 88110 | 103 | 107 |
| 88420 | 82 | 76 |
| 88480 | 135 | 110 |
| 88490 | 128 | 133 |
| 88100 | 345 | 305 |
| 88470 | 103 | 84 |
Équivalent avec des case when
select codepostal,
count(case when genre = 1 then 1 end) as nb_hommes,
count(case when genre = 2 then 1 end) as nb_femmes
from adherent
group by codepostal;
Somme
Calcule la somme des valeurs d'une colonne numérique.
SELECT SUM(vente) FROM ticket;
Exercice : Calculer le chiffre d’affaires global. (1 914 792.40)
select round(sum(quantite * prix_unitaire), 2) as chiffre_affaire from ligne;
>> 1914792.40
select sum(round(quantite * prix_unitaire, 2)) as chiffre_affaire from ligne;
>> 1915049.36
L'arrondi de la somme n'est pas égal à la somme des arrondis.
Moyenne
Calcule la moyenne des valeurs d'une colonne numérique.
select avg(prix) from article;
Maximum
Retourne la valeur maximale dans une colonne.
select max(age) from adherent;
Une autre solution consiste à trier la colonne est de limiter le résultat à une seule ligne
Exercice : Afficher l'article le plus cher (avec son prix).
select article, prix
from article
order by prix desc limit 1;
>> Pistache 63 €
Minimum
Retourne la valeur minimale dans une colonne.
select min(age) from adherent;
Une autre solution consiste à trier la colonne est de limiter le résultat à une seule ligne
Afficher l'article le moins cher (avec son prix). (Courgette Mini Fleur 0.3)
select article, prix
from article
order by prix asc limit 1;
Exercice : Pour chaque adhérent quel est la date de son premier et de son dernier ticket.
Une valeur
Avec la fonction any_value la colonne prend une valeur de la colonne sans distinction particulière.
select codepostal, any_value(nom)
from adherent
group by codepostal;
| codepostal | nom |
|---|---|
| 88480 | Fievet |
| 88490 | Maury |
| 88100 | Cuvelier |
| 88470 | Maurin |
| . | Jouve |
| 88210 | Aubertin |
| 88650 | Wagner |
| 88580 | Beaulieu |
| 88430 | Baudouin |
| 88520 | Bérard |
| 88110 | Frémont |
| 88420 | Besnard |
Toutes
La fonction bool_and ou son alias every renvoie vrai si toutes les lignes satisfont la condition.
Exercice : Afficher vrai si tous les articles d'une même famille ont tous leur prix supérieur à 10 €
select famille_code, bool_and(prix > 10) as sup_10
from article
group by famille_code
| famille_code | sup_10 |
|---|---|
| 03BANA | false |
| 03PECHE | false |
| 03RHUBARBE | false |
| 03FIGUE | true |
| 03ORANGE | false |
Au moins un
La fonction bool_or renvoie vrai si au moins une lignes satisfait la condition.
Exercice : Afficher vrai si au moins un article d'une famille a un prix supérieur à 5 €
select famille_code, bool_or(prix > 5) as sup_5
from article
group by famille_code
| famille_code | sup_5 |
|---|---|
| 03BANA | false |
| 03PECHE | true |
| 03RHUBARBE | true |
| 03FIGUE | true |
| 03ORANGE | false |
Concaténation en chaine
STRING_AGG construit une chaine de caractères séparés par un délimiteur
select string_agg(distinct famille_code, ', ')
from article;
>> '02, 02AROM, 02ARTICHAU, 02ASP, 02AUBE, 02BETT, ...'
Concaténation en tableau
ARRAY_AGG construit une tableau à partir des éléments. Très utilisé lors d'une sérialisation en JSON par exemple.
select array_agg(distinct famille_code)
from article;
>> ['02', '02AROM', '02ARTICHAU', '02ASP', '02AUBE', '02BETT', ...]
Filtre après agrégation
HAVING est emblable à WHERE, mais utilisé pour filtrer les résultats des fonctions d'agrégation après un GROUP BY.
Quels sont les personnes qui ont exactement 72 tickets ? (Cote et Lebreton)
select a.nom, a.prenom,
count(t.id) as nb_ticket
from adherent a
join ticket t on a.id = t.adherent_id
group by a.id
having count(t.id) = 72;
Avec postgreSQL et Microsoft SQL Server il n'est pas possible d'utiliser l'alias dans la condition having. La clause having intervient en amont du select dans le moteur d'exécution, l'alias n'est pas encore connu. Il faut réécrire la formule.
Rang
rank, percent_rank et dense_rank calcule le rang de la données.
cume_dist calcule la distribution cumulée.
Ces fonctions sont en réalité des fenêtres analytiques(window) mais peuvent être utilisées pour explorer la distribution.
Agrégats d'intervalles
postgreSQL support un type particulier que sont les intervalles de valeur(intervalle) en définitissant dans un même champ une borne inférieure et une supérieure.
range_agg fusionne les intervalles tandis que range_intersect_agg retourne l'intersection.