Kategorien:

Fensterfunktionen (Rangbezogen, Fensterrahmen)

NTH_VALUE

Gibt den n-ten Wert (bis zu 1.000) in einer geordneten Gruppe von Werten zurück.

Siehe auch:

FIRST_VALUE, LAST_VALUE

Syntax

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

Weitere Informationen zur Syntax von Fensterrahmen finden Sie unter Fensterrahmensyntax und Verwendung.

Nutzungshinweise

  • Der Eingabewert n darf nicht größer als 1.000 sein.

  • Wenn FROM { FIRST | LAST } nicht angegeben ist, ist FIRST der Standardwert, d. h. die Richtung ist vom Anfang der geordneten Liste.

  • Wenn { IGNORE | RESPECT } NULLS nicht angegeben ist, lautet der Standardwert RESPECT NULLS, d. h. es wird ein NULL-Wert zurückgegeben, wenn der Ausdruck einen NULL-Wert enthält und es sich dabei um den n-ten Wert im Ausdruck handelt.

  • Da NTH_VALUE eine rangbezogene Analysefunktion ist, muss ein Fenster angegeben werden, das aus folgenden Elementen besteht:

    • Unterklausel PARTITION BY <Ausdruck1> (optional)

    • Unterklausel ORDER BY <Ausdruck2> (erforderlich) mit einem optionalen Fensterrahmen Weitere Informationen zu zusätzlich unterstützten Sortieroptionen (Sortierreihenfolge, Sortieren von NULL-Werten usw.) finden Sie im Abfragekonstrukt ORDER BY.

  • Der optionale Fensterrahmen (kumulativ oder gleitend) gibt die Teilmenge der Zeilen innerhalb des Fensters an, für die die Funktion berechnet wird. Wenn kein Fensterrahmen angegeben ist, ist der Standard das gesamte Fenster:

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    Beachten Sie, dass dies vom ANSI-Standard abweicht, der folgenden Standard für Fensterrahmen vorgibt:

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    Weitere Informationen zu Fensterrahmen, einschließlich Syntax und Beispiele, finden Sie unter Fensterrahmensyntax und Verwendung.

Beispiele

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

Die nächste Abfrage stellt die Ausgaben von FIRST_VALUE, NTH_VALUE und LAST_VALUE gegenüber. Beachten Sie Folgendes:

  • Die Abfrage erstellt einen gleitenden Fensterrahmen, der drei Zeilen breit ist und Folgendes enthält:

    • Die Zeile, die der aktuellen Zeile vorausgeht.

    • Die aktuelle Zeile.

    • Die Zeile, die auf die aktuelle Zeile folgt.

  • Die 2 im Aufruf NTH_VALUE(i, 2) gibt die zweite Zeile im Fensterrahmen an (die in diesem Fall auch die aktuelle Zeile ist).

  • Wenn die aktuelle Zeile die allererste Zeile im Fensterrahmen ist, gibt es keine vorherige Zeile, auf die verwiesen werden kann, sodass FIRST_VALUE() einen NULL-Wert für diese Zeile zurückgibt.

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