Guide des fonctions SQL pour fenêtres

Tous les analystes de données ont intérêt à connaître les fonctions de fenêtre SQL. Elles améliorent votre jeu d'analyse de données et vous ouvrent les portes d'un nouveau niveau d'analyse de données. Cet article est votre point de départ dans le monde des fonctions de fenêtre SQL.

Les fonctions SQL window vous permettent d'afficher toutes les lignes de données et leurs valeurs agrégées en même temps. Cela ressemble un peu à GROUP BY sous stéroïdes, n'est-ce pas ? Mais ce n'est pas tout. Comme les fonctions de fenêtre effectuent divers calculs sur l'ensemble des lignes, elles permettent également de diviser les ensembles de données en sous-ensembles. Elles vous aident à classer facilement les données, à les agréger, à calculer les différences entre différentes périodes (par exemple, augmentation/diminution des bénéfices entre deux mois/trimestres/années) et à trouver des sommes cumulées, des totaux courants, des moyennes mobiles, etc.

Les fonctions de fenêtre sont un sujet complexe, mais leur apprentissage est beaucoup plus facile avec notre cours. Fonctions de fenêtrage cours. Il s'agit d'un cours interactif avec 218 exercices qui fournissent un apprentissage systématique avec beaucoup de codage. Vous apprendrez tout sur les clauses essentielles des fonctions de fenêtre - par exemple OVER(), ORDER BY, et PARTITION BY - et ce qu'est un cadre de fenêtre. Ensuite, vous apprendrez à utiliser tout cela dans le contexte de différentes fonctions de fenêtre.

Après l'apprentissage, il est temps de passer à la pratique (qui est aussi un apprentissage). Notre kit de pratique Fonctions de fenêtrage vous propose 100 exercices interactifs supplémentaires pour vous permettre de mettre en pratique vos connaissances.

La syntaxe de SQL Fonctions de fenêtrage

Les fonctions de fenêtre tirent leur nom d'un cadre de fenêtre, qui est un ensemble de lignes liées à la ligne actuelle.

Pour effectuer une opération de fonction de fenêtre sur le cadre de fenêtre, vous devez connaître la syntaxe générale de la fonction de fenêtre :

SELECT column_1,
       column_2,
	<window_function> OVER(PARTITION BY … ORDER BY … <window_frame>) AS column_alias
FROM table;

Plusieurs parties essentielles de cette syntaxe doivent être expliquées :

• La clause OVER: Il s'agit de la clause obligatoire pour définir un cadre de fenêtre. Par exemple, vous reconnaîtrez la fonction de fenêtre SUM(order_value) OVER() as a SUM(). Sans OVER(), il s'agit simplement d'une fonction agrégée SUM() ordinaire.
• PARTITION BY: Il s'agit d'une clause facultative permettant de partitionner l'ensemble de données, c'est-à-dire de le diviser en sous-ensembles. Cela vous permet d'appliquer une fonction de fenêtre à chaque partition séparément. Si cette clause est omise, l'ensemble des résultats constitue une seule partition.
• ORDER BY: Cette clause facultative (pour certaines fonctions de fenêtre) est utilisée pour spécifier l'ordre des lignes dans un cadre de fenêtre. Si vous omettez cette clause, l'ordre des lignes dans le cadre de la fenêtre sera arbitraire.
• <window_frame>: Cette clause définit les limites supérieure et inférieure d'un cadre de fenêtre. Deux clauses importantes sont utilisées à cet effet : ROWS et RANGE. ROWS définit le nombre de lignes qui précèdent et suivent la ligne actuelle. La clause RANGE définit l'étendue des lignes en fonction de leur valeur par rapport à la ligne actuelle. Pour en savoir plus, consultez notre article sur les différences entre ROWS et RANGE. Cette partie de la syntaxe est généralement omise, car le cadre de fenêtre par défaut est ce dont les utilisateurs ont le plus souvent besoin.

Le cadre de fenêtre par défaut, dans ce cas, dépend de l'utilisation ou non de la clause ORDER BY dans OVER(). Si c'est le cas, le cadre est constitué de la ligne actuelle et de toutes les lignes qui la précèdent dans la partition actuelle. Si vous ne spécifiez pas ORDER BY, le cadre de la fenêtre est la ligne actuelle et toutes les lignes qui la précèdent et la suivent dans la partition actuelle. Dans le second cas, le cadre de la fenêtre est en fait l'ensemble du jeu de données - ou la partition entière, si vous utilisez également PARTITION BY.

SQL courant Fonctions de fenêtrage

Il existe de nombreuses fonctions de fenêtre différentes. Voici un aperçu des plus courantes :

SQL Fonctions de fenêtrage Exemples

Maintenant que nous avons abordé les principes de base, il est temps de montrer plusieurs exemples pratiques de fonctions de fenêtre.

Dans tous les exemples, j'utiliserai la même table. Elle s'intitule exchange_rates et contient les taux de change d'avril 2024 de la Banque centrale européenne (BCE) pour trois paires de devises : EUR contre USD, EUR contre CHF et EUR contre JPY.

Voici un aperçu partiel des données.

Exemple de fonction de fenêtre de classement

Cet exemple vous montrera comment fonctionne DENSE_RANK(). Les deux autres fonctions de fenêtre de classement peuvent être utilisées de la même manière ; elles pourraient (en fonction des données) donner des résultats légèrement différents.

Le code ci-dessous classe les données du tableau du taux de change le plus élevé au taux de change le plus bas :

SELECT date,
	 currency_pair,
	 ecb_rate, 
	 DENSE_RANK() OVER (ORDER BY ecb_rate DESC) AS rank_ecb_rate
FROM exchange_rates;

Je sélectionne la date, la paire de devises et le taux. J'ai choisi DENSE_RANK() pour classer les données. C'est juste au cas où il y aurait les mêmes taux de change (très improbable, mais quand même...) ; je veux qu'ils soient classés de la même manière et je ne veux pas d'écarts dans le classement.

La fonction DENSE_RANK() est suivie de la clause OVER() qui définit la fonction de fenêtre. Entre les parenthèses de la clause, j'utilise une autre clause de fonction de fenêtre -ORDER BY. De cette façon, je demande à la fonction window DENSE_RANK() de classer les données en fonction du taux de la BCE par ordre décroissant.

Voici ce que j'obtiens en sortie :

Comme vous pouvez le voir, chaque ligne a été classée en fonction de la valeur de son taux. Si les taux sont identiques, DENSE_RANK() attribuera le même rang et ne sautera pas le rang. RANK() fera de même, mais sautera la séquence de rangs. ROW_NUMBER() attribuera un rang consécutif, même si certaines lignes ont le même taux de change.

Exemple de la fonction Aggregate Window

Voici un bel exemple de la façon dont vous pouvez utiliser la fonction de fenêtre AVG() pour calculer le taux moyen pour chaque paire de devises :

SELECT date,
	 currency_pair,
	 ecb_rate,
	 AVG(ecb_rate) OVER (PARTITION BY currency_pair) AS avg_rate_by_currency
FROM exchange_rates
ORDER BY currency_pair, date;

Comme dans la fonction agrégée classique AVG(), vous devez écrire la colonne dont vous voulez obtenir la moyenne. Pour en faire une fonction fenêtre, utilisez la clause OVER(). Cette fois, j'utilise PARTITION BY dans la colonne currency_pair. Ce faisant, je divise les données en sous-ensembles en fonction de la paire de devises.

En d'autres termes, je calcule le taux moyen pour chaque paire de devises séparément.

Jetez un coup d'œil à la sortie du code. Les données ont été triées par paire de devises et par date :

Le taux moyen EUR vs. CHF est de 0,9793, et cette valeur est répétée pour chaque ligne EUR vs. CHF. Lorsque la fonction de fenêtre atteint la paire de devises suivante, la moyenne est réinitialisée et calculée à nouveau ; pour EUR vs. JPY, elle est de 164,1211. Enfin, la moyenne pour EUR vs. USD est de 1,0795.

Cette fonction de fenêtre m'a permis de calculer les moyennes séparément et d' afficher les valeurs moyennes sans réduire les lignes individuelles. En d'autres termes, je peux voir chaque taux quotidien à côté de la moyenne pour cette paire de devises.

Exemples de fonctions de fenêtre analytique

Dans cette section, je vais montrer trois exemples de différentes fonctions analytiques de fenêtre SQL.

LAG()

L'exemple LAG() montre comment calculer la variation journalière. Cette fonction est utilisée pour accéder à la valeur des lignes précédentes. Une autre fonction de fenêtre analytique est LEAD(), qui fait exactement le contraire - elle récupère les données des lignes suivantes. Les deux fonctions ont fondamentalement la même syntaxe ; il suffit de changer le nom de la fonction.

Dans cet exemple, je souhaite calculer la variation quotidienne des taux de change :

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - LAG(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS daily_rate_change
FROM exchange_rates;

LAG() est une fonction permettant d'accéder à la (aux) valeur(s) de la (des) ligne(s) précédant la ligne actuelle. Dans l'exemple ci-dessus, je spécifie la colonne ecb_rate dans LAG(). Cela signifie que je veux accéder à la valeur du taux de la BCE. Je ne spécifie pas explicitement le nombre de lignes sur lesquelles je souhaite revenir, ce qui signifie que la valeur par défaut est d'une ligne.

OVER() vient après le nom de la fonction. Ici, je partitionne l'ensemble de données par paire de devises car je veux calculer la variation quotidienne du taux pour chaque paire de devises séparément.

J'utilise également ORDER BY pour trier les données à l'intérieur des partitions. Étant donné que la logique consiste à remonter d'un jour à l'autre, les données doivent être classées par ordre croissant de date.

Ainsi, la partie LAG() du code représente le taux de change du jour précédent. Pour obtenir la différence journalière, il suffit de soustraire cette valeur du taux de change actuel (ecb_rate - LAG(ecb_rate)).

Voici le résultat :

La première ligne est NULL parce qu'il n'y a pas de date antérieure, donc la différence ne peut pas être calculée. Dans la ligne suivante, la variation quotidienne du taux est de 0,9792-0,9765 = 0,0027. Le même principe consistant à prendre la valeur de la ligne précédente et à la soustraire de la valeur actuelle est appliqué à chaque ligne.

Comme l'ensemble de données est divisé par paire de devises, le calcul est réinitialisé lorsqu'il atteint d'autres paires de devises, c'est-à-dire EUR vs. JPY et EUR vs. USD.

FIRST_VALUE()

La fonction de fenêtre FIRST_VALUE() peut être utilisée sur nos données pour calculer les différences entre le taux de change actuel et le premier taux de change du mois.

Je peux le faire parce que FIRST_VALUE() renvoie la première valeur à l'intérieur de la partition. Son opposé est LAST_VALUE(), qui renvoie la dernière valeur de la partition. Les deux fonctions ont pratiquement la même syntaxe ; seul le nom de la fonction est différent (et le résultat, bien sûr !).

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - FIRST_VALUE(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS difference_current_first
	  FROM exchange_rates
ORDER BY currency_pair, date;

Je veux la valeur du taux ; c'est pourquoi il y a la colonne ecb_rate dans FIRST_VALUE(). Les données sont à nouveau divisées par paire de devises, car je veux un calcul pour chaque paire.

OK, donc FIRST_VALUE() est utilisé pour récupérer la valeur de la première ligne de la partition. Mais que se passe-t-il si j'ordonne les données de la partition par ordre croissant de date ? C'est exact ; la première ligne est celle qui contient le taux du premier jour du mois. Dans notre cas, il s'agit du premier taux de change du mois d'avril.

Il faut maintenant le soustraire du taux de change actuel. Comme nous savons que nos données ne concernent que le mois d'avril, nous obtenons la différence entre le taux actuel et le premier taux pour ce mois.

Lorsque la différence est de 0, le taux actuel et le taux le plus ancien sont identiques. Pour EUR vs. CHF, le premier taux est de 0,9765. Vérifions les deux premières lignes : 0 .97 65 - 0.9765 = 0.0000; 0.9792 - 0.9765 = 0.0027.

Le même principe est appliqué aux deux autres paires de devises.

NTILE()

Le dernier exemple que je montrerai est la fonction NTILE(), qui divise la fenêtre (ou partition) en groupes. L'argument entre parenthèses de la fonction NTILE() spécifie le nombre de groupes dans lesquels vous souhaitez que votre ensemble de données soit divisé.

La division se fait chronologiquement en classant les données par ordre croissant de date :

SELECT date,
	 currency_pair,
	 ecb_rate,
	 NTILE(3) OVER (ORDER BY date) AS group_number
FROM exchange_rates;

Voici le résultat :

Les données sont réparties en trois groupes. Étant donné qu'il y a 27 lignes de données au total, elles pourraient être divisées en groupes égaux de neuf lignes.

Fonctions de fenêtrage vs. GROUP BY et fonctions d'agrégation

Les fonctions de fenêtre d'agrégation, comme vous pouvez le supposer d'après leur nom, sont utilisées pour agréger des données. Mais qu'en est-il des fonctions d'agrégation "normales" et de GROUP BY? Elles sont également utilisées pour agréger des données. En quoi les fonctions de fenêtre sont-elles différentes ?

La principale différence est que les fonctions de fenêtre d'agrégat (et les fonctions de fenêtre en général) ne réduisent pas les lignes individuelles tout en affichant la valeur agrégée. En revanche, les fonctions GROUP BY et aggregate ne peuvent afficher que les valeurs agrégées ; elles réduisent les lignes individuelles.

En d'autres termes, les fonctions de fenêtre vous permettent d'afficher les données analytiques et agrégées en même temps.

SQL avancé Utilisation des fonctions de fenêtre

Les fonctions de fenêtre sont largement utilisées dans l'analyse des données, car elles permettent de répondre à de nombreuses exigences en matière de rapports d'activité.

Voici un aperçu de quelques-unes des utilisations les plus complexes des fonctions de fenêtre.

Classement

Oui, j'ai déjà parlé du classement et je vous ai montré un exemple de la façon de le faire. Il existe trois fonctions de fenêtre pour le classement des données : ROW_NUMBER(), RANK(), et DENSE_RANK(). Elles ne renvoient pas nécessairement le même résultat, car elles utilisent toutes des méthodes légèrement différentes pour classer les données. Celle que vous utiliserez dépendra de vos données et de ce que vous souhaitez obtenir.

Totaux courants et moyennes mobiles

Ces deux calculs sont généralement utilisés dans l'analyse des séries temporelles. Les séries temporelles sont des données montrant des valeurs à certains moments. L'analyse de ces données est, en fait, une analyse de séries temporelles. Son but est de dévoiler les tendances des données et de trouver les causes possibles des écarts significatifs par rapport à la tendance.

Le total courant (ou somme cumulative) est la somme de la ligne actuelle et de toutes les valeurs des lignes précédentes. Lorsque vous vous déplacez vers l'avenir, la taille du cadre temporel augmente d'une ligne/d'un point de données et la valeur est ajoutée au total courant de la ligne précédente.

Les moyennes mobiles représentent la valeur moyenne des n dernières périodes. Lorsque vous vous déplacez vers l'avenir, le cadre temporel se déplace, mais sa taille reste la même. Cette méthode est très utilisée dans le secteur financier, par exemple la moyenne mobile à 5 jours dans l'analyse des cours boursiers. De cette manière, le prix moyen est continuellement mis à jour et l'impact des changements importants à court terme sur le prix d'une action est neutralisé.

Différence entre deux lignes ou périodes de temps

La fonction de fenêtre utilisée pour calculer la différence entre deux lignes est LAG(), qui vous permet d'accéder aux valeurs des lignes précédentes. La différence entre deux périodes de temps est fondamentalement la même chose ; elle se réfère uniquement à la recherche de différences lorsque l'on travaille avec des séries temporelles. Dans la section des exemples, je vous ai montré comment procéder.

Analyse des séries temporelles

Les fonctions de fenêtre fonctionnent à merveille lorsque vous devez analyser des séries temporelles. Il n'y a pas que la fonction LAG() pour le faire, mais bien d'autres.

Problèmes courants avec SQL Fonctions de fenêtrage

Il existe plusieurs problèmes auxquels toute personne utilisant des fonctions de fenêtre est confrontée tôt ou tard :

1. Confondre les fonctions window avec les fonctions aggregate et GROUP BY, dont nous avons déjà parlé.
2. Essayer d'utiliser les fonctions de fenêtre dans WHERE. Cela n'est pas possible car SQL traite les conditions WHERE avant les fonctions de fenêtre.
3. Essayer d'utiliser les fonctions de fenêtre dans GROUP BY, ce qui n'est pas non plus autorisé en raison de l'ordre des opérations de SQL : les fonctions de fenêtre sont exécutées après GROUP BY.

Ressources supplémentaires pour la pratique du langage SQL Fonctions de fenêtrage

Les fonctions SQL à fenêtre font partie des outils SQL les plus utiles dont disposent les analystes de données. C'est particulièrement vrai lorsque vous allez au-delà des rapports de base et que vous avez besoin de calculs sophistiqués et de la possibilité d'afficher simultanément des données analytiques et agrégées.

Tous les sujets que j'ai abordés dans cet article nécessitent une pratique plus approfondie sur des exemples pratiques, que vous trouverez dans les articles et cours suivants :

1. 11 exercices SQL Fonctions de fenêtrage avec solutions
2. Les 10 meilleures questions d'entretien pour SQL Fonctions de fenêtrage
3. Aide-mémoire SQL Fonctions de fenêtrage
4. Fonctions de fenêtrage Cours
5. Fonctions de fenêtrage Ensemble d'exercices

N'oubliez pas que c'est en forgeant qu'on devient forgeron ! Ne vous contentez donc pas de lire les articles ; assurez-vous également de vous exercer au codage. Bon apprentissage !