NTH_VALUE-Funktion Oracle/PLSQL

In diesem Oracle-Lernprogramm wird erläutert, wie Sie die Oracle/PLSQL NTH_VALUE-Funktion mit Syntax und Beispielen verwenden.

Beschreibung

Die Oracle / PLSQL-Funktion NTH_VALUE gibt den n-ten Wert in einer geordneten Menge von Werten aus einem Analysefenster zurück. Sie ähnelt den Funktionen FIRST_VALUE und LAST_VALUE, mit der Ausnahme, dass mit NTH_VALUE eine bestimmte Position im Analysefenster gefunden werden kann, z.

Syntax

Die Syntax für die NTH_VALUE-Funktion in Oracle / PLSQL lautet:

NTH_VALUE (measure_column, n)
[FROM FIRST | FROM LAST]
[RESPECT NULLS | IGNORE NULLS]
OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

Parameter oder Argumente

measure_column Die Spalte oder den Ausdruck, die Sie zurückgeben möchten.

n Es ist der n-te Wert von measure_column im Analysefenster, den Sie zurückgeben möchten.

FROMFIRST|FROMLAST Wahlweise. Sie bestimmt, ob die Berechnung in der ersten Zeile des Analysefensters oder in der letzten Zeile des Analysefensters beginnt. Wenn dieser Parameter nicht angegeben wird, ist der Standardwert FROM FIRST, der in der ersten Zeile beginnt.

RESPECT NULLS | IGNORE NULLS Wahlweise. Sie bestimmt, ob NULL-Werte in die Analysefensterberechnung einbezogen oder ignoriert werden. Wenn dieser Parameter nicht angegeben wird, ist der Standardwert RESPECT NULLS, der NULL-Werte enthält.

query_partition_clause Wahlweise. Es wird verwendet, um die Ergebnisse basierend auf einem oder mehreren Ausdrücken in Gruppen zu unterteilen.

order_by_clause Wahlweise. Es wird verwendet, um die Daten in jeder Partition zu ordnen.

windowing_clause Wahlweise. Es bestimmt die Zeilen im Analysefenster, die ausgewertet werden sollen, und es ist wichtig, dass Sie die richtige windowing_clause verwenden. Andernfalls erhalten Sie möglicherweise unerwartete Ergebnisse. Es kann ein Wert sein wie:

windowing_clause Description
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Letzte Zeile im Fenster ändert sich, wenn sich die aktuelle Zeile ändert (Standardeinstellung)
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING Die erste Zeile im Fenster ändert sich, wenn sich die aktuelle Zeile ändert
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Alle Zeilen sind unabhängig von der aktuellen Zeile im Fenster enthalten

Notiz

  • Die Funktion NTH_VALUE gibt den n-ten Wert in einer geordneten Menge von Werten aus einem Analysefenster zurück.
  • Wenn das Datenquellenfenster weniger als n Zeilen enthält, gibt die Funktion NTH_VALUE NULL zurück.
  • Wenn der Parameter n NULL ist, gibt die Funktion NTH_VALUE einen Fehler zurück.

Gilt für

Die NTH_VALUE-Funktion kann in den folgenden Versionen von Oracle/PLSQL verwendet werden:

  • Oracle 12c, Oracle 11g

Beispiel

Schauen wir uns einige Beispiele der Oracle-Funktion NTH_VALUE an und untersuchen, wie Sie die Funktion NTH_VALUE in Oracle / PLSQL verwenden.

Zweithöchster Wert von Dept_id

In diesem NTH_VALUE-Beispiel haben wir eine Tabelle mit dem Namen employees mit den folgenden Daten:

EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID
100 Anita Borg 2500 10
200 Alfred Aho 3200 10
300 Bill Gates 2100 10
400 Linus Torvalds 3700 20
500 Michael Dell 3100 20
600 Nello Cristianini 2950 20
700 Rasmus Lerdorf 4900 20
800 Steve Jobs 2600 30
900 Thomas Kyte 5000 30

Lassen Sie uns nun demonstrieren, wie die Funktion NTH_VALUE funktioniert, indem Sie Daten aus der Employee-Tabelle auswählen. Geben Sie die folgende SQL-Anweisung in Oracle ein, um die dept_id und das zweithöchste Gehalt für dept_id 10 und 20 zurückzugeben:

Dies sind die Ergebnisse, die Sie sehen sollten:

DEPARTMENT_ID SECOND HIGHEST
10 2500
20 3700

In diesem Beispiel gibt der NTH_VALUE den durch NTH_VALUE(salary,2) angegebenen 2. Gehaltswert zurück. Das Analysefenster unterteilt die Ergebnisse nach dept_id und ordnet die Daten nach Gehalt in absteigender Reihenfolge an, wie in PARTITION BY dept_id ORDER BY salary DESC angegeben. Die RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING windowing_clause wird verwendet, um sicherzustellen, dass alle Zeilen unabhängig von der aktuellen Zeile enthalten sind. Wenn Sie diese windowing_clause in diesem Beispiel nicht enthalten, erhalten Sie unerwartete Ergebnisse.

Zweiter und dritter höchster Gehalt von Dept_id

Nun zeigen wir Ihnen, wie Sie mit der Funktion NTH_VALUE das 2. und 3. höchste Gehalt für dept_id 10 und 20 zurückgeben können.

Geben Sie basierend auf derselben employee-Tabelle die folgende SQL-Anweisung ein:

Dies sind die Ergebnisse, die Sie sehen sollten:

DEPARTMENT_ID SECOND HIGHEST THIRD HIGHEST
10 2500 2100
20 3700 3100

In diesem Beispiel haben wir die Funktion NTH_VALUE mehrfach verwendet, um verschiedene n-te Werte in der Employee-Tabelle zu finden.

Zweitniedrigster Lohn für alle Mitarbeiter

Schließlich verwenden wir die Funktion NTH_VALUE, um das zweitniedrigste Gehalt in der gesamten Tabelle der Mitarbeiter zurückzugeben. In diesem Beispiel benötigen wir keine query_partition_clause.

Geben Sie basierend auf den Daten in der employee-Tabelle die folgende SELECT-Anweisung ein:

Dies sind die Ergebnisse, die Sie sehen sollten:

SECOND LOWEST
2500

In diesem Beispiel haben wir die Funktion NTH_VALUE verwendet, um das zweitniedrigste Gehalt in der employee-Tabelle zu finden, und als solches das Gehalt in aufsteigender Reihenfolge sortiert, um die Daten im Analysefenster richtig anzuordnen (wie durch ORDER BY salary ASC angegeben).

Und da wir das zweitniedrigste Gehalt für die Tabelle haben möchten (nicht auf einer dept_id basiert), mussten wir query_partition_clause nicht einschließen, um die Daten zu partitionieren.