Fonctions de fenêtre SQL : Tout ce que vous devez savoir sur leur utilisation

La polyvalence de SQL en tant que langage d’interrogation de SGBD s’est accrue au fil des ans. Son utilité et sa polyvalence étendues en font le favori de tous les analystes de données.

Il existe un certain nombre de fonctions de niveau avancé en plus des fonctions normales de SQL. Ces fonctions sont communément appelées fonctions de fenêtre. Si vous traitez des données complexes et souhaitez effectuer des calculs avancés, vous pouvez les utiliser pour tirer le meilleur parti de vos données.

L’importance des fonctions de fenêtre

Plusieurs fonctions de fenêtre sont disponibles dans SQL, et chacune vous aidera à effectuer une série de calculs. Qu’il s’agisse de créer des partitions, de classer des lignes ou d’attribuer des numéros de ligne, ces fonctions de fenêtre font un peu de tout.

Les fonctions de fenêtre sont utiles lorsque vous appliquez des fonctions d’agrégation à un ensemble de données spécifique ou à une collection de lignes. Ces fonctions vont bien au-delà des fonctions d’agrégation fournies par GROUP BY. Toutefois, la principale différence est que, contrairement à la fonction de regroupement, vos données ne sont pas combinées en une seule ligne.

Vous ne pouvez pas utiliser les fonctions de fenêtre dans la fenêtre WHERE, DE, et GROUPE PAR déclarations.

Syntaxe d’une fonction de fenêtre

Lorsque vous faites référence à une fonction de fenêtre, vous devez respecter la structure syntaxique par défaut, afin qu’elle fonctionne correctement. Si vous structurez la commande de manière incorrecte, vous obtiendrez une erreur et votre code ne s’exécutera pas.

Voici la syntaxe par défaut :

 SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name; 

Où :

• coulmnname1 est le premier nom de colonne que vous souhaitez sélectionner.
• {fonction_fenêtre} est le nom d’une fonction d’agrégation comme sum, avg, count, row_number, rank, ou dense_rank.
• nom de la colonne2 est le nom de la colonne sur laquelle vous appliquez la fonction fenêtre.
• nom de la colonne3 est le nom de la troisième colonne, qui servira de base à la partition.
• nouvelle_colonne est une étiquette pour la nouvelle colonne que vous pouvez appliquer en utilisant la fonction AS mot-clé.
• nom_table est le nom de la table source.

Les fonctions de fenêtre sont différentes de certaines des commandes SQL les plus basiques. Contrairement aux fonctions d’agrégation de SQL, vous pouvez utiliser ces fonctions de fenêtre pour exécuter des fonctions avancées.

Préparation de l’ensemble de données

Vous pouvez utiliser la fonction CREATE TABLE pour créer une nouvelle table en SQL. Voici un exemple d’ensemble de données que ce guide utilisera pour définir certaines fonctions de fenêtre :

La fonction Somme expliquée

Supposons que vous souhaitiez calculer le total des ventes pour chaque valeur de la colonne catégorie. Voici comment vous pouvez le faire :

 SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample; 

Dans le code ci-dessus, l’instruction SQL extrait la catégorie et la couleur de l’ensemble de données d’origine. La fonction sum additionne la colonne sale_price. Elle le fait par catégorie, puisque la clause OVER spécifie le classement par la colonne catégorie. Le résultat final est le suivant :

Comment utiliser la fonction fenêtre Avg()

Tout comme la fonction somme, vous pouvez calculer la moyenne par ligne de données avec la fonction avg fonction. Au lieu de la somme, vous obtiendrez une colonne avec les ventes moyennes.

 SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample; 

Apprenez à utiliser la fonction Count() de la fenêtre

Tout comme les fonctions sum et avg, la fonction de fenêtre count en SQL est assez simple et fonctionne de la même manière que les deux autres fonctions. Lorsque vous passez la fonction count, vous obtenez le nombre total de chaque valeur dans la nouvelle colonne.

Voici comment vous pouvez calculer le compte :

 SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample; 

La fonction fenêtre Row_Number()

Contrairement à certaines des autres fonctions de fenêtre listées ci-dessus, la fonction row_number() fonctionne de manière légèrement différente. La fonction row_number() attribue un numéro de ligne à chaque ligne, en fonction de la clause order by. Le numéro de ligne de départ est 1 ; la fonction row_number attribue une valeur correspondante à chaque ligne jusqu’à la fin.

Voici la structure de base d’une fonction row_number() :

 SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample; 

Mais que se passe-t-il si vous voulez attribuer des numéros de ligne distincts à chaque élément de la catégorie ? La syntaxe ci-dessus définit un numéro de série continu, indépendamment des éléments stockés dans la catégorie. Par exemple, la catégorie des appareils électroménagers devrait avoir sa numérotation exclusive, suivie des classeurs, et ainsi de suite.

Vous pouvez utiliser l’option partition pour effectuer cette tâche simple, mais pratique. Le mot-clé partition attribue des numéros de ligne désignés à chaque élément de catégorie.

 SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample; 

Les fonctions Rank() et Dense_Rank()

Le site rang() fonctionne différemment de la fonction row_number() fonction. Vous devez spécifier le nom de la colonne dans la fonction order by, afin de l’utiliser comme base pour définir les valeurs de rang. Par exemple, dans l’exemple de code suivant, vous pouvez utiliser la colonne couleur dans la fonction order by. La requête utilisera ensuite cet ordre pour attribuer une valeur de classement à chaque ligne.

Vous pouvez utiliser la syntaxe du code ci-dessous pour passer une fonction de rang en SQL :

 SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample; 

Jetez un coup d’oeil à la sortie pour comprendre comment cette fonction fonctionne.

La fonction order by trie la catégorie de couleur, tandis que la fonction rank attribue un rang à chaque couleur. Cependant, toutes les valeurs d’une même couleur ont le même rang, tandis que les couleurs différentes ont des rangs distincts. La couleur noire apparaît trois fois dans l’ensemble de données ; au lieu d’attribuer une valeur de rang de 1, 2 et 3, les éléments de couleur noire obtiennent un rang de 1.

Cependant, la couleur suivante, Brown, obtient un rang 4 au lieu d’un rang 2. La fonction de rang saute les valeurs et attribue la valeur chronologique suivante aux différentes entrées. Si vous souhaitez attribuer une valeur de rang plus significative, vous pouvez utiliser la fonction dense_rank() fonction.

La fonction dense_rank ne saute aucune valeur de rang pendant la fonction order by. Par exemple, les trois premiers éléments de couleur (Noir) auront le rang 1. Cependant, la couleur suivante (Marron) n’aura pas un rang 4, mais un rang 2, qui est le numéro chronologique suivant dans la liste de numérotation. La fonction dense_rank est une fonction fenêtre plus pratique car elle attribue une valeur significative à la liste d’éléments.

Voici comment utiliser la fonction dense_rank en SQL :

 SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample; 

Et voici un exemple de ce à quoi ressemblera la sortie de cette fonction :

Les fonctions SQL à la rescousse

Les fonctions de fenêtre de SQL sont idéales pour effectuer des opérations analytiques avancées. Cependant, vous pouvez utiliser de nombreuses autres commandes SQL pour vous assurer que vos compétences en matière de calcul sont au top. Pour combiner et calculer plusieurs résultats en une seule fois, rien de mieux que d’utiliser les sous-requêtes de SQL.

Les sous-requêtes sont un excellent outil pour exécuter des fonctions avancées et améliorer la qualité de vos résultats. Selon le besoin du moment, vous pouvez personnaliser vos requêtes et les rendre plus efficaces pour répondre à vos exigences.