Fonctions d’agrégation en SQL

Créer des rapports à partir d’un jeu de données est une compétence essentielle lorsque vous travaillez avec des données. Au bout du compte, l’objectif est de répondre à des questions métier clés grâce aux données dont vous disposez. Très souvent, ces réponses sont présentées sous forme de graphiques, mais des rapports tabulaires peuvent aussi être nécessaires. Dans les deux cas, vous devrez parfois résumer les données par des calculs simples. En SQL, vous pouvez agréger les données à l’aide de fonctions d’agrégation. Avec ces fonctions, vous pourrez répondre à des questions comme :

• Quelle est la valeur maximale de some_column_from_the_table ? ou
• Quelles sont les valeurs minimales de some_column_from_the_table par rapport à another_column_from_the_table ?

… et bien d’autres.

Passons à la pratique pour réaliser quelques agrégations.

Remarque : pour suivre ce tutoriel, vous devez savoir écrire des requêtes de base en PostgreSQL (le SGBDR utilisé ici). Ce tutoriel peut servir de piqûre de rappel.

Configuration de la base de données

Commençons par mettre en place une base PostgreSQL et restaurer cette sauvegarde qui contient la table utilisée dans ce tutoriel. Si vous souhaitez apprendre à restaurer une sauvegarde de base sous PostgreSQL, suivez la première section de ce tutoriel.

Si la restauration s’est bien passée, vous devriez voir une table nommée international_debt dans la base (vous devrez d’abord créer une base si vous n’en avez pas). Jetons un coup d’œil aux premières lignes de la table (une simple requête SELECT suffit) :

La table contient des informations sur les statistiques d’endettement de différents pays du monde pour l’année en cours et selon plusieurs catégories (voir les colonnes indicator_name et indicator_code). La colonne debt indique le montant de la dette (en USD) d’un pays donné pour une catégorie spécifique. Ces données relèvent de l’économie et servent souvent à analyser la situation économique des pays. Elles proviennent de la Banque mondiale.

Maintenant que la base est prête, lançons quelques requêtes simples pour mieux comprendre les données. Ouvrez l’outil pgAdmin et commençons.

L’importance des informations simples

Sur la capture ci-dessus, vous voyez de nombreuses entrées dupliquées pour un même pays mais des catégories différentes. Une question s’impose rapidement :

Quels sont les différents pays présents dans la table ?

Si vous exécutez une requête SELECT sur la colonne country_name, vous n’obtiendrez pas la bonne réponse, car le résultat contiendra des doublons. Utilisons le mot-clé DISTINCT pour y remédier.

select distinct country_name from international_debt;
Propulsé par

Vous devriez obtenir un résultat de ce type :

Vous avez maintenant une réponse satisfaisante à la question précédente. Une dernière question avant de passer aux fonctions d’agrégation :

Combien de types d’indicateurs de dette différents la table contient-elle ?

La requête sera similaire à la précédente : il suffit de changer le nom de colonne. À vous de jouer ! Le résultat devrait ressembler à ceci :

Fonctions d’agrégation

Commençons par exécuter une requête avec une fonction d’agrégation et avançons progressivement. Vous apprendrez au passage la syntaxe et les constructions à suivre pour appliquer des fonctions d’agrégation en SQL.

select sum(debt) from international_debt;
Propulsé par

Et le résultat :

Avec la fonction d’agrégation SUM(), vous calculez la somme arithmétique d’une colonne numérique. Avec la requête ci-dessus, vous obtenez le montant total de la dette due par les pays listés dans la table.

Remarque : SUM() n’intègre pas les valeurs NULL dans son calcul. Passons à la question :

Quel est le montant maximal de dette ?

La fonction d’agrégation MAX() vient à la rescousse :

select max(debt) from international_debt;
Propulsé par

Et la réponse :

Comme SUM(), MAX() ignore les NULL dans ses calculs. Il existe aussi la fonction MIN(). Dites-nous la valeur minimale de la colonne debt dans les Commentaires ? Il est pertinent de vérifier s’il n’y a pas d’entrée invalide dans la colonne debt pour garantir la fiabilité des résultats obtenus jusqu’ici.

Remarque : utilisez ces fonctions en minuscules comme ci-dessus.

En exécutant : select * from international_debt where debt is null;, vous devriez obtenir un résultat vide. Cherchons maintenant le nombre total de pays distincts présents dans la table.

select count(distinct(country_name)) from international_debt;
Propulsé par

Vous constatez qu’il y a au total 124 pays distincts dans la table. Observez bien l’enchaînement des fonctions dans la requête ci-dessus. Oui, il est possible de combiner plusieurs fonctions d’agrégation de manière logique.

Supposons à présent que vous souhaitiez voir la valeur moyenne de la colonne debt. La fonction est AVG() :

select avg(debt) from international_debt;

La valeur affichée est 1306633214.966397971 (USD). Il est recommandé de présenter ces résultats avec des noms de colonnes explicites. Comme vous pouvez le voir, PostgreSQL renomme par défaut la colonne avec le nom de la fonction d’agrégation présente dans la requête. Donnons donc un alias pertinent :

select avg(debt) as Average_Debt_By_A_Country from international_debt;
Propulsé par

Le résultat est bien plus lisible :

Passons à un niveau un peu plus avancé. Pour répondre à des questions du type Quelles sont les valeurs minimales de some_column_from_the_table par rapport à another_column_from_the_table ?, vous devez associer une fonction d’agrégation à une clause GROUP BY. Voyons comment.

Fonctions d’agrégation + GROUP BY + plus

Imaginons que vous souhaitiez produire un rapport affichant le country_name et la somme de leurs dettes. Par exemple :

Des rapports comme celui-ci sont très courants. Quelle requête utiliser ? Vous devez appliquer SUM() sur debt et afficher également country_name avec la somme des dettes. Exécutons :

select country_name, sum(debt) from international_debt;
Propulsé par

Cela ne produit-il pas l’erreur suivante ?

ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function

Voyons ce que cela signifie. Lorsque vous utilisez une fonction d’agrégation (comme SUM()) avec une colonne non agrégée (comme country_name), vous devez passer la colonne non agrégée dans une clause GROUP BY. La requête correcte est donc :

select country_name, sum(debt) as total_debt from international_debt group by country_name;
Propulsé par

Et le résultat est conforme :

Remarque : notez l’utilisation d’un alias.

Supposons maintenant que vous deviez trier ce rapport selon total_debt par ordre décroissant. Vous vous souvenez de la clause ORDER BY ? Oui, vous pouvez aussi l’associer à des fonctions d’agrégation :

select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
Propulsé par

Le résultat est désormais trié à l’envers :

Remarque : observez la colonne utilisée dans ORDER BY.

Autre question importante :

Quel est le montant de dette le plus élevé par catégorie (trié à l’envers) ?

Vous utiliserez ici la fonction MAX(). Écrire la requête ne devrait plus poser de difficulté.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
Propulsé par

Vous obtenez un rapport clair :

Vous pouvez aussi limiter le nombre de lignes de ce type de rapport. Par exemple, pour n’inclure que les cinq premières entrées, utilisez la clause LIMIT.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
Propulsé par

Dernier rapport pour ce tutoriel : vous devez inclure les noms des pays dans le rapport précédent. Comment faire ? La requête suivante convient :

select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
Propulsé par

Encore un bon rapport :

Dans la requête ci-dessus, vous avez ajouté la colonne country_name après SELECT et également après GROUP BY. Vous pouvez étendre ce schéma à autant de colonnes que nécessaire.

L’ordre de GROUP BY, ORDER BY et LIMIT est crucial pour générer ce type de rapports. En cas d’inversion, vous obtiendrez des erreurs. Voyez plutôt :

select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
Propulsé par

Et vous obtenez :

ERROR:  syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...Propulsé par

Dans la requête ci-dessus, ORDER BY a été placé avant GROUP BY, ce qui est interdit. D’ailleurs, ce n’est pas applicable même sans fonctions d’agrégation. L’ordre correct est : GROUP BY -> ORDER BY -> LIMIT. Gardez-le en tête.

Pour aller plus loin

Bravo ! Vous êtes arrivé au bout de ce tutoriel. Vous avez découvert les principales fonctions d’agrégation de PostgreSQL et comment les utiliser pour produire des rapports utiles. Des compétences indispensables pour tout data scientist. Pour faire progresser vos compétences SQL de manière structurée, suivez ces cours DataCamp :

• Joining Data in SQL
• Intermediate SQL
• SQL for Exploratory Data Analysis