Appels de fonctions de fenêtrage

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érateur OR 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érotation RANK() 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 clause OVER.
  • 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 liste SELECT, sa liste d'arguments et sa clause OVER 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 clause ORDER BY de la requête, sa liste d'arguments peut faire référence à des alias de la liste SELECT.

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 :

Ces requêtes utilisent une fenêtre nommée :

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.

  • 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.
  • 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 de ORDER BY et de window_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 de PARTITION 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:

Ces requêtes incluent une fenêtre nommée dans une spécification de fenêtre :

Ces requêtes définissent l'ordre des lignes dans une partition :

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 clause ORDER 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 fonction UNIX_DATE(). Si vous souhaitez utiliser une plage avec un horodatage, utilisez la fonction UNIX_SECONDS(), UNIX_MILLIS() ou UNIX_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 ou current_row.
      • unbounded_preceding : le cadre de fenêtrage commence au début de la partition.
      • numeric_preceding ou numeric_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 ou unbounded_following.
      • numeric_preceding ou numeric_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.

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

Ces requêtes calculent des valeurs avec RANGE :

Ces requêtes calculent des valeurs avec une fenêtre partiellement ou totalement illimitée :

Ces requêtes calculent des valeurs avec des limites numériques :

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

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

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)