postgresql
Fonctions de fenêtre

exemple générique

Préparation des données:

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;

Fonctionnement:

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
;

Résultat:

 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)

Explication:

dist_by_i : dense_rank() over (order by i) est comme un row_number par valeurs distinctes. Peut être utilisé pour le nombre de valeurs distinctes de i ( count(DISTINCT i) wold not work). Utilisez simplement la valeur maximale.

prev_t : lag(t) over () est une valeur précédente de t sur toute la fenêtre. attention, il est nul pour la première ligne.

nth : nth_value(i, 6) over () est la valeur de la colonne de la sixième rangée i sur toute la fenêtre

num_by_i : count(true) over (partition by i) est une quantité de lignes pour chaque valeur de i

num_all : count(true) over () est une quantité de lignes sur une fenêtre entière

ntile : ntile(3) over() divise la fenêtre entière en 3 (autant que possible) égales en quantité

valeurs de colonne vs dense_rank vs rang vs row_number

ici vous pouvez trouver les fonctions.

Avec la table wf_example créée dans l'exemple précédent, exécutez:

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

Le résultat est:

 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

• Ordres de DENSE_RANK valeurs de i par apparence dans la fenêtre. i=1 apparaît, donc la première ligne a dense_rank, la prochaine et la troisième valeur ne changent pas, donc c'est dense_rank indique 1 - la première valeur n'a pas changé. quatrième ligne i=2 , il est la deuxième valeur de i rencontré, donc dense_rank montre 2, andso pour la ligne suivante. Ensuite, il rencontre la valeur i=3 à la 6ème rangée, donc il montre 3. Même pour le reste deux valeurs de i . La dernière valeur de dense_rank est donc le nombre de valeurs distinctes de i .

• row_number les commandes ROWS telles qu'elles sont listées.

• rank Ne pas confondre avec dense_rank cette fonction ordonne ROW NUMBER des valeurs i . Donc, il commence avec trois, mais a la valeur suivante 4, ce qui signifie que i=2 (nouvelle valeur) a été rencontré à la ligne 4. Même i=3 été rencontré à la ligne 6. Etc ..