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()

docnext count = 19

`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

Ranking-Funktionen

Ranking-Funktionen

CUME_DIST()

docnext count = 19

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()`