Fonctions de la fenêtre¶

Qu’est-ce qu’une fenêtre ?¶

Un fenêtrage est un groupe de lignes liées. Par exemple, une fenêtre peut être définie en fonction d’horodatages, toutes les lignes du même mois étant regroupées dans la même fenêtre. De plus, une fenêtre peut être définie en fonction de l’emplacement, toutes les lignes d’une ville particulière étant regroupées dans la même fenêtre.

Un fenêtrage peut comporter zéro, une ou plusieurs lignes. Pour plus de simplicité, la documentation de Snowflake indique généralement qu’une fenêtre contient plusieurs lignes.

Qu’est-ce qu’une fonction de fenêtre ?¶

Une fonction de fenêtre est une fonction qui opère sur une fenêtre de lignes.

Une fonction de fenêtre reçoit généralement deux paramètres :

• Une ligne. Plus précisément, une fonction de fenêtre reçoit entre 0 et plusieurs expressions. Dans presque tous les cas, au moins une de ces expressions fait référence à une colonne dans cette ligne. (La plupart des fonctions de fenêtre nécessitent au moins une colonne ou une expression, mais quelques fonctions de fenêtre, telles que certaines fonctions liées aux rangs, ne nécessitent pas de colonne ou d’expression explicite.)

• Une fenêtre de lignes associées qui inclut cette ligne. La fenêtre peut être la table entière ou un sous-ensemble des lignes de la table.

  Pour les fonctions hors fenêtre, tous les arguments sont généralement transmis explicitement à la fonction, par exemple :

  MY_FUNCTION(argument1, argument2, …)

  Les fonctions de fenêtre se comportent différemment. bien que la ligne courante soit transmise en tant qu’argument de la manière habituelle, la fenêtre est transmise à travers une clause séparée, appelée clause OVER. La syntaxe de la clause OVER est documentée ultérieurement.

Fonctions de fenêtre et valeurs NULL¶

Certaines fonctions ignorent les valeurs NULL. Par exemple, AVG calcule la moyenne des valeurs 1, 5 et NULL sur 3, en fonction de la formule suivante :

(1 + 5) / 2 = 3

Dans le numérateur et le dénominateur, seules les valeurs autres que NULL sont utilisées.

Si toutes les valeurs transmises à la fonction sont NULL, alors la fonction renvoie NULL.

Certaines fonctions de fenêtre peuvent recevoir plusieurs colonnes. Par exemple :

SELECT COUNT(col1, col2) FROM table1;

Dans ces cas, la fonction ignore une ligne si l’une des colonnes est NULL.

Par exemple, dans la requête suivante, COUNT renvoie 1, pas 4, car trois des quatre lignes contiennent au moins une valeur NULL dans les colonnes sélectionnées :

Créer une table et la remplir avec des valeurs :

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

Interrogez la table :

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

De même, si SUM est appelé avec une expression faisant référence à deux colonnes ou plus, et si l’une ou plusieurs de ces colonnes est NULL, alors l’expression donne un résultat NULL et la ligne est ignorée :

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

Notez que ce comportement diffère du comportement de GROUP BY, qui ne supprime pas les lignes lorsque certaines colonnes sont NULL :

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy

Exemple d’introduction¶

Supposons que vous possédiez une chaîne de magasins. La requête suivante indique le pourcentage du profit total de la chaîne généré par chaque magasin. La requête utilise la fonction RATIO_TO_REPORT, qui prend une valeur (par exemple, profit_net) de la ligne actuelle et la divise par la somme des valeurs correspondantes (profit_net) de toutes les autres lignes :

Créer et charger la table :

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

Interrogez la table :

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

Copy

Qu’est-ce qu’un cadre de fenêtre ?¶

Un cadre de fenêtre est un sous-groupe de lignes d’un fenêtrage. La création de sous-ensembles vous permet de calculer des valeurs uniquement sur le sous-groupe de lignes spécifié. Des cadres de fenêtre sont spécifiés comme une sous-clause supplémentaire dans la sous-clause ORDER BY de la clause OVER.

Qu’est-ce qu’une fonction liée au rang ?¶

Une fonction liée au rang indique le rang (la position) de la ligne en cours dans la fenêtre. Pour être utile, une fonction liée au rang doit être appelée sur une fenêtre de lignes déjà triée selon un critère utile. Par exemple, si les lignes d’une fenêtre contiennent des informations sur la rentabilité de magasins individuels dans une chaîne de magasins, et si les lignes sont triées par ordre décroissant de rentabilité, les rangs des lignes de la fenêtre (1, 2, 3, etc.) représentent la rentabilité des magasins (les plus rentables, les deuxièmes plus rentables, les troisièmes plus rentables, etc.). Par conséquent, les fonctions liées aux rangs sont toujours des fonctions sensibles à l’ordre et nécessitent la sous-clause ORDER BY de la clause OVER().

La fonction RANK renvoie une valeur entière positive comprise entre 1 et le nombre de lignes de la fenêtre (inclus).

Comme pour toute autre fonction de fenêtre, quand une fonction liée au rang est appelée, vous ne lui transmettez pas explicitement une ligne (ou plus précisément, 1 ou plusieurs colonnes d’une ligne, ou des expressions basées sur les colonnes de la ligne), mais aussi une fenêtre de lignes. La sortie de la fonction 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.

Avec la fonction RANK, vous pouvez :

• 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é.

Un rapport sur les ventes utilisant le classement peut ressembler à ce qui suit :

+------------+-------+------+
| Name       | Sales | Rank |
|------------+-------+------|
| Smith      |  2000 |    1 |
| Jones      |  1500 |    2 |
| Torkelson  |  1100 |    3 |
| Dolenz     |  1000 |    4 |
+------------+-------+------+

La section Exemples (dans cette rubrique) explique comment générer un tel rapport.

Syntaxe de la fenêtre¶

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )

La clause OVER indique que la fonction est utilisée en tant que fonction de fenêtre.

La sous-clause PARTITION BY permet de regrouper les lignes en sous-groupes, par exemple par ville, par année, etc. La clause PARTITION BY est facultative. Vous pouvez analyser tout un groupe de lignes sans le scinder en sous-groupes.

La clause ORDER BY ordonne les lignes dans la fenêtre. (Cela est différent du fait de trier la sortie d’une requête. Une requête peut avoir une clause ORDER BY qui contrôle l’ordre des lignes dans une fenêtre et une clause séparée ORDER BY, en dehors de la clause OVER, qui contrôle l’ordre de sortie de la requête entière.) Bien que la clause ORDER BY soit facultative pour certaines fonctions de fenêtre, elle est obligatoire pour d’autres. Par exemple, les fonctions de cadre de fenêtre et les fonctions liées au rang exigent que les données soient dans un ordre explicite et nécessitent donc une sous-clause ORDER BY.

Notes sur l’utilisation de fenêtres¶

• La clause OVER spécifie la fenêtre sur laquelle la fonction fonctionne. La clause se compose de l’un des éléments suivants (ou des deux) :

  • PARTITION BY expr1 : sous-clause qui définit la partition, le cas échéant, de la fenêtre (c’est-à-dire comment les données seront regroupées avant d’appliquer la fonction).

  • ORDER BY expr2 : sous-clause qui détermine l’ordre des lignes dans la fenêtre.

    La sous-clause ORDER BY suit des règles similaires à celles de la clause ORDER BY de la requête, par exemple en ce qui concerne les traitements ASC/DESC (croissant/décroissant) et NULL. Pour plus de détails sur les options supplémentaires prises en charge, voir la construction de requête ORDER BY.

    Différentes fonctions gèrent la clause ORDER BY de différentes manières :

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

    Note

    Pour plus de clarté, Snowflake recommande d’éviter les cadres de fenêtre implicites. Si votre requête utilise un cadre de fenêtre, faites-en un cadre de fenêtre explicite.

Syntaxe des cadres de fenêtre¶

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )

Où :

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

Copy

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

Copy

Notes sur l’utilisation du cadre de fenêtre¶

• Pour les cadres de fenêtres cumulatifs :

  • ROWS calcule le résultat de la ligne courante en utilisant toutes les lignes depuis le début ou la fin de la partition jusqu’à la ligne courante (selon la sous-clause ORDER BY spécifiée).

  • RANGE est similaire à ROWS, sauf qu’il ne calcule que le résultat pour les lignes qui ont la même valeur que la ligne courante (selon la sous-clause ORDER BY spécifiée).

• Pour les cadres de fenêtre glissants :

  • ROWS est inclusif et est toujours relatif à la ligne courante.

  • RANGE n’est pas pris en charge.

  • Si le nombre spécifié de ROWS précédentes ou suivantes dépasse les limites de la fenêtre, Snowflake considère la valeur comme NULL.

• Si aucun cadre de fenêtre n’est spécifié, la valeur par défaut est différente selon la fonction :

  • Pour les fonctions non liées au rang (COUNT, MAX, MIN, SUM), la valeur par défaut est le cadre de fenêtre cumulatif suivant (conformément à la norme ANSI) :

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  • Pour les fonctions liées au rang (FIRST_VALUE, LAST_VALUE, NTH_VALUE), la valeur par défaut est la fenêtre entière :

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    Notez que cela diffère de la norme ANSI.

Syntaxe de la fonction de fenêtre liée au rang¶

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

• Étant donné que les fonctions de fenêtre liées aux rangs sont sensibles à l’ordre, la clause ORDER BY est obligatoire et non facultative.

• Pour certaines fonctions liées aux rangs, telles que RANK, aucun argument d’entrée n’est requis. Dans le cas de la fonction RANK, la valeur renvoyée est basée uniquement sur le rang, déterminé par la sous-clause ORDER BY de la clause OVER. Par conséquent, il est inutile de transmettre un nom de colonne ou une expression à la fonction RANK.

Notes sur l’utilisation des fonctions de fenêtre liées au rang¶

• Une fonction peut être à la fois une fonction liée au rang et une fonction de fenêtre. Par exemple, vous pouvez classer les lignes dans une fenêtre glissante.

Exemples de cadres de fenêtre cumulatifs¶

Créer et remplir une table avec des valeurs :

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);

Exécuter une requête qui utilise un cadre de fenêtre cumulatif et affiche le résultat. Renvoyer un nombre, une somme, un nombre min et un nombre max cumulatifs pour les lignes de la fenêtre spécifiée pour la table :

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

Renvoyer un nombre, une somme, un nombre min et un nombre max par plage cumulatifs pour les lignes de la fenêtre spécifiée pour la table :

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Renvoyer les mêmes résultats que la requête ci-dessus en utilisant la sémantique du cadre de fenêtre par défaut (c’est-à-dire RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) :

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Exemples de cadres de fenêtre glissants¶

Créer et remplir une table avec des valeurs :

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');

Renvoyer les valeurs minimales de deux colonnes (numérique et chaîne) dans les fenêtres glissantes avant, après et englobant la ligne en cours :

select p, o, i AS i_col,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
    s,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |     2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |     3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 |  NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |     5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |     6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |     7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 |  NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Renvoyer les valeurs maximales de deux colonnes (numérique et chaîne) dans les fenêtres glissantes avant, après et englobant la ligne en cours :

SELECT p, o, i AS i_col,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
    s,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |     2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |     3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 |  NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |     5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |     6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |     7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Renvoyer la somme d’une colonne numérique dans les fenêtres glissantes avant, après et englobant la ligne en cours :

SELECT p, o, r AS r_col,
    SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
|   P |  O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 |  1 |    70 |        NULL |         180 |         280 |
| 100 |  2 |    30 |        NULL |         170 |         310 |
| 100 |  3 |    40 |          70 |          80 |         310 |
| 100 |  4 |    90 |         100 |          30 |         240 |
| 100 |  5 |    50 |         140 |        NULL |         210 |
| 100 |  6 |    30 |         160 |        NULL |         170 |
| 200 |  7 |    40 |        NULL |         110 |         150 |
| 200 |  8 |  NULL |        NULL |         110 |         150 |
| 200 |  9 |  NULL |          40 |          90 |         150 |
| 200 | 10 |    20 |          40 |        NULL |         110 |
| 200 | 11 |    90 |          40 |        NULL |         110 |
| 300 | 12 |    30 |        NULL |        NULL |          30 |
| 400 | 13 |    20 |        NULL |        NULL |          20 |
+-----+----+-------+-------------+-------------+-------------+

Exemples de fonctions liées au rang¶

L’exemple suivant montre comment classer les commerciaux en fonction du montant total (en dollars) vendu par chacun. La requête utilise la clause OVER pour créer une fenêtre contenant le total des ventes de chaque commercial. La sous-clause ORDER BY de la clause OVER place ces lignes dans l’ordre décroissant en fonction du total des ventes (c’est-à-dire du plus élevé au plus bas). La requête calcule ensuite le rang de chaque commercial par rapport aux autres commerciaux.

Commencer par créer la table et insérer les données :

CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
    ('Smith', 600),
    ('Jones', 1000),
    ('Torkelson', 700),
    ('Dolenz', 800);

Copy

Interroger maintenant les données :

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

La sortie ne s’affiche pas nécessairement par rang. Vous pouvez forcer l’affichage de la sortie dans l’ordre par rang à l’aide d’une clause ORDER BY (en dehors de la clause OVER), comme indiqué ci-dessous :

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

L’exemple précédent contient deux ORDER BY clauses :

• L’une contrôle l’ordre du classement.

• L’autre contrôle l’ordre de la sortie.

Ces clauses sont indépendantes. Par exemple, vous pouvez classer les classements en fonction du total des ventes (comme précédemment), mais en ordonnant les lignes de sortie en fonction du nom de famille du commercial :

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz           |              800 |          2 |
| Jones            |             1000 |          1 |
| Smith            |              600 |          4 |
| Torkelson        |              700 |          3 |
+------------------+------------------+------------+

Copy