postgresql
Funciones de ventana

ejemplo genérico

Preparando los datos:

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;

Corriendo:

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
;

Resultado:

 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)

Explicación:

dist_by_i : dense_rank() over (order by i) es como un número de fila por valores distintos. Puede usarse para el número de valores distintos de i ( count(DISTINCT i) no funcionará). Solo usa el valor máximo.

prev_t : lag(t) over () es un valor anterior de t en toda la ventana. importa que sea nulo para la primera fila.

nth : nth_value(i, 6) over () es el valor de la sexta fila columna i en toda la ventana

num_by_i : count(true) over (partition by i) es una cantidad de filas para cada valor de i

num_all : count(true) over () es una cantidad de filas en una ventana completa

ntile : ntile(3) over() divide toda la ventana a 3 (la mayor cantidad posible) igual en cantidad de partes

valores de columna vs dense_rank vs rango vs row_number

Aquí puedes encontrar las funciones.

Con la tabla wf_example creada en el ejemplo anterior, ejecute:

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

El resultado es:

 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 ordena VALORES de i por aparición en ventana. i=1 aparece, por lo que la primera fila tiene dense_rank, la siguiente y la tercera i el valor no cambia, por lo que es dense_rank muestra 1 : el valor FIRST no ha cambiado. la cuarta fila i=2 , es el segundo valor de i se reunió, por lo que dense_rank muestra 2, y también para la siguiente fila. Luego cumple con el valor i=3 en la 6ª fila, por lo que muestra 3. Lo mismo para el resto de los dos valores de i . Entonces, el último valor de dense_rank es el número de valores distintos de i .

• órdenes ROW_NUMBER FILAS en que se enumeran.

• Rango Para no confundirse con dense_rank esta función ordena el NÚMERO DE FILA de los valores i . Así que comienza igual con tres, pero tiene el siguiente valor 4, lo que significa que i=2 (nuevo valor) se cumplió en la fila 4. Igual i=3 se cumplió en la fila 6. Etc ..