Analysefunktionen

Analysefunktionen berechnen einen Aggregatwert basierend auf einer Gruppe von Zeilen. Sie unterscheiden sich von Aggregatfunktionen dadurch, dass sie für jede Gruppe mehrere Zeilen zurückgeben. Die Gruppe von Zeilen wird als Fenster bezeichnet und durch analytic_clause definiert. Für jede Zeile wird ein Schiebefenster von Zeilen definiert. Das Fenster bestimmt den Zeilenbereich, der zum Ausführen der Berechnungen für die aktuelle Zeile verwendet wird. Fenstergrößen können entweder auf einer physischen Anzahl von Zeilen oder einem logischen Intervall wie der Zeit basieren.

Analysefunktionen sind die letzten Operationen, die in einer Abfrage ausgeführt werden, mit Ausnahme der letzten ORDERBY -Klausel. Alle Verknüpfungen und alle WHEREGROUPBY und HAVING Klauseln werden abgeschlossen, bevor die Analysefunktionen verarbeitet werden. Daher können Analysefunktionen nur in der select-Liste oder ORDERBY -Klausel angezeigt werden.

Analysefunktionen werden häufig verwendet, um kumulative, bewegliche, zentrierte und Berichtsaggregate zu berechnen.

analytic_function::=

Beschreibung der Abbildung „analytic_function.gif“

analytic_clause::=

Beschreibung der Abbildung „analytic_clause.gif“

query_partition_clause::=

Beschreibung der Abbildung „query_partition_clause.gif“

order_by_clause::=

Beschreibung der Abbildung „order_by_clause.gif“

windowing_clause ::=

Beschreibung der Abbildung „windowing_clause.gif“

Die Semantik dieser Syntax wird in den folgenden Abschnitten erläutert.

analytic_function

Geben Sie den Namen einer Analysefunktion an (siehe Auflistung der Analysefunktionen im Anschluss an diese Semantikdiskussion).

Argumente

Analysefunktionen benötigen 0 bis 3 Argumente. Die Argumente können beliebige numerische Datentypen oder nicht-numerische Datentypen sein, die implizit in einen numerischen Datentyp konvertiert werden können. Oracle ermittelt das Argument mit der höchsten numerischen Priorität und konvertiert die verbleibenden Argumente implizit in diesen Datentyp. Der Rückgabetyp ist auch dieser Datentyp, sofern für eine einzelne Funktion nichts anderes angegeben ist.

analytic_clause

Use OVERanalytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROMWHEREGROUPBY, and HAVING clauses. Sie können Analysefunktionen mit dieser Klausel in der select-Liste oder ORDERBY -Klausel angeben. Um die Ergebnisse einer Abfrage basierend auf einer Analysefunktion zu filtern, verschachteln Sie diese Funktionen in der übergeordneten Abfrage, und filtern Sie dann die Ergebnisse der verschachtelten Unterabfrage.

Hinweise zur analytic_clause: Die folgenden Hinweise gelten für analytic_clause:

• Sie können Analysefunktionen nicht verschachteln, indem Sie eine Analysefunktion in einem Teil von analytic_clause angeben. Sie können jedoch eine Analysefunktion in einer Unterabfrage angeben und eine andere Analysefunktion darüber berechnen.

• Sie können OVERanalytic_clause mit benutzerdefinierten Analysefunktionen sowie integrierten Analysefunktionen angeben. Siehe FUNKTION ERSTELLEN.

query_partition_clause

Verwenden Sie die PARTITIONBY-Klausel, um die Abfrageergebnismenge in Gruppen basierend auf einem oder mehreren value_expr zu partitionieren. Wenn Sie diese Klausel weglassen, behandelt die Funktion alle Zeilen der Abfrageergebnismenge als eine einzelne Gruppe.

Um die query_partition_clause in einer Analysefunktion zu verwenden, verwenden Sie den oberen Zweig der Syntax (ohne Klammern). Um diese Klausel in einer Modellabfrage (im model_column_clauses) oder einem partitionierten äußeren Join (im outer_join_clause) zu verwenden, verwenden Sie den unteren Zweig der Syntax (mit Klammern).

Sie können mehrere Analysefunktionen in derselben Abfrage angeben, jede mit demselben oder unterschiedlichen PARTITIONBY Schlüsseln.

Wenn die abgefragten Objekte das Attribut parallel haben und Sie eine Analysefunktion mit der query_partition_clause angeben, werden auch die Funktionsberechnungen parallelisiert.

Gültige Werte von value_expr sind Konstanten, Spalten, nichtanalytische Funktionen, Funktionsausdrücke oder Ausdrücke, die eines dieser Elemente beinhalten.

order_by_clause

Verwenden Sie order_by_clause, um anzugeben, wie Daten innerhalb einer Partition geordnet werden. Für alle Analysefunktionen können Sie die Werte in einer Partition auf mehreren Schlüsseln anordnen, die jeweils durch eine value_expr definiert und jeweils durch eine Bestellsequenz qualifiziert sind.

Innerhalb jeder Funktion können Sie mehrere Sortierausdrücke angeben. Dies ist besonders nützlich, wenn Funktionen verwendet werden, die Werte ordnen, da der zweite Ausdruck Verknüpfungen zwischen identischen Werten für den ersten Ausdruck auflösen kann.

Immer wenn order_by_clause zu identischen Werten für mehrere Zeilen führt, verhält sich die Funktion wie folgt:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK und RANK geben für jedes der Reihen.

• ROW_NUMBER weist jeder Zeile einen eindeutigen Wert zu, auch wenn ein Gleichstand basierend auf dem order_by_clause . Der Wert basiert auf der Reihenfolge, in der die Zeile verarbeitet wird, und kann nicht deterministisch sein, wenn ORDERBY keine Gesamtreihenfolge garantiert.

• Für alle anderen Analysefunktionen hängt das Ergebnis von der Fensterspezifikation ab. Wenn Sie ein logisches Fenster mit dem Schlüsselwort RANGE angeben, gibt die Funktion für jede Zeile dasselbe Ergebnis zurück. Wenn Sie ein physisches Fenster mit dem Schlüsselwort ROWS angeben, ist das Ergebnis nicht deterministisch.

Einschränkungen der ORDER BY-Klausel Die folgenden Einschränkungen gelten für die ORDERBY-Klausel:

• Bei Verwendung in einer Analysefunktion muss order_by_clause einen Ausdruck annehmen (expr). Das Schlüsselwort SIBLINGS ist ungültig (es ist nur in hierarchischen Abfragen relevant). Position (position) und Spaltenaliase (c_alias) sind ebenfalls ungültig. Andernfalls ist diese order_by_clause die gleiche wie die Reihenfolge der Gesamtabfrage oder Unterabfrage.

• An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDERBY clause if it specifies any of the following windows:

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW. The short form of this is RANGEUNBOUNDEDPRECEDING.

  • RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING

  • RANGEBETWEENCURRENTROWANDCURRENTROW

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING

  Andere Fenstergrenzen als diese vier können nur einen Sortierschlüssel in der ORDERBY Klausel der Analysefunktion haben. Diese Einschränkung gilt nicht für Fenstergrenzen, die durch das Schlüsselwort ROW angegeben werden.

ASC / DESC Geben Sie die Reihenfolge an (aufsteigend oder absteigend). ASC ist der Standardwert.

NULLS FIRST / NULLS LAST Geben Sie an, ob zurückgegebene Zeilen mit Nullen zuerst oder zuletzt in der Reihenfolge angezeigt werden sollen.

NULLSLAST ist der Standardwert für aufsteigende Reihenfolge und NULLSFIRST ist der Standardwert für absteigende Reihenfolge.

Analysefunktionen arbeiten immer mit Zeilen in der Reihenfolge, die im order_by_clause der Funktion angegeben ist. Die order_by_clause der Funktion garantiert jedoch nicht die Reihenfolge des Ergebnisses. Verwenden Sie die order_by_clause der Abfrage, um die Reihenfolge der Endergebnisse zu gewährleisten.

windowing_clause

Einige Analysefunktionen erlauben die windowing_clause. In der Auflistung der Analysefunktionen am Ende dieses Abschnitts werden die Funktionen, die das windowing_clause zulassen, von einem Sternchen (*) gefolgt.

ROWS / RANGE Diese Schlüsselwörter definieren für jede Zeile ein Fenster (eine physikalische oder logische Menge von Zeilen), das zur Berechnung des Funktionsergebnisses verwendet wird. Die Funktion wird dann auf alle Zeilen im Fenster angewendet. Das Fenster bewegt sich durch die Abfrageergebnismenge oder Partition von oben nach unten.

• ROWS gibt das Fenster in physikalischen Einheiten (Zeilen) an.

• RANGE gibt das Fenster als logischen Offset an.

Sie können diese Klausel nur angeben, wenn Sie die order_by_clause angegeben haben. Einige Fenstergrenzen, die durch die RANGE -Klausel definiert werden, lassen Sie nur einen Ausdruck in der order_by_clause angeben. Siehe „Einschränkungen der ORDER BY-Klausel“.

Der von einer Analysefunktion mit logischem Offset zurückgegebene Wert ist immer deterministisch. Der von einer Analysefunktion mit einem physikalischen Offset zurückgegebene Wert kann jedoch zu nichtdeterministischen Ergebnissen führen, es sei denn, der Ordnungsausdruck führt zu einer eindeutigen Reihenfolge. Möglicherweise müssen Sie mehrere Spalten in order_by_clause angeben, um diese eindeutige Reihenfolge zu erreichen.

ZWISCHEN … UND Verwenden Sie die BETWEENAND-Klausel, um einen Start- und Endpunkt für das Fenster anzugeben. Der erste Ausdruck (vor AND) definiert den Startpunkt und der zweite Ausdruck (nach AND) definiert den Endpunkt.

Wenn Sie BETWEEN weglassen und nur einen Endpunkt angeben, betrachtet Oracle ihn als Startpunkt und der Endpunkt ist standardmäßig die aktuelle Zeile.

UNBOUNDED: Geben Sie UNBOUNDEDPRECEDING an, um anzuzeigen, dass das Fenster in der ersten Zeile der Partition beginnt. Dies ist die Startpunktspezifikation und kann nicht als Endpunktspezifikation verwendet werden.Geben Sie UNBOUNDEDFOLLOWING an, um anzuzeigen, dass das Fenster in der letzten Zeile der Partition endet. Dies ist die Endpunktspezifikation und kann nicht als Startpunktspezifikation verwendet werden.

AKTUELLE ZEILE Als Startpunkt gibt CURRENTROW an, dass das Fenster mit der aktuellen Zeile oder dem aktuellen Wert beginnt (je nachdem, ob Sie ROW bzw. RANGE angegeben haben). In diesem Fall kann der Endpunkt nicht value_exprPRECEDING .

Als Endpunkt gibt CURRENTROW an, dass das Fenster an der aktuellen Zeile oder dem aktuellen Wert endet (je nachdem, ob Sie ROW bzw. RANGE angegeben haben). In diesem Fall kann der Startpunkt nicht value_exprFOLLOWING .

value_expr PRECEDING oder value_expr FOLLOWING Für RANGE oder ROW:

• Wenn value_exprFOLLOWING der Startpunkt ist, muss der Endpunkt value_exprFOLLOWING sein.

• Wenn value_exprPRECEDING der Endpunkt ist, muss der Startpunkt value_exprPRECEDING sein.

Wenn Sie ein logisches Fenster definieren, das durch ein Zeitintervall im numerischen Format definiert ist, müssen Sie möglicherweise Konvertierungsfunktionen verwenden.

Wenn Sie ROWS angegeben haben:

• value_expr ist ein physikalischer Offset. Es muss eine Konstante oder ein Ausdruck sein und zu einem positiven numerischen Wert ausgewertet werden.

• Wenn value_expr Teil des Startpunkts ist, muss es zu einer Zeile vor dem Endpunkt ausgewertet werden.

Wenn Sie RANGE angegeben haben:

• value_expr ist ein logischer Offset. Es muss sich um eine Konstante oder einen Ausdruck handeln, der einen positiven numerischen Wert oder ein Intervallliteral ergibt. Informationen zu Intervallliteralen finden Sie unter „Literale“.

• Sie können nur einen Ausdruck im order_by_clause angeben.

• Wenn value_expr einen numerischen Wert ergibt, muss die ORDERBYexpr eine numerische oder DATE Datentyp.

• If value_expr evaluates to an interval value, then the ORDERBYexpr must be a DATE data type.

If you omit the windowing_clause entirely, then the default is RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW.

Analytic functions are commonly used in data warehousing environments. In der folgenden Liste der Analysefunktionen erlauben Funktionen, denen ein Sternchen (*) folgt, die vollständige Syntax, einschließlich der windowing_clause.

AVG *
CORR *
ANZAHL *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
ERSTER
FIRST_VALUE *
VERZÖGERUNG
LETZTER
LAST_VALUE *
BLEI
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANG
RATIO_TO_REPORT
REGR_ (Lineare Regression) Funktionen *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUMME *
VAR_POP *
VAR_SAMP *
VARIANZ *