Utilisation de fonctions de fenêtre

Les fonctions de fenêtre fonctionnent sur les fenêtres, qui sont des groupes de lignes liées les unes avec les autres (par exemple, par date ou par emplacement). Cette rubrique explique comment utiliser les différents types de fonctions de fenêtre prises en charge par Snowflake, notamment :

  • Fonctions générales de la fenêtre.

  • Fonctions de fenêtre qui calculent le rang (par exemple, le plus élevé, celui qui arrive en deuxième position, etc.).

  • Fonctions de fenêtre prenant en charge les cadres de fenêtre cumulatifs et les cadres de fenêtre glissants.

Ce document est destiné aux lecteurs qui ne maîtrisent pas encore les fonctions de fenêtre. Les lecteurs qui maîtrisent déjà ces fonctions pourraient trouver cette documentation de référence utile :

Dans ce chapitre :

Concepts de fenêtrage

Une fenêtre est un groupe de lignes. Une fenêtre peut contenir 0, 1 ou plusieurs lignes. Cependant, pour simplifier, on dit généralement qu’une fenêtre contient « plusieurs lignes ».

Toutes les lignes d’une fenêtre sont liées d’une manière ou d’une autre, par lieu notamment (par exemple, toutes de la même ville) ou par le temps (par exemple, toutes du même exercice comptable).

Une fonction qui utilise une fenêtre est une fonction de fenêtre.

Les fonctions classées en tant que fonctions de fenêtre permettent de répondre à des types de questions différents de ceux des fonctions scalaires :

  • Une requête utilisant une fonction scalaire répond à des questions sur une seule ligne, en utilisant uniquement les données de cette ligne.

  • Une requête utilisant une fonction fenêtre répond à des questions sur la relation d’une ligne avec d’autres lignes dans la même fenêtre.

Par exemple, supposons que vous gérez la filiale d’une chaîne de cinq magasins. Pour calculer les bénéfices de votre magasin, le calcul doit uniquement examiner les informations relatives à votre magasin, telles que ses revenus et ses coûts. Vous utiliseriez une fonction scalaire pour cette requête.

Pour calculer le bénéfice de votre magasin par rapport à d’autres magasins, le calcul doit prendre en compte des informations non seulement sur votre magasin, mais également sur d’autres magasins. Vous utiliseriez une fonction de fenêtre pour cette requête.

Vous pouvez considérer une fonction de fenêtre comme une fonction prenant deux arguments en compte : le premier argument est la colonne ou l’expression à utiliser dans le calcul, par exemple, le revenu ou le bénéfice. Le second argument définit la fenêtre (c’est-à-dire le groupe de lignes utilisé dans la comparaison) ; la fenêtre comprend à la fois la ligne actuelle (votre magasin) et les autres lignes avec lesquelles la comparer (autres magasins de la même chaîne).

Pour calculer le pourcentage de votre magasin par rapport aux bénéfices de la totalité de la chaîne de magasins, vous divisez le bénéfice de votre magasin par le bénéfice total de tous les magasins (bénéfices filiale/bénéfices chaîne).

Pour mieux comprendre les fonctions de fenêtre, cette rubrique explique comment calculer le pourcentage des bénéfices de votre filiale par rapport à ceux de votre entreprise, avec et sans fonction de fenêtre.

L’exemple suivant montre une façon de calculer le pourcentage de bénéfice de votre magasin par rapport aux bénéfices de la chaîne sans fonction de fenêtre.

Commencez par créer la table, charger les données et calculer le bénéfice de chaque magasin.

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;
Copy

Maintenant, affichez le pourcentage de chaque magasin par rapport au bénéfice total de la chaîne :

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales) AS chain_profit,
       store_profit / chain_profit * 100 AS store_percentage_of_chain_profit
     FROM store_sales
     ORDER BY branch_ID;
+-----------+--------------+--------------+----------------------------------+
| BRANCH_ID | STORE_PROFIT | CHAIN_PROFIT | STORE_PERCENTAGE_OF_CHAIN_PROFIT |
|-----------+--------------+--------------+----------------------------------|
|         1 |     10000.00 |     44000.00 |                      22.72727300 |
|         2 |     15000.00 |     44000.00 |                      34.09090900 |
|         3 |     10000.00 |     44000.00 |                      22.72727300 |
|         4 |      9000.00 |     44000.00 |                      20.45454500 |
+-----------+--------------+--------------+----------------------------------+
Copy

Si vous souhaitez obtenir un rapport plus détaillé, affichant les bénéfices de chaque magasin sous forme de pourcentage de tous les magasins de cette ville, utilisez la requête ci-dessous :

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales AS s2 WHERE s2.city = s1.city) AS city_profit,
       store_profit / city_profit * 100 AS store_percentage_of_city_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
|         1 |     10000.00 |    25000.00 |                     40.00000000 |
|         2 |     15000.00 |    25000.00 |                     60.00000000 |
|         3 |     10000.00 |    19000.00 |                     52.63157900 |
|         4 |      9000.00 |    19000.00 |                     47.36842100 |
+-----------+--------------+-------------+---------------------------------+
Copy

Il serait utile d’avoir une fonction qui ferait à peu près la même chose, en divisant les bénéfices de votre magasin par la somme des bénéfices de tous les magasins (ou par la somme des bénéfices d’un groupe de magasins spécifié, par exemple, tous les magasins dans la même ville). Une telle fonction peut prendre en compte deux arguments, l’un étant la colonne sur laquelle le calcul est effectué, et le second spécifiant les lignes avec lesquelles effectuer la comparaison. La deuxième colonne pourrait ressembler un peu à une clause WHERE. Vous pouvez utiliser cette fonction un peu de la manière suivante (il s’agit d’un pseudo-code, et non d’un code SQL valide) :

SELECT branch_ID,
       PERCENTAGE(net_profit, <where_condition>)
    FROM store_sales;
Copy

Cette fonction diviserait le bénéfice de la ligne actuelle (magasin actuel) par la somme des bénéfices de tous les magasins correspondant à <where_condition>.

Par exemple, pour calculer le pourcentage du bénéfice de chaque magasin dans chaque ville, le pseudo-code ressemblerait à ceci :

SELECT branch_ID,
       PERCENTAGE(net_profit, 'city')
     FROM store_sales;
Copy

SQL ne prend pas en charge la syntaxe présentée ci-dessus, mais prend en charge le concept de fonction de fenêtre, qui renvoie un résultat basé à la fois sur la ligne en cours et sur un groupe de lignes défini.

Snowflake n’a pas de fonction nommée PERCENTAGE, mais une fonction nommée RATIO_TO_REPORT, qui divise la valeur de la ligne actuelle par la somme des valeurs de toutes les lignes d’une fenêtre. Voici l’équivalent de la requête précédente.

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+
Copy

La clause OVER() définit le groupe de lignes utilisé dans le calcul. Il remplit le même rôle que le deuxième argument hypothétique (<condition_where>) de notre précédente fonction PERCENTAGE.

La sous-clause PARTITION BY nous permet de diviser cette fenêtre en sous-fenêtres, une par ville dans le cas présent. (Si l’instruction n’utilise pas explicitement de clause PARTITION BY, la fonction de fenêtre agit sur la totalité de l’entrée en tant que fenêtre unique.)

Si vous voulez voir le pourcentage de bénéfice par rapport à la chaîne entière, et pas seulement par rapport aux magasins d’une ville spécifique, omettez la clause PARTITION BY :

SELECT branch_ID,
       100 * RATIO_TO_REPORT(net_profit) OVER ()
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+-------------------------------------------+
| BRANCH_ID | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER () |
|-----------+-------------------------------------------|
|         1 |                               22.72727300 |
|         2 |                               34.09090900 |
|         3 |                               22.72727300 |
|         4 |                               20.45454500 |
+-----------+-------------------------------------------+
Copy

Fonctions de fenêtre sensibles à l’ordre

Le résultat de la requête précédente ne dépend pas de l’ordre des lignes sélectionnées par la sous-clause PARTITION BY. Le pourcentage des bénéfices de votre magasin par rapport à ceux de votre entreprise ne dépend pas de l’ordre dans lequel la requête lit les données des autres magasins.

Certaines requêtes, cependant, sont sensibles à l’ordre. Il existe deux types de fonctions de fenêtre sensibles à l’ordre :

  • Fonctions de fenêtre liées au classement.

  • Fonctions de cadre de fenêtre.

Certaines fonctions sont à la fois des fonctions liées aux rangs et des fonctions de cadre de fenêtre.

Les fonctions sensibles à l’ordre utilisent la sous-clause ORDER BY de la clause OVER, par exemple :

select order_sensitive_function(column_name) over ( [partition by <col2>] order by <col3> )
...
Copy

La clause ORDER BY peut être utilisée avec ASC (ordre croissant) ou DESC (ordre décroissant). La valeur par défaut est « ordre croissant ».

(La sous-clause ORDER BY de la clause OVER est distincte de la clause ORDER BY qui trie le résultat final d’une requête.)

Fonctions de cadre de fenêtre

Un cadre de fenêtre est un sous-ensemble de lignes d’une fenêtre.

Une fonction de cadre de fenêtre utilise un cadre de fenêtre pour calculer des éléments tels qu’une moyenne mobile.

Snowflake prend en charge deux types de cadres de fenêtre :

  • Cumulatif.

  • Glissant.

Cadres de fenêtre cumulatifs

Une fenêtre cumulative est une fenêtre à largeur variable qui commence à un point fixe et s’agrandit à chaque nouvelle ligne. Par exemple, les « ventes jusqu’à présent ce mois-ci » sont calculées à l’aide d’une fenêtre cumulative qui commence le premier jour du mois et se prolonge à mesure que les données de chaque nouveau jour sont ajoutées :

+--------+-------+--------------+
| Day of | Sales | Sales So Far |
| Month  | Today | This Month   |
|--------+-------+--------------+
|      1 |    10 |           10 |
|      2 |    10 |           20 |
|      3 |    10 |           30 |
|    ... |   ... |          ... |
+--------+-------+--------------+
Copy

Souvent, une fenêtre cumulative redémarre à partir de 0 à intervalles réguliers. Par exemple, si le tableau ci-dessus indiquait les ventes pour février, alors le 1er mars, le point de départ du cadre de fenêtre deviendra le 1er mars, et les ventes jusqu’à présent pour le mois seront réinitialisées à 0 et seront prises en compte à partir du 1er mars.

Cadres de fenêtres glissants

Vous pouvez imaginer un cadre de fenêtre glissant en pensant à ce que vous voyez lorsque vous regardez par la vitre latérale d’une voiture lorsque celle-ci est parallèle à une clôture. Si les poteaux de clôture sont régulièrement espacés et si la largeur de la fenêtre est un entier multiple de la distance entre les poteaux, alors le nombre de poteaux de clôture que vous voyez reste constant ; cependant, lorsque vous conduisez, les « anciens » poteaux de clôture se détachent de votre vue et les « nouveaux » se déplacent dans votre vue. Ainsi, vous ne voyez pas les mêmes poteaux de clôture au fil du temps, même si vous voyez le même nombre de poteaux de clôture à tout moment.

Un cadre de fenêtrage glissant est un cadre dont la largeur est fixe et qui « glisse le long » des lignes de la fenêtre vous montrant une tranche différente de la fenêtre à chaque fois. Comme avec la voiture roulant le long des poteaux de clôture, le cadre de fenêtre se déplace le long des données, les anciennes lignes disparaissant du cadre et les nouvelles lignes apparaissant, de sorte que la largeur du cadre (le nombre de lignes dans le cadre) est toujours la même.

Les fenêtres glissantes sont souvent utilisées pour calculer les moyennes mobiles. Une moyenne mobile est une moyenne calculée en fonction d’un intervalle de taille fixe (nombre de jours, par exemple). La moyenne est « mobile » car, bien que la taille de l’intervalle soit constante, les valeurs réelles de cet intervalle changent avec le temps (ou un autre facteur) au fur et à mesure que la fenêtre glisse vers l’avant.

Par exemple, les analystes boursiers analysent souvent des actions en se basant en partie sur la moyenne mobile de 13 semaines du cours d’une action. Le prix moyen mobile aujourd’hui correspond à la moyenne des prix à la fin de la journée et du prix à la fin de chaque jour au cours des 13 dernières semaines. Si les actions sont échangées 5 jours par semaine et s’il n’y a pas eu de jours fériés au cours des 13 dernières semaines, la moyenne mobile correspond au cours moyen des 65 derniers jours d’échange (y compris aujourd’hui).

L’exemple suivant montre ce qu’il advient d’une moyenne mobile du cours d’une action sur 13 semaines (91 jours) le dernier jour du mois de juin et les premiers jours du mois de juillet :

  • Le 30 juin, la fonction renvoie le prix moyen du 1er avril au 30 juin (inclus).

  • Le 1er juillet, la fonction renvoie le prix moyen du 2 avril au 1er juillet (inclus).

  • Le 2 juillet, la fonction renvoie le prix moyen du 3 avril au 2 juillet (inclus).

  • etc.

Note

Bien que les fenêtres glissantes aient une largeur fixe, lorsqu’une fenêtre est appliquée pour la première fois à une nouvelle source de données, celle-ci peut ne pas disposer de suffisamment de données pour remplir la fenêtre. Par exemple, si la tenue des enregistrements a commencé le 1er avril, et s’étend du 1er avril au 29 juin, alors la fenêtre glissante contiendra moins de 91 jours de données. La largeur de la fenêtre ne reste constante qu’après le remplissage de la fenêtre.

L’exemple ci-dessous utilise une petite fenêtre glissante (trois jours) sur les sept premiers jours du mois. Cela montre comment le cadre de la fenêtre glisse dans la fenêtre, en conservant toujours les trois valeurs les plus récentes pour le calcul du total dans la fenêtre. Cette illustration prend en compte le fait qu’au début de la période, la fenêtre peut ne pas être pleine :

3-day sliding window frame in 7-day window

Comme vous pouvez le constater à partir de cet exemple, la dernière colonne contient la somme des données de vente des trois derniers jours. Par exemple, la valeur de colonne pour le jour 4 est 36, ce qui correspond à la somme des ventes des jours 2, 3 et 4 (11 + 12 + 13) :

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+
Copy

Plus d’informations sur les fonctions de fenêtre sensibles à l’ordre

Les clauses PARTITION BY et ORDER BY sont indépendantes. Vous pouvez utiliser la clause ORDER BY sans la clause PARTITION BY. Pour classer votre magasin par rapport à tous les autres magasins de la chaîne, et pas seulement par rapport aux autres magasins de votre ville, utilisez la requête ci-dessous :

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
Copy

Note

Une sous-clause ORDER BY à l’intérieur d’une clause OVER contrôle uniquement l’ordre dans lequel la fonction de fenêtre traite les lignes ; la clause ne contrôle pas la sortie de la requête entière. Le contrôle de l’ordre de sortie de la requête nécessite toujours une clause externe ORDER BY au niveau supérieur de la requête. Ces clauses ORDER BY sont indépendantes et ne s’excluent pas mutuellement. La requête suivante utilise la première clause ORDER BY pour contrôler le traitement par la fonction de fenêtre et la seconde clause ORDER BY pour contrôler l’ordre de la sortie de la requête entière :

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;
Copy

Certaines fonctions de fenêtre sont sensibles à l’ordre et d’autres non.

  • Certaines fonctions de fenêtre nécessitent une clause ORDER BY.

  • Certaines fonctions de fenêtre interdisent une clause ORDER BY.

  • Certaines fonctions de fenêtre utilisent une clause ORDER BY s’il en existe une, mais ne l’exigent pas.

  • Certaines fonctions de fenêtre traitent une clause ORDER BY comme une clause de cadre de fenêtre cumulative implicite. (Plus d’informations sur les cadres de fenêtre implicites sont disponibles à l’adresse Notes sur l’utilisation du cadre de fenêtre.)

Prudence

Dans la plupart des situations, SQL est un langage explicite. Le langage fait ce que vous lui dites de faire, ni plus, ni moins. Il y a très peu de clauses implicites, c’est-à-dire qu’une clause crée implicitement une autre clause qui n’est pas visible. Pour certaines fonctions de fenêtre, une clause ORDER BY ajoute implicitement une clause supplémentaire (une clause de cadre de fenêtre cumulative). Cela change le comportement. Plus d’informations sur les clauses de cadres de fenêtre implicites sont disponibles à l’adresse Notes sur l’utilisation du cadre de fenêtre.

Parce qu’un comportement implicite plutôt qu’explicite peut conduire à des résultats difficiles à comprendre, Snowflake recommande d’éviter les cadres de fenêtre implicites. Au lieu de cela, rendez explicites tous les cadres de fenêtres.

Le diagramme suivant illustre la relation entre les fonctions de fenêtre, les fonctions de cadre de fenêtre et les fonctions liées aux rangs :

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+
Copy

Comme montré ci-dessus :

  • Certaines fonctions de fenêtre ne sont pas sensibles à l’ordre.

  • Certaines fonctions de fenêtre sont sensibles à l’ordre.

  • Les fonctions de fenêtre sensibles à l’ordre sont réparties en deux catégories :

    • Fonctions liées au rang.

    • Fonctions de cadre de fenêtre.

  • Certaines fonctions sensibles à l’ordre sont à la fois des fonctions liées aux rangs et des fonctions de cadre de fenêtre.

Fonctions de fenêtre et fonctions d’agrégation

De nombreuses fonctions de fenêtre et fonctions d’agrégation ont le même nom. Par exemple, il existe une fonction de fenêtre SUM() et une fonction d’agrégation SUM().

Pour distinguer l’utilisation des deux, rappelez-vous que :

  • Pour une fonction d’agrégation, l’entrée se compose de plusieurs lignes et la sortie d’une ligne.

  • Pour une fonction de fenêtre, il existe deux entrées, une fenêtre de lignes et une seule ligne à l’intérieur de cette fenêtre. Le sortie se compose d’une ligne par ligne d’entrée.

Une fonction d’agrégation vous indique une information, telle que la somme (SUM), sur toutes les lignes combinées, mais rien sur les lignes individuelles.

Une fonction de fenêtre vous indique une information sur la ligne actuelle par rapport à toutes les autres lignes de la fenêtre.

Les instructions SQL suivantes montrent la différence entre l’utilisation de la fonction d’agrégation SUM(), qui renvoie une ligne pour l’ensemble du groupe de lignes d’entrée, et l’utilisation de la fonction de fenêtre SUM(), qui renvoie une ligne pour chaque ligne du groupe de lignes en entrée :

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+
Copy

Appel d’une fonction de fenêtre

Cette section fournit des informations supplémentaires sur la syntaxe de l’appel de fonctions de fenêtre.

Les fenêtres et les cadres de fenêtre sont spécifiés à l’aide d’une clause OVER :

... OVER ( [ PARTITION BY <expr1> ]
           [ ORDER BY <expr2> [ { cumulativeFrame | slidingFrame } ] ]
         ) ...
Copy

La syntaxe de la fonction de fenêtre est décrite plus en détail dans Fonctions de la fenêtre. Cette rubrique porte sur le sous-ensemble de la syntaxe requise par les fonctions de fenêtre, plus précisément :

  • La sous-clause PARTITION BY partitionne les données dans Windows. Par exemple, si vous souhaitez calculer les sommes en cours sur plusieurs mois, vous pouvez partitionner les données par mois. Cela vous permettrait de calculer une somme courante des ventes pour janvier, une autre somme courante pour février, etc.

  • La sous-clause ORDER BY est principalement destinée aux fonctions de fenêtre liées aux rangs et aux fonctions de cadre de fenêtre glissantes et cumulatives ; elle détermine l’ordre des lignes dans chaque fenêtre.

Exemple illustré

Cet exemple utilise un exemple sur les commerciaux pour illustrer plusieurs des concepts décrits précédemment dans cette rubrique.

Supposons que vous souhaitiez générer un rapport financier indiquant des valeurs basées sur les ventes de la dernière semaine :

  • Ventes quotidiennes.

  • Classement dans la semaine (c’est-à-dire que les ventes ont été classées dans l’ordre croissant pour la semaine)

    Cela utilise une fonction de fenêtre liée au rang (RANK).

  • Ventes réalisées jusqu’à présent cette semaine (c’est-à-dire le « montant cumulé » pour tous les jours depuis le début de la semaine jusqu’à la date du jour)

    Cela utilise une fonction de fenêtre (SUM), avec un cadre de fenêtre cumulatif.

  • Total des ventes pour la semaine

    Cela utilise SUM comme une simple fonction de fenêtre.

  • Moyenne mobile sur 3 jours (c’est-à-dire la moyenne du jour actuel et des deux jours précédents)

    Cela utilise (AVG) en tant que fonction de fenêtre avec un cadre de fenêtre glissant.

Le rapport pourrait ressembler à ceci :

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+
Copy

Le SQL pour cette requête est quelque peu complexe. Plutôt que de l’afficher sous la forme d’une requête unique, cette discussion décompose le SQL pour les colonnes individuelles.

Dans un scénario réel, vous disposez de plusieurs années de données. Par conséquent, pour calculer des sommes et des moyennes pour une semaine de données spécifique, vous devez utiliser une fenêtre d’une semaine ou un filtre similaire à ceci :

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...
Copy

Toutefois, pour cet exemple, nous supposons que la table ne contient que les données de la semaine la plus récente.

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+
Copy

Calcul du classement des ventes

La colonne Rank est calculée à l’aide de la fonction RANK :

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+
Copy

Notez que bien qu’il y ait sept jours dans la période, il n’y a que cinq rangs différents (1, 2, 3, 5, 6). Il y avait deux ex aequo (pour la 3e place et la 6e place), il n’y a donc pas de lignes avec les rangs 4 ou 7.

Calcul des ventes réalisées jusqu’à présent cette semaine

La colonne Sales So Far This Week est calculée en utilisant SUM en tant que fonction de fenêtre avec un cadre de fenêtre cumulatif :

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+
Copy

Cela signifie de classer les lignes par date, puis de calculer pour chaque date la somme des ventes réalisées à partir du début du fenêtrage jusqu’à la date actuelle (comprise).

Calcul du total des ventes cette semaine

La colonne Total Sales This Week est calculée à l’aide de SUM en tant que simple fonction de fenêtre.

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+
Copy

Calcul d’une moyenne mobile sur trois jours

La colonne 3-Day Moving Average est calculée à l’aide de AVG en tant que fonction de fenêtre avec un cadre de fenêtre glissant :

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+
Copy

La différence entre ce cadre de fenêtre glissant et le cadre de fenêtre cumulatif décrit précédemment constitue simplement le point de départ :

  • Dans un cadre de fenêtre glissant, le point de départ glisse vers la droite.

  • Dans un cadre de fenêtre cumulatif, le point de départ est fixe et le cadre continue de s’accumuler avec chaque ligne supplémentaire au sein de la fenêtre.

Mettre tous ensemble

Voici la version finale de notre requête. Elle indique les informations suivantes :

  • Rang.

  • Cadre de fenêtre cumulatif.

  • Fonction de fenêtre simple.

  • Fonction de fenêtre glissante.

    SELECT day, 
           sales_today, 
           RANK()
               OVER (ORDER BY sales_today DESC) AS Rank,
           SUM(sales_today)
               OVER (ORDER BY day
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                   AS "SALES SO FAR THIS WEEK",
           SUM(sales_today)
               OVER ()
                   AS total_sales,
           AVG(sales_today)
               OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
                   AS "3-DAY MOVING AVERAGE"
        FROM store_sales_2
        ORDER BY day;
    +-----+-------------+------+------------------------+-------------+----------------------+
    | DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
    |-----+-------------+------+------------------------+-------------+----------------------|
    |   1 |          10 |    5 |                     10 |          84 |               10.000 |
    |   2 |          14 |    3 |                     24 |          84 |               12.000 |
    |   3 |           6 |    6 |                     30 |          84 |               10.000 |
    |   4 |           6 |    6 |                     36 |          84 |                8.666 |
    |   5 |          14 |    3 |                     50 |          84 |                8.666 |
    |   6 |          16 |    2 |                     66 |          84 |               12.000 |
    |   7 |          18 |    1 |                     84 |          84 |               16.000 |
    +-----+-------------+------+------------------------+-------------+----------------------+
    
    Copy

Exemples supplémentaires

Cette section fournit des exemples plus détaillés d’utilisation des sous-clauses de fenêtrage dans les fonctions de fenêtre et explique comment ces sous-clauses fonctionnent ensemble.

Ces exemples utilisent le tableau et les données suivants :

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;
Copy

Note

Beaucoup de ces exemples utilisent deux ORDER BY clauses, une pour la clause de fenêtrage et une pour placer le jeu de résultats dans l’ordre le plus lisible. Dans le cadre de cette rubrique, les références à la clause ORDER BY sont généralement des références à la clause dans la fenêtre.

Fenêtre avec la clause PARTITION BY

La sous-clause PARTITION BY de la clause de fenêtrage divise les données en sous-ensembles distincts en fonction de la valeur de l’expression d’entrée ; l’instruction SELECT est appliquée à chaque sous-ensemble et la sortie contient des lignes pour chaque sous-ensemble.

Notez que ceci est semblable, mais pas identique, à la manière dont la clause GROUP BY fonctionne.

L’exemple suivant montre la quantité vendue chaque mois et utilise la clause PARTITION BY pour diviser les données en sous-ensembles d’un mois :

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+
Copy

Comme vous pouvez le constater, les trois premières lignes sont des doublons. Il y avait 3 lignes d’entrée pour le mois #1 et la fonction de fenêtrage génère une ligne de sortie pour chaque ligne d’entrée. Il y a donc 3 lignes de sortie pour le mois #1 dans la sortie. La fonction SUM n’est pas utilisée en tant que fonction cumulative ou glissante ; elle est appliquée à la fenêtre entière et renvoie la même valeur pour la fenêtre entière à chaque fois. La fonction retourne donc des valeurs en double, comme indiqué ci-dessus.

Vous pouvez réduire les doublons en utilisant le mot clé DISTINCT :

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+
Copy

Dans ce cas particulier, vous pouvez utiliser une clause GROUP BY plutôt qu’une clause de fenêtrage. Par exemple :

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+
Copy

Cependant, GROUP BY n’est pas aussi flexible que le fenêtrage. Cependant, dans les requêtes plus complexes, vous ne pouvez pas toujours remplacer GROUP BY par une clause de fenêtrage.

Fenêtre avec la clause ORDER BY

La clause ORDER BY contrôle l’ordre des données dans chaque fenêtrage (et chaque partition s’il y a plus d’une partition). Ceci est utile si vous souhaitez afficher une « somme en cours » sur une période donnée, à mesure que de nouvelles lignes sont ajoutées.

Une somme en cours peut être calculée soit au début du fenêtrage jusqu’à la ligne actuelle (comprise) soit à partir de la ligne actuelle jusqu’à la fin du fenêtrage.

Une requête peut utiliser un fenêtrage « glissant », qui est un fenêtrage de largeur fixe qui traite N lignes spécifiées par rapport à la ligne actuelle (par exemple, les 10 lignes les plus récentes, y compris la ligne actuelle).

Les fenêtrages cumulatifs et glissants sont expliqués ci-dessous.

Exemples de cadres de fenêtre cumulatifs

Dans un cadre de fenêtrage « cumulatif », les valeurs sont calculées à partir du début du fenêtrage jusqu’à la ligne actuelle (ou à partir de la ligne actuelle jusqu’à la fin du fenêtrage) :

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

Le résultat de la requête inclut des commentaires supplémentaires indiquant comment la colonne CUMULATIVE_SUM_QUANTITY a été calculée :

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+
Copy

Exemples de cadres de fenêtre glissants

Dans le monde financier, les analystes étudient souvent les « moyennes mobiles ».

Par exemple, vous pourriez voir un graphique dans lequel l’axe des X est le temps et l’axe des Y indique le cours moyen des actions au cours des 13 dernières semaines ( la « moyenne mobile de 13 semaines »). Dans un graphique représentant une moyenne mobile du cours d’une action sur 13 semaines, le cours indiqué au 30 juin ne correspond pas au cours de l’action au 30 juin, mais au cours moyen de l’action pour les 13 semaines allant jusqu’au 30 juin inclus (c.-à-d. du 1er avril au 30 juin). La valeur au 1er juillet est le cours moyen du 2 avril au 1er juillet ; la valeur du 2 juillet correspond au cours moyen du 3 avril au 2 juillet, et ainsi de suite. Chaque jour, la fenêtre ajoute effectivement la valeur du jour le plus récent à la moyenne mobile et supprime la valeur du jour le plus ancien. Cela lisse les fluctuations au jour le jour et peut rendre les tendances plus faciles à reconnaître.

Les moyennes mobiles peuvent être calculées en utilisant un « fenêtrage glissant ». Le fenêtrage a une largeur spécifique en lignes. Dans l’exemple ci-dessus, 13 semaines correspondent à 91 jours. Le fenêtrage glissant serait donc de 91 jours. Si les mesures sont prises une fois par jour (par exemple, à la fin de la journée), le fenêtrage aura 91 lignes « de large ».

Pour définir une fenêtre large de 91 lignes :

SELECT AVG(price) OVER (ORDER BY timestamp1
                        ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
    FROM sales;
Copy

Note

Lorsque le fenêtrage commence, sa largeur peut être inférieure à 91 jours. Par exemple, supposons que vous souhaitiez obtenir le prix moyen mobile d’une action sur 13 semaines. Si le stock a été créé pour la première fois le 1er avril, puis le 3 avril, il n’y a que 3 jours d’informations sur les prix. La fenêtre s’étend donc sur trois lignes.

L’exemple suivant montre le résultat de la somme sur un fenêtrage glissant suffisamment large pour contenir deux échantillons :

SELECT MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
           AS SLIDING_SUM_QUANTITY
  FROM sales
  ORDER BY sales_date;
Copy

Le résultat de la requête inclut des commentaires supplémentaires indiquant comment la colonne SLIDING_SUM_QUANTITY a été calculée :

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+
Copy

Noter que la fonctionnalité de « fenêtrage glissant » nécessite la clause ORDER BY ; le fenêtrage glissant doit connaître l’ordre dans lequel les lignes entrent et sortent du fenêtrage glissant.

Fenêtre avec clauses PARTITION BY et ORDER BY

Vous pouvez combiner les clauses PARTITION BY et ORDER BY pour obtenir des sommes en cours dans les partitions. Dans cet exemple, les partitions durent un mois et, comme les sommes s’appliquent uniquement à l’intérieur d’une partition, la somme est réinitialisée à 0 au début de chaque nouveau mois :

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

Le résultat de la requête inclut des commentaires supplémentaires indiquant comment la colonne MONTHLY_CUMULATIVE_SUM_QUANTITY a été calculée :

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+
Copy

Vous pouvez combiner des partitions et des fenêtrages glissants. Dans l’exemple ci-dessous, le fenêtrage glissant est généralement large de deux lignes, mais chaque fois qu’une nouvelle partition (c’est-à-dire, un nouveau mois) est atteinte, le fenêtrage glissant commence uniquement par la première ligne de cette partition :

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

Le résultat de la requête inclut des commentaires supplémentaires indiquant comment la colonne MONTHLY_SLIDING_SUM_QUANTITY a été calculée :

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+
Copy