NTH_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 window_frame , voir Syntaxe et utilisation du cadre de fenêtre .

Arguments¶

n

Ceci spécifie la valeur de N à utiliser lors de la recherche de la Nième valeur.

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

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 (une valeur NULL est renvoyée si l’expression contient une valeur NULL et s’il s’agit de la énième valeur de l’expression).

• 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 ORDER BY la structure de requête 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 (cumulatif ou glissant) spécifie le sous-ensemble de lignes de la fenêtre pour lesquelles la fonction est calculée. Si window_frame 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 |
+---------------+-----------+---+-----------+---------+----------+