Fonctions

Il peut arriver que nous ayons besoin de données qui ne sont pas directement stockées dans les tables, mais de données calculées, basées sur les données stockées.

Pour cela, il existe ce qu’on appelle des fonctions SQL, permettant de calculer des agrégats à partir des données des tables. Les fonctions s’écrivent à la suite de l’instruction SELECT.

Il existe plusieurs façons de calculer à partir des autres colonnes, nous allons voir ici les méthodes principales. Ces méthodes de calcul peuvent être séparées en deux catégories : les colonnes calculées et les fonctions d’agrégation.

Profitons-en pour faire des retraitements avec quelques méthodes apprises.

Transformons les types des longitudes et latitudes en numérique

ALTER TABLE air_quality
ALTER COLUMN longitude TYPE NUMERIC USING longitude::NUMERIC;
ALTER TABLE air_quality
ALTER COLUMN latitude TYPE NUMERIC USING latitude::NUMERIC;

Supprimons les quelques lignes où la latitude est une valeur aberrante

DELETE
FROM air_quality
WHERE latitude > 42;

Modifions les valeurs des qualités des particules

UPDATE air_quality
SET "O3 Quality" = CASE
                       WHEN "O3 Quality" IS NULL THEN 0
                       WHEN "O3 Quality" = '--' THEN 0
                       WHEN "O3 Quality" = 'Good' THEN 2
                       WHEN "O3 Quality" = 'Moderate' THEN 1
END,
"NO2 Quality" = CASE
                    WHEN "NO2 Quality" IS NULL THEN 0
                    WHEN "NO2 Quality" = '--' THEN 0
                    WHEN "NO2 Quality" = 'Good' THEN 2
                    WHEN "NO2 Quality" = 'Moderate' THEN 1
END,
"PM10 Quality" = CASE
                     WHEN "PM10 Quality" IS NULL THEN 0
                     WHEN "PM10 Quality" = '--' THEN 0
                     WHEN "PM10 Quality" = 'Good' THEN 2
                     WHEN "PM10 Quality" = 'Moderate' THEN 1
END;

NB : nous avons remplacé les valeurs nulles par 0 car les calculs SQL ne prennent pas en compte les valeurs nulles comme des 0 par défaut. Si nous ne remplaçons pas les valeurs nulles, le résultat d’un calcul incluant une valeur nulle sera alors nul.

Transformons les types de ces qualités en numérique

ALTER TABLE air_quality
ALTER COLUMN "O3 Quality" TYPE NUMERIC USING "O3 Quality"::NUMERIC;
ALTER TABLE air_quality
ALTER COLUMN "NO2 Quality" TYPE NUMERIC USING "NO2 Quality"::NUMERIC;
ALTER TABLE air_quality
ALTER COLUMN "PM10 Quality" TYPE NUMERIC USING "PM10 Quality"::NUMERIC;

Colonnes calculées

Les colonnes calculées sont ajoutées dans le résultat et vont contenir des valeurs calculées à partir d’autres colonnes.

Pour créer ces colonnes, il suffit d’insérer le calcul à la suite de l’instruction SELECT. Par défaut sur postgreSQL, le nom de la colonne calculé est ‘ ?column ?’ (le nom par défaut dépend du SGBD). Il est donc nécessaire d’ajouter l’alias AS pour nommer la colonne.

SELECT <colonnes>, <calcul> AS <nom>
FROM <table>;

Exemple

Nous souhaitons additionner les qualités des trois particules O3, NO2, PM10 :

SELECT id, station, "O3 Quality", "NO2 Quality", "PM10 Quality",
"O3 Quality" + "NO2 Quality" + "PM10 Quality" as global_quality
FROM air_quality
ORDER BY id;

Fonctions d’agrégation

Les fonctions d’agrégation sont aussi ajoutées dans le résultat et vont contenir des valeurs calculées à partir d’autres colonnes et d’autres lignes.

De la même manière que les colonnes calculées, il suffit s’insérer notre fonction à la suite de l’instruction SELECT et de la nommer. Il convient de sélectionner uniquement les colonnes nécessaires aux calculs et les colonnes à grouper.

Il existe plusieurs fonctions d’agrégation comme :

–COUNT : compte le nombre d’observation sur une colonne.
–AVG : calcule la moyenne sur une colonne.
–SUM : calcule la somme de la colonne.
–MAX : renvoie la valeur maximale sur une colonne.
–MIN : renvoie la valeur minimale sur une colonne.

Ces fonctions prennent un sens lorsqu’elles sont associées à l’instruction GROUP BY. GROUP BY est similaire à la fonction groupby() utilisée dans les cours précédents avec python.

L’objectif est d’agréger ces calculs par les valeurs d’une colonne spécifique.

SELECT <colonnes>, fonction(<colonne>)
FROM <table>
GROUP BY <colonne>;

Exemple

Nous souhaitons calculer la moyenne, le maximum, le minimum de la qualité globale des particules (calculée précédemment) par station de Barcelone :

Dans un premier temps, créons une nouvelle table contenant les informations nécessaires aux agrégats grâce à l’instruction DDL CREATE TABLE.

CREATE TABLE sta_qua AS
SELECT id, station, "O3 Quality", "NO2 Quality", "PM10 Quality",
"O3 Quality" + "NO2 Quality" + "PM10 Quality" as global_quality
FROM air_quality
ORDER BY id;

A partir de cette table, calculons les agrégats et regroupons-les par station.

SELECT station, ROUND(AVG(global_quality),2) as moyenne,
MAX(global_quality), MIN(global_quality)
FROM sta_qua
GROUP BY station
ORDER BY station;

NB : à la différence des colonnes calculées, les fonctions d’agrégation ont le nom de la fonction comme nom par défaut, j’ai donc décidé de renommer le premier calcul en ‘moyenne’.

NB : vous trouverez dans le calcul la fonction ROUND. Cette fonction permet, comme son nom l’indique, d’arrondir le résultat d’un calcul.

ROUND(arg1, arg2) avec :
-arg1 = valeur à arrondir
-arg2 = nombre de décimales après la virgule.

L’instruction WHERE peut toujours être utilisée lors de calculs d’agrégat pour filtrer les données avant ces calculs. Si nous voulons calculer les agrégats uniquement sur ‘Barcelona – Ciutadella’ et ‘Barcelona – Eixample’ :

SELECT station, ROUND(AVG(global_quality),2) as moyenne,
MAX(global_quality), MIN(global_quality)
FROM sta_qua
WHERE station IN ('Barcelona - Ciutadella', 'Barcelona - Eixample')
GROUP BY station;

Cependant, si nous souhaitons filtrer les résultats sur les calculs, il faut utiliser l’instruction HAVING et non WHERE. HAVING est propre à l’instruction GROUP BY. L’un filtre avant, l’autre filtre après. Si nous souhaitons récupérer les stations dont la qualité moyenne de l’air est supérieure à 4 :

SELECT station, ROUND(AVG(global_quality),2) as moyenne,
MAX(global_quality), MIN(global_quality)
FROM sta_qua
GROUP BY station
HAVING ROUND(AVG(global_quality),2) > 4;

NB : en utilisant HAVING, il n’est pas possible de filtrer sur la nouvelle colonne que nous avons appelé ‘moyenne’ car elle n’est créée qu’après la transaction. Il convient donc de réécrire le calcul à la suite de HAVING.

Autres fonctions

Il existe de nombreuses fonctions SQL et chaque SGBD a ses propres fonctions, ses propres syntaxes. PostgreSQL propose des fonctions d’agrégation, des fonctions mathématiques (round, floor, power, exp, pi, log, etc…), des fonctions date (pour la manipulation des dates et des heures), des fonctions alphanumériques (manipulation des chaînes de caractère : concat, replace, lower, upper, length, etc…). Je vous invite à lire la documentation postgreSQL dès que vous en avez besoin.