Une fonction analytique calcule des valeurs sur un groupe de lignes et renvoie un résultat unique pour chaque ligne. Ceci est différent d’une fonction d’agrégation, qui renvoie un seul résultat pour un groupe de lignes.

Une fonction analytique inclut une clause OVER, qui définit une fenêtre de lignes autour de la ligne évaluée. Pour chaque ligne, le résultat de la fonction analytique est calculé en utilisant la fenêtre de lignes sélectionnée comme entrée, en effectuant éventuellement l’agrégation.

Avec les fonctions analytiques, vous pouvez calculer des moyennes mobiles, classer des éléments, calculer des sommes cumulatives et effectuer d’autres analyses.

Les fonctions suivantes peuvent être utilisées comme fonctions analytiques : fonctions de navigation, fonctions de numérotation et fonctions analytiques agrégées

Syntaxe des fonctions analytiques

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

Règles de notation

• Les crochets «  » indiquent les clauses facultatives.
• Parenthèses « () » indique les parenthèses littérales.
• La barre verticale « | » indique un OU logique.
• Les accolades « {} » englobent un ensemble d’options.
• Une virgule suivie d’une ellipse entre crochets «  » indique que l’élément précédent peut se répéter dans une liste séparée par des virgules.

Description

Une fonction analytique calcule les résultats sur un groupe de lignes. Vous pouvez utiliser la syntaxe suivante pour construire une fonction analytique :

• analytic_function_name : La fonction qui effectue une opération analytique.Par exemple, la fonction de numérotation RANK() peut être utilisée ici.
• argument_list : Arguments spécifiques à la fonction analytique.Certaines fonctions en ont, d’autres non.
• OVER: Mot-clé requis dans la syntaxe de la fonction analytique précédant la clause OVER.
• over_clause: Référence une fenêtre qui définit un groupe de lignes dans une table sur laquelle utiliser une fonction analytique.
• window_specification: Définit les spécifications de la fenêtre.
• window_frame_clause: Définit le cadre de la fenêtre pour la fenêtre.
• rows_range : Définit les lignes physiques ou la plage alogique d’un cadre de fenêtre.

Notes

Une fonction analytique peut apparaître sous la forme d’un opérande d’expression scalaire à deux endroits dans la requête :

• La liste SELECT. Si la fonction analytique apparaît dans la liste SELECT, sa liste d’arguments et la clause OVER ne peuvent pas faire référence à des alias introduits dans la même liste de SÉLECTION.
• La clause ORDER BY. Si la fonction analytique apparaît dans la clause ORDER BY de la requête, sa liste d’arguments peut faire référence à des alias de liste SELECT.

Une fonction analytique ne peut pas faire référence à une autre fonction analytique dans sa liste d’arguments ou sa clause OVER, même indirectement via un alias.

Une fonction analytique est évaluée après l’agrégation. Par exemple, la clause GROUP BY et les fonctions agrégées non analytiques sont évaluées en premier.Étant donné que les fonctions d’agrégation sont évaluées avant les fonctions analytiques, les fonctions d’agrégation peuvent être utilisées comme opérandes d’entrée pour les fonctions analytiques.

Renvoie

Un seul résultat pour chaque ligne de l’entrée.

Définition de la clause OVER

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

Description

La clause OVER fait référence à une fenêtre qui définit un groupe de lignes dans une table sur laquelle utiliser une fonction analytique. Vous pouvez fournir un named_window défini dans votre requête, ou vous pouvez définir les spécifications d’une nouvelle fenêtre.

Notes

Si aucune fenêtre nommée ni spécification de fenêtre n’est fournie, toutes les lignes d’entrée sont incluses dans la fenêtre pour chaque ligne.

Exemples utilisant la clause OVER

Ces requêtes utilisent les spécifications de la fenêtre :

• Calculer un total général
• Calculer un sous-total
• Calculer une somme cumulative
• Calculer une moyenne mobile
• Calculer le nombre d’éléments dans une plage
• Obtenir l’élément le plus populaire dans chaque catégorie
• Obtenez la dernière valeur d’une plage
• Rang de calcul

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être

Définir la spécification de la fenêtre

window_specification: ] ] 

Description

Définit les spécifications de la fenêtre.

• named_window : Nom d’une fenêtre existante définie avec une clause WINDOW.

• PARTITION BY: Décompose les lignes d’entrée en partitions séparées, sur lesquelles la fonction analytique 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, de types non groupables, de constantes ou de fonctions analytiques.
  • Si cette clause facultative n’est pas utilisée, toutes les lignes de la table d’entrée compriment une seule partition.
• ORDER BY : Définit l’ordre des lignes dans une partition.Cette clause est facultative dans la plupart des situations, mais est requise dans certains cas pour les fonctions de navigation.
• window_frame_clause: Pour aggregate analyticfunctions, définit le cadre de la fenêtre dans la partition en cours.Le cadre de la fenêtre détermine ce qu’il faut inclure dans la fenêtre.Si cette clause est utilisée, ORDER BY est requis sauf pour les fenêtres entièrement non arrondies.

Notes

Si ni la clause ORDER BY ni la clause cadre de fenêtre ne sont présentes, le cadre de fenêtre inclut toutes les lignes de cette partition.

Pour les fonctions analytiques agrégées, si la clause ORDER BY est présente mais que la clause window frame ne l’est pas, la clause window frame 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, ces règles 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 window specification.
• Vous ne pouvez pas avoir de définitions redondantes. 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 levée.

• L’ordre des clauses compte. PARTITION BY doit venir en premier, suivi de ORDER BY et window_frame_clause. Si vous ajoutez une fenêtre nommée, ses spécifications de fenêtre 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_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW 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 window. Si vous avez besoin de PARTITION BY, ajoutez-le à 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 utilisant la spécification de fenêtre

Ces requêtes définissent des partitions dans une fonction analytique :

• Calculez un sous-total
• Calculez une somme cumulative
• Obtenez l’élément le plus populaire dans chaque catégorie
• Obtenez la dernière valeur d’une plage
• Calculez le rang
• Utilisez une fenêtre nommée dans une clause de cadre de fenêtre

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être

Ces requêtes définissent comment les lignes sont ordonnées dans une partition:

• Calculer un sous-total
• Calculer une somme cumulative
• 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 rang
• Utiliser une fenêtre nommée dans une clause de cadre de fenêtre

Définir le cadre de fenêtre clause

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 | }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 PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

La clause cadre de fenêtre définit le cadre de fenêtre autour de la ligne courante dans la partition, sur laquelle la fonction analytique est évaluée.Seules les fonctions analytiques agrégées peuvent utiliser une clause de cadre de fenêtre.

• rows_range: Une clause qui définit un cadre de fenêtre avec des lignes physiques ou une plage logique.

  • ROWS: Calcule le cadre de la fenêtre en fonction des décalages physiques de la ligne actuelle. Par exemple, vous pouvez inclure deux lignes avant et après la ligne actuelle.
  • RANGE : Calcule le cadre de la fenêtre en fonction d’une plage logique de lignes autour de la ligne actuelle, en fonction de la valeur de clé ORDER BY de la ligne actuelle.La valeur de plage fournie est ajoutée ou soustraite à la valeur skey de la ligne actuelle pour définir une limite de plage de début ou de fin pour le cadre de fenêtre. Dans un cadre de fenêtre basé sur une plage, il doit y avoir exactement unexpression dans la clause ORDER BY, et l’expression doit avoir un type anumérique.

  Astuce: 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être avec une limite inférieure et supérieure.La première limite représente la limite inférieure. La deuxième limitereprésente la limite supérieure. Seules certaines combinaisons de limites peuvent êtreutilisé, comme indiqué dans la syntaxe précédente.

  • Définissez le début du cadre de la fenêtre avec unbounded_precedingnumeric_precedingnumeric_following, ou current_row.
    • unbounded_preceding: Le cadre de la fenêtre commence au début de la partie.
    • numeric_preceding ou numeric_following: Le début du cadre de la fenêtre est relatif à la ligne actuelle.
    • current_row: Le cadre de la fenêtre commence à la ligne en cours.
  • Définissez la fin du cadre de la fenêtre avec numeric_precedingnumeric_followingcurrent_row, ou unbounded_following.
    • numeric_preceding ou numeric_following : La fin du cadre de la fenêtre est relative à la ligne en cours.
    • current_row: Le cadre de la fenêtre se termine à la ligne actuelle.
    • unbounded_following: Le cadre de la fenêtre se termine à la fin de la partie.

• frame_start: Crée un cadre de fenêtre avec une limite inférieure.Le cadre de la fenêtre se termine à la ligne actuelle.

  • unbounded_preceding: Le cadre de la fenêtre commence au début de la partie.
  • numeric_preceding: Le début du cadre de la fenêtre est relatif à la ligne actuelle.
  • current_row: Le cadre de la fenêtre commence à la ligne en cours.

• numeric_expression : Une expression qui représente un type numérique.L’expression numérique doit être un paramètre entier constant et non négatif.

Notes

Si une limite s’étend au-delà du début ou de la fin d’une partition, le cadre de la fenêtre n’inclura que les lignes de cette partition.

Vous ne pouvez pas utiliser une clause de cadre de fenêtre avec des fonctions de navigation et des fonctions de numérotation, telles que RANK().

Exemples utilisant la clause window frame

Ces requêtes calculent des valeurs avec ROWS:

• Calculez une somme cumulative
• Calculez une moyenne mobile
• Obtenez l’élément le plus populaire dans chaque catégorie
• Obtenez la dernière valeur d’une plage
• Utilisez une fenêtre nommée dans une clause de cadre de fenêtre

Ces requêtes calculent les valeurs avec RANGE:

• Calculez les valeurs nombre d’éléments dans une plage

Ces requêtes calculent les valeurs avec une fenêtre partiellement ou entièrement non liée:

• Calculez un total général
• Calculez un sous-total
• Calculez une somme cumulative
• Obtenez l’élément le plus populaire de chaque catégorie
• Calculez le rang

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

• Calculez une somme cumulative
• Calculez une moyenne mobile
• Calculez le nombre d’éléments dans un range
• Obtenir la dernière valeur d’une plage
• Utiliser une fenêtre nommée dans une clause de cadre de fenêtre

Ces requêtes calculent les valeurs avec la ligne en cours comme limite:

• Calculer un total général
• Calculer un sous-total
• Calculer une somme cumulative

Référençant une fenêtre nommée

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

Une fenêtre nommée représente un groupe de lignes dans une table sur laquelle utiliser une fonction ananalytique. Une fenêtre nommée est définie dans la clause WINDOW, et référencée dans la fonction analytique OVER clause.In une clause OVER, une fenêtre nommée peut apparaître seule ou incorporé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être

Concepts de fonction de navigation

Les fonctions de navigation calculent généralement une value_expression sur une ligne différente du cadre de fenêtre de la ligne actuelle. La syntaxe de la clause OVER varie selon les fonctions de navigation.

Exigences pour la clause OVER :

• PARTITION BY : Facultatif.
• ORDER BY:
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

Pour toutes les fonctions de navigation, le type de données de résultat est le même que value_expression.

Concepts de fonction de numérotation

Les fonctions de numérotation attribuent des valeurs entières à chaque ligne en fonction de leur position dans la fenêtre spécifiée.

Exemple de RANK()DENSE_RANK(), et ROW_NUMBER():

WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+

• RANK(): Pour x= 5, rank vaut 4, puisque RANK() incrémente par le nombre de pairs dans le groupe de commande de fenêtre précédent.
• DENSE_RANK(): Pour x= 5, dense_rank est 3, puisque DENSE_RANK() augmente toujours de 1, ne sautant jamais de valeur.
• ROW_NUMBER(): Pour x=5, row_num vaut 4.

Concepts de fonctions analytiques d’agrégat

Une fonction d’agrégat est une fonction qui effectue un calcul sur un ensemble de valeurs. La plupart des fonctions agrégées peuvent être utilisées dans une fonction analytique. Ces fonctions d’agrégation sont appeléesfonctions analytiques agrégées.

Avec les fonctions analytiques agrégées, la clause OVER est ajoutée à l’appel de fonction agrégé ; la syntaxe de l’appel de fonction reste inchangée.Comme leurs homologues de fonctions agrégées, ces fonctions analytiques effectuentagrégations, mais spécifiquement sur le cadre de fenêtre pertinent pour chaque ligne.Les types de données de résultat de ces fonctions analytiques sont les mêmes que leurs homologues de fonction d’enregistrement.

Exemples de fonctions analytiques

Dans ces exemples, l’élément en surbrillance est la ligne courante. Les éléments en gras sont les lignes incluses dans l’analyse.

Tables communes utilisées dans les exemples

Les tables suivantes sont utilisées dans les exemples de requêtes analytiques agrégées suivants : ProduceEmployees, et Farm.

Table de production

Quelques 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 'orange', 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 || orange | fruit | 2 || cabbage | vegetable | 9 || apple | fruit | 8 || leek | vegetable | 2 || lettuce | vegetable | 10 |+-------------------------------------+

Table des employés

Quelques 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 ferme

Quelques 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

Ceci calcule un total général pour tous les éléments de la table Produce.

• (orange, pomme, poireau, chou, laitue, chou) = 54 achats totaux
• (orange, pomme, poireau, chou, laitue, chou) = 54 achats totaux
• (orange, pomme, poireau, chou, laitue, chou) = 54 achats totaux
• (orange, pomme, poireau, chou, laitue, chou) = 54 achats totaux
• (orange, pomme, poireau, chou, laitue, chou) = 54 achats totaux
• (orange, pomme, poireau, chou) (orange, pomme, poireau, chou, laitue, chou frisé) = 54 achats totaux
• (orange, pomme, poireau, chou, laitue, chou frisé) = 54 achats totaux

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 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

Ce calcul calcule un sous-total pour chaque catégorie du Produce table.

• fruits
  • (orange, pomme) = 10 achats totaux
  • (orange, pomme) = 10 achats totaux
• légumes
  • (poireau, chou, laitue, chou frisé) = 44 achats totaux
  • (poireau, chou, laitue, chou frisé) = 44 achats totaux
  • (poireau, chou, laitue, chou frisé) = 44 achats totaux
  • (poireau, chou, laitue, chou frisé) = 44 total des achats
  • (poireau, chou, laitue, chou frisé) = 44 total des achats

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 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 cumulative

Ce calcul calcule une somme cumulative pour chaque catégorie dans le tableau Produce. La somme est calculée par rapport au serveur défini à l’aide de la clause ORDER BY.

• fruits
  • (orange, pomme) = 2 achats totaux
  • (orange, pomme) = 10 achats totaux
• légumes
  • (poireau, chou, laitue, chou frisé) = 2 achats totaux
  • (poireau, chou, laitue, chou frisé) = 11 achats totaux
  • (poireau, chou, laitue, chou frisé) = 21 achats totaux
  • (poireau, chou, laitue, chou frisé) = 44 achats totaux

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

Ceci fait la même chose que l’exemple précédent. Vous n’avez pas besoin d’ajouter CURRENT ROW comme limite, sauf si vous le souhaitez pour la lisibilité.

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS UNBOUNDED PRECEDING ) AS total_purchasesFROM 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 commence deux fois avant la ligne actuelle de la partition.

• (orange, poireau, pomme, chou, laitue, chou) = NULL
• (orange, poireau, pomme, chou, laitue, chou) = NULL
• (orange, poireau, pomme, chou, laitue, chou) = 2
• (orange, poireau, pomme, chou, laitue, chou) = 4
• (orange, poireau, pomme, chou, laitue, chou) = 12
• (orange, 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_purchasesFROM Produce;+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 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

Ceci calcule une moyenne mobile dans la table Produce.La limite inférieure est de 1 rangée avant la rangée actuelle. La limite supérieure est de 1 ligne après la ligne actuelle.

• (orange, poireau, pomme, chou, laitue, chou) = 2 achats moyens
• (orange, poireau, pomme, chou, laitue, chou) = 4 achats moyens
• (orange, poireau, pomme, chou, laitue, chou) = 6,3333 achats moyens
• (orange, poireau, pomme, chou, laitue, chou) = 9 achats moyens
• (orange, poireau, pomme, chou, laitue, chou) = 9 achats moyens
• (orange, poireau, pomme, chou) , chou, laitue, chou frisé) = 14 achats moyens
• (orange, poireau, pomme, chou, laitue, chou frisé) = 16.5 achats moyens

SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | avg_purchases |+-------------------------------------------------------+| orange | 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 |+-------------------------------------------------------+

Calculez le nombre d’articles dans une plage

Cet exemple obtient le nombre d’animaux ayant un compte de population similaire dans la table Farm.

• (oie, chien, bœuf, chèvre, canard, chat) = 4 animaux entre la plage de population 0-2.
• (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux entre 1 et 3.
• (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux entre 1 et 3.
• (oie, chien, bœuf, chèvre, canard, chat) = 5 animaux entre 1 et 3.
• (oie, chien, bœuf, chèvre, canard, chat) = 4 animaux entre 2 et 4.
• (oie, chien, bœuf, chèvre, canard, chat) = 1 animal entre 22 et 24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM 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 |+----------------------------------------------------------+

Obtenez l’article le plus populaire dans chaque catégorie

Cet exemple obtient l’article le plus populaire dans chaque catégorie. Il définit comment les lignes d’une fenêtre sont partitionnées et ordonnées dans chaque partition. La table Produce est référencée.

• fruit
  • (orange, pomme) =la pomme est la plus populaire
  • (orange, 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
  • (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_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | kale || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

Obtenez la dernière valeur dans une plage

Cet exemple obtient l’élément le plus populaire dans un cadre de fenêtre spécifique, en utilisant le Produce table. Le cadre de la fenêtre analyse jusqu’à troisfonds à la fois. Regardez de près la colonne most_popular pour les légumes.Au lieu d’obtenir l’article le plus populaire dans une catégorie spécifique, il obtient l’article le plus populaire dans une gamme spécifique de cette catégorie.

• fruit
  • (orange, pomme) =la pomme est la plus populaire
  • (orange, 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é) = la laitue est la plus populaire
  • (poireau, chou, laitue, chou frisé) = 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_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 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 il inclut une fenêtre nommée appelée item_window. Certaines des spécifications de la fenêtre sont définies directement dans la clause OVER et certaines sont définies 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_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Rang de calcul

Cet exemple calcule le rang de chaque employé au sein de son service, en fonction de sa date de début. La spécification de la fenêtre est définie directement dans la clause OVER. La table Employees est référencée.

• département 1
  • (Jacob, Anthony, Andrew) = Attribuer le rang 1 à Jacob
  • (Jacob, Anthony, Andrew) = Attribuer le rang 2 à Anthony
  • (Jacob, Anthony, Andrew) = Attribuer le rang 3 à Andrew
• département 2
  • (Isabella, Daniel, Jose) = Attribuer le rang 1 à Isabella
  • (Isabella, Daniel, Jose) = Attribuer le rang 2 à Daniel
  • (Isabella, Daniel, Jose) = Attribuez le rang 3 à Jose

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM 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 |+--------------------------------------------+

Utilisez une fenêtre nommée dans une clause de cadre de fenêtre

Vous pouvez définir une partie de votre logique dans une fenêtre nommée et une partie de celle-ci dans clause de cadre awindow. Cette logique est combinée. Voici un exemple, en utilisant la table Produce.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)+-------------------------------------------------------+| item | purchases | category | most_popular |+-------------------------------------------------------+| orange | 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 ces exemples:

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW 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_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

L’exemple suivant produit une erreur car une clause de cadre de fenêtre 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_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)