postgresql
Fensterfunktionen

allgemeines Beispiel

Daten vorbereiten:

create table wf_example(i int, t text,ts timestamptz,b boolean);
insert into wf_example select 1,'a','1970.01.01',true;
insert into wf_example select 1,'a','1970.01.01',false;
insert into wf_example select 1,'b','1970.01.01',false;
insert into wf_example select 2,'b','1970.01.01',false;
insert into wf_example select 3,'b','1970.01.01',false;
insert into wf_example select 4,'b','1970.02.01',false;
insert into wf_example select 5,'b','1970.03.01',false;
insert into wf_example select 2,'c','1970.03.01',true;

Laufen:

select *
  , dense_rank() over (order by i) dist_by_i 
  , lag(t) over () prev_t 
  , nth_value(i, 6) over () nth
  , count(true) over (partition by i) num_by_i 
  , count(true) over () num_all
  , ntile(3) over() ntile
from wf_example
;

Ergebnis:

 i | t |           ts           | b | dist_by_i | prev_t | nth | num_by_i | num_all | ntile
---+---+------------------------+---+-----------+--------+-----+----------+---------+-------
 1 | a | 1970-01-01 00:00:00+01 | f |         1 |        |   3 |        3 |       8 |     1
 1 | a | 1970-01-01 00:00:00+01 | t |         1 | a      |   3 |        3 |       8 |     1
 1 | b | 1970-01-01 00:00:00+01 | f |         1 | a      |   3 |        3 |       8 |     1
 2 | c | 1970-03-01 00:00:00+01 | t |         2 | b      |   3 |        2 |       8 |     2
 2 | b | 1970-01-01 00:00:00+01 | f |         2 | c      |   3 |        2 |       8 |     2
 3 | b | 1970-01-01 00:00:00+01 | f |         3 | b      |   3 |        1 |       8 |     2
 4 | b | 1970-02-01 00:00:00+01 | f |         4 | b      |   3 |        1 |       8 |     3
 5 | b | 1970-03-01 00:00:00+01 | f |         5 | b      |   3 |        1 |       8 |     3
(8 rows)

Erläuterung:

dist_by_i : dense_rank() over (order by i) ist eine Reihennummer für verschiedene Werte. Kann für die Anzahl der unterschiedlichen Werte von i verwendet werden ( count(DISTINCT i) funktioniert nicht). Verwenden Sie einfach den Maximalwert.

prev_t : lag(t) over () ist ein vorheriger Wert von t über das gesamte Fenster. Beachten Sie, dass es für die erste Zeile null ist.

nth: nth_value(i, 6) over () ist der Wert der Spalte sechster Zeilen i über die ganzen Fenster

num_by_i : count(true) over (partition by i) ist eine Anzahl von Zeilen für jeden Wert von i

num_all : count(true) over () ist eine Anzahl von Zeilen über ein ganzes Fenster

ntile : ntile(3) over() teilt das gesamte Fenster in 3 (so viel wie möglich) gleich viele Teile auf

Spaltenwerte vs dense_rank vs rank vs row_number

Hier finden Sie die Funktionen.

Führen Sie mit der im vorherigen Beispiel erstellten Tabelle wf_example Folgendes aus:

select i
  , dense_rank() over (order by i)
  , row_number() over ()
  , rank() over (order by i)
from wf_example

Das Ergebnis ist:

 i | dense_rank | row_number | rank
---+------------+------------+------
 1 |          1 |          1 |    1
 1 |          1 |          2 |    1
 1 |          1 |          3 |    1
 2 |          2 |          4 |    4
 2 |          2 |          5 |    4
 3 |          3 |          6 |    6
 4 |          4 |          7 |    7
 5 |          5 |          8 |    8

• dense_rank befiehlt WERTE von i durch Erscheinung im Fenster. i=1 erscheint, also hat die erste Zeile dense_rank, der nächste und der dritte i-Wert ändert sich nicht, so dass es dense_rank zeigt, dass 1 - der dense_rank Wert nicht geändert wird. vierte Reihe i=2 , es ist der zweite Wert von i erfüllt, so dass dense_rank 2 zeigt, und so für die nächste Reihe. Dann trifft es in der 6. Zeile auf den Wert i=3 , also zeigt es 3. Gleiche Werte für die restlichen zwei Werte von i . Der letzte Wert von dense_rank ist also die Anzahl der unterschiedlichen Werte von i .

• row_number ordnet ROWS so an, wie sie aufgelistet sind.

• rank Nicht zu verwechseln mit dense_rank Diese Funktion ordnet ROW NUMBER von i- Werten an. Es beginnt also gleich mit drei Einsen, hat aber den nächsten Wert 4, was bedeutet, dass i=2 (neuer Wert) in Zeile 4 erfüllt wurde. Das gleiche i=3 wurde in Zeile 6 getroffen.