Syntaxe et utilisation des fonctions de fenêtre¶
Dans ce chapitre :
Snowflake prend en charge un grand nombre de fonctions SQL analytiques appelées fonctions de fenêtre. Les détails de chaque fonction sont documentés sur des pages de référence individuelles. L’objectif de cette section est de fournir des informations de référence générales qui s’appliquent à certaines ou à toutes les fonctions de fenêtre, y compris la syntaxe détaillée des principaux composants de la clause OVER :
Clause PARTITION BY
Clause ORDER BY
Syntaxe des cadres de fenêtre
Les utilisateurs qui ne sont pas familiarisés avec les fonctions de fenêtre peuvent lire la documentation conceptuelle dans Analyser les données avec les fonctions de fenêtre.
Syntaxe¶
<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
Où :
windowDefinition ::=
[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
Où :
windowFrameClause ::=
{
{ ROWS | RANGE } UNBOUNDED PRECEDING
| { ROWS | RANGE } <n> PRECEDING
| { ROWS | RANGE } CURRENT ROW
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
Paramètres¶
OVER( [ windowDefinition ] )Spécifie que la fonction est utilisée comme fonction de fenêtre et spécifie la fenêtre sur laquelle la fonction opère. La clause OVER doit contenir des parenthèses, mais elles peuvent être vides, selon les exigences de la fonction en question. Une clause OVER vide n’a pas de partitions et un cadre de fenêtre par défaut implicite.
PARTITION BY expr1Groupe les lignes en partitions, par produit, ville ou année, par exemple. Les lignes d’entrée sont groupées par partitions, puis la fonction est calculée sur chaque partition. La clause PARTITION BY est facultative ; vous pouvez analyser un ensemble de lignes comme une seule partition.
ORDER BY expr2Ordonne les lignes dans chaque partition, ou dans l’ensemble des lignes si aucune partition n’est spécifiée. Cette clause ORDER BY est distincte de la clause ORDER BY qui contrôle l’ordre de toutes les lignes renvoyées dans le résultat final d’une requête. Bien que la clause ORDERBY soit facultative pour certaines fonctions de fenêtre, elle est obligatoire pour d’autres. Par exemple, les fonctions de fenêtre de classement telles que RANK et NTILE exigent que leurs données d’entrée soient dans un ordre significatif.
La clause ORDER BY d’une fonction de fenêtre suit des règles similaires à celles de la clause ORDER BY principale dans une requête, par rapport à l’ordre ASC/DESC (croissant/décroissant) et le traitement de NULL. Pour plus de détails, voir ORDER BY.
Différentes fonctions gèrent la clause ORDERBY 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 appliquent un cadre de fenêtre implicite à la clause ORDER BY. (Pour plus d’informations, voir Notes sur l’utilisation des cadres de fenêtre.)
{ ROWS | RANGE }Spécifie le type ou le mode du cadre de fenêtre, qui définit soit un nombre physique de lignes (ROWS) ou un ensemble de lignes calculé logiquement (RANGE). Voir Cadres de fenêtre basés sur des plages ou sur des lignes.
Les deux types de cadre spécifient les points de départ et de fin, en utilisant soit des limites nommées implicites, soit des valeurs de décalage explicites. Une limite nommée est définie avec les mots-clés CURRENT ROW, UNBOUNDED PRECEDING et UNBOUNDED FOLLOWING. Les décalages explicites sont définis avec des nombres ou des intervalles (
n PRECEDINGoun FOLLOWING).
{ RANGE BETWEEN n PRECEDING | n FOLLOWING }Spécifie un cadre de fenêtre basé sur une plage avec des décalages explicites.
Les cadres de fenêtres RANGE BETWEEN avec des décalages explicites ne doivent en avoir qu’une seule expression ORDER BY. Les types de données suivants sont pris en charge pour cette expression :
DATE, TIMESTAMP, TIMESTAMP_LTZ , TIMESTAMP_NTZ (DATETIME) , TIMESTAMP_TZ
NUMBER, y compris INT, FLOAT et ainsi de suite.
TIME et les autres types de données Snowflake ne sont pas pris en charge lorsque ce type de cadre de fenêtre est utilisé. Pour d’autres cadres de fenêtre, d’autres types de données, tels que VARCHAR, peut être utilisé dans l’expression ORDER BY.
Pour les cadres de fenêtres RANGE BETWEEN, n doit être une constante non signée (une valeur numérique positive, y compris 0) ou une constante INTERVAL positive :
Si
expr2est un type de données numérique,ndoit être une constante non signée.Si
expr2est un TIMESTAMP type de données,ndoit être une INTERVAL constante. Par exemple,INTERVAL '12 hours'ouINTERVAL '3 days'.Si
expr2est un DATE type de données,npeut être une constante non signée ou une constante INTERVAL, mais le début et la fin du cadre doivent utiliser le même type de données pour la valeurn.
Lorsque l’expression ORDER BY est ascendante (ASC), la syntaxe
n FOLLOWINGsignifie « lignes avec des valeurs supérieures à (ou ultérieures à) x » etn PRECEDINGsignifie « lignes avec des valeurs inférieures à (ou antérieures à) x », où x est la valeur ORDER BY de la ligne actuelle. Lorsque l’expression ORDER BY est décroissante (DESC), c’est le contraire qui est vrai. (Les décalages0 PRECEDINGet0 FOLLOWINGsont équivalents à CURRENT ROW.)
Limites liées aux BETWEEN RANGE¶
Le sous-ensemble suivant de fonctions de fenêtre prend en charge la syntaxe RANGE BETWEEN avec des décalages explicites :
STDDEV, STDDEV_SAMP, STDDEV_POP (et alias)
VARIANCE , VARIANCE_SAMP, VARIANCE_POP (et alias)
De plus, notez que :
Les versions DISTINCT de ces fonctions ne prennent pas en charge cette syntaxe.
Les limitations suivantes s’appliquent lorsque la fonction de fenêtre COUNT est utilisée avec cette syntaxe.
Un seul argument d’entrée est pris en charge.
Les requêtes génériques
COUNT(table.*)ne sont pas prises en charge. Par exemple, vous ne pouvez pas spécifier :COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
Vous ne pouvez pas spécifier un cadre qui entraîne une inversion logique des positions de début et de fin de l’image. Par exemple, les cadres suivants renvoient des erreurs car la ligne de fin du cadre précède la ligne de début :
ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
Comportement RANGE BETWEEN lorsque l’expression ORDER BY contient des valeurs NULL¶
Notez le comportement suivant lorsqu’un le cadre de fenêtre RANGE BETWEEN est utilisé et que la colonne ORDER BY contient des valeurs NULL :
Lorsque la clause ORDER BY spécifie NULLS FIRST, les lignes avec des valeurs NULL dans la colonne ORDER BY sont incluses dans les cadres UNBOUNDED PRECEDING.
Lorsque la clause ORDER BY spécifie NULLS LAST, les lignes avec des valeurs NULL dans la colonne ORDER BY sont incluses dans les cadres UNBOUNDED FOLLOWING.
Les lignes avec des valeurs NULL dans la colonne ORDER BY sont incluses dans une limite de cadre à décalage explicite uniquement lorsque la valeur ORDER BY de la ligne actuelle est NULL.
Voir Exemples RANGE BETWEEN avec valeurs NULL dans la clause ORDER BY.
Notes sur l’utilisation des cadres de fenêtre¶
Toutes les fonctions de fenêtre prennent en charge les cadres de fenêtre. Cependant, la prise en charge de la syntaxe de cadre de fenêtre varie selon la fonction. 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 classées (telles que COUNT, MAX, MIN et SUM), la valeur par défaut est le cadre de fenêtre suivant (conformément à la norme ANSI) :
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Pour les fonctions de classement (telles que 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 ce comportement n’est pas conforme à la norme ANSI.
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, définissez un cadre de fenêtre explicite.
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 généralement.
Exemples¶
Cette section contient des exemples qui montrent comment utiliser les fonctions de fenêtre de différentes manières. Pour des exemples supplémentaires, voir Analyser les données avec les fonctions de fenêtre et les pages pour les fonctions individuelles.
Exemple d’introduction¶
Supposons que vous possédiez une chaîne de magasins. La requête suivante montre le pourcentage du bénéfice total de la chaîne généré par chaque magasin. La requête utilise la fonction RATIO_TO_REPORT, qui prend une valeur (net_profit) de la ligne actuelle et la divise par la somme des valeurs correspondantes 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;
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 |
+-----------+------------+-------------------------+
Cadre de fenêtre avec une position de départ non délimitée¶
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écutez une requête qui utilise un cadre de fenêtre avec une position de départ illimitée et affichez la sortie. Renvoyer des valeurs COUNT, SUM, AVG, MIN et MAX cumulatives pour chaque ligne dans chaque partition :
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 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 ROWS 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Cadres de fenêtre avec décalages explicites¶
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');
Retourner les résultats de la fonction MIN pour deux colonnes (numérique et chaîne) sur des fenêtres coulissantes avant, après et y compris la ligne actuelle :
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 |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Retourner les résultats de la fonction MAX pour deux colonnes (numérique et chaîne) sur des fenêtres coulissantes avant, après et y compris la ligne actuelle :
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 de classement¶
L’exemple suivant montre comment classer les ventes en fonction du montant total (en dollars) vendu par chaque commercial. La clause ORDER BY dans la clause OVER trie les totaux par ordre décroissant (du plus élevé au plus bas). La requête calcule le rang de chaque commercial par rapport à tous les autres commerciaux.
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);
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 |
+------------------+------------------+------------+
La sortie n’est pas nécessairement classée par rang. Pour afficher les résultats classés par rang, spécifiez une clause ORDER BY pour la requête elle-même (en plus de la clause ORDER BY pour la fonction de fenêtre), comme indiqué ici :
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 |
+------------------+------------------+------------+
L’exemple précédent comporte deux clauses ORDER BY :
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 |
+------------------+------------------+------------+
Exemple RANGE BETWEEN avec des décalages numériques explicites¶
L’exemple suivant utilise la syntaxe RANGE BETWEEN avec des décalages numériques explicites. Pour exécuter cet exemple, suivez ces instructions : Créer et charger la table menu_items. Pour des exemples similaires qui utilisent des décalages INTERVAL, voir Utilisation d’agrégations fenêtrées pour les calculs glissants.
La requête suivante calcule le coût moyen des marchandises vendues pour les catégories d’éléments de menu disponibles dans un food truck. La fonction de fenêtre ne partitionne pas les résultats ; par conséquent, les moyennes sont calculées sur le jeu de résultats, sous réserve d’un cadre basé sur une plage.
La limite du cadre est la valeur du coût des marchandises dans la ligne actuelle, plus deux (la première ligne = 0,50 + 2,00, par exemple). Les lignes sont éligibles au cadre lorsqu’elles se situent dans cette plage de deux dollars.
SELECT menu_category, menu_cogs_usd,
AVG(menu_cogs_usd)
OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
FROM menu_items
WHERE menu_category IN('Beverage','Dessert','Snack')
GROUP BY menu_category, menu_cogs_usd
ORDER BY menu_category, menu_cogs_usd;
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage | 0.50 | 1.18333 |
| Beverage | 0.65 | 1.37857 |
| Beverage | 0.75 | 1.50000 |
| Dessert | 0.50 | 1.18333 |
| Dessert | 1.00 | 1.87500 |
| Dessert | 1.25 | 2.05000 |
| Dessert | 2.50 | 3.16666 |
| Dessert | 3.00 | 3.50000 |
| Snack | 1.25 | 2.05000 |
| Snack | 2.25 | 2.93750 |
| Snack | 4.00 | 4.00000 |
+---------------+---------------+----------+
Par exemple, la valeur avg_cogs de la première ligne est 1,1833. Elle est calculée comme la somme de toutes les valeurs menu_cogs_usd comprises entre 0,50 et 2,50, divisées par le nombre de ces lignes :
(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333
L’avant-dernière ligne a une valeur avg_cogs de 2,93750. Elle est calculée comme la somme de toutes les valeurs menu_cogs_usd comprises entre 2,25 et 4,25, divisées par le nombre de ces lignes :
(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750
La dernière ligne renvoie 4,0 pour avg_cogs et menu_cogs_usd. Ce résultat est précis car seule cette ligne appartient au cadre ; 4,0 est la valeur menu_cogs_usd maximum dans l’ensemble du résultat, de sorte qu’il devient un cadre à une seule ligne. Il n’y a pas de lignes « suivantes ».
Notez que cette requête a une clause ORDER BY pour la fonction de fenêtre et une clause ORDER BY pour les résultats finaux de la requête. La sortie ORDER BY finale n’influence pas le calcul des résultats de la fonction de fenêtre. Le jeu de résultats ordonné pour le calcul de la fonction est un jeu de résultats intermédiaire que la requête finale n’affiche pas.
Exemples RANGE BETWEEN avec valeurs NULL dans la clause ORDER BY¶
La table nulls contient cinq lignes et deux ont des valeurs NULL dans la colonne c1. Créer et charger la table comme suit :
CREATE OR REPLACE TABLE nulls(c1 int, c2 int);
INSERT INTO nulls VALUES
(1,10),
(2,20),
(3,30),
(NULL,20),
(NULL,50);
Quand NULLS LAST est spécifié et le cadre de la fenêtre utilise des décalages explicites, des lignes avec NULL dans c1 ne sont inclus dans le cadre que lorsque la valeur ORDER BY de la ligne actuelle est NULL. La requête suivante renvoie une somme de 50 quand la rangée 3 est la ligne actuelle. La ligne NULL suivante n’est pas incluse dans le cadre.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 30 |
| 2 | 20 | 60 |
| 3 | 30 | 50 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
Quand NULLS LAST est spécifié et que le cadre de la fenêtre utilise UNBOUNDED FOLLOWING, les lignes avec NULL dans c1 sont inclus dans le cadre. La requête suivante renvoie une somme de 120 quand la rangée 3 est la ligne actuelle. Les deux lignes NULL suivantes sont incluses dans le cadre.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 130 |
| 2 | 20 | 130 |
| 3 | 30 | 120 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
Créer et charger la table heavy_weather¶
Pour créer et insérer des lignes dans la table heavy_weather, qui est utilisée dans certaines fonctions de fenêtre exemples, exécutez ce script.
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('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')
;