FirebirdSQL logo
 AggregatfunktionenKontextvariablen 

Sortierung

Die Unterklausel ORDER BY kann mit oder ohne Partitionen verwendet werden.Die ORDER BY-Klausel innerhalb von OVER gibt die Reihenfolge an, in der die Window-Funktion Zeilen verarbeitet.Diese Reihenfolge muss nicht mit den Auftragszeilen übereinstimmen, die in der Ausgabe erscheinen.

Fensterfunktionen haben ein wichtiges Konzept: Für jede Zeile gibt es eine Reihe von Zeilen in ihrer Partition namens window frame.Standardmäßig besteht der Rahmen bei der Angabe von ORDER BY aus allen Zeilen vom Anfang der Partition bis zur aktuellen Zeile und Zeilen gleich dem aktuellen ORDER BY-Ausdruck.Ohne ORDER BY besteht der Standardrahmen aus allen Zeilen in der Partition.

Daher erzeugt die Klausel ORDER BY für Standardaggregationsfunktionen bei der Verarbeitung von Zeilen Teilaggregationsergebnisse.

Beispiel
select
    id,
    salary,
    sum(salary) over (order by salary) cumul_salary
  from employee
  order by salary;
Ergebnisse
id  salary  cumul_salary
--  ------  ------------
3     8.00          8.00
4     9.00         17.00
1    10.00         37.00
5    10.00         37.00
2    12.00         49.00

Dann gibt cumul_salary die partielle/akkumulierte (oder laufende) Aggregation (der SUM-Funktion) zurück.Es mag seltsam erscheinen, dass 37,00 für die IDs 1 und 5 wiederholt wird, aber so sollte es funktionieren.Die ORDER BY-Schlüssel werden zusammen gruppiert, und die Aggregation wird einmal berechnet (aber die beiden summieren 10,00).Um dies zu vermeiden, können Sie das Feld "ID" am Ende der Klausel "ORDER BY" hinzufügen.

Es ist möglich, mehrere Windows mit unterschiedlichen Reihenfolgen und ORDER BY-Teilen wie ASC/DESC und NULLS FIRST/LAST zu verwenden.

Bei einer Partition funktioniert ORDER BY genauso, aber an jeder Partitionsgrenze wird die Aggregation zurückgesetzt.

Alle Aggregationsfunktionen können ORDER BY verwenden, außer LIST().

Window Frames

Ein Fensterrahmen (window frame) gibt an, welche Zeilen für die aktuelle Zeile bei der Auswertung der Fensterfunktion berücksichtigt werden sollen.

Der Rahmen besteht aus drei Teilen: Einheit, Anfangsbindung und Endbindung.Die Einheit kann RANGE oder ROWS sein, die definiert, wie die Grenzen funktionieren.

Die Grenzen sind:

  • <expr> PRECEDING

  • <expr> FOLLOWING

  • CURRENT ROW

  • Bei RANGE sollte ORDER BY genau einen Ausdruck angeben, und dieser Ausdruck sollte numerisch, Datum, Uhrzeit oder Zeitstempel sein.Für <expr> PRECEDING wird expr vom ORDER BY Ausdruck abgezogen und für <expr> FOLLOWING wird expr hinzugefügt.Für CURRENT ROW wird der Ausdruck unverändert verwendet.

    Alle Zeilen innerhalb der aktuellen Partition, die sich zwischen den Grenzen befinden, werden als Teil des resultierenden Fensterrahmens betrachtet.

  • Bei ROWS sind ORDER BY-Ausdrücke nicht durch Anzahl oder Typ beschränkt.Für diese Einheit beziehen sich <expr> PRECEDING und <expr FOLLOWING auf die Zeilenposition innerhalb der aktuellen Partition und nicht auf die Werte der Sortierschlüssel.

Sowohl UNBOUNDED PRECEDING als auch UNBOUNDED FOLLOWING funktionieren identisch mit RANGE und ROWS.UNBOUNDED PRECEDING beginnt in der ersten Zeile der aktuellen Partition und UNBOUNDED FOLLOWING in der letzten Zeile der aktuellen Partition.

Die Frame-Syntax mit <window_frame_start> spezifiziert den Start-Frame, wobei der End-Frame `CURRENT ROW ist.

Einige Window-Funktionen verwerfen Frames:

  • ROW_NUMBER, LAG und LEAD funktionieren immer als ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  • DENSE_RANK, RANK, PERCENT_RANK und CUME_DIST funktionieren immer als RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  • FIRST_VALUE, LAST_VALUE und NTH_VALUE respektieren Frames, aber das Verhalten der RANGE-Einheit ist identisch mit ROWS.

Beispiel für die Verwendung von Frame

Wenn die ORDER BY-Klausel verwendet wird, aber eine Frame-Klausel weggelassen wird, berücksichtigt die Standardeinstellung die Partition bis zur aktuellen Zeile.In Kombination mit SUM ergibt dies eine laufende Summe:

select
  id,
  salary,
  sum(salary) over (order by salary) sum_salary
from employee
order by salary;

Ergebnis:

| id | salary | sum_salary |
|---:|-------:|-----------:|
|  3 |   8.00 |       8.00 |
|  4 |   9.00 |      17.00 |
|  1 |  10.00 |      37.00 |
|  5 |  10.00 |      37.00 |
|  2 |  12.00 |      49.00 |

Wenn wir dagegen einen Rahmen für die gesamte Partition anwenden, erhalten wir die Summe für die gesamte Partition.

select
  id,
  salary,
  sum(salary) over (
    order by salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) sum_salary
from employee
order by salary;

Ergebnis:

| id | salary | sum_salary |
|---:|-------:|-----------:|
|  3 |   8.00 |      49.00 |
|  4 |   9.00 |      49.00 |
|  1 |  10.00 |      49.00 |
|  5 |  10.00 |      49.00 |
|  2 |  12.00 |      49.00 |

Dieses Beispiel soll nur demonstrieren, wie dies funktioniert;das Ergebnis dieses speziellen Beispiels wäre einfacher mit sum(salary) over() zu erzeugen.

Mit dieser Abfrage können wir einen Bereichsrahmen verwenden, um die Anzahl der Mitarbeiter mit Gehältern zwischen (Gehalt eines Mitarbeiters - 1) und (Gehalt + 1) zu berechnen:

select
  id,
  salary,
  count(*) over (
    order by salary
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) range_count
from employee
order by salary;

Ergebnis:

| id | salary | range_count |
|---:|-------:|------------:|
|  3 |   8.00 |           2 |
|  4 |   9.00 |           4 |
|  1 |  10.00 |           3 |
|  5 |  10.00 |           3 |
|  2 |  12.00 |           1 |

docnext count = 22

Named Windows

Die WINDOW-Klausel kann verwendet werden, um ein Fenster explizit zu benennen, beispielsweise um sich wiederholende oder verwirrende Ausdrücke zu vermeiden.

Ein benanntes Fenster kann verwendet werden

  1. in der OVER-Klausel, um auf eine Fensterdefinition zu verweisen, z.B. ÜBER window_name

  2. als Basisfenster eines anderen benannten oder Inline-(OVER)-Windows, wenn es kein Fenster mit Rahmen ist (ROWS- oder RANGE-Klauseln)

    Note

    Ein Window mit einem Basis-Window kann weder 'PARTITION BY' haben, noch die Reihenfolge ('ORDER BY') eines Basis-Windows überschreiben.

Ranking-Funktionen

Die Rangordnungsfunktionen berechnen den Ordinalrang einer Zeile innerhalb der Window-Partition.

Diese Funktionen können mit oder ohne Partitionierung und Ordnung verwendet werden.Sie zu verwenden, ohne sie zu bestellen, macht jedoch fast nie Sinn.

Die Rangfolgefunktionen können verwendet werden, um verschiedene Arten von inkrementellen Zählern zu erstellen.Betrachten Sie SUM(1) OVER (ORDER BY SALARY) als Beispiel dafür, was sie tun können, jeder auf unterschiedliche Weise.Es folgt eine Beispielabfrage, die auch mit dem Verhalten von SUM verglichen wird.

select
    id,
    salary,
    dense_rank() over (order by salary),
    rank() over (order by salary),
    row_number() over (order by salary),
    sum(1) over (order by salary)
  from employee
  order by salary;
Ergebnisse
id  salary  dense_rank  rank  row_number  sum
--  ------  ----------  ----  ----------  ---
 3    8.00           1     1           1    1
 4    9.00           2     2           2    2
 1   10.00           3     3           3    4
 5   10.00           3     3           4    4
 2   12.00           4     5           5    5

Der Unterschied zwischen "DENSE_RANK" und "RANK" besteht darin, dass nur in "RANK" eine Lücke in Bezug auf doppelte Zeilen (relativ zur Window-Reihenfolge) vorhanden ist.DENSE_RANK vergibt weiterhin fortlaufende Nummern nach dem doppelten Gehalt.Andererseits vergibt ROW_NUMBER immer fortlaufende Nummern, auch wenn es doppelte Werte gibt.

CUME_DIST()

Verfügbar in

DSQL, PSQL

Rückgabetyp

DOUBLE PRECISION

Syntax
CUME_DIST () OVER <window_name_or_spec>

Die Verteilungsfunktion 'CUME_DIST' berechnet den relativen Rang einer Zeile innerhalb einer Fensterpartition.'CUME_DIST' wird berechnet als die Anzahl der Zeilen, die der aktuellen Zeile vorausgehen oder der aktuellen Zeile gleichgestellt sind, dividiert durch die Anzahl der Zeilen in der Partition.

Mit anderen Worten, CUME_DIST() OVER <window_name_or_spec> entspricht COUNT({Sternchen}) OVER <window_name_or_spec> / COUNT(*) OVER()

CUME_DIST-Beispiele

select
  id,
  salary,
  cume_dist() over (order by salary)
from employee
order by salary;
Ergebnis
id salary cume_dist
-- ------ ---------
 3   8.00       0.2
 4   9.00       0.4
 1  10.00       0.8
 5  10.00       0.8
 2  12.00         1

DENSE_RANK()

Verfügbar in

DSQL, PSQL

Ergebnistyp

BIGINT

Syntax
DENSE_RANK () OVER <window_name_or_spec>

Gibt den Rang von Zeilen in einer Partition einer Ergebnismenge ohne Rangordnungslücken zurück.Zeilen mit den gleichen window_order Werten erhalten den gleichen Rang innerhalb der Partition window-partition, falls angegeben.Der dichte Rang einer Zeile ist gleich der Anzahl verschiedener Rangwerte in der Partition vor der aktuellen Zeile plus eins.

DENSE_RANK-Beispiele

select
  id,
  salary,
  dense_rank() over (order by salary)
from employee
order by salary;
Ergebnis
id salary dense_rank
-- ------ -----------
 3  8.00           1
 4  9.00           2
 1 10.00           3
 5 10.00           3
 2 12.00           4

NTILE()

Verfügbar in

DSQL, PSQL

Rückgabetyp

BIGINT

Syntax
NTILE ( number_of_tiles ) OVER <window_name_or_spec>
Table 1. Argumente für NTILE
Argument Beschreibung

number_of_tiles

Anzahl der Kacheln (Gruppen).Beschränkt auf ein positives Ganzzahlliteral, einen benannten Parameter (PSQL) oder einen Positionsparameter (DSQL).

NTILE verteilt die Zeilen der aktuellen Fensterpartition in die angegebene Anzahl von Kacheln (Gruppen).

NTILE-Beispiele

select
  id,
  salary,
  rank() over (order by salary),
  ntile(3) over (order by salary)
from employee
order by salary;
Ergebnis
ID SALARY RANK NTILE
== ====== ==== =====
 3   8.00    1     1
 4   9.00    2     1
 1  10.00    3     2
 5  10.00    3     2
 2  12.00    5     3

PERCENT_RANK()

Verfügbar in

DSQL, PSQL

Ergebnistyp

DOUBLE PRECISION

Syntax
PERCENT_RANK () OVER <window_name_or_spec>

Die Verteilungsfunktion 'PERCENT_RANK' berechnet den relativen Rang einer Zeile innerhalb einer Fensterpartition.PERCENT_RANK wird berechnet als [fblangref40-windowfuncs-rank-de] minus 1 der aktuellen Zeile geteilt durch die Anzahl der Zeilen in der Partition minus 1.

Mit anderen Worten, PERCENT_RANK() OVER <window_name_or_spec> entspricht (RANK() OVER <window_name_or_spec> - 1) / CAST(COUNT(*) OVER() - 1 AS DOUBLE PRECISION)

PERCENT_RANK Examples

select
  id,
  salary,
  rank() over (order by salary),
  percent_rank() over (order by salary)
from employee
order by salary;
Ergebnis
id salary rank percent_rank
-- ------ ---- ------------
 3   8.00    1            0
 4   9.00    2         0.25
 1  10.00    3          0.5
 5  10.00    3          0.5
 2  12.00    5            1

RANK()

Verfügbar in

DSQL, PSQL

Ergebnistyp

BIGINT

Syntax
RANK () OVER <window_name_or_spec>

Gibt den Rang jeder Zeile in einer Partition der Ergebnismenge zurück.Zeilen mit den gleichen Werten von window-order erhalten den gleichen Rang wie in der Partition _window-partition, falls angegeben.Der Rang einer Zeile entspricht der Anzahl der Rangwerte in der Partition vor der aktuellen Zeile plus eins.

RANK-Beispiele

select
  id,
  salary,
  rank() over (order by salary)
from employee
order by salary;
Ergebnis
id salary rank
-- ------ -----
 3   8.00     1
 4   9.00     2
 1  10.00     3
 5  10.00     3
 2  12.00     5
Siehe auch

[fblangref40-windowfuncs-dense-rank-de], [fblangref40-windowfuncs-row-number-de]

ROW_NUMBER()

Verfügbar in

DSQL, PSQL

Ergebnistyp

BIGINT

Syntax
ROW_NUMBER () OVER <window_name_or_spec>

Gibt die fortlaufende Zeilennummer in der Partition der Ergebnismenge zurück, wobei '1' die erste Zeile in jeder der Partitionen ist.

ROW_NUMBER-Beispiele

select
  id,
  salary,
  row_number() over (order by salary)
from employee
order by salary;
Ergebnis
id salary rank
-- ------ -----
 3   8.00     1
 4   9.00     2
 1  10.00     3
 5  10.00     4
 2  12.00     5
Siehe auch

[fblangref40-windowfuncs-dense-rank-de], [fblangref40-windowfuncs-rank-de]

Navigationsfunktionen

Die Navigationsfunktionen rufen den einfachen (nicht aggregierten) Wert eines Ausdrucks aus einer anderen Zeile der Abfrage innerhalb derselben Partition ab.

Important

FIRST_VALUE, LAST_VALUE und NTH_VALUE wirken auch auf einen Window-Frame.Für Navigationsfunktionen wendet Firebird einen Standardrahmen von der ersten bis zur aktuellen Zeile der Partition an, nicht bis zur letzten.Mit anderen Worten, es verhält sich so, als ob der folgende Frame angegeben wäre:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Dies führt wahrscheinlich zu seltsamen oder unerwarteten Ergebnissen für "NTH_VALUE" und insbesondere "LAST_VALUE".
Beispiel für Navigationsfunktionen
select
    id,
    salary,
    first_value(salary) over (order by salary),
    last_value(salary) over (order by salary),
    nth_value(salary, 2) over (order by salary),
    lag(salary) over (order by salary),
    lead(salary) over (order by salary)
  from employee
  order by salary;
Ergebnisse
id  salary  first_value  last_value  nth_value     lag    lead
--  ------  -----------  ----------  ---------  ------  ------
3     8.00         8.00        8.00     <null>  <null>    9.00
4     9.00         8.00        9.00       9.00    8.00   10.00
1    10.00         8.00       10.00       9.00    9.00   10.00
5    10.00         8.00       10.00       9.00   10.00   12.00
2    12.00         8.00       12.00       9.00   10.00  <null>

FIRST_VALUE()

Verfügbar in

DSQL, PSQL

Ergebnistyp

Das gleiche wie type wie expr

Syntax
FIRST_VALUE ( <expr> ) OVER <window_name_or_spec>
Table 1. Arguments of FIRST_VALUE
Argument Beschreibung

expr

Ausdruck.Kann eine Tabellenspalte, Konstante, Variable, Ausdruck, Skalarfunktion enthalten.Aggregatfunktionen sind als Ausdruck nicht zulässig.

Gibt den ersten Wert der aktuellen Partition zurück.

Siehe auch

[fblangref40-windowfuncs-last-value-de], [fblangref40-windowfuncs-nth-value-de]

LAG()

Verfügbar in

DSQL, PSQL

Ergebnistyp

Das gleiche wie type wie expr

Syntax
LAG ( <expr> [, <offset [, <default>]])
  OVER <window_name_or_spec>
Table 1. Arguments of LAG
Argument Beschreibung

expr

Ausdruck.Kann eine Tabellenspalte, Konstante, Variable, Ausdruck, Skalarfunktion enthalten.Aggregatfunktionen sind als Ausdruck nicht zulässig.

offset

Der Offset in Zeilen vor der aktuellen Zeile, um den durch expr identifizierten Wert zu erhalten.Wenn offset nicht angegeben ist, ist der Standardwert 1.offset kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, der zu einem positiven ganzzahligen Wert führt, oder ein anderer Typ, der implizit in BIGINT konvertiert werden kann.offset darf nicht negativ sein (verwenden Sie stattdessen LEAD).

default

Der Standardwert, der zurückgegeben werden soll, wenn offset außerhalb der Partition zeigt.Der Standardwert ist NULL.

Die LAG-Funktion ermöglicht den Zugriff auf die Zeile in der aktuellen Partition mit einem gegebenen Offset vor der aktuellen Zeile.

Wenn offset außerhalb der aktuellen Partition zeigt, wird default zurückgegeben, oder NULL, wenn kein Standard angegeben wurde.

LAG-Beispiele

Angenommen, Sie haben die Tabelle 'RATE', in der der Wechselkurs für jeden Tag gespeichert ist.Um die Änderung des Wechselkurses in den letzten fünf Tagen zu verfolgen, können Sie die folgende Abfrage verwenden.

select
  bydate,
  cost,
  cost - lag(cost) over (order by bydate) as change,
  100 * (cost - lag(cost) over (order by bydate)) /
    lag(cost) over (order by bydate) as percent_change
from rate
where bydate between dateadd(-4 day to current_date)
and current_date
order by bydate
Ergebnis
bydate     cost   change percent_change
---------- ------ ------ --------------
27.10.2014  31.00 <null>         <null>
28.10.2014  31.53   0.53         1.7096
29.10.2014  31.40  -0.13        -0.4123
30.10.2014  31.67   0.27         0.8598
31.10.2014  32.00   0.33         1.0419
Siehe auch

[fblangref40-windowfuncs-lead-de]

LAST_VALUE()

Verfügbar in

DSQL, PSQL

Ergebnistyp

Das gleiche wie type wie expr

Syntax
LAST_VALUE ( <expr> ) OVER <window_name_or_spec>
Table 1. Argumente für LAST_VALUE
Argument Beschreibung

expr

Ausdruck.Kann eine Tabellenspalte, Konstante, Variable, Ausdruck, Skalarfunktion enthalten.Aggregatfunktionen sind als Ausdruck nicht zulässig.

Gibt den letzten Wert der aktuellen Partition zurück.

Siehe auch Hinweis zu Frames für Navigationsfunktionen.

Siehe auch

[fblangref40-windowfuncs-first-value-de], [fblangref40-windowfuncs-nth-value-de]

LEAD()

Verfügbar in

DSQL, PSQL

Ergebnistyp

Das gleiche wie type wie expr

Syntax
LEAD ( <expr> [, <offset [, <default>]])
  OVER <window_name_or_spec>
Table 1. Argumente für LEAD
Argument Beschreibung

expr

Ausdruck.Kann eine Tabellenspalte, Konstante, Variable, Ausdruck, Skalarfunktion enthalten.Aggregatfunktionen sind als Ausdruck nicht zulässig.

offset

Der Offset in Zeilen nach der aktuellen Zeile, um den durch expr identifizierten Wert zu erhalten.Wenn offset nicht angegeben ist, ist der Standardwert 1.offset kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, der zu einem positiven ganzzahligen Wert führt, oder ein anderer Typ, der implizit in BIGINT konvertiert werden kann.offset darf nicht negativ sein (verwenden Sie stattdessen LAG).

default

Der Standardwert, der zurückgegeben werden soll, wenn offset außerhalb der Partition zeigt.Der Standardwert ist NULL.

Die LEAD-Funktion ermöglicht den Zugriff auf die Zeile in der aktuellen Partition mit einem gegebenen Offset nach der aktuellen Zeile.

Wenn offset außerhalb der aktuellen Partition zeigt, wird default zurückgegeben, oder NULL, wenn kein Standard angegeben wurde.

Siehe auch

[fblangref40-windowfuncs-lag-de]

NTH_VALUE()

Verfügbar in

DSQL, PSQL

Ergebnistyp

Das gleiche wie type wie expr

Syntax
NTH_VALUE ( <expr>, <offset> )
  [FROM {FIRST | LAST}]
  OVER <window_name_or_spec>
Table 1. Arguments of NTH_VALUE
Argument Beschreibung

expr

Ausdruck.Kann eine Tabellenspalte, Konstante, Variable, Ausdruck, Skalarfunktion enthalten.Aggregatfunktionen sind als Ausdruck nicht zulässig.

offset

Der Versatz in Zeilen vom Anfang (FROM FIRST) oder dem letzten (FROM LAST), um den durch expr identifizierten Wert zu erhalten.offset kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, der zu einem positiven ganzzahligen Wert führt, oder ein anderer Typ, der implizit in BIGINT konvertiert werden kann.offset kann nicht null oder negativ sein.

Die Funktion NTH_VALUE gibt den Nten Wert ab der ersten (FROM FIRST) oder der letzten (FROM LAST) Zeile des aktuellen Frames zurück, siehe auch note on Rahmen für Navigationsfunktionen.Offset 1 mit FROM FIRST entspricht FIRST_VALUE und Offset 1 mit FROM LAST entspricht LAST_VALUE.

Siehe auch

[fblangref40-windowfuncs-first-value-de], [fblangref40-windowfuncs-last-value-de]

Aggregatfunktionen innerhalb der Window-Spezifikation

Es ist möglich, Aggregatfunktionen (aber keine Window-Funktionen) innerhalb der OVER-Klausel zu verwenden.In diesem Fall wird zuerst die Aggregatfunktion angewendet, um die Windows zu bestimmen, und erst dann werden die Window-Funktionen auf diese Window- angewendet.

Note

Bei Verwendung von Aggregatfunktionen innerhalb von OVER müssen alle Spalten, die nicht in Aggregatfunktionen verwendet werden, in der GROUP BY-Klausel von SELECT angegeben werden.
Verwenden einer Aggregatfunktion in einer Window-spezifikation
select
  code_employee_group,
  avg(salary) as avg_salary,
  rank() over (order by avg(salary)) as salary_rank
from employee
group by code_employee_group