hakeの日記

Windows環境でプログラミングの勉強をしています。

SQLite - ウィンドウ関数

database SQLite

特定のレコード範囲をウィンドウ設定して、そのウィンドウに対して操作を行う機能。

sqlite> select * from AAA;
tt          ii
----------  ----------
aa          6
bb          5
aa          4
bb          3
aa          2
bb          1

　

OVER句

ウィンドウの設定はOVER句の後に直接記述するか、後ろにWINDOW句で別名定義してOVER句ではその別名を呼ぶ方法がある。
ウィンドウ範囲を設定しない場合は、最初のレコードから現在のレコードまでがウィンドウ範囲となる。
下の例は、カラムiiでソートしたレコード群の中で最初のレコードから現在のレコードまでをウィンドウ範囲として、そのウィンドウ範囲内でのカラムiiの合計、最大値、最小値を求めている。

sqlite> select *,
   ...> sum(ii) over( order by ii),
   ...> max(ii) over defw,
   ...> min(ii) over defw
   ...> from AAA
   ...> window defw as (order by ii);
tt          ii          sum(ii) over( order by ii)  max(ii) over defw  min(ii) over defw
----------  ----------  --------------------------  -----------------  -----------------
bb          1           1                           1                  1
aa          2           3                           2                  1
bb          3           6                           3                  1
aa          4           10                          4                  1
bb          5           15                          5                  1
aa          6           21                          6                  1

　

PARTITION BY指定

OVER句の中でPARTITION BYでカラムを指定することで、そのカラムの値でウィンドウをグループ分けして各々のウィンドウ毎で処理を行うことができる。

sqlite> select *,
   ...> sum(ii) over defw,
   ...> max(ii) over defw,
   ...> min(ii) over defw
   ...> from AAA
   ...> window defw as (partition by tt order by ii);
tt          ii          sum(ii) over defw  max(ii) over defw  min(ii) over defw
----------  ----------  -----------------  -----------------  -----------------
aa          2           2                  2                  2
aa          4           6                  4                  2
aa          6           12                 6                  2
bb          1           1                  1                  1
bb          3           4                  3                  1
bb          5           9                  5                  1

　

ROWS BETWEENによるレコード範囲指定

前述の様にウィンドウ範囲を指定しない場合は、最初のレコードから現在のレコードまでがウィンドウ範囲となる。
範囲を指定する場合はBETWEENを用いて開始レコードと終了レコードを指定する。
範囲指定の種類は以下が使用できる。

UNBOUNDED PRECEDING  最初のレコード
n PRECEDING          n個前のレコード
CURRENT ROW          現在のレコード
n FOLLOWING          n個後のレコード
UNBOUNDEE FOLLOWING  最後のレコード

　
ウィンドウ範囲を指定しない場合は

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

を指定したことになる。

ちなみにBETWEENを用いずに開始レコードのみを指定した場合は、開始レコード～現在のレコードまでがウィンドウ範囲となる。
　

以下の例では、一つ前のレコードから一つ前のレコードまで、すなわち一つ前のレコードのみをウィンドウ範囲prevに、同様に一つ後のレコードのみをウィンドウ範囲nextに設定して、各々のレコードのカラムiiを表示させている。
ここで使用しているmax()にはカラム値を取得する以外の意味はないのでmin()でも可である。

sqlite> select ii,
   ...> max(ii) over prev,
   ...> max(ii) over next
   ...> from AAA
   ...> window prev as (order by ii rows between 1 preceding and 1 preceding),
   ...> next as (order by ii rows between 1 following and 1 following);
ii          max(ii) over prev  max(ii) over next
----------  -----------------  -----------------
1                              2
2           1                  3
3           2                  4
4           3                  5
5           4                  6
6           5

　

FILTER句

OVER句の前に記述することで、その条件を満たしたレコードのみで処理を行う様にする。
下の例はカラムiiの値が偶数の場合のみsum()で加算している。

sqlite> select ii,
   ...> sum(ii) filter(where ii%2=0) over defw
   ...> from AAA
   ...> window defw as (order by ii rows between unbounded preceding and current row);
ii          sum(ii) filter(where ii%2=0) over defw
----------  --------------------------------------
1
2           2
3           2
4           6
5           6
6           12

　

参考