Avez-vous utilisé les fonctions SQL Window ? Avez-vous besoin d’exemples de requêtes rapides que vous pouvez toujours consulter lorsque vous travaillez avec des bases de données relationnelles ou d’une antisèche que vous pouvez incorporer dans votre flux de travail de développeur ?
Pour répondre à ce besoin, nous avons créé ce tutoriel pour vous aider à améliorer vos opérations SQL. Si vous n’avez jamais utilisé les fonctions SQL Window, cet article est un bon point de départ.
Les fonctions Window sont des fonctions de calcul qui réduisent la complexité des requêtes SQL et augmentent leur efficacité. Elles sont dérivées de deux composants : une fenêtre, un ensemble de lignes, et des fonctions, qui impliquent des blocs de code SQL prédéfinis pour effectuer des opérations sur les données. En utilisant les fonctions SQL Window, vous pouvez effectuer des analyses avancées sans écrire de requêtes complexes.
Lors de l’analyse ou de la création de rapports de données, vous pouvez utiliser les fonctions SQL Window pour agréger ou comparer des données dans des fenêtres de lignes spécifiques. Ces fonctions vous permettent d’éviter les jointures automatiques ou les sous-requêtes, d’agréger des données sans réduire les résultats à une seule valeur pour l’ensemble d’une table et de comparer les valeurs des lignes à l’aide de calculs tels que les totaux, les pourcentages ou même les classements.
Les fonctions de fenêtre SQL fonctionnent en trois étapes simples. Tout d’abord, vous définissez une fenêtre. En effet, les fonctions Windows opèrent sur un ensemble de lignes définies à l’aide de la clause OVER(). Ensuite, la clause PARTITION BY divise l’ensemble des résultats en partitions auxquelles la fonction est appliquée. Enfin, la clause ORDER by détermine l’ordre des lignes dans chaque partition.
REMARQUE : le fenêtrage en SQL ne doit pas être confondu avec l’agrégation. L’agrégation implique des fonctions traditionnelles, par exemple, (SUM et AVG), qui opèrent sur plusieurs lignes, les regroupant en un seul résultat. Pour les distinguer, rappelez-vous que les fonctions Window travaillent sur une fenêtre (ensemble spécifié de lignes), en conservant les lignes individuelles dans leur résultat.
Passons maintenant à une approche pratique de l’utilisation des fonctions SQL de type fenêtre. Pour chaque catégorie, vous disposerez d’un tableau récapitulatif pour une référence rapide.
Syntaxe des fonctions SQL de fenêtre
La syntaxe générale des fonctions de fenêtre comprend trois éléments : la fonction elle-même, la clause OVER et une PARTITION BY facultative. Voici la décomposition de chacun de ces éléments.
#1. La fonction
La fonction indique l’opération de calcul que vous ciblez sur la ligne. Il peut s’agir de fonctions d’agrégation standard (par exemple, SUM, AVG et COUNT). Ou d’une fonction analytique (par exemple, ROW_NUMBER, RANK, LEAD et LAG).
Voici un exemple utilisant la fonction SUM.
SELECT
colonne1,
colonne2,
colonne3,
SUM(column3) OVER () AS total_weight
FROM
votre_table ;
#2. Clause OVER
La clause OVER vous permet de définir les lignes sur lesquelles la fonction doit opérer. Il existe deux types de clauses : PARTITION BY et ORDER BY. La première est facultative et divise les résultats en sections auxquelles la fonction window est appliquée.
Pour le second, il spécifie l’ordre des lignes dans chaque partition. Si elle n’est pas spécifiée, la fonction traite la fenêtre comme un ensemble non ordonné. Voici un exemple.
SELECT
colonne1,
colonne2,
colonne3,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS running_total_weight
FROM
votre_table ;
#3. Clause PARTITION BY
Cette clause, facultative, divise le résultat en partitions auxquelles la fonction window est appliquée. Sa puissance peut être réalisée en effectuant des calculs indépendamment avec chaque partition. En voici une illustration.
SELECT
colonne1,
colonne2,
colonne3,
AVG(column3) OVER (PARTITION BY column1) AS avg_weight_per_group
FROM
votre_table ;
Dans l’exemple ci-dessus, la fonction AVG calcule la moyenne de la colonne3 indépendamment pour chaque valeur distincte de la colonne1. En d’autres termes, pour chaque groupe d’éléments dans la colonne1, vous aurez la colonne3 avec le poids moyen pour cet élément spécifique.
La syntaxe générale d’une fonction SQL de fenêtre est la suivante :
SELECT
colonne1,
colonne2,
colonne3,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS window_function_result
FROM
votre_table ;
La syntaxe ci-dessus peut varier légèrement en fonction du choix de la base de données, mais la structure générale reste cohérente.
Fonctions de fenêtre agrégées en SQL
Les fonctions de fenêtre d’agrégat en SQL réutilisent les fonctions d’agrégat existantes, telles que COUNT() ou SUM(), en modifiant la façon dont l’agrégat est défini et le format des résultats. Cela signifie qu’elles effectuent des calculs sur une fenêtre liée à la ligne courante dans l’ensemble de résultats. Vous pouvez les utiliser pour obtenir des valeurs agrégées basées sur une fenêtre ou un cadre spécifié. Voici une brève description de chacun d’entre eux.
#1. MIN()
Cette fonction renvoie la valeur minimale d’une expression spécifiée sur un cadre.
MIN(colonne) SUR (PARTITION BY partition_expression ORDER BY sort_expression
ROWS ENTRE le début et la fin)
Examinons un exemple de requête, en particulier le minimum mobile, glissant ou roulant. Dans cet exemple, la valeur minimale est calculée pour chaque ligne de l’ensemble de résultats pour une plage spécifique de lignes autour de cette ligne.
SELECT
colonne1,
colonne2,
MIN(column2) OVER (ORDER BY date_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_min
FROM
votre_table ;
Ainsi, column1 et column2 sont les colonnes que vous sélectionnez, MIN(column2) est la fonction agrégée qui calcule la valeur minimale et OVER(ORDER BY some_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) définit la fenêtre de calcul. Elle spécifie la fenêtre, qui comprend la ligne actuelle et les deux lignes qui la précèdent, sur la base du spécificateur d’ordre some_column. Cela signifie que chaque ligne de l’ensemble de résultats moving_min contient la valeur minimale de la colonne2. Notez que je n’ai pas inclus la clause PARTITION BY.
#2. MAX()
Notre fonction renvoie la valeur maximale d’une expression spécifiée sur une fenêtre. Voici la syntaxe.
MAX(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression
ROWS ENTRE le début et la fin)Voici un exemple de requête qui calcule le maximum courant d’une colonne sur l’ensemble des résultats, classés par la colonne date.
SELECT
colonne,
MAX(colonne) SUR (ORDER BY date_colonne ROWS UNBOUNDED PRECEDING) AS running_max
FROM
votre_table ;#3. AVG()
Cette fonction renvoie la valeur moyenne d’une expression spécifiée sur une trame. Vérifiez la syntaxe.
AVG(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression
ROWS ENTRE le début et la fin)Pour un exemple de requête, considérez l’exemple ci-dessous où la requête calcule la moyenne sur une fenêtre qui inclut la ligne actuelle, celle qui la précède et celle qui la suit, ordonnées par la colonne date_colonne.
SELECT
colonne,
AVG(column) OVER (ORDER BY date_column ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM
votre_table ;
#4. SUM()
Cette fonction est utilisée lorsque vous souhaitez renvoyer la somme d’une expression spécifiée sur un cadre. Vous trouverez ci-dessous la syntaxe.
SUM(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression
ROWS ENTRE le début et la fin)Pour un exemple de requête, considérez la somme cumulative ci-dessous. Dans ce cas, la requête calcule la somme d’une colonne sur l’ensemble des résultats, classés par la colonne date.
SELECT
colonne,
SUM(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS cumulative_sum
FROM
votre_table ;#5. COUNT()
Fonction utilisée pour renvoyer le nombre de lignes dans une fenêtre. Sa syntaxe peut être écrite comme suit
COUNT(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression
ROWS ENTRE le début et la fin)Pour un exemple de cas d’utilisation, considérez le cas ci-dessous où la requête calcule le nombre de lignes sur l’ensemble du jeu de résultats, ordonné par la colonne date.
SELECT
colonne,
COUNT(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS running_count
FROM
votre_tableVoici un bref résumé des fonctions de la fenêtre d’agrégation.
| Fonction | Objectif | Exemple d’utilisation |
|---|---|---|
| MIN() | Renvoie la valeur minimale d’une expression spécifiée sur un cadre. | MIN(column) OVER (ORDER BY date_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) – Calcule le minimum mobile pour chaque ligne. |
| MAX() | Renvoie la valeur maximale d’une expression spécifiée sur une fenêtre. | MAX(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) – Calcule le maximum mobile sur l’ensemble des résultats |
| AVG() | AVG() Renvoie la valeur moyenne d’une expression spécifiée sur une trame. | AVG(column) OVER (ORDER BY date_column ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) – Calcule la moyenne mobile sur une fenêtre. |
| SUM() | Renvoie la somme d’une expression spécifiée sur une fenêtre. | SUM(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) – Calcule la somme cumulative sur l’ensemble des résultats. |
| COUNT() | Renvoie le nombre de lignes dans une fenêtre ou le nombre total de lignes. | COUNT(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) – Calcule le nombre de lignes sur l’ensemble des résultats. |
Fonctions de fenêtre de valeur en SQL
En SQL, les fonctions de fenêtre de valeur sont utilisées pour allouer des valeurs de ligne à partir d’autres lignes. Contrairement aux fonctions d’agrégation, qui renvoient une valeur unique pour chaque groupe, elles renvoient une valeur pour chaque ligne en fonction d’une fenêtre spécifique ou d’un cadre de lignes.
Ce modèle d’opération vous permet d’accéder aux données d’autres lignes par rapport à la fenêtre actuelle, ce qui facilite l’analyse et la création de rapports.
Voici un bref aperçu de chacune des fonctions de fenêtre de valeur.
#1. LEAD()
La fonction de fenêtre SQL LEAD() fonctionne à l’opposé de LAG() et est utilisée pour renvoyer les valeurs des lignes suivantes. Pour simplifier, elle décale les valeurs d’une ligne vers le haut. La syntaxe pour l’appeler est similaire à celle de LAG(). Voici comment l’écrire.
LEAD(column, offset, default_value) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Pour votre exemple de requête, voyez le cas ci-dessous, qui récupère la prochaine valeur d’une colonne basée sur le critère d’ordre, `date_column`, avec une valeur par défaut de 0 s’il n’y a pas de prochaine valeur.
SELECT
colonne,
LEAD(column, 1, 0) OVER (ORDER BY date_column) AS lead_value
FROM
votre_table ;#2. LAG()
La fonction LAG() est la plus populaire. Elle attribue à chaque ligne la valeur de la ligne précédente. En d’autres termes, elle décale n’importe quelle colonne d’une ligne, ce qui vous permet d’effectuer des requêtes en utilisant des valeurs décalées. Dans de tels cas, la fonction window inclura la clause ORDER BY dans la clause OVER puisque l’aspect de l’ordre doit être pris en compte. La syntaxe est la suivante :
LAG(colonne, décalage, valeur_par défaut) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Un exemple de requête peut être écrit comme indiqué ci-dessous. La requête extrait la valeur précédente d’une colonne ordonnée par la colonne date. S’il n’y a pas de valeur précédente, la valeur par défaut est 0.
SELECT
colonne,
LAG(colonne, 1, 0) OVER (ORDER BY date_colonne) AS lag_value
FROM
votre_table ;
#3. FIRST_VALUE()
Vous pouvez utiliser cette fonction pour récupérer la valeur d’une expression spécifiée pour la première ligne d’une fenêtre. La syntaxe est la suivante
FIRST_VALUE(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Exemple :
SELECT
colonne,
PREMIÈRE_VALEUR(colonne) SUR (PARTITION PAR colonne_catégorie ORDER PAR colonne_date) AS première_valeur
FROM
votre_table ;
La requête récupère la dernière valeur d’une colonne dans chaque partition, classée par date_colonne.
#4. LAST_VALUE()
Cette fonction vous permet de récupérer la dernière valeur d’une donnée dans un cadre défini. La syntaxe :
LAST_VALUE(column) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Exemple :
SELECT
colonne,
LAST_VALUE(column) OVER (PARTITION BY category_column ORDER BY date_column) AS last_value
FROM
votre_table ;
A présent, cela devrait être un peu évident…
#5. N_TH VALUE()
En tant que dernier membre du groupe, cette fonction permet de récupérer la valeur à une position spécifiée dans le cadre d’une fenêtre. Vous devez également spécifier l’expression que vous souhaitez utiliser pour récupérer la nième valeur. Cette expression peut être une colonne, une expression mathématique ou toute autre expression SQL valide représentant les données qui vous intéressent. Pour nos exemples, nous avons utilisé une colonne. Il en va de même pour LAST_VALUE() et FIRST_VALUE().
La syntaxe :
NTH_VALUE(column, n) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Exemple :
SELECT
colonne,
NTH_VALUE(column, 3) OVER (PARTITION BY category_column ORDER BY date_column) AS third_value
FROM
votre_table ;
| Fonction | Objectif | Utilisation Exemple |
|---|---|---|
| LEAD() | Renvoie les valeurs des lignes suivantes dans une fenêtre. | LEAD(column, 1, 0) OVER (ORDER BY date_column) AS lead_value – Récupère la valeur suivante en fonction du critère d’ordre, avec une valeur par défaut s’il n’y a pas de valeur suivante. |
| LAG() | Renvoie les valeurs des lignes précédentes dans une fenêtre. | LEAD(column, 1, 0) OVER (ORDER BY date_column) AS lead_value – Récupère la valeur suivante en fonction du critère d’ordre, avec une valeur par défaut s’il n’y a pas de valeur suivante. |
| FIRST_VALUE() | Récupère la valeur d’une expression spécifiée pour la première ligne d’une fenêtre. | FIRST_VALUE(column) OVER (PARTITION BY category_column ORDER BY date_column) AS first_value – Obtient la première valeur dans chaque catégorie en fonction de l’ordre des dates. |
| LAST_VALUE() | Récupère la dernière valeur d’une expression spécifiée dans un cadre défini. | LAST_VALUE(column) OVER (PARTITION BY category_column ORDER BY date_column) AS last_value – Récupère la dernière valeur de chaque catégorie en fonction de l’ordre des dates. |
| NTH_VALUE() | Récupère la valeur à une position spécifiée dans le cadre d’une fenêtre. | NTH_VALUE(column, 3) OVER (PARTITION BY category_column ORDER BY date_column) AS third_value – Récupère la troisième valeur de chaque catégorie en fonction de l’ordre des dates. |
Fonction de fenêtre de classement en SQL
Les fonctions de fenêtre de classement en SQL sont utiles pour attribuer des nombres (rangs ou positions) aux lignes d’un ensemble de résultats en fonction d’un critère d’ordre spécifique. Elles sont utiles pour analyser et ordonner les données et identifier les positions relatives des lignes.
Si les fonctions de fenêtre ne sont pas disponibles, vous devrez écrire plusieurs requêtes imbriquées, ce qui est inefficace. Remarque concernant le classement des fonctions de fenêtre : la clause ORDER BY doit toujours être présente. Vous trouverez ci-dessous de brèves explications sur le classement des fonctions dans la famille.
#1. RANK()
La fonction RANK() est la plus courante et attribue des valeurs de classement basées sur l’ordre que vous avez spécifié. Sa syntaxe..
RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Pour mettre cela en perspective, regardez la requête ci-dessous. Elle attribue un rang à chaque employé en fonction de ses ventes ; les ventes les plus élevées obtiennent le rang le plus bas.
SELECT
id_employé,
ventes,
RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM
table_des_ventes ;#2. DENSE RANK()
Bien que son fonctionnement soit similaire à celui de `RANK()`, il possède une caractéristique distinctive essentielle : s’il y a des égalités dans l’ensemble de données, elles obtiennent la même valeur de classement. Il ne saute aucun nombre, assignant les valeurs suivantes à la ligne suivante. Écrivez-le sous la forme suivante..
DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Pour votre cas de test, considérez la requête ci-dessous. Elle attribue un rang dense à chaque étudiant en fonction de sa note de test, sans laisser d’écart, même en cas d’égalité des notes.
SELECT
l'identifiant de l'étudiant,
score_test,
DENSE_RANK() OVER (ORDER BY test_score DESC) AS score_rank
FROM
table_score#3. ROW NUMBERS()
C’est le plus simple de tous. En suivant l’ordre défini par la clause ORDER BY dans la sous-section OVER, on donne des numéros à toutes les lignes, en commençant par 1. La syntaxe est la suivante..
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Dans l’exemple de requête ci-dessous, nous attribuons des numéros de ligne uniques à chaque produit en fonction des ventes, en commençant par le chiffre d’affaires le plus élevé (1).
SELECT
produit_id,
catégorie,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num
FROM
table_produits ;#4. PERCENT_RANK()
Cette fonction utilise la fonction RANK pour définir le classement final. Elle vise à définir un classement relatif pour les lignes de l’ensemble de résultats, en l’exprimant sous la forme d’un pourcentage, de sorte que le résultat soit compris entre 0 et 1. La valeur 0 est attribuée à la première ligne et la valeur 1 à la dernière. La syntaxe est la suivante..
PERCENT_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Voici une requête dans laquelle nous attribuons un rang à chaque employé en fonction de ses performances de vente.
SELECT
identifiant_client,
revenu,
PERCENT_RANK() OVER (ORDER BY revenue DESC) AS revenue_percent_rank
FROM
table_de_recettes ;#5. QCUT (NTILE)
Bien que rarement utilisé, il fonctionne comme ROW_NUMBER. Cependant, vous donnez des numéros à une collection de lignes (buckets) au lieu de numéroter les lignes. Le nombre de godets est transmis en tant qu’argument à la fonction N-TILE. Par exemple, N-TILE(10) divise l’ensemble de données en 10 groupes. La syntaxe est la suivante..
NTILE(nombre_de_seaux) OVER (PARTITION BY partition_expression ORDER BY sort_expression)L’exemple ci-dessous divise les employés en quartiles, comme décrit dans la fonction.
SELECT
employee_id,
salaire,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM
table_salaire_employé ;#6. CUME_DIST
Le dernier membre calcule la distribution cumulative d’une valeur dans un ensemble de données triées. Il attribue une valeur entre 0 et 1 pour représenter les positions relatives des lignes. 0 au début et 1 à la fin. Syntaxe : CUME_DIST()
CUME_DIST() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Dans l’exemple ci-dessous, la requête évalue la répartition des clients en fonction de leur âge, avec un pourcentage de clients inférieur ou égal à la ligne actuelle.
SELECT
customer_id,
age,
CUME_DIST() OVER (ORDER BY age) AS age_cume_dist
FROM
données_clients ;Une fiche de référence rapide pour les fonctions de la fenêtre de classement est disponible ci-dessous.
| Fonction | Objectif | Exemples d’utilisation |
|---|---|---|
| RANK() | Attribue des valeurs de classement en fonction de l’ordre spécifié. | RANK() OVER (ORDER BY sales DESC) AS sales_rank – Attribue un rang à chaque employé en fonction de ses ventes. |
| DENSE_RANK() | Similaire à RANK(), mais traite les égalités sans écarts. | DENSE_RANK() OVER (ORDER BY test_score DESC) AS score_rank – Attribue un rang dense aux étudiants en fonction de leurs résultats aux tests. |
| ROW_NUMBER() | Attribue des numéros de ligne uniques à la suite de la clause ORDER BY. | ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num – Attribue des numéros de ligne aux produits en fonction des ventes dans chaque catégorie. |
| PERCENT_RANK() | Définit le classement relatif sous forme de pourcentage (0 à 1). | PERCENT_RANK() OVER (ORDER BY revenue DESC) AS revenue_percent_rank – Attribue un rang en pourcentage aux clients en fonction de leur chiffre d’affaires. |
| NTILE(nombre_de_seaux) | Divise les lignes en groupes spécifiés | NTILE(4) OVER (ORDER BY salary) AS salary_quartile – Divise les employés en quartiles en fonction de leur salaire. |
| CUME_DIST() | Calcule la distribution cumulative (0 à 1) des valeurs. | CUME_DIST() OVER (ORDER BY age) AS age_cume_dist – Évalue la distribution cumulative des clients en fonction de l’âge, représentée sous forme de pourcentage. |
Conclusion
Dans cet article, nous avons présenté les fonctions SQL Window, une technique fine pour parcourir vos requêtes SQL, simplifiant ainsi vos opérations sur les données. Nous avons examiné les modèles disponibles pour les fonctions Window, présenté leur syntaxe, prévisualisé leurs exemples, expliqué leur utilisation et conclu par un exemple d’antisèche que vous pouvez adopter dans votre flux de données.
Bien entendu, la syntaxe peut varier en fonction de votre base de données. Veillez à rechercher la syntaxe correcte pour votre choix. Pour aller plus loin, consultez toujours la documentation. Cela signifie que vous devez travailler avec la bonne documentation, qu’il s’agisse de SQL ou de PostgreSQL.
Vous pouvez maintenant consulter notre antisèche SQL complète que mon collègue et moi-même utilisons tout au long de nos activités quotidiennes de développeur.