postgresql
Window-functies

generiek voorbeeld

Gegevens voorbereiden:

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;

Rennen:

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
;

Resultaat:

 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)

Uitleg:

dist_by_i : dense_rank() over (order by i) is als een rijnummer per afzonderlijke waarde. Kan worden gebruikt voor het aantal afzonderlijke waarden van i ( count(DISTINCT i) niet werken). Gebruik gewoon de maximale waarde.

prev_t : lag(t) over () is een eerdere waarde van t over het hele venster. houd er rekening mee dat het nul is voor de eerste rij.

nth : nth_value(i, 6) over () is de waarde van zesde rijen kolom i over het hele venster

num_by_i : count(true) over (partition by i) is een aantal rijen voor elke waarde van i

num_all : count(true) over () is een aantal rijen over een heel venster

ntile : ntile(3) over() splitst het hele venster op 3 (zoveel mogelijk) gelijk in aantal delen

kolomwaarden versus dicht_rank versus rang versus rij_nummer

hier vindt u de functies.

Met de tabel wf_example gemaakt in het vorige voorbeeld, voer je uit:

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

Het resultaat is:

 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 bestelt VALUES van i per uiterlijk in venster. i=1 verschijnt, dus de eerste rij heeft dichte_rank, de volgende en de derde i-waarde verandert niet, dus het is dense_rank toont 1 - EERSTE waarde niet gewijzigd. vierde rij i=2 , het is de tweede waarde van ik ontmoette, dus dense_rank geeft 2 weer, en dus voor de volgende rij. Dan komt het overeen met waarde i=3 op de 6e rij, dus het toont 3. Hetzelfde voor de rest twee waarden van i . Dus de laatste waarde van dense_rank is het aantal verschillende waarden van i .

• row_number bestelt ROWS zoals deze worden weergegeven.

• rang Niet te verwarren met dense_rank deze functie bestelt RIJ AANTAL i waarden. Dus het begint hetzelfde met drie, maar heeft de volgende waarde 4, wat betekent dat i=2 (nieuwe waarde) werd gehaald op rij 4. Dezelfde i=3 werd ontmoet op rij 6. Enz.