FirebirdSQL logo

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

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

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

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

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

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.

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

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.

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.

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.

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