FirebirdSQL logo
 Fonctions agrégéesVariables de contexte 

Selon la spécification SQL, les fonctions window (également connues sous le nom de fonctions analytiques) sont un type de fonctions d’agrégation qui ne réduisent pas la granularité. Les données agrégées sont sorties avec les données non agrégées.

Syntaxiquement, une fonction Window est appelée en spécifiant son nom, qui est toujours suivi du mot clé OVER() avec les arguments éventuels entre parenthèses. C’est la différence syntaxique avec une fonction normale ou une fonction agrégée. Les fonctions Windows ne peuvent être que dans une liste SELECT et une clause ORDER BY.

La clause OVER peut contenir le regroupement ("selection"), le tri et le fenêtrage.

Disponible en

DSQL

Syntaxe
<window_function> ::=
    <aggregate_function> OVER <window_name_or_spec>
  | <window_function_name> ([<expr> [, <expr> ...]]) OVER <window_name_or_spec>

<window_name_or_spec> ::=
  <window_specification> | window_name

<window_function_name> ::=
     <ranking_function>
   | <navigation_function>

<window_specification> ::=
   ([window_name] [<window_partition>] [<window_order>] [<window_frame>])


<window_partition> ::= PARTITION BY <expr> [, <expr> ...]

<window_order> ::=
  ORDER BY <expr> [<direction>] [<nulls_placement>]
        [, <expr> [<direction>] [<nulls_placement>] ...]

<direction> ::= ASC | DESC

<nulls_placement> ::= NULLS {FIRST | LAST}

<window_frame> ::=
  {ROWS | RANGE} <window_frame_extent>

<window_frame_extent> ::=
  <window_frame_start> | <window_frame_between>

<window_frame_start> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window_frame_between> ::=
  BETWEEN <window_frame_bound_1> AND <window_frame_bound_2>

<window_frame_bound_1> ::=
    UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW
  | <expr> FOLLOWING

<window_frame_bound_2> ::=
    <expr> PRECEDING | CURRENT ROW | <expr> FOLLOWING
  | UNBOUNDED FOLLOWING

<aggregate_function> ::= Fonctions agrégées

<ranking_function> ::=
    DENSE_RANK
  | RANK
  | PERCENT_RANK
  | CUME_DIST
  | NTILE
  | ROW_NUMBER

<navigation_function> ::=
    LEAD
  | LAG
  | FIRST_VALUE
  | LAST_VALUE
  | NTH_VALUE

<query-spec> ::=
  SELECT
    [<first-clause>] [<skip-clause>]
    [<distinct-clause>]
    <select-list>
    <from-clause>
    [<where-clause>]
    [<group-clause>]
    [<having-clause>]
    [<named-windows-clause>]
    [<order-clause>]
    [<rows-clause>]
    [<offset-clause>] [<limit clause>]
    [<plan-clause>]

<named-windows-clause> ::=
  WINDOW <window-definition> [, <window-definition>] ...

<window-definition> ::=
  window_name AS <window_specification>
Table 1. Options pour les fonctions windows
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, un scalaire ou une fonction d’agrégation. Les fonctions de Window ne sont pas autorisées comme expression.

window_partition

Expression de partition.

window_order

Expression de tri.

window_frame

Une expression pour définir le cadre de la fonction window

window_name

Nom de la fonction.

direction

Direction du tri.

nulls_placement

La position du pseudo-digit NULL dans l’ensemble trié.

aggregate_function

Fonction agrégée.

ranking_function

Fonction de classement.

navigation_function

Fonction de navigation.

Fonctions agrégées

Toutes les fonctions agrégées peuvent être utilisées comme des fonctions de Window, en ajoutant une clause OVER.

Supposons que nous ayons une table EMPLOYEE avec les colonnes ID, NAME et SALARY. Nous avons besoin de montrer pour chaque employé, son salaire correspondant et le pourcentage de la masse salariale.

Une simple requête permet de résoudre ce problème comme suit :

select
    id,
    department,
    salary,
    salary / (select sum(salary) from employee) percentage
from employee
order by id;
Résultat
id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.2040
2  SALES       12.00     0.2448
3  SALES        8.00     0.1632
4  R & D        9.00     0.1836
5  R & D       10.00     0.2040

La requête est répétitive et peut prendre beaucoup de temps, surtout si EMPLOYEE est une représentation complexe.

Cette requête peut être réécrite sous une forme plus rapide et plus élégante en utilisant les fonctions de la Window :

select
  id,
  department,
  salary,
  salary / sum(salary) OVER () percentage
from employee
order by id;

Ici, sum(salaire) OVER () calcule la somme de tous les salaires de la requête (table des employés).

docnext count = 20

Partitionnement

Comme pour les fonctions d’agrégation, qui peuvent travailler seules ou par rapport à un groupe, les fonctions de Window peuvent également travailler pour des groupes, qui sont appelés "partitions" ou partitions.

Syntaxe
<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])

Pour chaque ligne, la fonction window évalue uniquement les lignes qui appartiennent à la même section que la ligne actuelle.

L’agrégation sur un groupe peut produire plus d’une ligne, donc les résultats de la requête principale sont ajoutés à l’ensemble résultant créé par la section, en utilisant la même liste d’expressions que pour la section.

En continuant avec l’exemple de l’employé, au lieu de compter le pourcentage du salaire de chaque employé par rapport au salaire total de l’employé, comptons le pourcentage du salaire total de l’employé du même département :Pour chaque ligne, la fonction de fenêtrage évalue uniquement les lignes qui appartiennent à la même section que la ligne actuelle.

L’agrégation sur un groupe peut produire plus d’une ligne, donc les résultats de la requête principale sont ajoutés à l’ensemble résultant créé par la section, en utilisant la même liste d’expressions que pour la section.

En continuant avec l’exemple de l’employé, au lieu de compter le pourcentage du salaire de chaque employé par rapport au salaire total de l’employé, comptons le pourcentage du salaire total de l’employé du même département :

Example 1. Partitionnement avec OVER
select
  id,
  department,
  salary,
  salary / sum(salary) OVER (PARTITION BY department) percentage
from employee
order by id;
Résultat
id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.3448
2  SALES       12.00     0.6000
3  SALES        8.00     0.4000
4  R & D        9.00     0.3103
5  R & D       10.00     0.3448

Triage

La clause ORDER BY peut être utilisée avec ou sans sectionnement. La clause ORDER BY dans OVER spécifie l’ordre dans lequel la fonction Window traitera les lignes. Cet ordre ne doit pas nécessairement être le même que l’ordre de sortie des lignes.

Pour les fonctions d’agrégation standard, la clause ORDER BY à l’intérieur de la clause OVER entraîne le renvoi de résultats d’agrégation partiels au fur et à mesure du traitement des enregistrements.

Example 1. Trier avec OVER
SELECT
  id,
  salary,
  SUM(salary) OVER (ORDER BY salary) AS cumul_salary
FROM employee
ORDER BY salary;
Résultat
id salary cumul_salary
-- ------ ------------
3    8.00         8.00
4    9.00        17.00
1   10.00        37.00
5   10.00        37.00
2   12.00        49.00

Dans ce cas, cumul_salary renvoie une agrégation partielle/accumulative (fonction SUM). Il peut sembler étrange que la valeur 37,00 soit répétée pour les identifiants 1 et 5, mais c’est normal.Le tri (ORDER BY) des clés les regroupe, et l’agrégat est calculé une fois (mais en additionnant les deux valeurs 10.00 en une fois). Pour éviter cela, vous pouvez ajouter un champ ID à la fin de la clause ORDER BY.

Ceci est dû au fait qu’il n’y a pas de cadre de window, qui par défaut, avec ORDER BY, est constitué de toutes les lignes depuis le début de la section jusqu’à la ligne actuelle et des lignes égales à la ligne actuelle dans la clause ORDER BY (c’est-à-direL’INTERVALLE ENTRE LA LIGNE PRÉCÉDENTE NON BORNÉE ET LA LIGNE ACTUELLE). Sans ORDER BY, le cadre par défaut est constitué de toutes les lignes de la section.Plus de détails sur (cadres de window) seront expliqués plus tard.

Vous pouvez utiliser plusieurs Windows avec des tris différents, et compléter l’offre ORDER BY avec les options ASC / DESC et NULLS {FIRST | LAST}.

Avec les sections, la proposition ORDER BY fonctionne de la même manière, mais à la limite de chaque section, les agrégats sont éliminés.

Toutes les fonctions d’agrégation peuvent utiliser la proposition ORDER BY sauf LIST().

L’exemple suivant montre le montant du prêt, le montant des remboursements cumulés et le solde des remboursements.

Example 2. Utilisation de OVER(ORDER BY …​) pour les totaux cumulatifs
SELECT
  payments.id AS id,
  payments.bydate AS bydate,
  credit.amount AS credit_amount,
  payments.amount AS pay,
  SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
  SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                     payments.id) AS s_amount2,
  credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                                     payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate
Résultat
ID BYDATE     CREDIT_AMOUNT PAY    S_AMOUNT S_AMOUNT2 BALANCE
-- ---------- ------------- ------ -------- --------- ----------
1  15.01.2015 1000000       100000  100000  100000    900000
2  15.02.2015 1000000       150000  250000  250000    750000
3  15.03.2015 1000000       130000  400000  380000    620000
4  15.03.2015 1000000        20000  400000  400000    600000
5  15.04.2015 1000000       200000  600000  600000    400000
6  15.05.2015 1000000       150000  750000  750000    250000
7  15.06.2015 1000000       150000 1000000  900000    100000
8  15.06.2015 1000000       100000 1000000 1000000         0

Cadre de window

L’ensemble des lignes d’une section sur lesquelles la fonction Window opère est appelé le cadre de Window (window frames). Le cadre de Window détermine quelles lignes doivent être prises en compte pour la ligne courante lors de l’évaluation d’une fonction Window.

Un cadre de Window se compose de trois parties : une unité (unit), une limite de début et une limite de fin. Les mots-clés RANGE ou ROWS peuvent être utilisés comme unité et ceux-ci définissent comment les limites de la Window vont fonctionner. Les limites de la Window sont définies par les expressions suivantes

  • <expr> PRECEDING

  • <expr> FOLLOWING

  • CURRENT ROW

Les clauses ROWS et RANGE nécessitent que la clause ORDER BY soit spécifiée.Si la clause ORDER BY n’est pas présente, alors pour les fonctions d’agrégation, le cadre de la Window est constitué de toutes les lignes de la partition.Si la clause ORDER BY est spécifiée, le cadre de la Window se compose par défaut de toutes les lignes depuis le début de la partition jusqu’à la ligne actuelle, plus toutes les lignes suivantes qui sont égales à la ligne actuelle selon la clause ORDER BY,c’est-à-dire `Etendue entre la rangée précédente non bornée et la rangée actuelle'.

La clause ROWS limite les lignes dans une section en spécifiant un nombre fixe de lignes précédant ou suivant la ligne courante.Alternativement, la clause RANGE restreint logiquement les lignes d’une section en spécifiant une plage de valeurs relative à la valeur de la ligne courante.Les lignes précédentes et suivantes sont déterminées en fonction de l’ordre donné dans la clause ORDER BY.

  • Si le cadre de la Window est défini avec une clause RANGE, la clause ORDER BY ne peut contenir qu’une seule expression et l’expression doit être de type numérique, DATE, TIME ou TIMESTAMP.Pour <expr> PRECEDING, l’expression expr est soustraite de l’expression dans ORDER BY et pour <expr> FOLLOWING, elle est ajoutée.Pour CURRENT ROW, l’expression dans ORDER BY est utilisée telle quelle.

    Ensuite, toutes les lignes (dans une section) situées entre les limites sont considérées comme faisant partie du cadre de la Window résultante.

  • Si le cadre de la Window est défini avec une clause ROWS, il n’y a aucune restriction sur le nombre et les types d’expressions placées sur la clause ORDER BY.Dans ce cas, la clause <expr> PRECEDING indique le nombre de lignes précédant la ligne courante, respectivement la clause <expr> FOLLOWING indique le nombre de lignes suivant la ligne courante.

Le UNBOUNDED PRECEDING et le UNBOUNDED FOLLOWING fonctionnent de la même manière pour les clauses ROWS et RANGE.La mention UNBOUNDED PRECEDING indique que la Window commence à la première ligne de la section. Le UNBOUNDED PRECEDING ne peut être spécifié que comme point de départ de la Window.La mention UNBOUNDED FOLLOWING indique que la Window se termine par la dernière ligne de la section. La clause UNBOUNDED FOLLOWING ne peut être spécifiée que comme point final de la Window.

L’expression CURRENT ROW indique que la Window commence ou se termine sur la ligne courante lorsqu’elle est utilisée conjointement avec la clause ROWS,ou que la Window se termine à la valeur courante lorsqu’elle est utilisée avec la clause RANGE.Le CURRENT ROW peut être spécifié à la fois comme un point de départ et un point d’arrivée.

La clause BETWEEN est utilisée en conjonction avec le mot clé ROWS ou RANGE pour spécifier le point de limite inférieur (début) ou supérieur (fin) de la Window.La limite supérieure ne peut être plus petite que la limite inférieure.

Note

Si seul le point de départ de la Window est spécifié, le point d’arrivée de la Window est considéré comme la LIGNE ACTUELLE. Par exemple, si ROWS 1 PRECEDING est spécifié, cela revient à spécifier ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.

Certaines fonctions de la Window ignorent l’expression du cadre :

  • ROW_NUMBER, LAG et LEAD fonctionnent toujours comme ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • DENSE_RANK, RANK, PERCENT_RANK et CUME_DIST travailler comme RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • FIRST_VALUE, LAST_VALUE et NTH_VALUE travailler sur le cadre, mais RANGE fonctionne de manière identique ROWS.

Ainsi, les options ROWS et RANGE offrent une certaine souplesse dans le réglage de la taille de la Window flottante. Les options suivantes sont les plus courantes :

  • La borne inférieure est fixe (elle coïncide avec la première ligne du groupe ordonné) et la borne supérieure est rampante (elle coïncide avec la ligne actuelle du groupe ordonné). Dans ce cas, nous obtenons un total cumulé (agrégat cumulé). Dans ce cas, la taille de la Window change (elle s’agrandit d’un côté) et la Window elle-même se déplace en raison de l’agrandissement. La situation inverse est également possible, lorsque la frontière inférieure est rampante et que la frontière supérieure est fixe. Dans ce cas, la Window s’effilera.

  • Si les limites supérieure et inférieure sont fixes par rapport à la ligne courante, par exemple, 1 ligne avant la ligne courante et 2 après la ligne courante, nous avons un agrégat glissant. Dans ce cas, la taille de la Window est fixe et la Window elle-même coulisse.

Windows

Par exemple, si le cadre de la Window est donné par l’expression RANGE 5 PRECEDING, une Window mobile sera générée qui inclut les lignes précédentes du groupe dont la valeur est inférieure à celle de la ligne actuelle de pas plus de 5.

Example 1. Utilisation des plages Windows
SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary RANGE 1 PRECEDING) AS s8
FROM
    employee
ID  SALARY     S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3     8.00  49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4     9.00  49.00   17.00   17.00   41.00   49.00   29.00   37.00   17.00
1    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
5    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
2    12.00  49.00   49.00   49.00   12.00   49.00   12.00   12.00   12.00

Vous pouvez utiliser les fonctions FIRST_VALUE et LAST_VALUE pour voir quelles valeurs seront incluses dans la plage. Cela aide à voir les plages de Windows et à vérifier si les paramètres sont définis correctement.

Windows en ligne

Un terme de Window est spécifié en unités physiques, les chaînes de caractères. Par exemple, si le cadre de la Window est spécifié par l’expression ROWS 5 PRECEDING, la Window comprendra jusqu’à 6 chaînes de caractères : la chaîne actuelle et les cinq précédentes (l’ordre est déterminé par la construction ORDER BY).

Example 1. Utilisation des Windows de portée
SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary ROWS 1 PRECEDING) AS s8
FROM
    employee
ID SALARY      S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3    8.00   49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4    9.00   49.00   17.00   17.00   41.00   49.00   19.00   27.00   17.00
1   10.00   49.00   37.00   27.00   32.00   49.00   20.00   29.00   19.00
5   10.00   49.00   37.00   37.00   22.00   49.00   22.00   32.00   20.00
2   12.00   49.00   49.00   49.00   12.00   49.00   12.00   22.00   22.00

Windows nommées

Afin d’éviter d’avoir à écrire des expressions complexes à chaque fois que l’on définit une Window, le nom de la Window peut être spécifié dans la clause WINDOW. Le nom de la Window peut être utilisé dans la clause OVER pour faire référence à la définition de la Window, et peut également être utilisé comme Window de base pour une autre Window nommée ou intégrée (dans la clause OVER). Les Windows encadrées (avec les clauses RANGE et ROWS) ne peuvent pas être utilisées comme Window de base, mais peuvent être utilisées dans la clause OVER window_name. Une Window qui utilise une référence à une Window de base ne peut pas avoir de clause PARTITION BY et ne peut pas remplacer le tri par une clause ORDER BY.

Example 1. Utilisation de Windows nommées
SELECT
    id,
    department,
    salary,
    count(*) OVER w1,
    first_value(salary) OVER w2,
    last_value(salary) OVER w2,
    sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;

Fonctions de classement

Les fonctions de classement calculent le numéro de classement dans une section de Window.

Ces fonctions peuvent être utilisées avec ou sans sectionnement et triage, mais leur utilisation sans triage n’a presque jamais de sens.

Les fonctions de classement peuvent être utilisées pour créer différents types de compteurs incrémentaux. Considérons SUM(1) OVER (ORDER BY SALARY) comme un exemple de ce qu’elles peuvent faire, chacune d’une manière différente. Ci-dessous, une requête d’exemple pour comparer leur comportement par rapport à `SUM'.

SELECT
  id,
  salary,
  DENSE_RANK() OVER (ORDER BY salary),
  RANK() OVER (ORDER BY salary),
  PERCENT_RANK() OVER(ORDER BY salary),
  CUME_DIST() OVER(ORDER BY salary),
  NTILE(3) OVER(ORDER BY salary),
  ROW_NUMBER() OVER (ORDER BY salary),
  SUM(1) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary dense_rank rank      percent_rank         cume_dist ntile row_number sum
-- ------ ---------- ---- ----------------- ----------------- ----- ---------- ---
3    8.00          1    1 0.000000000000000 0.200000000000000     1          1   1
4    9.00          2    2 0.250000000000000 0.400000000000000     1          2   2
1   10.00          3    3 0.500000000000000 0.800000000000000     2          3   4
5   10.00          3    3 0.500000000000000 0.800000000000000     2          4   4
2   12.00          4    5 1.000000000000000 1.000000000000000     3          5   5

DENSE_RANK()

Disponible en

DSQL

Syntaxe
DENSE_RANK() OVER {<window_specification> | window_name}
Type de résultat de retour

BIGINT

Renvoie le rang des lignes dans la section de l’ensemble de résultats sans lacunes dans le classement. Les lignes avec les mêmes valeurs <order_exp> ont le même rang dans le groupe <partition_exp>, si spécifié. Le rang d’une ligne est égal au nombre de valeurs de rang différentes dans la section précédant la ligne actuelle, augmenté de un.

Example 1. Utilisation de DENSE_RANK
SELECT
  id,
  salary,
  DENSE_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary dense_rank
-- ------ ----------
3    8.00          1
4    9.00          2
1   10.00          3
5   10.00          3
2   12.00          4
Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-row-number].

RANK()

Disponible en

DSQL

Syntaxe
RANK() OVER {<window_specification> | window_name}
type de résultat de retour

BIGINT

Renvoie le rang de chaque ligne dans la section du jeu de résultats. Les lignes avec les mêmes valeurs <order_exp> ont le même rang dans le groupe <partition_exp> si spécifié. Le rang d’une ligne est calculé comme un plus le nombre de rangs avant cette ligne.

Example 1. utiliser RANK
SELECT
  id,
  salary,
  RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary rank
-- ------ ------
3    8.00      1
4    9.00      2
1   10.00      3
5   10.00      3
2   12.00      5
Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-dense-rank],[fblangref-windowfuncs-row-number].

PERCENT_RANK()

Disponible en

DSQL

Syntaxe
PERCENT_RANK() OVER {<window_specification> | window_name}
type de résultat de retour

DOUBLE PRECISION

Renvoie le rang relatif de la ligne actuelle dans un groupe de lignes. La fonction PERCENT_RANK est utilisée pour calculer la position relative d’une valeur dans une section ou un ensemble de requêtes résultant. La plage des valeurs renvoyées par PERCENT_RANK est supérieure à 0 et inférieure ou égale à 1. La première ligne de tout ensemble PERCENT_RANK est 0. Les valeurs NULL par défaut sont incluses et traitées comme les plus petites valeurs possibles.

Note

La fonction PERNCENT RANK est calculée comme (RANK-1)/(total_rows - 1), où total_rows est le nombre total de lignes dans la section.
Example 1. utiliser PERNCENT RANK
SELECT
  id,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary percent_rank
-- ------ ------------
3    8.00          0.0
4    9.00         0.25
1   10.00          0.5
5   10.00          0.5
2   12.00          1.0
Voir aussi :

SELECT,PARTITION BY,ORDER BY,RANK,CUME_DIST.

CUME_DIST()

Disponible en

DSQL

Syntaxe
CUME_DIST() OVER {<window_specification> | window_name}
type de résultat de retour

DOUBLE PRECISION

La fonction CUME_DIST calcule la distribution cumulative d’une valeur dans le jeu de données. La valeur de retour est comprise entre 0 et 1. La fonction CUME_DIST est calculée comme suit : (nombre de lignes précédant ou égal à la ligne courante) / (nombre total de lignes). La même valeur de distribution cumulative est toujours calculée pour des valeurs égales. Les valeurs par défaut NULL sont incluses et traitées comme les plus petites valeurs possibles.

Example 1. utiliser CUME_DIST
SELECT
  id,
  salary,
  CUME_DIST() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary    cume_dist
-- ------ ------------
3    8.00          0.2
4    9.00          0.4
1   10.00          0.8
5   10.00          0.8
2   12.00          1.0
Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-percent-rank].

NTILE()

Disponible en

DSQL

Syntaxe
NTILE(<expr>) OVER {<window_specification> | window_name}
Table 1. Paramètres de la fonction NTILE
Paramètre Description

expr

Expression de type entier. Indique le nombre de groupes dans lesquels chaque section doit être divisée.
type de résultat de retour

BIGINT

La fonction NTILE organise les lignes d’une section ordonnée en un nombre spécifié de groupes de façon à ce que les tailles des groupes soient aussi proches que possible. Les groupes sont numérotés en commençant par un. Pour chaque ligne, la fonction NTILE retourne le numéro du groupe auquel la ligne appartient.

Si le nombre de lignes de la section n’est pas divisible par <expr>, des groupes de deux tailles différentes d’une unité sont formés. Les plus grands groupes viennent avant les plus petits dans l’ordre donné par la clause OVER.

Example 1. utiliser NTILE
SELECT
  id,
  salary,
  NTILE(3) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary        ntile
-- ------ ------------
3    8.00            1
4    9.00            1
1   10.00            2
5   10.00            2
2   12.00            3
Voir aussi :

SELECT,PARTITION BY,ORDER BY.

ROW_NUMBER()

Disponible en

DSQL

Syntaxe
ROW_NUMBER() OVER {<window_specification> | window_name}
type de résultat de retour

BIGINT

Renvoie le numéro de ligne consécutif dans la section de l’ensemble de résultats, où 1 correspond à la première ligne de chaque section.

Example 1. utiliser ROW_NUMBER
SELECT
  id,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary row_number
-- ------ ----------
3    8.00          1
4    9.00          2
1   10.00          3
5   10.00          4
2   12.00          5
Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-dense-rank].

Fonctions de navigation

Les fonctions de navigation récupèrent les valeurs simples (non agrégées) d’une expression à partir d’une autre chaîne de requête dans la même section.

Important

Les fonctions FIRST_VALUE, LAST_VALUE et NTH_VALUE opèrent sur le cadre de la Window (window frames). Par défaut, si ORDER BY est donné, le cadre consiste en toutes les lignes depuis le début de la division jusqu’à la ligne courante, plus toutes les lignes suivantes qui sont égales à la ligne courante selon la clause ORDER BY, c’est à dire

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Pour cette raison, les résultats des fonctions NTH_VALUE et en particulier LAST_VALUE peuvent sembler étranges. Pour éliminer cet "inconvénient", vous pouvez définir un cadre de Window différent, par exemple :

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Example 1. Fonctions de navigation
SELECT
  id,
  salary,
  FIRST_VALUE(salary) OVER (ORDER BY salary),
  LAST_VALUE(salary) OVER (ORDER BY salary),
  NTH_VALUE(salary, 2) OVER (ORDER BY salary),
  LAG(salary) OVER (ORDER BY salary),
  LEAD(salary) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Résultat
id salary first_value last_value nth_value    lag   lead
-- ------ ----------- ---------- --------- ------ ------
3    8.00        8.00       8.00    <null> <null>   9.00
4    9.00        8.00       9.00      9.00   8.00  10.00
1   10.00        8.00      10.00      9.00   9.00  10.00
5   10.00        8.00      10.00      9.00  10.00  12.00
2   12.00        8.00      12.00      9.00  10.00 <null>

Variante avec un cadre de Window modifié pour les fonctions LAST_VALUE et NTH_VALUE.

SELECT
  id,
  salary,
  FIRST_VALUE(salary) OVER (ORDER BY salary),
  LAST_VALUE(salary) OVER w,
  NTH_VALUE(salary, 2) OVER w,
  LAG(salary) OVER (ORDER BY salary),
  LEAD(salary) OVER (ORDER BY salary)
FROM employee
WINDOW
  w AS (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY salary;
Résultat
id salary first_value last_value nth_value    lag   lead
-- ------ ----------- ---------- --------- ------ ------
3    8.00        8.00      12.00      9.00 <null>   9.00
4    9.00        8.00      12.00      9.00   8.00  10.00
1   10.00        8.00      12.00      9.00   9.00  10.00
5   10.00        8.00      12.00      9.00  10.00  12.00
2   12.00        8.00      12.00      9.00  10.00 <null>

FIRST_VALUE()

Disponible en

DSQL

Syntaxe
FIRST_VALUE(<expr>) OVER {<window_specification> | window_name}
Table 1. paramètres de fonction FIRST_VALUE
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, une fonction non agrégée ou une UDR. Les fonctions agrégées ne sont pas autorisées comme expression.
type de résultat de retour

le même que l’argument de la fonction expr.

Renvoie la première valeur d’un ensemble ordonné de valeurs de cadre de Window.

Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-last-value],[fblangref-windowfuncs-nth_value].

LAG()

Disponible en

DSQL

Syntaxe
LAG(<expr> [, <offset> [, <default>]])
  OVER {<window_specification> | window_name}
Table 1. paramètres de fonction LAG
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, une fonction non agrégée ou une UDR. Les fonctions agrégées ne sont pas autorisées comme expression.

offset

Le nombre de lignes avant la ligne courante à partir desquelles la valeur doit être récupérée. Si aucune valeur n’est spécifiée, la valeur par défaut est 1. offset peut être une colonne, une requête imbriquée ou une autre expression qui calcule une valeur entière positive, ou un autre type qui peut être implicitement converti en `BIGINT'. offset ne peut pas être une valeur négative ou une fonction analytique.

default

Valeur par défaut qui est retournée si le décalage (offset) pointe en dehors de la section. La valeur par défaut est NULL.
type de résultat de retour

est le même que l’argument de la fonction expr.

La fonction LAG permet d’accéder à une chaîne avec un décalage physique spécifié (offset) avant le début de la chaîne courante.

Si le décalage (offset) pointe en dehors de la section, la valeur default sera renvoyée, qui a pour valeur par défaut NULL.

Exemples:
Example 1. utiliser fonctions LAG

Supposons que vous ayez une table "taux" qui stocke le taux de change pour chaque jour. Vous devez retracer les mouvements du taux au cours des cinq derniers jours.

SELECT
  bydate,
  cost,
  cost - LAG(cost) OVER(ORDER BY bydate) AS change,
  100 * (cost - LAG(cost) OVER(ORDER BY bydate)) /
    LAG(cost) OVER(ORDER BY bydate) AS percent_change
FROM rate
WHERE bydate BETWEEN DATEADD(-4 DAY TO current_date)
  AND current_date
ORDER BY bydate
Résultat
bydate        cost    change   percent_change
----------   -----   -------   --------------
27.10.2014   31.00    <null>           <null>
28.10.2014   31.53      0.53           1.7096
29.10.2014   31.40     -0.13          -0.4123
30.10.2014   31.67      0.27           0.8598
31.10.2014   32.00      0.33           1.0419
Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-lead].

LAST_VALUE()

Disponible en

DSQL

Syntaxe
LAST_VALUE(<expr>) OVER {<window_specification> | window_name}
Table 1. paramètres de fonction LAST_VALUE
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, une fonction non agrégée ou une UDR. Les fonctions agrégées ne sont pas autorisées comme expression.
type de résultat de retour

est le même que l’argument de la fonction expr.

Renvoie la dernière valeur d’un ensemble ordonné de valeurs de cadre de Window.

Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-first-value],[fblangref-windowfuncs-nth_value].

LEAD()

Disponible en

DSQL

Syntaxe
LEAD(<expr> [, <offset> [, <default>]])
  OVER {<window_specification> | window_name}
Table 1. paramètres de fonction LEAD
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, une fonction non agrégée ou une UDR. Les fonctions agrégées ne sont pas autorisées comme expression.

offset

Le nombre de lignes après la ligne courante jusqu’à la ligne à partir de laquelle la valeur doit être récupérée. Si aucun argument n’est spécifié, la valeur par défaut est 1. offset peut être une colonne, une requête imbriquée ou une autre expression qui calcule une valeur entière positive, ou un autre type qui peut être implicitement converti en BIGINT.offset ne peut pas être une valeur négative ou une fonction analytique.

default

Valeur par défaut retournée si le décalage (offset) pointe en dehors de la section. La valeur par défaut est NULL.
type de résultat de retour

est le même que l’argument de la fonction expr.

La fonction LEAD permet d’accéder à une chaîne de caractères à un décalage physique donné (offset) après la chaîne de caractères courante.

Si le décalage (offset) pointe en dehors de la section, la valeur default sera retournée, qui prend par défaut la valeur NULL.

Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-lag].

NTH_VALUE()

Disponible en

DSQL

Syntaxe
NTH_VALUE(<expr> [, <offset>]) [FROM FIRST | FROM LAST]
  OVER {<window_specification> | window_name}
Table 1. paramètres de fonction NTH_VALUE
Paramètre Description

expr

Expression : peut contenir une colonne de table, une constante, une variable, une expression, une fonction non agrégée ou une UDR. Les fonctions agrégées ne sont pas autorisées comme expression.

offset

Numéro d’enregistrement à partir du premier (option FROM FIRST) ou du dernier (option FROM LAST) enregistrement.
type de résultat de retour

est le même que l’argument de la fonction expr.

La fonction NTH_VALUE renvoie une Nième valeur à partir du premier (option FROM FIRST) ou du dernier (option FROM LAST) enregistrement. La valeur par défaut est FROM FIRST. L’offset 1 du premier enregistrement sera équivalent à la fonction FIRST_VALUE, l’offset 1 du dernier enregistrement sera équivalent à la fonction LAST_VALUE.

Voir aussi :

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-first-value],[fblangref-windowfuncs-last-value].

Fonctions agrégées dans une Window

Les fonctions agrégées (mais pas les fonctions Windows) sont autorisées comme arguments des fonctions Windows ainsi que dans la clause OVER. Dans ce cas, les fonctions agrégées sont évaluées en premier lieu et seulement ensuite les fonctions de Window sont superposées à celles-ci.

Note

Lorsque vous utilisez des fonctions d’agrégation comme arguments de fonction de Window, toutes les colonnes non utilisées dans les fonctions d’agrégation doivent être spécifiées dans la clause `GROUP BY'.
Example 1. utiliser de la fonction agrégée comme argument d’une Window
SELECT
    code_employee_group,
    AVG(salary) AS avg_salary,
    RANK() OVER(ORDER BY AVG(salary)) AS salary_rank
FROM employee
GROUP BY code_employee_group