Analyser les données avec les fonctions de fenêtre¶

Introduction¶

Une fonction de fenêtre est une fonction SQL analytique qui opère sur un groupe de lignes liées appelées partition. Une partition est généralement un groupe logique de lignes selon une dimension familière, comme la catégorie de produit, l’emplacement, la période ou l’unité commerciale. Les résultats des fonctions sont calculés sur chaque partition, par rapport à un cadre de fenêtre implicite ou explicite. Un cadre de fenêtre est un ensemble fixe ou variable de lignes par rapport à la ligne actuelle. La ligne actuelle est une ligne d’entrée unique pour laquelle le résultat de la fonction est actuellement calculé. Les résultats de la fonction sont calculés ligne par ligne dans chaque partition, et chaque ligne du cadre de la fenêtre prend son tour comme ligne actuelle.

La syntaxe qui définit ce comportement est la clause OVER pour la fonction. Dans de nombreux cas, la clause OVER fait la distinction entre une fonction de fenêtre et une fonction SQL standard avec le même nom (comme AVG ou SUM). La clause OVER se compose de trois éléments principaux :

• Une clause PARTITION BY

• Une clause ORDER BY

• Une spécification de cadre de fenêtre

Selon la fonction ou la requête en question, tous ces composants peuvent être facultatifs ; une fonction de fenêtre avec une clause OVER vide est valide : OVER(). Cependant, dans la plupart des requêtes analytiques, les fonctions de fenêtre nécessitent un ou plusieurs composants de clause OVER explicites. Vous pouvez appeler une fonction de fenêtre dans n’importe quel contexte prenant en charge d’autres fonctions SQL. Les sections suivantes expliquent plus en détail les concepts derrière les fonctions de fenêtre et présentent quelques exemples d’introduction. Pour des informations complètes sur la syntaxe, voir Syntaxe et utilisation des fonctions de fenêtre.

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

Une bonne façon de découvrir les fonctions de fenêtre est de comparer les fonctions d’agrégation classiques avec leurs homologues de fonctions de fenêtre. Plusieurs fonctions d’agrégation standard, comme SUM, COUNT et AVG, ont des fonctions de fenêtre correspondantes avec le même nom. Pour distinguer les deux, notez ce qui suit :

• 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, l’entrée se compose d’une ligne dans une partition et la sortie d’une ligne par ligne d’entrée.

Par exemple, la fonction d’agrégation SUM renvoie une valeur totale unique pour toutes les lignes d’entrée, tandis qu’une fonction de fenêtre renvoie plusieurs totaux : un pour chaque ligne (la ligne actuelle) par rapport à toutes les autres lignes de la partition.

Pour voir comment cela fonctionne, commencez par créer et charger la table menu_items qui contient le coût des marchandises vendues et les prix des articles du menu du foodtruck. Utilisez une fonction AVG standard pour trouver le coût moyen des marchandises pour les articles du menu dans différentes catégories :

SELECT menu_category,
    AVG(menu_cogs_usd) avg_cogs
  FROM menu_items
  GROUP BY 1
  ORDER BY menu_category;

+---------------+------------+
| MENU_CATEGORY |   AVG_COGS |
|---------------+------------|
| Beverage      | 0.60000000 |
| Dessert       | 1.79166667 |
| Main          | 6.11046512 |
| Snack         | 3.10000000 |
+---------------+------------+

Notez que la fonction renvoie un résultat groupé pour avg_cogs.

Vous pouvez également spécifier une clause OVER et utiliser AVG comme une fonction de fenêtre. (Le résultat est limité à 15 lignes du tableau de 60 lignes.)

SELECT menu_category,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;

+---------------+----------+
| MENU_CATEGORY | AVG_COGS |
|---------------+----------|
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
+---------------+----------+

Notez que la fonction renvoie une moyenne pour chaque ligne de chaque partition et réinitialise le calcul lorsque la valeur de la colonne de partitionnement change. Pour rendre la valeur de la fonction de fenêtre plus apparente, ajoutez une clause ORDER BY et un cadre de fenêtre à la définition de fonction. Renvoyez également les valeurs menu_cogs_usd brutes, en plus des moyennes. Vous pourrez ainsi voir comment fonctionnent les calculs spécifiques. Cette requête est un exemple simple de « moyenne mobile », un calcul glissant qui dépend d’un cadre de fenêtre explicite. Pour plus d’exemples comme celui-ci, voir Analyse des données de séries temporelles.

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category ORDER BY menu_price_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd
  LIMIT 15;

+---------------+----------------+---------------+----------+
| MENU_CATEGORY | MENU_PRICE_USD | MENU_COGS_USD | AVG_COGS |
|---------------+----------------+---------------+----------|
| Beverage      |           2.00 |          0.50 |  0.58333 |
| Beverage      |           3.00 |          0.50 |  0.57500 |
| Beverage      |           3.00 |          0.75 |  0.63333 |
| Beverage      |           3.50 |          0.65 |  0.65000 |
| Dessert       |           3.00 |          0.50 |  0.91666 |
| Dessert       |           4.00 |          1.00 |  1.58333 |
| Dessert       |           5.00 |          1.25 |  2.08333 |
| Dessert       |           6.00 |          2.50 |  2.66666 |
| Dessert       |           6.00 |          2.50 |  2.75000 |
| Dessert       |           7.00 |          3.00 |  3.00000 |
| Main          |           5.00 |          1.50 |  2.03333 |
| Main          |           6.00 |          2.60 |  3.00000 |
| Main          |           6.00 |          2.00 |  2.33333 |
| Main          |           6.00 |          2.40 |  3.13333 |
| Main          |           8.00 |          4.00 |  3.66666 |
+---------------+----------------+---------------+----------+

Le cadre de fenêtre ajuste les calculs moyens de telle sorte que seules la ligne actuelle et les deux lignes qui la suivent (dans la partition) soient prises en compte. La dernière ligne d’une partition n’a pas de lignes suivantes, donc la moyenne de la dernière ligne Beverage, par exemple, est la même que la valeur menu_cogs_usd correspondante (0.65). La sortie de la fonction de fenêtre dépend de la ligne individuelle transmise à la fonction et des valeurs des autres lignes éligibles pour le cadre de fenêtre.

Ordre des lignes pour les fonctions de fenêtre¶

L’exemple de fonction de fenêtre AVG précédent utilise une clause ORDER BY dans la définition de la fonction pour garantir que le cadre de fenêtre est soumis à des données triées (par menu_price_usd dans ce cas).

Deux types de fonctions de fenêtre nécessitent une clause ORDER BY :

• Fonctions de fenêtre avec cadres de fenêtre explicites qui effectuent des opérations de glissement sur des sous-ensembles de lignes de chaque partition, comme le calcul de totaux continus ou de moyennes mobiles. Sans clause ORDER BY, le cadre de fenêtre n’a pas de sens ; l’ensemble des lignes « précédentes » et « suivantes » doit être déterministe.

• Fonctions de fenêtre de classement, comme CUME_DIST, RANK et DENSE_RANK, qui renvoient des informations basées sur le « rang » d’une ligne. Par exemple, si vous classez les magasins par ordre descendant de profit par mois, le magasin qui enregistre le plus de profit sera classé 1 ; le deuxième magasin le plus rentable sera classé 2, etc.

La clause ORDER BY pour une fonction de fenêtre prend en charge la même syntaxe que la clause ORDER BY principale qui trie les résultats finaux d’une requête. Ces deux clauses ORDER BY sont séparées et distinctes. Une 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 ; elle ne contrôle pas la sortie de la requête entière. Dans de nombreux cas, vos requêtes de fonction de fenêtre contiendront les deux types de clauses ORDER BY.

Les clauses PARTITION BY et ORDER BY à l’intérieur de la clause OVER sont également indépendantes. Vous pouvez utiliser la clause ORDER BY sans la clause PARTITION BY, et inversement.

Vérifiez la syntaxe des fonctions de fenêtre individuelles avant d’écrire des requêtes. Les exigences syntaxiques pour la clause ORDER BY varient selon la fonction :

• Certaines fonctions de fenêtre nécessitent 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 n’autorisent pas la clause ORDER BY.

• Certaines fonctions de fenêtre interprètent une clause ORDER BY comme un cadre de fenêtre implicite.

Utilisation de différents types de cadres de fenêtre¶

Les cadres de fenêtre sont définis explicitement ou implicitement. Ils dépendent de la présence d’une clause ORDER BY à l’intérieur de la clause OVER :

• Pour une syntaxe de cadre explicite, voir windowFrameClause sous Syntaxe. Vous pouvez définir des limites ouvertes : du début de la partition à la ligne actuelle ; de la ligne actuelle à la fin de la partition ; ou complètement « sans limite » de bout en bout. Vous pouvez également utiliser des décalages explicites (inclus) relatifs à la ligne actuelle de la partition.

• Les cadres implicites sont utilisés par défaut lorsque la clause OVER n’inclut pas de windowFrameClause. Le cadre par défaut dépend de la fonction en question. Voir aussi Notes sur l’utilisation des cadres de fenêtre.

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));

+-------------------------+--------+-------+-------------+
| START_TIME              | PRECIP | CITY  | COUNTY      |
|-------------------------+--------+-------+-------------|
| 2021-12-30 11:23:00.000 |   0.12 | Lebec | Los Angeles |
| 2021-12-30 11:43:00.000 |   0.98 | Lebec | Los Angeles |
| 2021-12-30 13:53:00.000 |   0.23 | Lebec | Los Angeles |
| 2021-12-30 14:53:00.000 |   0.13 | Lebec | Los Angeles |
| 2021-12-30 15:15:00.000 |   0.29 | Lebec | Los Angeles |
| 2021-12-30 17:53:00.000 |   0.10 | Lebec | Los Angeles |
| 2021-12-30 18:53:00.000 |   0.09 | Lebec | Los Angeles |
| 2021-12-30 19:53:00.000 |   0.07 | Lebec | Los Angeles |
| 2021-12-30 20:53:00.000 |   0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+

AVG(precip)
  OVER(ORDER BY start_time
    RANGE BETWEEN CURRENT ROW AND INTERVAL '3 hours' FOLLOWING)

RANGE BETWEEN CURRENT ROW AND INTERVAL '1 day' FOLLOWING

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;

+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      5.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     18.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |      6.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;

+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

OVER(PARTITION BY col1 ORDER BY col2 ROWS UNBOUNDED PRECEDING)

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

Cadres de fenêtres glissants¶

Un cadre de fenêtre glissant est un cadre dont la largeur est fixe et qui « glisse le long » des lignes de la partition vous montrant une tranche différente de la partition à chaque fois. Le nombre de lignes dans le cadre reste le même, sauf au début ou à la fin d’une partition, où il peut contenir moins de lignes.

Les fenêtres glissantes sont souvent utilisées pour calculer les moyennes mobiles, qui sont basées sur un intervalle de taille fixe (comme un certain nombre de jours). 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 une autre dimension).

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).

L’exemple suivant utilise une petite fenêtre glissante (trois jours) sur les sept premiers jours du mois. Cet exemple prend en compte le fait qu’au début de la période, la partition peut ne pas être pleine :

Comme vous pouvez le voir dans la maquette correspondante d’un résultat de requête, 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 |
|    ... |   ... |           ... |
+--------+-------+---------------+

Fonctions de fenêtre de classement¶

La syntaxe d’une fonction de fenêtre de classement est essentiellement la même que celle d’autres fonctions de fenêtre. Les exceptions sont les suivantes :

• Les fonctions de fenêtre de classement nécessitent que la clause ORDER BY soit à l’intérieur de la clause OVER.

• Pour certaines fonctions de classement, telles que RANK, aucun argument d’entrée n’est requis. Pour la fonction RANK, la valeur renvoyée est basée uniquement sur le classement numérique, tel que déterminé par la clause ORDER BY à l’intérieur de la clause OVER. Par conséquent, il est inutile de transmettre un nom de colonne ou une expression à la fonction.

La fonction de classement la plus simple s’appelle RANK. Vous pouvez utiliser cette fonction pour :

• Classer les commerciaux en fonction des chiffres de ventes, du plus élevé au plus bas.

• Classer les pays en fonction de leur GDP par habitant (revenu par personne), du plus élevé au plus faible.

• Classer les pays en fonction de la pollution atmosphérique, du moins pollué au plus pollué.

Cette fonction identifie simplement la position de classement numérique d’une ligne dans un ensemble classé de lignes. La première ligne a le rang 1, la deuxième le rang 2, et ainsi de suite. L’exemple suivant montre l’ordre de classement des commerciaux en fonction du Amount Sold :

+-------------+-------------+------+
| Salesperson | Amount Sold | Rank |
|-------------+-------------+------|
| Smith       |        2000 |    1 |
| Jones       |        1500 |    2 |
| Torkelson   |        1200 |    3 |
| Dolenz      |        1100 |    4 |
+-------------+-------------+------+

Les lignes doivent être triées avant l’attribution des classements. Par conséquent, vous devez utiliser une clause ORDER BY à l’intérieur de la clause OVER.

Prenons l’exemple suivant : vous souhaitez comparer le bénéfice de votre magasin par rapport à celui des filiales de la chaîne de magasins, c’est-à-dire savoir si votre magasin se classe en premier, en deuxième, en troisième, etc. Cet exemple classe chaque magasin en fonction de la rentabilité de sa ville. Les lignes sont classées par ordre décroissant (bénéfice le plus élevé en premier), ainsi le magasin le plus rentable a le rang 1 :

SELECT city, branch_ID, net_profit,
       RANK() OVER (PARTITION BY city ORDER BY net_profit DESC) AS rank
    FROM store_sales
    ORDER BY city, rank;
+-----------+-----------+------------+------+
| CITY      | BRANCH_ID | NET_PROFIT | RANK |
|-----------+-----------+------------+------|
| Montreal  |         3 |   10000.00 |    1 |
| Montreal  |         4 |    9000.00 |    2 |
| Vancouver |         2 |   15000.00 |    1 |
| Vancouver |         1 |   10000.00 |    2 |
+-----------+-----------+------------+------+

Copy

La sortie d’une fonction de classement dépend de :

• La ligne individuelle transmise à la fonction.

• Des valeurs des autres lignes de la partition.

• De l’ordre de toutes les lignes dans la partition.

Snowflake fournit différentes fonctions de classement. Pour une liste de ces fonctions et plus de détails sur leur syntaxe, voir Fonctions de fenêtre.

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

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;

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)

• 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)

• Total des ventes pour la semaine

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

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 |
+--------+-------+------+--------------+-------------+--------------+

Le SQL pour cette requête est quelque peu complexe. Plutôt que d’afficher l’exemple 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

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 |
+-----+-------------+-------------+

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 |
+-----+-------------+----------------------+

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 |
+-----+-------------+------+------------------------+-------------+----------------------+

Exemples supplémentaires¶

Cette section fournit plus d’exemples de fonctions de fenêtre et illustre comment les clauses PARTITION BY et ORDER BY 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)
    ;

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;

+-----------+----------+-------------------------+
| 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
+-----------+----------+-------------------------+

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

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;

+-----------+----------+----------------------+
| 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)
+-----------+----------+----------------------+

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;

+-----------+---------------------------------+
| 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)
+-----------+---------------------------------+

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;

+-----------+----------+------------------------------+
| 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)
+-----------+----------+------------------------------+

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 |
+-----------+-----------+------------------------------------------------------------+

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 |
+-----------+-------------------------------------------+