Guide complet des principales fonctions SQL
Les fonctions de base avant d'aborder les fonctions de fenêtrage.
Maîtrisez ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD et les agrégations cumulatives. Exemples concrets avec les données Capital Bikeshare de Washington DC.
Une fonction de fenêtrage effectue un calcul sur un ensemble de lignes de la table qui sont liées d'une certaine manière à la ligne courante. Contrairement aux fonctions d'agrégation classiques, l'utilisation d'une fonction de fenêtrage ne regroupe pas les lignes en une seule ligne de sortie — les lignes conservent leur identité séparée.
L'exemple le plus pratique est un total cumulé :
SELECT duree_secondes,
SUM(duree_secondes) OVER (ORDER BY heure_debut) AS total_cumulatif
FROM tutoriel.vélos_partage_q1_2012;Cette requête crée une agrégation (total_cumulatif) sans utiliser GROUP BY. Chaque ligne conserve son identité, mais affiche la somme cumulée jusqu'à cette ligne.
Ce tutoriel utilise les données du programme Capital Bikeshare de Washington DC (premier trimestre 2012). Chaque ligne représente un trajet. Les champs heure_debut et heure_fin sont au format timestamp SQL.
Illustration du partitionnement et de l'ordre des fenêtres
La première partie de l'agrégation, SUM(duree_secondes), ressemble à n'importe quelle autre agrégation. L'ajout de OVER la désigne comme fonction de fenêtrage.
"Prendre la somme de duree_secondes sur l'ensemble du résultat, ordonné par heure_debut."
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut)
AS total_cumulatif
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';Explication : La requête groupe et ordonne par station_debut. Dans chaque station, elle est ordonnée par heure_debut, et le total cumulatif somme la ligne courante et toutes les lignes précédentes. Quand station_debut change, total_cumulatif recommence.
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER (PARTITION BY station_debut) AS total_station_debut
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';Vous ne pouvez pas utiliser les fonctions de fenêtrage et les agrégations standard dans la même requête. Plus précisément, vous ne pouvez pas inclure de fonctions de fenêtrage dans une clause GROUP BY.
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER (PARTITION BY station_debut) AS total_station,
COUNT(duree_secondes) OVER (PARTITION BY station_debut) AS nb_trajets,
AVG(duree_secondes) OVER (PARTITION BY station_debut) AS moyenne_station
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut) AS total_cumulatif,
COUNT(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut) AS nb_cumulatif,
AVG(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut) AS moyenne_cumulative
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';Exécutez ces deux requêtes dans votre environnement SQL. La différence entre la version avec et sans ORDER BY est fondamentale pour comprendre les fenêtres glissantes.
ROW_NUMBER() affiche le numéro d'une ligne donnée. Il commence à 1 et numérote selon ORDER BY.
SELECT station_debut,
heure_debut,
duree_secondes,
ROW_NUMBER() OVER (ORDER BY heure_debut) AS numero_ligne
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';SELECT station_debut,
heure_debut,
duree_secondes,
ROW_NUMBER() OVER (PARTITION BY station_debut ORDER BY heure_debut) AS numero_ligne
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';Supprimer les doublons en ne gardant que la première occurrence (WHERE numero_ligne = 1).
RANK() est légèrement différent de ROW_NUMBER(). Si plusieurs lignes ont la même valeur selon ORDER BY, elles reçoivent le même rang.
SELECT station_debut,
duree_secondes,
RANK() OVER (PARTITION BY station_debut ORDER BY heure_debut) AS rang
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';| Valeur | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 10 | 1 | 1 | 1 |
| 2 | |||
| 3 | |||
| 15 | 4 | 4 | 2 |
| 15 | 5 | 4 | 2 |
| 20 | 6 | 6 | 3 |
NTILE(nombre_de_tranches) divise les données en tranches (quartiles, quintiles, percentiles).
SELECT station_debut,
duree_secondes,
NTILE(4) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS quartile,
NTILE(5) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS quintile,
NTILE(100) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS percentile
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondes;Si le nombre de lignes dans une partition est inférieur au nombre de tranches, NTILE ne peut pas les répartir correctement. Par exemple, avec 2 lignes et NTILE(100), vous n'aurez que des valeurs 1 et 2. Pour des percentiles précis, assurez-vous d'avoir au moins 100 lignes.
LAG extrait des lignes précédentes, LEAD extrait des lignes suivantes.
SELECT station_debut,
duree_secondes,
LAG(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS lag,
LEAD(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS lead
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondes;SELECT station_debut,
duree_secondes,
duree_secondes - LAG(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS difference
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondes;SELECT *
FROM (
SELECT station_debut,
duree_secondes,
duree_secondes - LAG(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS difference
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
) sous_requete
WHERE sous_requete.difference IS NOT NULL;LAG(colonne, décalage, valeur_par_défaut) permet de spécifier une valeur par défaut au lieu de NULL.
Si vous utilisez plusieurs fonctions de fenêtrage avec la même définition, créez un alias avec WINDOW.
SELECT station_debut,
duree_secondes,
NTILE(4) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS quartile,
NTILE(5) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS quintile,
NTILE(100) OVER (PARTITION BY station_debut ORDER BY duree_secondes) AS percentile
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondes;SELECT station_debut,
duree_secondes,
NTILE(4) OVER ntile_window AS quartile,
NTILE(5) OVER ntile_window AS quintile,
NTILE(100) OVER ntile_window AS percentile
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
WINDOW ntile_window AS (PARTITION BY station_debut ORDER BY duree_secondes)
ORDER BY station_debut, duree_secondes;WINDOW doit toujours venir après la clause WHERE et avant ORDER BY.SELECT station_debut,
heure_debut,
duree_secondes,
AVG(duree_secondes) OVER (
PARTITION BY station_debut
ORDER BY heure_debut
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moyenne_glissante
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08';ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : défaut (cumul depuis le début)ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : fenêtre glissante de 3 lignesROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING : fenêtre glissante de 5 lignes| Fonction | Description | Cas d'usage typique |
|---|---|---|
ROW_NUMBER() |
Numérotation unique | Suppression doublons, pagination |
RANK() |
Rangs avec sauts (1,1,1,4...) | Classements avec ex-aequo |
DENSE_RANK() |
Rangs sans sauts (1,1,1,2...) | Classements compacts |
NTILE(n) |
Division en n tranches | Quartiles, percentiles |
LAG() |
Valeur de la ligne précédente | Différence avec période précédente |
LEAD() |
Valeur de la ligne suivante | Prévision, comparaison future |
GROUP BY réduit le nombre de lignes (une ligne par groupe). PARTITION BY dans une fonction de fenêtrage conserve toutes les lignes et ajoute une colonne calculée par groupe. Les fonctions de fenêtrage sont plus flexibles pour les calculs cumulatifs et les comparaisons entre lignes.
Oui. ORDER BY seul (sans PARTITION) traite toute la table comme une seule partition. Exemple : SUM(ventes) OVER (ORDER BY date) donne un total cumulé sur tout l'historique.
Oui, par tous les SGBD modernes : PostgreSQL (8.4+), MySQL (8.0+), SQL Server (2005+), Oracle, SQLite (3.25+). Les versions plus anciennes peuvent ne pas les supporter.
Utilisez PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ou DATE_TRUNC('month', date) selon votre SGBD, puis ORDER BY date.
Très bonnes sur des ensembles de données de taille raisonnable (millions de lignes). Elles évitent les sous-requêtes auto-jointures coûteuses. Cependant, sur des billions de lignes, un partitionnement intelligent est recommandé.
Oui, comme dans un ORDER BY classique : ORDER BY colonne1, colonne2. Les fonctions ROW_NUMBER() et RANK() utiliseront cette hiérarchie pour départager les égalités.
Les fonctions de fenêtrage sont un outil puissant pour l'analyse de données avancée. Elles permettent de calculer des totaux cumulatifs, des classements, des différences entre lignes et des percentiles, tout en conservant l'intégralité des lignes.