postgresql
Funzioni della finestra

esempio generico

Preparazione dei dati:

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;

In esecuzione:

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
;

Risultato:

 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)

Spiegazione:

dist_by_i : dense_rank() over (order by i) è come un row_number per valori distinti. Può essere usato per il numero di valori distinti di i ( count(DISTINCT i) wold non funziona). Basta usare il valore massimo.

prev_t : lag(t) over () è un valore precedente di t sull'intera finestra. ricorda che è nullo per la prima riga.

nth : nth_value(i, 6) over () è il valore della sesta colonna colonna i su tutta la finestra

num_by_i : count(true) over (partition by i) è una quantità di righe per ogni valore di i

num_all : count(true) over () è una quantità di righe su un'intera finestra

ntile : ntile(3) over() divide l'intera finestra in 3 (il più possibile) uguale in parti di quantità

valori delle colonne vs dense_rank vs rank vs row_number

qui puoi trovare le funzioni.

Con la tabella wf_example creata nell'esempio precedente, esegui:

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

Il risultato è:

 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 ordina VALORI di i per aspetto in finestra. appare i=1 , quindi la prima riga ha dense_rank, il prossimo e il terzo valore i non cambiano, quindi è dense_rank mostra 1 - il valore FIRST non viene modificato. quarta riga i=2 , è il secondo valore che ho incontrato, quindi dense_rank mostra 2, andso per la riga successiva. Quindi soddisfa il valore i=3 alla sesta riga, quindi mostra 3. Lo stesso per il resto due valori di i . Quindi l'ultimo valore di dense_rank è il numero di valori distinti di i .

• row_number ordina ROWS come sono elencati.

• rank Non confondere con dense_rank questa funzione ordina ROW NUMBER di i valori. Quindi inizia lo stesso con tre, ma ha il valore successivo 4, che significa che i=2 (nuovo valore) è stato raggiunto alla riga 4. Lo stesso i=3 stato incontrato alla riga 6. Ecc ..