Catégories :

Fonctions de la fenêtre (en lien avec le rang, cadre de fenêtre)

LAST_VALUE

Renvoie la dernière valeur à l’intérieur d’un groupe ordonné de valeurs.

Voir aussi :

FIRST_VALUE , NTH_VALUE

Syntaxe

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

Pour plus d’informations sur la syntaxe cadre_fenêtre , voir Syntaxe et utilisation du cadre de fenêtre .

Notes sur l’utilisation

  • Pour assurer la compatibilité avec les implémentations de cette fonction dans d’autres systèmes, { IGNORE | RESPECT } NULLS peut également être spécifié dans les arguments de la fonction :

    LAST_VALUE( <expr> [ { IGNORE | RESPECT } NULLS ] ) OVER ...

  • Si { IGNORE | RESPECT } NULLS n’est pas spécifié, la valeur par défaut est RESPECT NULLS. En d’autres termes, une valeur NULL est renvoyée si l’expression contient une valeur NULL et qu’il s’agit de la dernière valeur de l’expression.

  • LAST_VALUE est une fonction en lien avec le rang, il faut donc spécifier une fenêtre composée des éléments suivants :

    • Sous-clause PARTITION BY <expr1> (facultatif).

    • Sous-clause ORDER BY <expr2> (obligatoire) avec un cadre_fenêtre facultatif. Pour plus d’informations sur les options de classement supplémentaires prises en charge (ordre de tri, ordre de valeurs NULL, etc.), voir la structure de requête ORDER BY.

  • Le paramètre facultatif cadre_fenêtre (cumulatif ou glissant) spécifie le sous-ensemble de lignes de la fenêtre pour lesquelles la fonction est calculée.

    • Pour les cadres de fenêtre, cette fonction prend en charge uniquement les cadres de fenêtre basés sur ROWS, pas les cadres de fenêtre basés sur RANGE.

      Prise en charge

      ROWS BETWEEN ... AND ...

      Non pris en charge

      RANGE BETWEEN ... AND ...

    • Si cadre_fenêtre n’est pas spécifié, la fenêtre par défaut est la fenêtre entière :

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      Cela 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 d’informations sur les cadres de fenêtre, y compris la syntaxe et des exemples, voir Syntaxe et utilisation du cadre de fenêtre.

Exemples

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

La requête suivante met en contraste les sorties de FIRST_VALUE, NTH_VALUE, et LAST_VALUE. Remarques importantes :

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

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 |      NULL |       1 |        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 |      NULL |       1 |        2 |
|             2 |         2 | 2 |         1 |       2 |        3 |
|             2 |         3 | 3 |         2 |       3 |        4 |
|             2 |         4 | 4 |         3 |       4 |        4 |
+---------------+-----------+---+-----------+---------+----------+