Catégories :

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

NTH_VALUE

Renvoie la énième valeur (jusqu’à 1 000) dans un groupe ordonné de valeurs.

Voir aussi :

FIRST_VALUE , LAST_VALUE

Syntaxe

NTH_VALUE( <expr> , n ) [ FROM { FIRST | LAST } ] [ { 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

  • La valeur d’entrée n ne peut pas être supérieure à 1 000.

  • Si FROM { FIRST | LAST } n’est pas spécifié, la direction par défaut est FIRST, c’est-à-dire celle du début de la liste ordonnée.

  • 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 énième valeur de l’expression.

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

Exemples

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

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

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