Une fonction de fenêtrage, également appelée fonction analytique, calcule des valeurs sur un groupe de lignes et renvoie un résultat unique pour chacune d'elles. Cela diffère d'une fonction d'agrégation, qui renvoie un résultat unique pour un groupe de lignes.
Une fonction de fenêtrage inclut une clause OVER
, qui définit une fenêtre de lignes autour de la ligne en cours d'évaluation. Pour chaque ligne, le résultat de la fonction de fenêtrage est calculé en utilisant la fenêtre de lignes sélectionnée comme entrée, en effectuant éventuellement une agrégation.
Grâce aux fonctions de fenêtrage, vous pouvez calculer des moyennes mobiles, classer des éléments, calculer des sommes cumulées et effectuer d'autres analyses.
Syntaxe des fonctions de fenêtrage
function_name ( [ argument_list ] ) OVER over_clause over_clause: { named_window | ( [ window_specification ] ) } window_specification: [ named_window ] [ PARTITION BY partition_expression [, ...] ] [ ORDER BY expression [ { ASC | DESC } ] [, ...] ] [ window_frame_clause ] window_frame_clause: { rows_range } { frame_start | frame_between } rows_range: { ROWS | RANGE }
Règles de notation
- Les crochets
[ ]
indiquent les clauses facultatives. - Les parenthèses
( )
correspondent à des parenthèses littérales. - La barre verticale
|
correspond à un opérateurOR
logique. - Les accolades
{ }
renferment un ensemble d'options. - Une virgule suivie de points de suspension entre crochets
[, ... ]
indique que l'élément précédent peut être répété dans une liste d'éléments séparés par des virgules.
Description
Une fonction de fenêtrage calcule les résultats sur un groupe de lignes. Vous pouvez utiliser la syntaxe suivante pour créer une fonction de fenêtrage:
function_name
: fonction qui effectue une opération de fenêtrage. Par exemple, la fonction de numérotationRANK()
peut être utilisée ici.argument_list
: arguments spécifiques à la fonction. Seules certaines fonctions les possèdent.OVER
: mot clé requis dans la syntaxe de la fonction de fenêtrage précédant la clauseOVER
.over_clause
: fait référence à une fenêtre qui définit un groupe de lignes dans une table sur laquelle appliquer une fonction de fenêtrage.window_specification
: définit les spécifications de la fenêtre.window_frame_clause
: définit le cadre de fenêtrage de la fenêtre.rows_range
: définit les lignes physiques ou une plage logique pour un cadre de fenêtrage.
Remarques
Une fonction de fenêtrage peut apparaître en tant qu'opérande d'expression scalaire à deux endroits de la requête:
- Dans la liste
SELECT
. Si la fonction de fenêtrage apparaît dans la listeSELECT
, sa liste d'arguments et sa clauseOVER
ne peuvent pas faire référence aux alias introduits dans la même liste SELECT. - Dans la clause
ORDER BY
. Si la fonction de fenêtrage apparaît dans la clauseORDER BY
de la requête, sa liste d'arguments peut faire référence à des alias de la listeSELECT
.
Une fonction de fenêtrage ne peut pas faire référence à une autre fonction de fenêtrage dans sa liste d'arguments ou sa clause OVER
, même de manière indirecte par le biais d'un alias.
Une fonction de fenêtrage est évaluée après l'agrégation. Par exemple, la clause GROUP BY
et les fonctions d'agrégation qui ne sont pas des fonctions de fenêtrage sont évaluées en premier.
Les fonctions d'agrégation étant évaluées avant les fonctions de fenêtrage, les fonctions d'agrégation peuvent être utilisées en tant qu'opérandes d'entrée pour les fonctions de fenêtrage.
Renvoie
Un résultat unique pour chaque ligne de l'entrée.
Définir la clause OVER
function_name ( [ argument_list ] ) OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
Description
La clause OVER
fait référence à une fenêtre qui définit un groupe de lignes dans une table sur laquelle appliquer une fonction de fenêtrage. Vous pouvez fournir une fenêtre named_window
définie dans votre requête ou définir les spécifications d'une nouvelle fenêtre.
Remarques
Si aucune fenêtre nommée ni spécification de fenêtre n'est fournie, toutes les lignes d'entrée sont incluses pour chaque ligne dans la fenêtre.
Exemples d'utilisation de la clause OVER
Ces requêtes utilisent des spécifications de fenêtre :
- Calculer un total général
- Calculer un sous-total
- Calculer une somme cumulée
- Calculer une moyenne mobile
- Calculer le nombre d'éléments dans une plage
- Obtenir l'élément le plus populaire dans chaque catégorie
- Obtenir la dernière valeur d'une plage
- Calculer le classement
Ces requêtes utilisent une fenêtre nommée :
- Obtenir la dernière valeur d'une plage
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Définir la spécification de fenêtre
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
Description
Définit les spécifications de la fenêtre.
named_window
: nom d'une fenêtre existante définie avec une clauseWINDOW
.
PARTITION BY
: divise les lignes d'entrée en partitions distinctes, sur lesquelles la fonction de fenêtrage est évaluée indépendamment.- Plusieurs expressions de partition sont autorisées dans la clause
PARTITION BY
. - Une expression ne peut pas contenir de types à virgule flottante ou non groupables, de constantes ou de fonctions de fenêtrage.
- Si cette clause facultative n'est pas utilisée, toutes les lignes de la table d'entrée comprennent une seule partition.
- Plusieurs expressions de partition sont autorisées dans la clause
ORDER BY
: définit l'ordre des lignes dans une partition. Cette clause est facultative dans la plupart des cas, mais elle peut être obligatoire dans certains cas pour les fonctions de navigation.window_frame_clause
: pour les fonctions analytiques d'agrégation, définit le cadre de fenêtrage dans la partition actuelle. Le cadre de fenêtrage détermine les éléments à inclure dans la fenêtre. Si cette clause est utilisée,ORDER BY
est requis, sauf pour les fenêtres entièrement illimitées.
Remarques
Si ni la clause ORDER BY
, ni la clause de cadre de fenêtrage ne sont présentes, le cadre de fenêtrage inclut toutes les lignes de cette partition.
Pour les fonctions analytiques d'agrégation, si la clause ORDER BY
est présente, mais pas la clause de cadre de fenêtrage, la clause de cadre de fenêtrage suivante est utilisée par défaut :
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Par exemple, les requêtes suivantes sont équivalentes :
SELECT book, LAST_VALUE(item)
OVER (ORDER BY year)
FROM Library
SELECT book, LAST_VALUE(item)
OVER (
ORDER BY year
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library
Règles d'utilisation d'une fenêtre nommée dans la spécification de fenêtre
Si vous utilisez une fenêtre nommée dans vos spécifications de fenêtre, les règles suivantes s'appliquent :
- Les spécifications de la fenêtre nommée peuvent être étendues avec de nouvelles spécifications que vous définissez dans la clause de spécification de fenêtre.
- Les définitions redondantes ne sont pas possibles. Si vous avez une clause
ORDER BY
dans la fenêtre nommée et la clause de spécification de fenêtre, une erreur est générée. L'ordre des clauses est important.
PARTITION BY
doit apparaître en premier, suivi deORDER BY
et dewindow_frame_clause
. Si vous ajoutez une fenêtre nommée, ses spécifications sont traitées en premier.--this works: SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce WINDOW item_window AS (ORDER BY purchases) --this does not work: SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popular FROM Produce WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Une fenêtre nommée et
PARTITION BY
ne peuvent pas apparaître ensemble dans la spécification de fenêtre. Si vous avez besoin dePARTITION BY
, vous devez l'ajouter à la fenêtre nommée.Vous ne pouvez pas faire référence à une fenêtre nommée dans une clause
ORDER BY
, une requête externe ou une sous-requête.
Exemples d'utilisation de la spécification de fenêtre
Ces requêtes définissent des partitions dans une fonction de fenêtrage:
- Calculer un sous-total
- Calculer une somme cumulée
- Obtenir l'élément le plus populaire dans chaque catégorie
- Obtenir la dernière valeur d'une plage
- Calculer le classement
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Ces requêtes incluent une fenêtre nommée dans une spécification de fenêtre :
- Obtenir la dernière valeur d'une plage
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Ces requêtes définissent l'ordre des lignes dans une partition :
- Calculer un sous-total
- Calculer une somme cumulée
- Calculer une moyenne mobile
- Calculer le nombre d'éléments dans une plage
- Obtenir l'élément le plus populaire dans chaque catégorie
- Obtenir la dernière valeur d'une plage
- Calculer le classement
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Définir la clause de cadre de fenêtrage
window_frame_clause:
{ rows_range } { frame_start | frame_between }
rows_range:
{ ROWS | RANGE }
frame_between:
{
BETWEEN unbounded_preceding AND frame_end_a
| BETWEEN numeric_preceding AND frame_end_a
| BETWEEN current_row AND frame_end_b
| BETWEEN numeric_following AND frame_end_c
}
frame_start:
{ unbounded_preceding | numeric_preceding | [ current_row ] }
frame_end_a:
{ numeric_preceding | current_row | numeric_following | unbounded_following }
frame_end_b:
{ current_row | numeric_following | unbounded_following }
frame_end_c:
{ numeric_following | unbounded_following }
unbounded_preceding:
UNBOUNDED PRECEDING
numeric_preceding:
numeric_expression PRECEDING
unbounded_following:
UNBOUNDED FOLLOWING
numeric_following:
numeric_expression FOLLOWING
current_row:
CURRENT ROW
La clause de cadre de fenêtrage définit le cadre de fenêtrage autour de la ligne actuelle dans une partition, sur lequel la fonction de fenêtrage est évaluée. Seules les fonctions analytiques d'agrégation peuvent utiliser une clause de cadre de fenêtrage.
rows_range
: clause qui définit un cadre de fenêtrage avec des lignes physiques ou une plage logique.ROWS
: calcule le cadre de fenêtrage en fonction des décalages physiques par rapport à la ligne actuelle. Par exemple, vous pouvez inclure deux lignes avant et après la ligne active.RANGE
: calcule le cadre de fenêtrage en fonction d'une plage logique de lignes autour de la ligne active, en fonction de la valeur de cléORDER BY
de la ligne active. La valeur de la plage fournie est ajoutée ou soustraite à la valeur de clé de la ligne actuelle afin de définir une limite de début ou de fin à la plage pour le cadre de fenêtrage. Dans un cadre de fenêtrage basé sur une plage, la clauseORDER BY
doit contenir exactement une expression, de type numérique.
Conseil : Si vous souhaitez utiliser une plage avec une date, utilisez
ORDER BY
avec la fonctionUNIX_DATE()
. Si vous souhaitez utiliser une plage avec un horodatage, utilisez la fonctionUNIX_SECONDS()
,UNIX_MILLIS()
ouUNIX_MICROS()
.frame_between
: crée un cadre de fenêtrage avec une limite inférieure et supérieure. La première représente la limite inférieure. La deuxième représente la limite supérieure. Seules certaines combinaisons de limites peuvent être utilisées, comme indiqué dans la syntaxe précédente.- Définissez le début du cadre de fenêtrage avec
unbounded_preceding
,numeric_preceding
,numeric_following
oucurrent_row
.unbounded_preceding
: le cadre de fenêtrage commence au début de la partition.numeric_preceding
ounumeric_following
: le début du cadre de fenêtrage dépend de la ligne actuelle.current_row
: le cadre de fenêtrage commence à la ligne actuelle.
- Définissez la fin du cadre de fenêtrage avec
numeric_preceding
,numeric_following
,current_row
ouunbounded_following
.numeric_preceding
ounumeric_following
: la fin du cadre de fenêtrage dépend de la ligne actuelle.current_row
: le cadre de fenêtrage se termine à la ligne actuelle.unbounded_following
: le cadre de fenêtrage se termine à la fin de la partition.
- Définissez le début du cadre de fenêtrage avec
frame_start
: crée un cadre de fenêtrage avec une limite inférieure. Le cadre de fenêtrage se termine à la ligne actuelle.unbounded_preceding
: le cadre de fenêtrage commence au début de la partition.numeric_preceding
: le début du cadre de fenêtrage dépend de la ligne actuelle.current_row
: le cadre de fenêtrage commence à la ligne actuelle.
numeric_expression
: expression représentant un type numérique. L'expression numérique doit être un entier ou un paramètre constant et non négatif.
Remarques
Si une limite dépasse le début ou la fin d'une partition, le cadre de fenêtrage n'inclut que les lignes de cette partition.
Vous ne pouvez pas utiliser de clause de cadre de fenêtrage avec des fonctions de navigation et des fonctions de numérotation, telles que RANK()
.
Exemples utilisant la clause de cadre de fenêtrage
Ces requêtes calculent des valeurs avec ROWS
:
- Calculer une somme cumulée
- Calculer une moyenne mobile
- Obtenir l'élément le plus populaire dans chaque catégorie
- Obtenir la dernière valeur d'une plage
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Ces requêtes calculent des valeurs avec RANGE
:
Ces requêtes calculent des valeurs avec une fenêtre partiellement ou totalement illimitée :
- Calculer un total général
- Calculer un sous-total
- Calculer une somme cumulée
- Obtenir l'élément le plus populaire dans chaque catégorie
- Calculer le classement
Ces requêtes calculent des valeurs avec des limites numériques :
- Calculer une somme cumulée
- Calculer une moyenne mobile
- Calculer le nombre d'éléments dans une plage
- Obtenir la dernière valeur d'une plage
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Ces requêtes calculent des valeurs avec la ligne actuelle comme limite :
Référencer une fenêtre nommée
SELECT query_expr,
function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ ASC | DESC [, ...] ]
[ window_frame_clause ]
named_window_expression:
named_window AS { named_window | ( [ window_specification ] ) }
Une fenêtre nommée représente un groupe de lignes dans une table sur laquelle appliquer une fonction de fenêtrage. Une fenêtre nommée est définie dans la clause WINDOW
et référencée dans la clause OVER
d'une fonction de fenêtrage.
Dans une clause OVER
, une fenêtre nommée peut apparaître seule ou intégrée dans une spécification de fenêtre.
Exemples
- Obtenir la dernière valeur d'une plage
- Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Filtrer les résultats avec la clause QUALIFY
La clause QUALIFY
peut être utilisée pour filtrer les résultats d'une fonction de fenêtrage.
Pour obtenir plus d'informations et des exemples, consultez la documentation sur la clause QUALIFY
.
Exemples de fonctions de fenêtrage
Dans ces exemples, l'élément en surbrillance correspond à la ligne actuelle. Les éléments en gras sont les lignes incluses dans l'analyse.
Tables courantes utilisées dans les exemples
Les tables suivantes sont utilisées dans les exemples de requêtes analytiques d'agrégation suivants : Produce
, Employees
et Farm
.
Table de production
Certains exemples font référence à une table appelée Produce
:
WITH Produce AS
(SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
UNION ALL SELECT 'banana', 2, 'fruit'
UNION ALL SELECT 'cabbage', 9, 'vegetable'
UNION ALL SELECT 'apple', 8, 'fruit'
UNION ALL SELECT 'leek', 2, 'vegetable'
UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce
+-------------------------------------+
| item | category | purchases |
+-------------------------------------+
| kale | vegetable | 23 |
| banana | fruit | 2 |
| cabbage | vegetable | 9 |
| apple | fruit | 8 |
| leek | vegetable | 2 |
| lettuce | vegetable | 10 |
+-------------------------------------+
Table des employés
Certains exemples font référence à une table appelée Employees
:
WITH Employees AS
(SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees
+-------------------------------------+
| name | department | start_date |
+-------------------------------------+
| Isabella | 2 | 1997-09-28 |
| Anthony | 1 | 1995-11-29 |
| Daniel | 2 | 2004-06-24 |
| Andrew | 1 | 1999-01-23 |
| Jacob | 1 | 1990-07-11 |
| Jose | 2 | 2013-03-17 |
+-------------------------------------+
Table de la ferme
Certains exemples font référence à une table appelée Farm
:
WITH Farm AS
(SELECT 'cat' as animal, 23 as population, 'mammal' as category
UNION ALL SELECT 'duck', 3, 'bird'
UNION ALL SELECT 'dog', 2, 'mammal'
UNION ALL SELECT 'goose', 1, 'bird'
UNION ALL SELECT 'ox', 2, 'mammal'
UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm
+-------------------------------------+
| animal | category | population |
+-------------------------------------+
| cat | mammal | 23 |
| duck | bird | 3 |
| dog | mammal | 2 |
| goose | bird | 1 |
| ox | mammal | 2 |
| goat | mammal | 2 |
+-------------------------------------+
Calculer un total général
Vous calculez ainsi un total général pour tous les éléments de la table Produce
.
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
- (banane, pomme, poireau, chou, laitue, chou frisé) = 54 achats au total
SELECT item, purchases, category, SUM(purchases)
OVER () AS total_purchases
FROM Produce
+-------------------------------------------------------+
| item | purchases | category | total_purchases |
+-------------------------------------------------------+
| banana | 2 | fruit | 54 |
| leek | 2 | vegetable | 54 |
| apple | 8 | fruit | 54 |
| cabbage | 9 | vegetable | 54 |
| lettuce | 10 | vegetable | 54 |
| kale | 23 | vegetable | 54 |
+-------------------------------------------------------+
Calculer un sous-total
Un sous-total est alors calculé pour chaque catégorie de la table Produce
.
- Fruit
- (banane, pomme) = 10 achats au total
- (Banane, pomme) = 10 achats au total
- Légume
- (poireau, chou, laitue, chou frisé) = 44 achats au total
- (poireau, chou, laitue, chou frisé) = 44 achats au total
- (poireau, chou, laitue, chou frisé) = 44 achats au total
- (poireau, chou, laitue, chou frisé) = 44 achats au total
SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS total_purchases
FROM Produce
+-------------------------------------------------------+
| item | purchases | category | total_purchases |
+-------------------------------------------------------+
| banana | 2 | fruit | 10 |
| apple | 8 | fruit | 10 |
| leek | 2 | vegetable | 44 |
| cabbage | 9 | vegetable | 44 |
| lettuce | 10 | vegetable | 44 |
| kale | 23 | vegetable | 44 |
+-------------------------------------------------------+
Calculer une somme cumulée
Cette opération calcule une somme cumulée pour chaque catégorie de la table Produce
. La somme est calculée par rapport à l'ordre défini à l'aide de la clause ORDER BY
.
- Fruit
- (banane, pomme) = 2 achats au total
- (Banane, pomme) = 10 achats au total
- Légume
- (poireau, chou, laitue, chou frisé) = 2 achats au total
- (poireau, chou, laitue, chou frisé) = 11 achats au total
- (poireau, chou, laitue, chou frisé) = 21 achats au total
- (poireau, chou, laitue, chou frisé) = 44 achats au total
SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_purchases
FROM Produce
+-------------------------------------------------------+
| item | purchases | category | total_purchases |
+-------------------------------------------------------+
| banana | 2 | fruit | 2 |
| apple | 8 | fruit | 10 |
| leek | 2 | vegetable | 2 |
| cabbage | 9 | vegetable | 11 |
| lettuce | 10 | vegetable | 21 |
| kale | 23 | vegetable | 44 |
+-------------------------------------------------------+
La procédure est identique à celle de l'exemple précédent. Vous n'avez pas besoin d'ajouter CURRENT ROW
en tant que limite, sauf pour des raisons de lisibilité.
SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS UNBOUNDED PRECEDING
) AS total_purchases
FROM Produce
Dans cet exemple, tous les éléments de la table Produce
sont inclus dans la partition. Seules les lignes précédentes sont analysées. L'analyse démarre deux lignes avant la ligne actuelle dans la partition.
- (banane, poireau, pomme, chou, laitue, chou frisé) = NUL
- (banane, poireau, pomme, chou, laitue, chou frisé) = NUL
- (banane, poireau, pomme, chou, laitue, chou frisé) = 2
- (banane, poireau, pomme, chou, laitue, chou frisé) = 4
- (banane, poireau, pomme, chou, laitue, chou frisé) = 12
- (banane, poireau, pomme, chou, laitue, chou frisé) = 21
SELECT item, purchases, category, SUM(purchases)
OVER (
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
) AS total_purchases
FROM Produce;
+-------------------------------------------------------+
| item | purchases | category | total_purchases |
+-------------------------------------------------------+
| banana | 2 | fruit | NULL |
| leek | 2 | vegetable | NULL |
| apple | 8 | fruit | 2 |
| cabbage | 9 | vegetable | 4 |
| lettuce | 10 | vegetable | 12 |
| kale | 23 | vegetable | 21 |
+-------------------------------------------------------+
Calculer une moyenne mobile
Cette action calcule une moyenne mobile dans la table Produce
.
La limite inférieure est une ligne avant la ligne actuelle. La limite supérieure est une ligne après la ligne actuelle.
- (banane, poireau, pomme, chou, laitue, chou frisé) = 2 achats en moyenne
- (banane, poireau, pomme, chou, laitue, chou frisé) = 4 achats en moyenne
- (banane, poireau, pomme, chou, laitue, chou frisé) = 6,3333 achats en moyenne
- (banane, poireau, pomme, chou, laitue, chou frisé) = 9 achats en moyenne
- (banane, poireau, pomme, chou, laitue, chou frisé) = 14 achats en moyenne
- (banane, poireau, pomme, chou, laitue, chou frisé) = 16,5 achats en moyenne
SELECT item, purchases, category, AVG(purchases)
OVER (
ORDER BY purchases
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS avg_purchases
FROM Produce
+-------------------------------------------------------+
| item | purchases | category | avg_purchases |
+-------------------------------------------------------+
| banana | 2 | fruit | 2 |
| leek | 2 | vegetable | 4 |
| apple | 8 | fruit | 6.33333 |
| cabbage | 9 | vegetable | 9 |
| lettuce | 10 | vegetable | 14 |
| kale | 23 | vegetable | 16.5 |
+-------------------------------------------------------+
Calculer le nombre d'éléments dans une plage
Dans cet exemple, la table Farm
indique le nombre d'animaux présentant une population similaire.
- (oie, chien, bœuf, chèvre, canard, chat) = 4 animaux compris entre 0 et 2
- (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux compris entre 1 et 3
- (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux compris entre 1 et 3
- (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux compris entre 1 et 3
- (oie, chien, bœuf, chèvre, canard, chat) = 4 animaux compris entre 2 et 4
- (oie, chien, bœuf, chèvre, canard, chat) = 1 animal compris entre 22 et 24
SELECT animal, population, category, COUNT(*)
OVER (
ORDER BY population
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS similar_population
FROM Farm;
+----------------------------------------------------------+
| animal | population | category | similar_population |
+----------------------------------------------------------+
| goose | 1 | bird | 4 |
| dog | 2 | mammal | 5 |
| ox | 2 | mammal | 5 |
| goat | 2 | mammal | 5 |
| duck | 3 | bird | 4 |
| cat | 23 | mammal | 1 |
+----------------------------------------------------------+
Obtenir l'élément le plus populaire dans chaque catégorie
Cet exemple présente l'élément le plus populaire dans chaque catégorie. Il définit comment les lignes d'une fenêtre sont partitionnées et classées dans chaque partition. La table Produce
est référencée.
- Fruit
- (banane, pomme) = la pomme est la plus populaire
- (banane, pomme) = la pomme est la plus populaire
- Légume
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
SELECT item, purchases, category, LAST_VALUE(item)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS most_popular
FROM Produce
+----------------------------------------------------+
| item | purchases | category | most_popular |
+----------------------------------------------------+
| banana | 2 | fruit | apple |
| apple | 8 | fruit | apple |
| leek | 2 | vegetable | kale |
| cabbage | 9 | vegetable | kale |
| lettuce | 10 | vegetable | kale |
| kale | 23 | vegetable | kale |
+----------------------------------------------------+
Obtenir la dernière valeur d'une plage
Dans cet exemple, nous obtenons l'élément le plus populaire dans un cadre de fenêtrage spécifique, à l'aide de la table Produce
. Le cadre de fenêtrage analyse jusqu'à trois lignes à la fois. Examinez attentivement les légumes de la colonne most_popular
.
Au lieu d'obtenir l'élément le plus populaire dans une catégorie spécifique, elle obtient l'élément le plus populaire dans une plage spécifique de cette catégorie.
- Fruit
- (banane, pomme) = la pomme est la plus populaire
- (banane, pomme) = la pomme est la plus populaire
- Légume
- (poireau, chou, laitue, chou frisé) = le chou est le plus populaire
- (poireau, chou, laitue, chou frisé) = la laitue est la plus populaire
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
- (poireau, chou, laitue, chou frisé) = le chou frisé est le plus populaire
SELECT item, purchases, category, LAST_VALUE(item)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce
+----------------------------------------------------+
| item | purchases | category | most_popular |
+----------------------------------------------------+
| banana | 2 | fruit | apple |
| apple | 8 | fruit | apple |
| leek | 2 | vegetable | cabbage |
| cabbage | 9 | vegetable | lettuce |
| lettuce | 10 | vegetable | kale |
| kale | 23 | vegetable | kale |
+----------------------------------------------------+
Cet exemple renvoie les mêmes résultats que l'exemple précédent, mais inclut une fenêtre nommée appelée item_window
. Certaines spécifications de fenêtre sont définies directement dans la clause OVER
et d'autres dans la fenêtre nommée.
SELECT item, purchases, category, LAST_VALUE(item)
OVER (
item_window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases)
Calculer le classement
Cet exemple calcule le classement de chaque employé au sein de son service en fonction de sa date de début. La spécification de fenêtre est définie directement dans la clause OVER
. La table Employees
est référencée.
- Service 1
- (Jacob, Antoine, André) = attribuer le rang 1 à Jacob
- (Jacob, Antoine, André) = attribuer le rang 2 à Antoine
- (Jacob, Antoine, André) = attribuer le rang 3 à André
- Service 2
- (Isabelle, Daniel, José) = attribuer le rang 1 à Isabelle
- (Isabelle, Daniel, José) = attribuer le rang 2 à Daniel
- (Isabelle, Daniel, José) = attribuer le rang 3 à José
SELECT name, department, start_date,
RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;
+--------------------------------------------+
| name | department | start_date | rank |
+--------------------------------------------+
| Jacob | 1 | 1990-07-11 | 1 |
| Anthony | 1 | 1995-11-29 | 2 |
| Andrew | 1 | 1999-01-23 | 3 |
| Isabella | 2 | 1997-09-28 | 1 |
| Daniel | 2 | 2004-06-24 | 2 |
| Jose | 2 | 2013-03-17 | 3 |
+--------------------------------------------+
Utiliser une fenêtre nommée dans une clause de cadre de fenêtrage
Vous pouvez définir une partie de votre logique dans une fenêtre nommée et une partie dans une clause de cadre de fenêtrage. Cette logique est combinée. Voici un exemple utilisant la table Produce
.
SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+-------------------------------------------------------+
| item | purchases | category | most_popular |
+-------------------------------------------------------+
| banana | 2 | fruit | apple |
| apple | 8 | fruit | apple |
| leek | 2 | vegetable | lettuce |
| cabbage | 9 | vegetable | kale |
| lettuce | 10 | vegetable | kale |
| kale | 23 | vegetable | kale |
+-------------------------------------------------------+
Vous pouvez également obtenir les résultats précédents avec les exemples suivants :
SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
item_window AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
item_window AS (b)
L'exemple suivant génère une erreur, car une clause de cadre de fenêtrage a été définie deux fois :
SELECT item, purchases, category, LAST_VALUE(item)
OVER (
item_window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce
WINDOW item_window AS (
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)