La clause RANGE dans les fonctions de fenêtre SQL : 5 exemples pratiques

Qu'est-ce qu'une clause RANGE dans les fonctions fenêtres SQL ? Cinq exemples pratiques vous montreront comment et quand l'utiliser.

La clause RANGE est utilisée assez rarement dans les fonctions de fenêtre SQL. Je ne sais pas pourquoi, peut-être que les gens n'y sont pas habitués. C'est dommage, car c'est loin d'être une clause inutile ; elle peut être très utile, et je vais vous le montrer dans cinq exemples.

Syntaxe de la clause RANGE

Lorsque je parle de la clause RANGE, je parle de celle utilisée dans les fonctions de la fenêtre SQL, dont la syntaxe est la suivante :

OVER (   
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
) 

Lorsque vous regardez la syntaxe ci-dessus, vous voyez que ROW ou RANGE peuvent tous deux faire partie de la fonction fenêtre. Leur syntaxe est la suivante :

[<Clauses ou clause RANGE> ENTRE <Début expr> ET <Fin expr>]

Le cadre de fenêtre par défaut sans le ORDER BY est la partition entière. Mais lorsque vous utilisez le ORDER BY, le cadre de la fenêtre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Il est normal d'oublier cette syntaxe, surtout si elle est nouvelle pour vous ou si vous ne l'utilisez pas très souvent. N'hésitez pas à ouvrir votre Cheat SheetFonctions de fenêtrage pendant que vous lisez cet article.

Vous êtes prêt à vous lancer ? Très bien ! Tout d'abord, parlons de RANGE et ROW.

Les clauses RANGE et ROW sont-elles identiques ?

Non, elles ne le sont pas. Elles ont cependant le même objectif : spécifier les points de départ et d'arrivée dans la partition, dans le but de limiter les lignes. Cependant, chaque clause s'y prend différemment. La clause ROW le fait en spécifiant un nombre fixe de lignes qui précèdent ou suivent la ligne actuelle.

La clause RANGE, quant à elle, limite les lignes de manière logique ; elle spécifie la plage de valeurs par rapport à la valeur de la ligne actuelle.

Quels types de données puis-je utiliser dans la clause RANGE ?

Vous pouvez utiliser deux types de données avec la clause RANGE: les types numériques et les types date/heure.

Vous pouvez utiliser les types de données numériques et la clause RANGE dans presque toutes les bases de données courantes. Malheureusement, Microsoft SQL Server ne la prend pas en charge.

En ce qui concerne les types de données date/heure, seules quelques bases de données populaires permettent de les utiliser avec la clause RANGE. Il s'agit de PostgreSQL, MySQL et Oracle DB.

Passons maintenant aux exemples et voyons comment RANGE fonctionne en pratique ! Si les fonctions de fenêtre SQL sont nouvelles pour vous, vous devriez peut-être commencer par vérifier ce que sont les fonctions de fenêtre et comment elles se comparent aux fonctions d'agrégation. Revenez quand vous aurez fini de lire et nous continuerons.

Exemple 1 - Calculer le total courant

Les données avec lesquelles je vais travailler sont dans le tableau revenue. Les colonnes sont :

• id - L'ID de la date et la clé primaire (PK) de la table.
• date - La date de la recette.
• revenue_amount - Le montant de la recette.

Votre tâche consiste à calculer les totaux courants des recettes à l'aide de la clause RANGE. Commençons par le faire sans SQL. Si vous disposez des données suivantes, quel sera le total courant (également appelé somme cumulée) ?

Le total courant pour le 2021-05-01 est égal aux recettes pour cette date : 12 573,25. Cela s'explique par le fait qu'il n'y a pas de lignes précédentes à inclure dans le calcul. Vient ensuite le 2021-05-02. Le total courant est le revenu d'aujourd'hui ajouté au revenu du jour précédent : 11,348.22 + 12,573.25 = 23,921.47.

Remarquez qu'il y a une autre ligne avec un montant de recettes différent pour 2021-05-02. Peut-être est-ce pour une autre branche, un autre pays, un autre produit ou autre. Cela fonctionne de la même manière : 14 895,13 + 23 921,47 = 38 816,60. (La clause RANGE fonctionne même s'il y a plusieurs lignes avec la même date). Vient ensuite le 2021-05-03. Le total courant pour cette date sera de 14 388,14 + 38 816,60 = 53 204,74. Enfin, le total courant pour le 2021-05-04 sera de 18 847,54 + 53 204 = 72 052,28.

Comment faire de même en utilisant la clause RANGE? Cela pourrait se faire de la manière suivante :

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

J'ai utilisé la fonction SUM() sur la colonne revenue_amount; c'est l'opération nécessaire pour obtenir le total courant. Pour que la fonction SUM() devienne une fonction fenêtre, vous avez besoin de la clause OVER(). Le calcul de la fonction fenêtre est effectué par ordre croissant, car je veux m'assurer que le revenu est additionné de la date la plus ancienne à la plus récente. Vient ensuite la clause RANGE. Elle limite la fenêtre aux dates précédant la date actuelle (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) et la date actuelle. Ce sont les lignes qui seront incluses dans le calcul du total courant.

Voici ce que vous obtenez lorsque vous exécutez le code :

Remarquez que lorsqu'il y a plusieurs valeurs pour une date (2021-05-02), le code inclut les deux lignes dans le calcul du total courant pour cette date. C'est pourquoi il y a 38 816,60 dans la colonne running_total pour cette date.

Exemple 2 - Calcul de la moyenne mobile

Voyons maintenant comment utiliser la clause RANGE avec des types de données date/heure - et sans l'aide de la colonne id ! Nous allons utiliser une table légèrement modifiée, qui s'appelle désormais revenue_per_shop. Elle contient les données relatives aux recettes de deux magasins. Les colonnes sont les suivantes :

• date - La date de la recette.
• shop - Le nom de la boutique.
• revenue_amount - Le montant des recettes de ce magasin à cette date.

Tout d'abord, vérifions que vous comprenez bien ce qu'est une moyenne mobile. Une moyenne mobile sur deux jours comprend la journée en cours et la journée précédente. Voici quelques exemples de données pour vous montrer comment fonctionne une moyenne mobile :

La moyenne mobile sur deux jours pour le 2021-05-01 est le revenu quotidien lui-même : 12,573.25. Cela s'explique par le fait qu'il n'y a pas d'autres lignes à inclure dans le calcul. Le calcul du 2021-05-02 comprend deux dates : (12 573,25 + 11 348,22)/2 = 11 960,74. Les autres lignes suivent la même logique à deux dates - la date actuelle et la date précédente.

Alors comment calculer la même mesure pour chaque magasin séparément ? Comme ceci :

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Le code sélectionne d'abord certaines colonnes du tableau. Puis vient la partie amusante. J'utilise la fonction AVG() sur la colonne revenue_amount parce que je veux le revenu moyen. Encore une fois, il s'agit d'une fonction de fenêtre, donc elle doit avoir la clause OVER(). J'utilise le PARTITION BY pour spécifier la colonne sur laquelle je veux agréger les données ; c'est la colonne shop car je veux la moyenne mobile pour chaque magasin séparément. L'opération est à nouveau ordonnée par date. Dans la clause RANGE, je spécifie simplement les lignes à inclure dans le calcul. Puisque je travaille avec des dates, je vais obtenir la date précédente en indiquant : BETWEEN INTERVAL '1' DAY PRECEDING.

Le travail avec les données de date/heure diffère selon les bases de données. Vous devrez peut-être l'écrire de cette façon dans certaines bases de données : RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW. Le résultat devrait néanmoins être le même :

Moyenne mobile pour les bases de données qui ne prennent pas en charge l'utilisation de RANGE avec les types de données date/heure

Que devez-vous faire si votre base de données ne prend pas en charge l'utilisation de RANGE avec les types de données date/heure ? Il existe un moyen de "tromper" votre base de données ; en fait, il existe probablement plusieurs moyens. En voici une qui calcule la différence entre chaque date et le 2021-05-01 (c'est-à-dire la première date des données). Vous obtenez la différence sous la forme d'un nombre entier, que vous pouvez utiliser à la place de la date. Voici le code :

SELECT
  shop,
  date,
  revenue_amount,
  date - '2021_05_01' AS day_difference,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY (date - '2021_05_01')
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

J'ai intentionnellement inclus la colonne day_difference dans le résultat pour que vous compreniez la logique. J'ai utilisé la même différence dans la clause ORDER BY afin de pouvoir utiliser un nombre entier avec la clause RANGE. Et la moyenne mobile est la même que dans l'exemple ci-dessus ; voyez par vous-même. (Je ne montre que les cinq premières lignes pour gagner de la place).

Exemple 3 - Trouver la dernière valeur d'une plage de valeurs

Cette utilisation de la clause RANGE vous permet de trouver la dernière valeur dans une plage définie. Par exemple, en utilisant le tableau revenue_by_shopje peux obtenir la dernière valeur pour chaque magasin séparément. Dans ce cas, la dernière valeur correspond aux dernières données disponibles, à savoir le chiffre d'affaires du 2021-05-15. Comment obtenir ces données ?

En utilisant la clause RANGE, bien sûr :

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

La fonction de fenêtre que j'ai utilisée cette fois-ci est LAST_VALUE(). Une fois encore, je l'utilise sur la colonne revenue_amount. J'ai partitionné les données par magasin, comme précédemment. Et je les ai ordonnées par date, comme précédemment. Pour obtenir la dernière valeur, j'ai utilisé RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. N'oubliez pas que la plage par défaut avec la clause ORDER BY est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Si vous ne la modifiez pas, vous obtiendrez un résultat erroné. Le bon résultat est le suivant :

Exemple 4 - Trouver le nombre d'éléments dans une plage de valeurs

Voici une autre façon amusante et utile d'utiliser la clause RANGE. Comment trouver le nombre de fois où le revenu quotidien d'un magasin se situe entre 1 000 (dollars, euros...) en dessous et au-dessus de sa valeur actuelle ?

Ce code pourrait vous aider :

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

J'utilise la fonction de fenêtre COUNT(). Comme je ne suis pas intéressé par la séparation des revenus par magasin, il n'y a pas de PARTITION BY. Le comptage sera effectué par ordre croissant en fonction du montant du revenu. L'intervalle est défini par RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING.

Voici ce que le code va retourner :

Laissez-moi vous expliquer ce que ce résultat vous dit. Si vous prenez la première ligne, le résultat dans la colonne number_of_days est 2. Il y a deux cas où le revenu est compris entre 3 987,56 et 5 987,56. Pourquoi cette fourchette ? Le revenu pour 2021-05-13 est de 4 987,56. Donc 4 987,56 - 1 000 = 3 987,56 et 4 987,56 + 1 000 = 5 987,56. Voulez-vous vérifier le résultat ? Quelles sont les deux instances qui se situent dans cette fourchette ? Évidemment, les deux premières :

Voulez-vous vérifier la deuxième ligne ? Elle dit qu'il y a trois instances entre 4 894,12 et 6 894,12 - ces trois-là :

La même logique s'applique au reste des données.

Exemple 5 - Trouver la valeur maximale

Cet exemple n'est pas aussi simple qu'il n'y paraît. Je ne parle pas de la valeur maximale commune. Pour la trouver, vous n'auriez pas besoin de la clause RANGE. Mais que diriez-vous de trouver la valeur maximale (ou le revenu, dans ce cas) sur cinq jours ? Ces cinq jours comprennent la date du jour, jusqu'à trois jours avant et un jour après la date du jour. Vous connaissez probablement déjà la logique après tous ces exemples d'utilisation de RANGE. Voici ma solution :

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

J'utilise la fonction MAX() comme une fonction fenêtre. Encore une fois, je l'utilise avec la colonne revenue_amount. Il n'y a pas de PARTITION BY dans la clause OVER() car je ne suis pas intéressé par la séparation des données à quelque niveau que ce soit. La définition de la plage n'est pas très difficile : RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING. Cela comprendra la date actuelle, trois jours avant et un jour après. Voici le résultat :

Vérifions le résultat pour 2021-05-05 - marqué en rose. La plage est marquée en jaune. Pour obtenir le revenu maximum dans cette plage, SQL va comparer les valeurs : 14,388.14, 18,847.54, 9,845.29, 14,574.56, 11,500.63, 16,897.21, 9,634.56, 14,255.87, 11,248.33, 21,489.22. Lequel est le plus élevé ? C'est 21 489,22.

Après avoir appris comment trouver la valeur maximale à l'aide de la clause RANGE, j'ai atteint le nombre maximum d'exemples prévus pour cet article. Si vous voulez d 'autres exemples de fonctions de fenêtre, vous pouvez toujours lire cet article.

RANGE a vraiment beaucoup d'utilisations, n'est-ce pas ?

Je pense que ces cinq exemples vous montrent un bon éventail des possibilités de la clause RANGE. Ce n'est pas tout ce que vous pouvez faire avec elle. Son utilisation dépend des données dont vous disposez et probablement d'un peu d'imagination. On ne parle pas souvent de cette clause, ce qui est dommage. Je conseille à tout le monde de l'apprendre. Elle pourrait vous faire gagner du temps dans la création de solutions de contournement. La clause RANGE est élégante et vraiment pas très compliquée.

Où pouvez-vous apprendre à connaître la clause RANGE ? Dans notre coursFonctions de fenêtrage , bien sûr. Vous y apprendrez non seulement la clause RANGE, mais aussi tous les aspects des fonctions de la fenêtre. Vous pouvez également utiliser le cours pour mettre en pratique vos compétences en SQL. Si vous voulez en savoir plus, cet article vous dira tout sur le cours et ce qu'il propose.