postgresql
ウィンドウ関数

一般的な例

データの準備：

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;

ランニング：

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
;

結果：

 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)

説明：

dist_by_i ： dense_rank() over (order by i)は、異なる値ごとのrow_numberに似ています。 i（ count(DISTINCT i) wold not work）の異なる値の数に使用できます。最大値を使用してください。

prev_t ： lag(t) over ()は、ウィンドウ全体の前のtの値です。最初の行ではnullであることに注意してください。

n番目 ： nth_value(i, 6) over ()ウィンドウ全体にわたって第六行列iの値であります

num_by_i ： count(true) over (partition by i)は、iの各値に対する行の量です

num_all ： count(true) over ()は、ウィンドウ全体にわたる行の量です

ntile ： ntile(3) over()は、ウィンドウ全体を3（可能な限り）の部分に分割します

列の値vs dense_rank vs rank vs row_number

ここで関数を見つけることができます。

前の例で作成されたテーブルwf_exampleを使用して、次を実行します。

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

結果は次のとおりです。

 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は、ウィンドウの外観によってiの 値を注文します。 i=1が表示されるので、最初の行はdense_rankを持ち、次と3番目のi値は変化しないので、 dense_rankは1 - FIRST値が変更されていないことを示します。第4行i=2 、それはiの第2の値なので、 dense_rankは2を示し、次の行についても示します。それは値満たしi=3 6行目では、それは私の残りの二つの値3.同じ示します。したがって、 dense_rankの最後の値は、 iの別個の値の数です。

• row_numberは、リストされているROWSを並べ替えます。

• rank dense_rankと混同しないでdense_rankこの関数は、 i値のROW NUMBERを注文します。それで3つのものと同じように始まりますが、次の値4があります。これは、4行目でi=2 （新しい値）が満たされたことを意味します。同じi=3が6行目で満たされました。