Catégories :

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

FIRST_VALUE

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

Voir aussi :

LAST_VALUE , NTH_VALUE

Syntaxe

FIRST_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 :

    FIRST_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 première valeur de l’expression).

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

    Notez que 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.

  • Le ORDER BY de la clause FIRST_VALUE n’affecte que l’ordre des lignes de la clause FIRST_VALUE ; il ne contrôle pas l’ordre des résultats de l’ensemble de la requête.

Exemples

Ceci montre une requête simple en utilisant FIRST_VALUE(). Cette requête contient deux sous-clauses ORDER BY , une pour contrôler l’ordre des lignes dans chaque partition, et une pour contrôler l’ordre de la sortie de la requête complète.

SELECT
        column1,
        column2,
        FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 NULLS LAST) AS column2_first
    FROM VALUES
       (1, 10), (1, 11), (1, null), (1, 12),
       (2, 20), (2, 21), (2, 22)
    ORDER BY column1, column2;
+---------+---------+---------------+
| COLUMN1 | COLUMN2 | COLUMN2_FIRST |
|---------+---------+---------------|
|       1 |      10 |            10 |
|       1 |      11 |            10 |
|       1 |      12 |            10 |
|       1 |    NULL |            10 |
|       2 |      20 |            20 |
|       2 |      21 |            20 |
|       2 |      22 |            20 |
+---------+---------+---------------+

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