Firebirds SQL-Sprachführer 4.0

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 |

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

in der OVER-Klausel, um auf eine Fensterdefinition zu verweisen, z.B. ÜBER window_name
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

Window Frames

Window Frames

Beispiel für die Verwendung von Frame

Named Windows

Ranking-Funktionen

CUME_DIST()

CUME_DIST-Beispiele

DENSE_RANK()

DENSE_RANK-Beispiele

NTILE()

NTILE-Beispiele

PERCENT_RANK()

PERCENT_RANK Examples

RANK()

RANK-Beispiele

ROW_NUMBER()

ROW_NUMBER-Beispiele

Navigationsfunktionen

Beispiel für Navigationsfunktionen

FIRST_VALUE()

LAG()

LAG-Beispiele

LAST_VALUE()

LEAD()

NTH_VALUE()

Aggregatfunktionen innerhalb der Window-Spezifikation

`CUME_DIST()`

`CUME_DIST`-Beispiele

`DENSE_RANK()`

`DENSE_RANK`-Beispiele

`NTILE()`

`NTILE`-Beispiele

`PERCENT_RANK()`

`PERCENT_RANK` Examples

`RANK()`

`RANK`-Beispiele

`ROW_NUMBER()`

`ROW_NUMBER`-Beispiele

`FIRST_VALUE()`

`LAG()`

`LAG`-Beispiele

`LAST_VALUE()`

`LEAD()`

`NTH_VALUE()`