Ansicht
Dokumentation

ABENSQL_WIN_FUNC - SQL WIN FUNC

CL_GUI_FRONTEND_SERVICES - Frontend Services   General Data in Customer Master  
Diese Dokumentation steht unter dem Copyright der SAP AG.

- win_func

... AVG( col $[AS dtype$] )
  $| MEDIAN( sql_exp )
  $| MAX( sql_exp )
  $| MIN( sql_exp )
  $| SUM( sql_exp )
  $| STDDEV( sql_exp )
  $| VAR( sql_exp )
  $| CORR( sql_exp1,sql_exp2 )
  $| CORR_SPEARMAN( sql_exp,sql_exp2 )
  $| COUNT( sql_exp )
  $| COUNT( * )
  $| COUNT(*)
  $| ROW_NUMBER( )
  $| RANK( )
  $| DENSE_RANK( )
  $| NTILE( n )
  $| LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$] )
  $| FIRST_VALUE$|LAST_VALUE( col ) ...

Varianten:

1. ... AVG( ... ) $| ... $| COUNT(*)

2. ... ROW_NUMBER( )

3. ... RANK( )

4. ... DENSE_RANK( )

5. ... NTILE( n )

6. ... LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$] )

7. ... FIRST_VALUE$|LAST_VALUE( col )

Wirkung

Fensterfunktion in einem Fensterausdruck. Fensterfunktionen sind:

• Aggregatfunktionen AVG( ... ), ..., COUNT(*), die auch in Aggregatausdrücken verwendet werden können.

• Rangfolgefunktionen ROW_NUMBER( ), RANK( ), DENSE_RANK( ) und NTILE (n), die nur in Fensterausdrücken verwendet werden können.

• Wertfunktionen LEAD( ... ), LAG( ... ), FIRST_VALUE ( ... ), LAST_VALUE ( ... ), die nur in Fensterausdrücken verwendet werden können.
  
  

Variante 1

... AVG( ... ) $| ... $| COUNT(*)

Wirkung

Angabe einer der Aggregatfunktionen AVG, MEDIAN, MAX, MIN, STDDEV, VAR, CORR, CORR_SPEARMAN, SUM, COUNT, oder COUNT(*) als Fensterfunktion. Die Aggregatfunktionen werten die Zeilen des aktuellen Fensters bzw. des durch einen Zusatz ORDER BY hinter OVER definierten Rahmens aus. Die Aggregatfunktionen wirken wie allgemein beschrieben mit folgenden Unterschieden:

• In einem Fensterausdruck ist der Zusatz DISTINCT nicht erlaubt.

• Das Ergebnis der Funktion COUNT hat den Datentyp INT8 statt INT4.

• Wenn STDDEV und VAR als Fensterausdrücke verwendet werden, können sie nur den Datentyp FLTP als Argument haben.

Für die Argumente von Aggregatfunktionen gilt ebenfalls das Gleiche wie für allgemein beschrieben mit dem Unterschied, dass in einem Fensterausdruck das Argument einer Aggregatfunktion als Fensterfunktion auch selbst eine Aggregatfunktion sein kann. Dies ist genau dann der Fall, wenn in der aktuellen Query eine Gruppierung mit der GROUP BY-Klausel erfolgt. Dann werden die Fenster auf der zusammengefassten Ergebnismenge definiert und die Aggregatausdrücke, die als Spaltenangaben der aktuellen SELECT-Liste erlaubt sind, können alleinstehend oder als Teil eines SQL-Ausdrucks als Argument von Fensterfunktionen der dortigen Fensterausdrücke verwendet werden. Eine Fensterfunktion bestimmt dann ihr Ergebnis aus den aggregierten Werten der Zeilen des aktuellen Fensters.

Hinweis

Dadurch, dass der Zusatz DISTINCT nicht angegeben werden kann, können mit COUNT( sql_exp ) keine Zeilen mit unterschiedlichen Ergebnissen von sql_exp gezählt werden, sondern nur Zeilen, die keinen Null-Wert enthalten.

Fensterausdrücke mit Gruppierung

Variante 2

... ROW_NUMBER( )

Wirkung

Angabe der Rangfolgefunktion ROW_NUMBER als Fensterfunktion. Diese Rangfolgefunktion hat kein Argument sondern vergibt für jede Zeile eine Zeilennummer vom Datentyp INT8. Die Zeilen jedes Fensters werden ausgehend vom Startwert 1 durchnummeriert. Die Nummerierung erfolgt in der Reihenfolge, in welche die Zeilen eines Fensters bearbeitet werden. Diese ist entweder undefiniert oder kann durch den Zusatz ORDER BY hinter OVER definiert werden.

Hinweis

Wenn der ORDER BY nicht hinter OVER angegeben ist, vergibt ROW_NUMBER zwar eine eindeutige Zeilennummer, diese hat aber keine Bedeutung bezüglich einer Ordnung.

Beispiele zu Fensterausdrücken

Variante 3

... RANK( )

Wirkung

Angabe der Rangfolgefunktion RANK als Fensterfunktion. Diese Rangfolgefunktion hat kein Argument sondern vergibt für jede Zeile einen Rang vom Datentyp INT8. Sie kann nur zusammen mit ORDER BY hinter OVER angegeben werden.

Der Rang einer Zeile ist die Stellung dieser Zeile in der durch den Zusatz ORDER BY hinter OVER definierten Rangfolge und ist wie folgt definiert:

• Alle Zeilen, die bezüglich des Sortierkriteriums mehrfach vorkommen, haben den gleichen Rang. Dieser ist die niedrigste Zeilennummer dieser Gruppe, wie sie von der Funktion ROW_NUMBER bestimmt wird.

• Die erste Gruppe jedes Fensters startet mit dem Wert 1.

Hinweis

Wenn es in einem Fenster keine mehrfach vorkommenden Zeilen bezüglich des Sortierkriteriums gibt, ergibt RANK das gleiche Ergebnis wie ROW_NUMBER. Andernfalls ist eine durch RANK bestimmte Rangfolge keine lückenlose Zählung. Für eine lückenlose Zählung kann DENSE_RANK verwendet werden.

Fensterausdrücke mit Sortierung

Variante 4

... DENSE_RANK( )

Wirkung

Angabe der Rangfolgefunktion DENSE_RANK als Fensterfunktion. Diese Rangfolgefunktion hat kein Argument sondern vergibt für jede Zeile einen Rang vom Datentyp INT8. Sie kann nur zusammen mit ORDER BY hinter OVER angegeben werden.

DENSE_RANK funktioniert im Wesentlichen wie RANK, mit dem Unterschied, dass der Rang nicht durch die niedrigste Zeilennummer der Gruppen gleicher Werte bezüglich des Sortierkriteriums bestimmt, sondern ausgehend von der ersten Gruppe lückenlos gezählt wird.

Hinweis

Wenn es in einem Fenster keine mehrfach vorkommenden Zeilen bezüglich des Sortierkriteriums gibt, ergibt DENSE_RANK das gleiche Ergebnis wie RANK.

Fensterausdrücke mit Sortierung

Variante 5

... NTILE( n ) OVER( $[PARTITION BY sql_exp1$]
               ORDER BY col
$[ASCENDING$|DESCENDING$]) ...

Wirkung

Angabe der Rangfolgefunktion NTILE als Fensterfunktion. Die Fensterfunktion teilt die Zeilen eines Fensters in n Buckets. Das Ziel ist es, alle Buckets mit derselben Anzahl an Zeilen zu füllen, indem man die nach ORDER BY angegebene Regel befolgt.

Wenn die Anzahl der Zeilen des Fensters m nicht gleichmäßig zwischen der Anzahl der Buckets n verteilt werden kann, wird der Rest r so verteilt, dass die ersten (m MOD n) Buckets jeweils ein Element mehr enthalten. Die Buckets sind ausgehend vom Startwert 1 nummeriert, und das Ergebnis der NTILE-Funktion ist die Nummer des Buckets, zu dem eine bestimmte Zeile gehört.

n muss eine Hostvariable, ein Hostausdruck, oder ein Literal vom Typ b, s,i, oder int8 sein, das einen positiven Integer darstellt. Die OVER-Klausel inklusive ORDER BY ist obligatorisch.

Wenn n negativ ist, tritt für Literale und Hostkonstanten ein Syntaxfehler auf. Wenn n eine Variable oder ein Ausdruck ist, kann statt einem Syntaxfehler ein Datenbankfehler und die jeweilige Ausnahme CX_SY_OPEN_SQL_DB auftreten. Das Ergebnis der NTILE-Funktion ist immer vom Typ INT8.

Hinweis

Da die Anzahl von Zeilen maximal um 1 variieren kann, können Zeilen mit dem gleichen Wert auch in verschiedenen Buckets sein.

Beispiel

Gliedert alle in Tabelle DEMO_EMPLOYEES aufgelisteten Mitarbeiter nach deren Gehalt und verteilt sie in fünf Gehaltsgruppen. Gruppe 1 hat einen Eintrag mehr, da die Anzahl von Mitarbeitern (11) nicht auf fünf gleich große Gruppen verteilt werden kann.

Window Function NTILE.

Variante 6

... LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$]

Wirkung

Angabe der Wertefunktionen LEAD oder LAG als Fensterfunktion. Sie können nur zusammen mit ORDER BY hinter OVER angegeben werden.

Die Funktionen geben als Ergebnis den Wert des SQL-Ausdrucks sql_exp1 für die durch den Zusatz diff definierte Zeile des aktuellen Fensters bzw. des durch einen Zusatz ORDER BY hinter OVER definierten Rahmens zurück. Für diff kann ein Literal oder eine Hostkonstante vom ABAP-Typ b, s, i oder int8 angegeben werden, deren Wert eine positive Zahl ungleich 0 ist.

• Für die Funktion LEAD bestimmt diff die Zeile, die entsprechend weit nach der aktuellen Zeile positioniert ist.

• Für die Funktion LAG bestimmt diff die Zeile, die entsprechend weit vor der aktuellen Zeile positioniert ist.

Wenn diff nicht angegeben ist, wird implizit der Wert 1, d.h. bei LEAD die direkt folgende bzw. bei LAG die direkt vorhergehende Zeile verwendet. Wenn die durch diff bestimmte Zeile nicht im aktuellen Fenster vorhanden ist, ist das Ergebnis standardmäßig der Null-Wert. Wenn der optionale SQL-Ausdruck sql_exp2 angegeben ist, wird dieser bei nicht vorhandener Zeile für die aktuelle Zeile ausgewertet und zurückgegeben.

Das Ergebnis der Funktionen LEAD und LAG hat folgenden Datentyp:

• Falls sql_exp2 nicht angegeben ist, wird der Datentyp durch sql_exp1 bestimmt.

• Falls sql_exp2 angegeben ist, müssen die Ergebnisse von sql_exp1 und sql_exp2 so zueinander passen, dass ein gemeinsamer Ergebnistyp bestimmt werden kann: Die Datentypen müssen entweder gleich sein oder der Datentyp eines Ausdrucks muss den Wert des anderen Ausdrucks vollständig darstellen können. Das Ergebnis hat den Dictionary-Typ des Ausdrucks mit dem größten Wertebereich.

Hinweise

• Die Fensterfunktionen LEAD oder LAG eignen sich für Berechnungen, wie z.B. die Bestimmung der Differenzen zwischen Werten der aktuellen Zeile und Werten der vorhergehenden bzw. nachfolgenden Zeilen.

• Bei Verwendung der Fensterfunktionen LEAD oder LAG wird die Syntaxprüfung im strikten Modus ab Release ausgeführt.

Beispiel

SELECT-Anweisung mit den Fensterfunktionen LEAD und LAG als Operanden eines arithmetischen Ausdrucks. Da der Zusatz PARTITION nicht angegeben ist, gibt es nur ein einziges Fenster mit allen Zeilen der Ergebnismenge. Da für LEAD und LAG jeweils nur ein Argument angegeben ist, wird die Differenz zwischen den Werten der Spalte NUM1 mit der direkt folgenden bzw. direkt vorangehenden Zeile berechnet und nicht vorhandene Zeilen führen zu Null-Werten. Letztere werden über einen Null-Indikator festgestellt. Das Programm DEMO_SELECT_OVER_LEAD_LAG_DIFF verwendet diese SELECT-Anweisung und ein Ausführen des Programms gibt das Ergebnis aus.

Fensterfunktionen LEAD und LAG

Variante 7

... FIRST_VALUE$|LAST_VALUE( col )

Wirkung

Angabe der Wertefunktionen FIRST_VALUE oder LAST_VALUE als Fensterfunktion. Die Funktion FIRST_VALUE gibt den ersten Wert und die Funktion LAST_VALUE den letzten Wert einer geordneten Wertemenge zurück.

Wenn der Wert Null oder der Ausdruck leer ist, wird Null zurückgegeben (siehe Beispiel, Zeile H)

OVER und ORDER BY sind obligatorisch. PARTITION BY ist optional. Wenn ein Fenster in Partitionen geteilt ist, gibt die FIRST_VALUE/LAST_VALUE-Funktion ein Ergebnis für jede Partition zurück (siehe Beispiel). Wenn keine PARTITON BY-Klausel vorhanden ist, arbeiten die Funktionen auf dem gesamten Fenster.

Bei der LAST_VALUE-Funktion sin Rahmen ein wichtiger Aspekt, der berücksichtigt werden sollte. Der Standardrahmen ist ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Somit gibt die Funktion LAST_VALUE immer den Wert aus der aktuellen Zeile zurück. Um den letzten Wert für eine Partition oder ein Fenster zu finden, muss der richtige Rahmen explizit angegeben werden: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Beispiel

In dem Programm DEMO_SELECT_FIRST_LASTl werden die Zeilen aus der Tabelle DEMO_UPDATE in drei Partitionen geteilt, je nach Wert in COL1. Innerhalb der Partitionen werden die Zeilen nach ihrem Wert in COL3 geordnet.

Die Spalte FIRST_VALUE gibt den ersten Wert der COL2 für jede Partition zurück.

Die Spalte LAST_VALUE gibt nicht den letzten Wert zurück. Wie oben beschrieben, geht der Standardrahmen von der ersten Zeile bis zur aktuellen Zeile. Wenn COL3 doppelte Werte enthält, werden die Spalten als gleichrangig gesehen und der letzte Wert aus der Gruppe gleichrangiger Spalten wird zurückgegeben. Um den letzten Wert aus COL2 der Partition zu erhalten, muss die Rahmengröße exakt angegeben werden, wie in LAST_VALUE_CORRECT gezeigt.

In diesem Beispiel hat COL3 mehrere doppelte Werte. Das Schlüsselfeld - hier das Feld ID - wird dazu verwendet, Zeilen mit dem gleichen Wert zu ordnen.

IMAGE @@ABDOC_FIRST_LAST_VALUE.png@@487@@410@@

Addresses (Business Address Services)   Addresses (Business Address Services)  
Diese Dokumentation steht unter dem Copyright der SAP AG.

Length: 22764 Date: 20240523 Time: 104651     sap01-206 ( 326 ms )