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 a augmenté au fil des ans. Son utilité et sa polyvalence étendues en font un favori de tous les temps pour chaque analyste de données.

Il existe de nombreuses fonctions de niveau avancé en plus des fonctions habituelles 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. De la création de partitions au classement des lignes ou à l’attribution de 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 sur un jeu de données spécifique ou une collection de lignes. Ces fonctions vont au-delà des fonctions d’agrégation fournies par GROUP BY. Cependant, 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 les instructions WHERE , FROM et GROUP BY .

Syntaxe d’une fonction de fenêtre

Lorsque vous vous référez à une fonction de fenêtre, vous devez suivre la structure de syntaxe 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 pourra pas s’exécuter.

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.
• {window_function} est le nom d’une fonction d’agrégation telle que sum, avg, count, row_number, rank ou dense_rank.
• nomcolonne2 est le nom de la colonne sur laquelle vous appliquez la fonction de fenêtre.
• nomcolonne3 est le troisième nom de colonne, qui formera la base de la partition.
• new_column est une étiquette pour la nouvelle colonne que vous pouvez appliquer à l’aide du mot-clé AS .
• nom_table est le nom de la table source.

Les fonctions de fenêtre sont différentes de certaines des commandes SQL les plus élémentaires. Contrairement aux fonctions d’agrégation dans 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 l’ instruction CREATE TABLE pour créer une nouvelle table dans SQL. Voici un exemple d’ensemble de données que ce guide utilisera pour définir certaines fonctions de fenêtre :

La fonction de somme expliquée

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

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. Il le fait par catégorie, puisque la clause OVER spécifie le classement par la colonne de catégorie. Le résultat final est le suivant :

Comment utiliser la fonction de fenêtre Avg()

Comme la fonction sum, vous pouvez calculer la moyenne par ligne de données avec la fonction avg . 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 de fenêtre Count()

Semblable aux fonctions sum et avg, la fonction de fenêtre de comptage 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 nombre :

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

La fonction de fenêtre Row_Number()

Contrairement à certaines des autres fonctions de fenêtre répertoriées ci-dessus, le row_number() fonctionne légèrement différemment. 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 ; le 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 souhaitez attribuer des numéros de ligne distincts à chaque élément de catégorie ? La syntaxe ci-dessus définit un numéro de série évolutif, quels que soient les éléments stockés dans la catégorie. Par exemple, la catégorie des appareils doit avoir sa numérotation exclusive, suivie de classeurs, et ainsi de suite.

Vous pouvez utiliser la fonction de partition pour effectuer cette tâche simple mais pratique. Le mot-clé de partition attribue des numéros de ligne désignés sur la base de 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()

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

Vous pouvez utiliser la syntaxe de code ci-dessous pour transmettre une fonction de classement en SQL :

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

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

La fonction d’ordre par trie la catégorie de couleur, tandis que la fonction de classement attribue un rang à chaque couleur. Cependant, toutes les mêmes valeurs de couleur ont le même rang, tandis que les différentes couleurs 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 du rang 2. La fonction de rang ignore les valeurs et attribue la valeur chronologique suivante aux différentes entrées. Si vous souhaitez attribuer une valeur de classement plus significative, vous pouvez utiliser la fonction dense_rank() .

La fonction dense_rank n’ignore aucune valeur de rang lors de l’ordre par fonction. 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 de fenêtre plus pratique car elle attribue une valeur significative à la liste des é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 calcul sont de premier ordre. Lorsque vous combinez et calculez plusieurs résultats en une seule fois, il n’y a rien de mieux que d’utiliser les sous-requêtes SQL.

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