Pourquoi devrais-je apprendre les fonctions de fenêtre SQL ?

Le langage SQL existe depuis plus de 25 ans. Sa syntaxe de base - comme SELECT, WHERE, GROUP BY, HAVING et ORDER BY - est bien connue. Mais existe-t-il autre chose que le SQL traditionnel ? Pouvez-vous aller au-delà des bases et améliorer vos compétences ?

La réponse à cette question est oui. Il existe une version moderne de SQL. Dans cet article, nous allons nous plonger dans cette version moderne et découvrir les fonctions de fenêtre SQL. Si vous êtes un débutant avancé qui souhaite analyser des données à l'aide des fonctions de fenêtre SQL (également appelées fonctions analytiques), cet article est fait pour vous.

Un bref historique de SQL

Histoire du développement de SQL - normalisation

SQL est un langage célèbre mais très ancien. Il a été introduit dans les années 1970 par IBM. En 1986, les groupes de normalisation ANSI et ISO ont officiellement adopté une définition standard du "langage de base de données SQL" (SQL-1986).

En 1992, une révision majeure (SQL- 92) de la norme initiale a été effectuée. C'est le SQL standard que nous utilisons aujourd'hui. C'est vrai - cela fait 28 ans que SQL-92 a été officiellement adopté. Ses concepts de base (SELECT, WHERE, etc.) sont familiers, que vous utilisiez Oracle, DB2, MySQL, PostgreSQL ou une autre base de données. Dans chaque SGBD, vous écrirez des instructions SQL similaires en raison de cette même normalisation.

Les concepts de base de SQL sont anciens, mais ils sont très utiles. Je dirais même qu'ils sont universels, car ils fonctionnent quel que soit le secteur auquel vous les appliquez. Pour apprendre SQL, ou même pour évoluer en tant qu'analyste, vous allez devoir apprendre ces concepts.

Pourtant, le monde informatique a beaucoup changé depuis 1992. SQL a sûrement évolué depuis lors ? Oui, SQL a évolué et de nouvelles révisions des normes ont été effectuées. Les années 2000 ont vu le début du SQL moderne, un concept qui va au-delà des instructions de base. En 2003, les fonctions de fenêtre SQL ont été introduites. Parlons des avantages des fonctions de fenêtre SQL et des raisons pour lesquelles il est bon de les connaître.

Mais d'abord, passons en revue un élément que les débutants confondent souvent avec les fonctions de fenêtre : les fonctions d'agrégation SQL.

Rappel sur les fonctions d'agrégation

Si vous connaissez le langage SQL traditionnel, vous avez probablement utilisé les fonctions d'agrégation, qui vous permettent d'effectuer des calculs sur des ensembles de lignes et d'obtenir une seule ligne de sortie ou un seul résultat. Par exemple, vous avez peut-être calculé des totaux ou des moyennes sur un ensemble de lignes ou vous avez peut-être compté le nombre de lignes par catégorie. Dans ce cas, vous avez utilisé au moins certaines des fonctions d'agrégation de SQL : SUM(), AVG(), MIN(), MAX() et COUNT(). Elles sont souvent utilisées avec les clauses GROUP BY et HAVING dans les instructions SELECT.

Voyons un exemple : comment calculer le prix moyen par groupe de lignes en utilisant GROUP BY.

Nous allons utiliser des données Forex sur les taux de change des devises. Voici notre tableau d'entrée :

Taux de change des devises - tableau CURRENCYTRADE

Cette table contient les prix de clôture des échanges de devises GBP-USD et EUR-USD. Dans cet exemple, nous utilisons six cours de clôture pour chaque paire de devises. À partir de ces données, nous allons calculer le prix de clôture moyen pour chaque paire de devises (GBPUSD, EURUSD) séparément.

Voici l'instruction SELECT qui calcule le prix de clôture moyen pour chaque paire de devises :

select ticker,avg(close) as average_price from CURRENCYTRADE group by ticker;

L'image ci-dessous montre le résultat sur la droite :

Le résultat est présenté comme une sortie à une seule ligne pour chaque paire de devises. Les calculs (la valeur moyenne pour chaque paire) ont été effectués sur six lignes pour chaque paire. Il s'agit d'un exemple simple de fonction agrégée.

Maintenant, plongeons dans les fonctions de fenêtre.

Qu'est-ce qu'une fonction fenêtre SQL ?

Les fonctions de fenêtre SQL sont-elles similaires à la syntaxe GROUP BY des agrégats ? J'aime à le penser. Comme une fonction d'agrégation utilisée avec une clause GROUP BY, une fonction fenêtre effectue également des calculs sur un ensemble de lignes. Toutefois, le résultat d'une fonction fenêtre n'est pas présenté sous la forme d'une seule ligne de sortie pour chaque groupe, c'est-à-dire que les lignes ne sont pas réduites dans le tableau résultant. Au lieu de cela, chaque ligne de la table d'entrée est renvoyée.

La différence entre une fonction agrégée et une fonction fenêtre en SQL est simple. Une fonction d'agrégation réduit toutes les lignes à un seul résultat, ce qui signifie que vous perdez l'accès aux lignes individuelles. Une fonction fenêtre permet d'accéder à chaque ligne dans la fenêtre définie. Ceci est illustré dans l'image ci-dessous :

Différence entre les fonctions d'agrégation et de fenêtre SQL

Pour apprendre les fonctions de fenêtre SQL, je recommande le cours interactif Fonctions de fenêtrage cours interactif à l'adresse LearnSQL.fr.

Revenons à notre exemple. Si vous exécutez cette partie du code ...

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

... le prix de clôture moyen pour chaque paire de devises sera attribué à chaque ligne de la CURRENCYTRADE tableau.

Le résultat sera quelque chose comme ceci :

Comme vous pouvez le voir, la fonction fenêtre n'a pas regroupé les résultats en une seule ligne de sortie par groupe de paires de devises. Au lieu de cela, chaque ligne contient maintenant des informations supplémentaires: le prix de clôture moyen pour la paire de devises appropriée. Cela peut être très utile, car de nombreuses analyses nécessitent des informations supplémentaires pour chaque ligne tout en conservant toutes les colonnes de l'ensemble de données initial.

Syntaxe de la fonction fenêtre

Dans notre dernier exemple de fonction de fenêtre, nous avons utilisé des mots-clés spéciaux comme OVER() et PARTITION BY :

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

Ce sont les principaux mots-clés qui définissent une fonction fenêtre. Voici une brève explication :

1. OVER indique qu'il s'agit d'une fonction fenêtre. À l'intérieur de la clause OVER, nous pouvons avoir PARTITION, ORDER BY et d'autres clauses de cadre de fenêtre qui nous indiquent comment la fenêtre est encadrée (c'est-à-dire les groupes et leur ordre). Bien que nous n'ayons pas utilisé la clause de cadre de fenêtre (ROW ou RANGE) dans notre exemple, n'oubliez pas que vous pouvez utiliser l'une ou l'autre dans la clause OVER pour spécifier des cadres dans les partitions.
2. PARTITION nous indique comment les données sont regroupées ou encadrées. Elle est placée à l'intérieur de la clause OVER. Dans notre exemple, nous avons calculé le prix moyen de chaque paire de devises ; nous avons donc partitionné notre fenêtre en fonction de la colonne ticker.
3. ORDER BY (que nous n'avons pas utilisé dans notre exemple) est souvent utilisé pour déterminer l'ordre des lignes dans chaque cadre.
4. ROW ou RANGE est utilisé si nous voulons limiter davantage les lignes à l'intérieur de la partition. Pour ce faire, nous spécifions les points de départ et d'arrivée dans la partition. Quelle que soit la clause que vous employez, elle doit être utilisée avec ORDER BY. La syntaxe ressemble à ceci :

[ROWS | RANGE] BETWEEN  AND 

Le <ligne_de_début> est désigné par l'un des éléments suivants :

• PRÉCÉDENT NON BORNÉ : La fenêtre commence dans la première ligne de la partition.
• CURRENT ROW : La fenêtre commence dans la rangée actuelle.
• <littéral entier non signé> PRECEDANT ou SUIVANT.

Et le <ending_row> utilise l'un des éléments suivants :

• UNBOUNDED FOLLOWING : La fenêtre se termine dans la dernière ligne de la partition.
• CURRENT ROW : La fenêtre se termine dans la ligne actuelle.
• <littéral entier non signé> PRECEDANT ou SUIVANT.

Vous trouverez d'autres exemples de clauses de cadre de fenêtre ici.

Ne vous inquiétez pas si vous n'êtes pas à l'aise avec cette syntaxe. La pratique vous aidera ; je peux vous recommander le cours de LearnSQL.fr's Fonctions de fenêtrage qui contient de nombreuses informations utiles.

Maintenant que vous avez appris comment utiliser les fonctions analytiques SQL dans vos requêtes, l'étape suivante consiste à examiner les types de fonctions de fenêtre disponibles en SQL. Jusqu'à présent, nous avons seulement montré comment AVG() (une fonction d'agrégation) peut être utilisée comme fonction de fenêtre. Voyons maintenant ce que les autres fonctions peuvent faire.

Types de fonctions de fenêtre

Il existe trois principaux types de fonctions fenêtre :

• Les fonctions fenêtre agrégées : AVG(), MIN(), MAX(), COUNT(), SUM(). Ces fonctions permettent de calculer les valeurs moyennes, minimales ou maximales, le nombre total de lignes ou la somme totale à l'intérieur de chaque cadre défini. Les fonctions de fenêtre agrégée renvoient une valeur unique pour chaque ligne de la requête sous-jacente.

• Fonctions de fenêtre de classement : RANK(), ROW_NUMBER(), et similaires. Les fonctions de fenêtre de classement sont utilisées pour classer les lignes dans chaque cadre. Par exemple, RANK() permet de classer une valeur dans un groupe de valeurs. L'expression ORDER BY de la clause OVER détermine la valeur de classement. Chaque valeur est classée dans sa partition. Les rangs ayant des valeurs égales pour les critères de classement reçoivent le même rang.

  Prenons un autre exemple de fonction de fenêtre de classement. ROW_NUMBER() détermine le numéro ordinal de la ligne actuelle dans sa partition. Une fois encore, le critère ORDER BY de la clause OVER détermine ce numéro. Chaque valeur est ordonnée dans sa partition.

• Fonctions de fenêtre de valeur : LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Ces fonctions sont très utiles pour établir des rapports et résumer des données. Les fonctions de fenêtre LAG() et LEAD() renvoient la valeur de la ligne située avant (LEAD()) ou après (LAG()) la ligne actuelle dans une partition. Si aucune ligne n'existe, un null est renvoyé. De même, la fonction fenêtre FIRST_VALUE() / LAST_VALUE() renvoie la valeur de l'expression spécifiée pour la première (ou la dernière) ligne dans le cadre de la fenêtre.

Nous avons déjà vu un exemple d'utilisation des fonctions fenêtre agrégées, vous pouvez donc comprendre pourquoi elles sont utiles dans l'analyse des données. En fait, les trois types de fonctions de fenêtre SQL sont fréquemment utilisés dans des analyses complexes. Elles constituent une excellente fonctionnalité de SQL.

Vous avez besoin d'un exemple concret ? Supposons que vous travaillez dans le secteur des opérations de change. Très souvent, lorsque vous vendez ou achetez des positions, vous examinez le prix de clôture de la minute ou de l'heure précédente ; pour cela, vous utilisez la fonction LAG(). Vous pouvez également classer vos prix de clôture en utilisant les fonctions de fenêtre de classement dans une fenêtre de temps spécifique. Vous pouvez également trouver le prix de clôture initial ou final à l'aide des fonctions de fenêtre de valeur.

Utilisation des fonctions de fenêtre SQL : LAG()

Approfondissons l'utilisation des fonctions analytiques SQL dans la vie réelle. Pour chaque ligne, voyons le prix de clôture de la ligne précédente. Nous allons utiliser une fonction fenêtre de classement :

select *,LAG(close) OVER(PARTITION BY ticker ORDER BY datetime) AS previous_close from CURRENCYTRADE;

Nous utilisons LAG(), qui renvoie la valeur de la ligne précédente. Ici, le OVER indique qu'il s'agit d'une fonction de fenêtre dans laquelle nous regroupons les lignes par paire de devises. Comme nous utilisons LAG(), nous avons également besoin d'une clause ORDER BY pour trier les données dans chaque cadre avant d'affecter les prix de clôture de la ligne précédente. Nous trions les données par la colonne date, ce qui signifie que nous aurons le prix de clôture de la minute précédente dans chaque ligne actuelle. L'image ci-dessous montre à quoi ressemble le résultat :

Fonction de décalage de fenêtre

Ce code est plus simple et plus facile à maintenir. C'est l'un des principaux avantages de l'utilisation des fonctions de fenêtre. En effet, elles présentent de nombreux avantages, comme nous allons le voir.

Avantages de l'utilisation de SQL Fonctions de fenêtrage

Les fonctions de fenêtre sont utiles lorsque vous n'avez pas besoin de réduire les lignes de l'ensemble de résultats, c'est-à-dire de regrouper les données de résultat dans une seule ligne de sortie. Au lieu d'une seule ligne de sortie, une seule valeur pour chaque ligne de la requête sous-jacente est renvoyée. C'est le principal avantage, si vous voulez mon avis.

Voici quelques-uns des autres avantages des fonctions analytiques SQL :

• Les fonctions de fenêtre vous permettent de rassembler à la fois des valeurs agrégées et non agrégées en une seule fois. En effet, pour chaque valeur de ligne renvoyée, il n'y a pas de regroupement ou de réduction de cette ligne. Vous pouvez conserver toutes les colonnes de chaque ligne et ajouter des valeurs supplémentaires calculées par la fonction de fenêtre. C'est un avantage majeur lorsque vous avez besoin de valeurs agrégées et non agrégées dans un seul tableau.
• Leur syntaxe est simple, et il est plus facile de maintenir le code en production. Imaginez le temps qu'il vous faudrait pour implémenter l'équivalent de la fonction LEAD(), LAG() ou RANK() en utilisant le SQL traditionnel. Ou pour simplement attribuer des moyennes à chaque ligne sans fonctions de fenêtre ! Vous devriez d'abord utiliser la fonction agrégée GROUP BY, suivie d'un LEFT JOIN avec le tableau de données d'entrée original. Votre code serait plus compliqué et plus difficile à maintenir. Voici un article intéressant qui montre comment un exemple peut être résolu à la fois avec des curseurs et des fonctions analytiques SQL. Le code de la fonction analytique est plus propre et plus simple, ne pensez-vous pas ?
• Vous pouvez facilement attribuer à une ligne actuelle une valeur provenant d'une ligne précédente ou d'une ligne successive. Dans certaines bases de données, cette option est en fait beaucoup plus rapide que l'utilisation d'une solution avec le curseur ou une sous-requête corrélée. De tels codes sont plus complexes et plus difficiles à maintenir. Voici un bel article qui compare les performances (fonctions de fenêtre vs curseur vs sous-requête) dans une base de données MS SQL. Pas mal, non ?

En savoir plus sur SQL Fonctions de fenêtrage

La plupart des utilisateurs de SQL sont des débutants avancés (selon le modèle de Dreyfus) et ne connaissent peut-être pas vraiment le SQL moderne. Savoir comment utiliser les fonctions de fenêtre est une technique plus avancée, mais c'est une technique qui vaut vraiment la peine d'être apprise ! Si vous décidez d'apprendre les fonctions de fenêtre, trouvez un cours qui comporte de nombreux exemples d'utilisation de fonctions analytiques SQL sur des cas concrets. Et, surtout, recherchez un cours comportant de nombreux exercices pour vous entraîner ! N'oubliez pas que plus vous vous exercez, plus vous apprenez vite et mieux.

Outre les cours en ligne, vous pouvez toujours consulter le blog LearnSQL. Il regorge d'articles intéressants qui expliquent les fonctions de fenêtre, comme par exemple :

• Exemple de fonction fenêtre SQL avec explications
• Common SQL Fonctions de fenêtrage: Fonctions positionnelles
• Comment utiliser les fonctions de classement en SQL
• SQL commun Fonctions de fenêtrage: Utilisation des partitions avec les fonctions de classement