LAST_VALUE¶

Syntaxe¶

LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )

Pour plus de détails sur la syntaxe window_frame, voir Syntaxe et utilisation des fonctions de fenêtre.

Arguments¶

expr

L’expression qui détermine la valeur de retour.

expr1

L’expression par laquelle il faut partitionner les lignes. Vous pouvez spécifier une seule expression ou une liste d’expressions séparées par des virgules. Par exemple :

PARTITION BY column_1, column_2

Copy

expr2

L’expression par laquelle il faut ordonner les lignes. Vous pouvez spécifier une seule expression ou une liste d’expressions séparées par des virgules. Par exemple :

ORDER BY column_3, column_4

Copy

{ IGNORE | RESPECT } NULLS

Ignorer ou respecter les valeurs de NULL lorsqu’une valeur expr contient des valeurs NULL :

• IGNORE NULLS renvoie la dernière valeur non NULL.

• RESPECT NULLS renvoie une valeur NULL s’il s’agit de la dernière valeur de l’expression.

Par défaut : RESPECT NULLS

Notes sur l’utilisation¶

• Cette fonction est une fonction de rang, elle doit donc spécifier une fenêtre. Une clause de fenêtre se compose des sous-clauses suivantes :

  • Sous-clause PARTITION BY expr1 (facultatif).

  • Sous-clause ORDER BY expr2 (obligatoire). Pour plus d’informations sur les options de classement supplémentaires prises en charge (ordre de tri, ordre de valeurs NULL, etc.), consultez la documentation sur la clause ORDER BY qui suit les mêmes règles.

  • Sous-clause window_frame (facultatif).

• L’ordre des lignes dans une fenêtre (et donc le résultat de la requête) n’est entièrement déterministe que si les clés de la clause ORDER BY rendent chaque ligne unique. Prenons l’exemple suivant :

  ... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
  

  Copy

  Le résultat de la requête peut varier si une partition contient des valeurs de la colonne o qui sont identiques, ou qui seraient identiques dans une comparaison insensible à la casse.

• La clause ORDER BY à l’intérieur de la clause OVER contrôle l’ordre des lignes uniquement dans la fenêtre, et non l’ordre des lignes dans la sortie de la requête entière. Pour contrôler l’ordre de sortie, utilisez une clause ORDER BY distincte au niveau le plus externe de la requête.

• Le paramètre facultatif window_frame spécifie le sous-ensemble de lignes de la fenêtre pour lesquelles la fonction est calculée. Si aucun cadre de fenêtre n’est spécifié, le cadre par défaut est la fenêtre entière :

  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  Ce comportement diffère de la norme ANSI, qui spécifie la valeur par défaut suivante pour les cadres de fenêtre :

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Pour plus de détails sur la syntaxe window_frame, voir Syntaxe et utilisation des fonctions de fenêtre.

Exemples¶

Le premier exemple renvoie les résultats de LAST_VALUE pour column2 partitionné par column1 :

SELECT
    column1,
    column2,
    LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_last
  FROM VALUES
    (1, 10), (1, 11), (1, 12),
    (2, 20), (2, 21), (2, 22);

+---------+---------+--------------+
| COLUMN1 | COLUMN2 | COLUMN2_LAST |
|---------+---------+--------------|
|       1 |      10 |           12 |
|       1 |      11 |           12 |
|       1 |      12 |           12 |
|       2 |      20 |           22 |
|       2 |      21 |           22 |
|       2 |      22 |           22 |
+---------+---------+--------------+

L’exemple suivant renvoie les résultats de trois fonctions associées : FIRST_VALUE, NTH_VALUE et LAST_VALUE.

• La requête crée un cadre de fenêtre glissant de trois lignes de large, qui contient :

  • La ligne qui précède la ligne actuelle.

  • La ligne actuelle.

  • La ligne qui suit la ligne actuelle.

• Le 2 de l’appel NTH_VALUE(i, 2) indique la deuxième ligne du cadre de la fenêtre (qui, dans ce cas, est également la ligne actuelle).

• Lorsque la ligne actuelle est la toute première ligne dans le cadre de la fenêtre, il n’y a pas de ligne précédente à référencer, donc FIRST_VALUE renvoie NULL pour cette ligne.

• Les limites du cadre s’étendent parfois au-delà des lignes d’une partition, mais les lignes inexistantes ne sont pas prises en compte dans les calculs de la fonction de fenêtre. Par exemple, lorsque la ligne actuelle est la toute première ligne de la partition et que le cadre de fenêtre est ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, il n’y a pas de ligne précédente à laquelle se référer, de sorte que la fonction FIRST_VALUE renvoie la valeur de la première ligne de la partition.

• Les résultats ne sont jamais identiques pour les trois fonctions, compte tenu des données figurant dans la table. Ces fonctions sélectionnent la première, la dernière ou la nième valeur pour chaque ligne du cadre, et la sélection des valeurs s’applique séparément à chaque partition.

Pour exécuter cet exemple, commencez par créer et charger la table :

CREATE TABLE demo1 (i INTEGER, partition_col INTEGER, order_col INTEGER);

INSERT INTO demo1 (i, partition_col, order_col) VALUES
  (1, 1, 1),
  (2, 1, 2),
  (3, 1, 3),
  (4, 1, 4),
  (5, 1, 5),
  (1, 2, 1),
  (2, 2, 2),
  (3, 2, 3),
  (4, 2, 4);

À présent, exécutez la requête suivante :

SELECT partition_col, order_col, i,
       FIRST_VALUE(i)  OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FIRST_VAL,
       NTH_VALUE(i, 2) OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS NTH_VAL,
       LAST_VALUE(i)   OVER (PARTITION BY partition_col ORDER BY order_col
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LAST_VAL
  FROM demo1
  ORDER BY partition_col, i, order_col;

+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
|             1 |         1 | 1 |         1 |       2 |        2 |
|             1 |         2 | 2 |         1 |       2 |        3 |
|             1 |         3 | 3 |         2 |       3 |        4 |
|             1 |         4 | 4 |         3 |       4 |        5 |
|             1 |         5 | 5 |         4 |       5 |        5 |
|             2 |         1 | 1 |         1 |       2 |        2 |
|             2 |         2 | 2 |         1 |       2 |        3 |
|             2 |         3 | 3 |         2 |       3 |        4 |
|             2 |         4 | 4 |         3 |       4 |        4 |
+---------------+-----------+---+-----------+---------+----------+