Utilisation de fonctions de fenêtre¶

Fonctions de la fenêtre liées au rang¶

La sous-catégorie la plus simple des fonctions sensibles à l’ordre est la sous-catégorie de fonctions liées au rang.

La fonction la plus simple liée au rang est la fonction RANK. La fonction rank répertorie simplement le rang, qui correspond à l’ordre d’une ligne dans une fenêtre de lignes ordonnée. La première ligne de la fenêtre a le rang 1 ; la deuxième ligne de la fenêtre a le rang 2 ; etc. L’exemple ci-dessous montre l’ordre de classement des commerciaux en fonction du montant vendu :

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

Copy

La fonction RANK ne trie pas les lignes ; les lignes doivent déjà être en ordre dans la fenêtre avant que RANK soit appelée. Pour mettre les lignes en ordre, utilisez la sous-clause ORDER BY de la clause OVER.

Pour illustrer la sous-clause ORDER BY, 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. Voici un exemple d’utilisation d’une fonction de fenêtre sensible à l’ordre pour classer 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 liée au rang dépend de :

• La ligne individuelle transmise à la fonction.

• Les valeurs des autres lignes de la fenêtre transmises à la fonction.

• L’ordre de toutes les lignes dans la fenêtre.

Snowflake fournit différentes fonctions liées aux rangs. Les noms de ces fonctions et plus de détails sur la syntaxe des fonctions de fenêtre liées aux rangs figurent dans Fonctions de la fenêtre.

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ê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 :

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

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

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

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

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

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

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.

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