Fonctions de la fenêtre

Une fonction de fenêtre fonctionne sur un groupe (fenêtrage) de lignes liées.

Chaque fois qu’une fonction de fenêtrage est appelée, une ligne (la ligne actuelle de la fenêtre) et la fenêtre des lignes contenant la ligne actuelle sont transmises. La fonction de fenêtre renvoie une ligne de sortie pour chaque ligne d’entrée. La sortie dépend de la ligne individuelle transmise à la fonction et des valeurs des autres lignes de la fenêtre transmises à la fonction.

Certaines fonctions de fenêtre sont sensibles à l’ordre. Il existe deux principaux types de fonctions de fenêtre sensibles aux commandes :

  • Fonctions liées au rang.

  • Fonctions de cadre de fenêtre.

Les fonctions liées au rang répertorient des informations en fonction du « rang » d’une ligne. Par exemple, si vous classez les magasins par ordre descendant de profit par an, le magasin qui enregistre le plus de profit sera classé 1 ; le deuxième magasin le plus rentable sera classé 2, etc.

Les fonctions de cadre de fenêtre vous permettent d’effectuer des opérations de roulement, telles que le calcul d’un total cumulé ou d’une moyenne mobile, sur un sous-ensemble des lignes de la fenêtre.

Les utilisateurs qui ne sont pas familiarisés avec les fonctions de fenêtre, les fonctions liées aux rangs ou les fonctions de cadre de fenêtre peuvent lire la documentation conceptuelle dans Utilisation de fonctions de fenêtre.

Dans ce chapitre :

Vue d’ensemble

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.

Liste des fonctions qui prennent en charge les fenêtres

La liste ci-dessous montre toutes les fonctions de fenêtre.

Notez que certaines fonctions répertoriées en tant que fonctions de cadre de fenêtre ne prennent pas en charge tous les types possibles de cadres de fenêtre.

Nom de la fonction

Fenêtre

Cadre de fenêtre

Liées au rang

Remarques

Fenêtre générale

N’utilise pas la syntaxe de fenêtre standard.

Utilise une syntaxe différente de celle des autres fonctions de fenêtre.

Utilise une syntaxe différente de celle des autres fonctions de fenêtre.

STDDEV et STDDEV_SAMP sont des alias.

Alias pour VAR_POP.

Alias pour VAR_SAMP.

Liées au rang

Prend en charge les cadres de fenêtre cumulatifs basés sur des plages, mais pas les autres types de cadres de fenêtre.

Agrégation Bitwise

Agrégation booléenne

Hachage

Agrégation de données semi-structurées

Comptage de valeurs distinctes

Régression linéaire

Statistiques et probabilités

Estimation de cardinalité . (en utilisant HyperLogLog)

Alias pour HLL.

Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de HLL_ACCUMULATE ou HLL_COMBINE.

Estimation de similarité . (en utilisant MinHash)

Alias pour APPROXIMATE_SIMILARITY.

Estimation de fréquence . (en utilisant Économie d’espace)

Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de APPROX_TOP_K_ACCUMULATE ou APPROX_TOP_K_COMBINE.

Estimation du centile . (en utilisant t-Digest)

Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE.

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;
Copy

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.

Types de cadres de fenêtre

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

Cumulatif:

Permet de calculer les valeurs continues du début de la fenêtre jusqu’à la ligne courante ou de la ligne courante jusqu’à la fin de la fenêtre.

Glissant:

Permet de calculer les valeurs continues entre deux lignes (incluses) de la fenêtre, par rapport à la ligne courante.

Pour plus d’informations sur les cadres de fenêtre, y compris la syntaxe utilisée pour les cadres de fenêtre, consultez Syntaxe et utilisation du cadre de fenêtre.

Syntaxe et utilisation de la fenêtre

Syntaxe de la fenêtre

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

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.

Note

Les fonctions qui prennent en charge les cadres de fenêtre utilisent une syntaxe modifiée/améliorée. Pour plus de détails, voir Syntaxe et utilisation du cadre de fenêtre (dans ce chapitre).

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 et utilisation du cadre de fenêtre

Syntaxe des cadres de fenêtre

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

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

Note

Les cadres de fenêtre nécessitent que les données de la fenêtre soient dans un ordre connu. Par conséquent, la clause ORDER BY à l’intérieur de la clause OVER est requise pour la syntaxe du cadre de fenêtre, même si cette clause ORDER BY est facultative dans la syntaxe générale des fonctions de fenêtre.

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.

Conseils généraux

  • La syntaxe montre que toutes les sous-clauses de la clause OVER sont facultatives pour les fonctions de fenêtrage. Ce n’est pas un hasard (autrement dit, vous pouvez utiliser OVER sans aucune sous-clause entre parenthèses). Cela peut être utile dans des scénarios spécifiques (par exemple, désactiver le traitement en parallèle).

  • PARTITION BY n’est pas toujours compatible avec GROUP BY.

  • Si une requête utilise plusieurs fonctions de fenêtre, elle doit généralement partitionner le jeu de données d’entrée de chaque fonction de la même manière. Par exemple, la première instruction ci-dessous est plus susceptible d’être correcte que la seconde instruction ci-dessous :

    SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;
    
    SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
    
    Copy

    Le message d’erreur SQL compilation error: ... is not a valid group by expression indique souvent que les différentes colonnes des clauses « project » de l’instruction SELECT ne sont pas partitionnées de la même manière et peuvent par conséquent générer un nombre différent de lignes.

Exemples

Des exemples supplémentaires sont disponibles dans Utilisation de fonctions de fenêtre.

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);
Copy

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

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

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

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');
Copy

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

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

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