やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3窓関数(フレーム)パターン網羅

 フレームについてよくわからなかったので、とりあえずパターン網羅してみた。

成果物

まとめ

  • SQLite3.29.0の窓枠タイプrangeは数値型しか使えない
  • 構文エラーになるパターンがあった
    • between current row and M preceding
    • between N following and M preceding
    • between N following and current row

おさらい

フレームタイプ 意味
rows 現在の行を基準にして個々の行をカウントすることにより、フレームの開始および終了境界が決定される
groups 開始および終了境界が、現在のグループに関連する「グループ」をカウントすることによって決定される
range ORDER BY句に厳密に1つの用語Xが必要。パーティションの全行に対して式Xの値を計算し、Xの値が現在の行のXの値の特定の範囲内にある行をフレーミングすることにより、フレームの要素が決定される。詳細
フレーム境界 意味
unbounded preceding 境界はパーティションの最初の行である
式 preceding 式は負でない整数の定数数値式であること。境界は現在行とその前にある行まで。0 preceding=current row
式 preceding(フレームタイプ: rows) 境界は現在行とその前にある行まで。
式 preceding(フレームタイプ: groups) グループはピア行のセットである。order by句の語が同値の行。境界は現在行とそのグループの前のグループ。
式 preceding(フレームタイプ: range) order by句には単一の語Xが必要。Xii番目にあるX列値、Xcを現在行のX列値とする。境界は、XiXcの式内にある最初の行である。
  1. Xi,Xcいずれかが非数値なら、境界はXi is Xctrueとなる最初の行である
  2. それ以外の場合、order byascなら、境界はXi >= Xc-式となる最初の行である
  3. それ以外の場合、order bydescなら、境界はXi <= Xc-式となる最初の行である
current row 現在の行。rangeおよびgroupsフレームタイプの場合、exclude句によって特に除外されない限り、現在の行のピアもフレームに含まれる
式 following 式 precedingと同じだが、境界が現在行の「前」ではなく、現在行の「後」に単位である点が異なる
unbounded following 境界はパーティションの最後の行である
exclude 意味
exclude no others デフォルト。行を除外しない。フレームの開始と終了で定義されている中から。
exclude current row 現在の行はフレームから除外する。現在行のピアは、groupsおよびrangeフレームタイプのフレームに残ります。
exclude group 現在行およびそのピア行はフレームから除外する。exclude句を処理する場合、同じorder by値を持つすべての行、またはorder by句がない場合はパーティション内のすべての行は、フレームタイプがrowsであってもピアと見なす。
exclude ties 現在行はフレームの一部だが、現在行のピアは除外する。

 今回excludeは対象外。

データ

CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES   (1, 'A', 'one'  ),
                        (2, 'B', 'two'  ),
                        (3, 'C', 'three'),
                        (4, 'D', 'one'  ),
                        (5, 'E', 'two'  ),
                        (6, 'F', 'three'),
                        (7, 'G', 'one'  );

rows

rows unbounded preceding

 全結果セットの先頭をフレームとする。

select 
  min(b) over (
    order by b 
    rows unbounded preceding
  ) 
from t1;
A
A
A
A
A
A
A

rows N preceding

 現在行から1行前をフレームとする。

select 
  min(b) over (
    order by b 
    rows 1 preceding
  ) 
from t1;
A
A
B
C
D
E
F

rows current row

 現在行をフレームとする。

select 
  min(b) over (
    order by b 
    rows current row
  ) 
from t1;
A
B
C
D
E
F
G

rows between unbounded preceding and N preceding

 先頭から現在行までをフレームとするが、N行飛ばす?

select 
  group_concat(b) over (
    order by b 
    rows between unbounded preceding and 3 preceding
  ) 
from t1;


A
A,B
A,B,C
A,B,C,D

rows between unbounded preceding and current row

 先頭から現在行までをフレームとする。

select 
  group_concat(b) over (
    order by b 
    rows between unbounded preceding and current row
  ) 
from t1;
A
A,B
A,B,C
A,B,C,D
A,B,C,D,E
A,B,C,D,E,F
A,B,C,D,E,F,G

rows between unbounded preceding and N following

 先頭から現在行+N行をフレームとする。

select 
  group_concat(b) over (
    order by b 
    rows between unbounded preceding and 1 following
  ) 
from t1;
A,B
A,B,C
A,B,C,D
A,B,C,D,E
A,B,C,D,E,F
A,B,C,D,E,F,G
A,B,C,D,E,F,G

rows between unbounded preceding and unbounded following

 先頭から末尾までをフレームとする。

select 
  group_concat(b) over (
    order by b 
    rows between unbounded preceding and unbounded following
  ) 
from t1;
A,B,C,D,E,F,G
A,B,C,D,E,F,G
A,B,C,D,E,F,G
A,B,C,D,E,F,G
A,B,C,D,E,F,G
A,B,C,D,E,F,G
A,B,C,D,E,F,G

rows between N preceding and M preceding

 現在行とそのN行前までをフレームとするが、その末尾M行を消す。

  • フレーム位置
    • 開始: 現在行-N
    • 終了: 現在行-M
select 
  group_concat(b) over (
    order by b 
    rows between 1 preceding and 1 preceding
  ) 
from t1;
A
B
C
D
E
F

 なんか数値を変えるとよくわからんことになる。

N M 結果
1 2 全行空
1 3 全行空
2 1 Error: near "from": syntax error

 N=3,M=3なら以下。

select 
  group_concat(b) over (
    order by b 
    rows between 3 preceding and 1 preceding
  ) 
from t1;
A
A,B
A,B,C
B,C,D
C,D,E
D,E,F

rows between N preceding and current row

 現在行-N行から現在行までをフレームとする。

  • 開始: 現在行-N
  • 終了: 現在行
select 
  group_concat(b) over (
    order by b 
    rows between 3 preceding and current row
  ) 
from t1;
A
A,B
A,B,C
A,B,C,D
B,C,D,E
C,D,E,F
D,E,F,G

rows between N preceding and M following

 現在行-N行から現在行+M行までをフレームとする。

  • 開始: 現在行-N
  • 終了: 現在行+M
select 
  group_concat(b) over (
    order by b 
    rows between 1 preceding and 1 following
  ) 
from t1;
A,B
A,B,C
B,C,D
C,D,E
D,E,F
E,F,G
F,G

 移動平均を求めるのに使えそう。

rows between N preceding and unbounded following

 先頭から末尾までをフレームとする。

  • 開始: 現在行-N
  • 終了: 末尾
select 
  group_concat(b) over (
    order by b 
    rows between 1 preceding and unbounded following
  ) 
from t1;
A,B,C,D,E,F,G
A,B,C,D,E,F,G
B,C,D,E,F,G
C,D,E,F,G
D,E,F,G
E,F,G
F,G

rows between current row and M preceding

 現在行から現在行-M行前までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行-M
select 
  group_concat(b) over (
    order by b 
    rows between current row and 1 preceding
  ) 
from t1;
Error: unsupported frame specification

 え。その後、M2,3,-3としても同様。

rows between current row and current row

 現在行から現在行までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行
select 
  group_concat(b) over (
    order by b 
    rows between current row and current row
  ) 
from t1;
A
B
C
D
E
F
G

rows between current row and M following

 現在行から現在行+M行までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行+M
select 
  group_concat(b) over (
    order by b 
    rows between current row and 1 following
  ) 
from t1;
A,B
B,C
C,D
D,E
E,F
F,G
G

rows between current row and unbounded following

 現在行から末尾までをフレームとする。

  • 開始: 現在行
  • 終了: 末尾
select 
  group_concat(b) over (
    order by b 
    rows between current row and unbounded following
  ) 
from t1;
A,B,C,D,E,F,G
B,C,D,E,F,G
C,D,E,F,G
D,E,F,G
E,F,G
F,G
G

rows between N following and M preceding

 エラー。文脈的にfollowingprecedingの位置が逆。

  • 開始: 現在行+M
  • 終了: 現在行-M
select 
  group_concat(b) over (
    order by b 
    rows between 1 following and 1 preceding
  ) 
from t1;
Error: unsupported frame specification

 followingprecedingの位置が逆ならOKなのだろう。

rows between N following and current row

 エラー。文脈的にfollowingcurrent rowの位置が逆。

  • 開始: 現在行+N
  • 終了: 現在行
select 
  group_concat(b) over (
    order by b 
    rows between 1 following and current row
  ) 
from t1;
Error: unsupported frame specification

rows between N following and M following

 現在行+Nから現在行+M行までをフレームとする。

  • 開始: 現在行+N
  • 終了: 現在行+M
select 
  group_concat(b) over (
    order by b 
    rows between 1 following and 2 following
  ) 
from t1;
B,C
C,D
D,E
E,F
F,G
G

 もちろんN<=Mであるべき。さもないと空が返る。

select 
  group_concat(b) over (
    order by b 
    rows between 1 following and 1 following
  ) 
from t1;
B
C
D
E
F
G
select 
  group_concat(b) over (
    order by b 
    rows between 2 following and 1 following
  ) 
from t1;

rows between N following and unbounded following

 現在行+Nから末尾までをフレームとする。

  • 開始: 現在行+N
  • 終了: 末尾
select 
  group_concat(b) over (
    order by b 
    rows between 1 following and unbounded following
  ) 
from t1;
B,C,D,E,F,G
C,D,E,F,G
D,E,F,G
E,F,G
F,G
G

groups

groups unbounded preceding

 ピアの先頭をフレームとする。

select 
  min(b) over (
    partition by c 
    order by b 
    groups unbounded preceding
  ), b, c
from t1;
A|A|one
A|D|one
A|G|one
C|C|three
C|F|three
B|B|two
B|E|two

groups N preceding

 現在行から同一ピア内のN行前をフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups 1 preceding
  ), b, c
from t1;
A|A|one
A,D|D|one
D,G|G|one
C|C|three
C,F|F|three
B|B|two
B,E|E|two

groups current row

 現在行をフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups current row
  ), b, c
from t1;
A|A|one
D|D|one
G|G|one
C|C|three
F|F|three
B|B|two
E|E|two

groups between unbounded preceding and N preceding

 同一ピアの先頭から、その3行前までをフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between unbounded preceding and 3 preceding
  ), b, c
from t1;
|A|one
|D|one
|G|one
|C|three
|F|three
|B|two
|E|two

 そんな範囲は存在しないので空。

groups between unbounded preceding and current row

 同一ピアの先頭から現在行までをフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between unbounded preceding and current row
  ), b, c
from t1;
A|A|one
A,D|D|one
A,D,G|G|one
C|C|three
C,F|F|three
B|B|two
B,E|E|two

groups between unbounded preceding and N following

 同一ピアの先頭から現在行+N行をフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between unbounded preceding and 1 following
  ), b, c
from t1;
A,D|A|one
A,D,G|D|one
A,D,G|G|one
C,F|C|three
C,F|F|three
B,E|B|two
B,E|E|two

groups between unbounded preceding and unbounded following

 同一ピアの先頭から末尾までをフレームとする。

select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between unbounded preceding and unbounded following
  ), b, c
from t1;
A,D,G|A|one
A,D,G|D|one
A,D,G|G|one
C,F|C|three
C,F|F|three
B,E|B|two
B,E|E|two

groups between N preceding and M preceding

 同一ピアの現在行-Nから現在行-Mまでをフレームとする。

  • 開始: 現在行-N
  • 終了: 現在行-M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 preceding and 1 preceding
  ), b, c
from t1;
|A|one
A|D|one
D|G|one
|C|three
C|F|three
|B|two
B|E|two

groups between N preceding and current row

 同一ピアの現在行-N行から現在行までをフレームとする。

  • 開始: 現在行-N
  • 終了: 現在行
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 3 preceding and current row
  ), b, c
from t1;
A|A|one
A,D|D|one
A,D,G|G|one
C|C|three
C,F|F|three
B|B|two
B,E|E|two

groups between N preceding and M following

 同一ピアの現在行-N行から現在行+M行までをフレームとする。

  • 開始: 現在行-N
  • 終了: 現在行+M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 preceding and 1 following
  ), b, c
from t1;
A,D|A|one
A,D,G|D|one
D,G|G|one
C,F|C|three
C,F|F|three
B,E|B|two
B,E|E|two

groups between N preceding and unbounded following

 同一ピアの現在行-Nから末尾までをフレームとする。

  • 開始: 現在行-N
  • 終了: 末尾
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 preceding and unbounded following
  ), b, c
from t1;
A,D,G|A|one
A,D,G|D|one
D,G|G|one
C,F|C|three
C,F|F|three
B,E|B|two
B,E|E|two

groups between current row and M preceding

 同一ピアの現在行から現在行-M行前までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行-M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between current row and 1 preceding
  ), b, c
from t1;
Error: unsupported frame specification

 順序が逆なら成功するのだろう。

groups between current row and current row

 同一ピアの現在行から現在行までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between current row and current row
  ), b, c
from t1;
A|A|one
D|D|one
G|G|one
C|C|three
F|F|three
B|B|two
E|E|two

groups between current row and M following

 同一ピアの現在行から現在行+M行までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行+M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between current row and 1 following
  ), b, c
from t1;
A,D|A|one
D,G|D|one
G|G|one
C,F|C|three
F|F|three
B,E|B|two
E|E|two

groups between current row and unbounded following

 同一ピアの現在行から末尾までをフレームとする。

  • 開始: 現在行
  • 終了: 末尾
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between current row and unbounded following
  ), b, c
from t1;
A,D,G|A|one
D,G|D|one
G|G|one
C,F|C|three
F|F|three
B,E|B|two
E|E|two

groups between N following and M preceding

 エラー。文脈的にfollowingprecedingの位置が逆。

  • 開始: 現在行+M
  • 終了: 現在行-M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 following and 1 preceding
  ), b, c
from t1;
Error: unsupported frame specification

 followingprecedingの位置が逆ならOKなのだろう。

groups between N following and current row

 エラー。文脈的にfollowingcurrent rowの位置が逆。

  • 開始: 現在行+N
  • 終了: 現在行
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 following and current row
  ), b, c
from t1;
Error: unsupported frame specification

groups between N following and M following

 同一ピアの現在行+Nから現在行+M行までをフレームとする。

  • 開始: 現在行+N
  • 終了: 現在行+M
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 following and 2 following
  ), b, c
from t1;
D,G|A|one
G|D|one
|G|one
F|C|three
|F|three
E|B|two
|E|two

groups between N following and unbounded following

 同一ピアの現在行+Nから末尾までをフレームとする。

  • 開始: 現在行+N
  • 終了: 末尾
select 
  group_concat(b) over (
    partition by c 
    order by b 
    groups between 1 following and unbounded following
  ), b, c
from t1;
D,G|A|one
G|D|one
|G|one
F|C|three
|F|three
E|B|two
|E|two

range

  • order by句の語が対象
  • 行数でなく列値を使う
  • 型は数値のみ対象(それ以外はXi = Xcが真の行のみ取得という謎仕様)

 行数が不定のときに役立つとか。

create table T(price integer, buyed text);
insert into T values(100, '2019-01-01 00:00:00');
insert into T values(130, '2019-01-01 01:00:00');
insert into T values(108, '2019-01-01 20:00:00');
insert into T values(120, '2019-01-02 00:00:00');
insert into T values(105, '2019-01-02 10:00:00');
insert into T values(112, '2019-01-03 05:00:00');
insert into T values(119, '2019-01-03 14:00:00');
insert into T values(200, '2019-02-01 00:00:00');
insert into T values(300, '2019-03-01 00:00:00');
insert into T values(999, '2020-01-01 00:00:00');

range unbounded preceding

 先頭から現在行までをフレームとする。

select 
  min(price) over (
    order by buyed 
    range unbounded preceding
  ) 
from T;
100
100
100
100
100
100
100
100
100
100
select 
  group_concat(price) over (
    order by buyed 
    range unbounded preceding
  ), price
from T;
100|100
100,130|130
100,130,108|108
100,130,108,120|120
100,130,108,120,105|105
100,130,108,120,105,112|112
100,130,108,120,105,112,119|119
100,130,108,120,105,112,119,200|200
100,130,108,120,105,112,119,200,300|300
100,130,108,120,105,112,119,200,300,999|999

range N preceding

 現在行から1行前をフレームとする。

select 
  group_concat(price) over (
    order by buyed 
    range '1 day' preceding
  ) 
from T;
Error: frame starting offset must be a non-negative number
select 
  group_concat(price) over (
    order by buyed 
    range '2019-01-03 00:00:00' preceding
  ) 
from T;
Error: frame starting offset must be a non-negative number

 なんと、数値以外のときはXi = Xcが真の行のみ取得される。つまり日付型によるrangeは使えない……。というか、実行してみた結果をみるに、正数以外エラーになるのでは?

 日付が使えないとか……ないわ。SQLite3は日付型などなくテキスト型だからな……。日付でrangeできる日は来るのだろうか。バージョンアップによる改善がされたらいいな。

 仕方ないので数値priceを使う。

select 
  group_concat(price) over (
    order by price 
    range 110 preceding
  ), price
from T;
100|100
100,105|105
100,105,108|108
100,105,108,112|112
100,105,108,112,119|119
100,105,108,112,119,120|120
100,105,108,112,119,120,130|130
100,105,108,112,119,120,130,200|200
200,300|300
999|999

 どゆこと? 110以下だけ対象になると思ったのに。112とかも出ちゃってる。謎。

range current row

 現在行の値と同値ならフレームとする。のだと思う。

select 
  group_concat(price) over (
    order by price 
    range current row
  ), price
from T;
100|100
105|105
108|108
112|112
119|119
120|120
130|130
200|200
300|300
999|999

range between unbounded preceding and N preceding

 現在行値との差分がN以上または以下の、現在行以前までの行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and 110 preceding
  ), price
from T;
|100
|105
|108
|112
|119
|120
|130
|200
100,105,108,112,119,120,130|300
100,105,108,112,119,120,130,200,300|999
select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and 50 preceding
  ), price
from T;
|100
|105
|108
|112
|119
|120
|130
100,105,108,112,119,120,130|200
100,105,108,112,119,120,130,200|300
100,105,108,112,119,120,130,200,300|999
select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and 20 preceding
  ), price
from T;
|100
|105
|108
|112
|119
100|120
100,105,108|130
100,105,108,112,119,120,130|200
100,105,108,112,119,120,130,200|300
100,105,108,112,119,120,130,200,300|999

 Nは値でなく差分値。

range between unbounded preceding and current row

 先頭から現在行までをフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and current row
  ), price
from T;
100|100
100,105|105
100,105,108|108
100,105,108,112|112
100,105,108,112,119|119
100,105,108,112,119,120|120
100,105,108,112,119,120,130|130
100,105,108,112,119,120,130,200|200
100,105,108,112,119,120,130,200,300|300
100,105,108,112,119,120,130,200,300,999|999

range between unbounded preceding and N following

 現在行の値との差がN以内の行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and 10 following
  ), price
from T;
100,105,108|100
100,105,108,112|105
100,105,108,112|108
100,105,108,112,119,120|112
100,105,108,112,119,120|119
100,105,108,112,119,120,130|120
100,105,108,112,119,120,130|130
100,105,108,112,119,120,130,200|200
100,105,108,112,119,120,130,200,300|300
100,105,108,112,119,120,130,200,300,999|999

range between unbounded preceding and unbounded following

 先頭から末尾までをフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between unbounded preceding and unbounded following
  ), price
from T;
100,105,108,112,119,120,130,200,300,999|100
100,105,108,112,119,120,130,200,300,999|105
100,105,108,112,119,120,130,200,300,999|108
100,105,108,112,119,120,130,200,300,999|112
100,105,108,112,119,120,130,200,300,999|119
100,105,108,112,119,120,130,200,300,999|120
100,105,108,112,119,120,130,200,300,999|130
100,105,108,112,119,120,130,200,300,999|200
100,105,108,112,119,120,130,200,300,999|300
100,105,108,112,119,120,130,200,300,999|999

range between N preceding and M preceding

 現在行値-N〜現在行値-Mの値。差分値MNの行をフレームとする。

  • Xi >= Xcの行
  • N >= M
  • ふつうはpreceding and followingの順と思われる
select 
  group_concat(price) over (
    order by price 
    range between 50 preceding and 10 preceding
  ), price
from T;
|100
|105
|108
100|112
100,105,108|119
100,105,108|120
100,105,108,112,119,120|130
|200
|300
|999

range between N preceding and current row

 差分がNで現在行までに存在する行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between 10 preceding and current row
  ), price
from T;
100|100
100,105|105
100,105,108|108
105,108,112|112
112,119|119
112,119,120|120
120,130|130
200|200
300|300
999|999

range between N preceding and M following

 現在行値-N〜現在行値+M行の行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between 10 preceding and 20 following
  ), price
from T;
100,105,108,112,119,120|100
100,105,108,112,119,120|105
100,105,108,112,119,120|108
105,108,112,119,120,130|112
112,119,120,130|119
112,119,120,130|120
120,130|130
200|200
300|300
999|999

range between N preceding and unbounded following

 現在行値との差が10ある行からそれ以降までをフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between 10 preceding and unbounded following
  ), price
from T;
100,105,108,112,119,120,130,200,300,999|100
100,105,108,112,119,120,130,200,300,999|105
100,105,108,112,119,120,130,200,300,999|108
105,108,112,119,120,130,200,300,999|112
112,119,120,130,200,300,999|119
112,119,120,130,200,300,999|120
120,130,200,300,999|130
200,300,999|200
300,999|300
999|999

range between current row and M preceding

 エラー。precedingandの前につけるものと思われる。

select 
  group_concat(price) over (
    order by price 
    range between current row and 10 preceding
  ), price
from T;
Error: unsupported frame specification

range between current row and current row

 現在行から現在行までをフレームとする。

  • 開始: 現在行
  • 終了: 現在行
select 
  group_concat(price) over (
    order by price 
    range between current row and current row
  ), price
from T;
100|100
105|105
108|108
112|112
119|119
120|120
130|130
200|200
300|300
999|999

range between current row and M following

 現在行とその差分がM以上または以下の行をフレームとする。

  • 開始: 現在行
  • 終了: 現在行+M
select 
  group_concat(price) over (
    order by price 
    range between current row and 10 following
  ), price
from T;
100,105,108|100
105,108,112|105
108,112|108
112,119,120|112
119,120|119
120,130|120
130|130
200|200
300|300
999|999

range between current row and unbounded following

 現在行から末尾までをフレームとする。

  • 開始: 現在行
  • 終了: 末尾
select 
  group_concat(price) over (
    order by price 
    range between current row and unbounded following
  ), price
from T;
100,105,108,112,119,120,130,200,300,999|100
105,108,112,119,120,130,200,300,999|105
108,112,119,120,130,200,300,999|108
112,119,120,130,200,300,999|112
119,120,130,200,300,999|119
120,130,200,300,999|120
130,200,300,999|130
200,300,999|200
300,999|300
999|999

range between N following and M preceding

 エラー。文脈的にfollowingprecedingの位置が逆。

  • 開始: 現在行+M
  • 終了: 現在行-M
select 
  group_concat(price) over (
    order by price 
    range between 10 following and 20 preceding
  ), price
from T;
Error: unsupported frame specification

range between N following and current row

 エラー。文脈的にfollowingcurrent rowの位置が逆。

  • 開始: 現在行+N
  • 終了: 現在行
select 
  group_concat(price) over (
    order by price 
    range between 10 following and current row
  ), price
from T;
Error: unsupported frame specification

range between N following and M following

 現在行値+Nから現在行値+Mまでの行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between 10 following and 20 following
  ), price
from T;
112,119,120|100
119,120|105
119,120|108
130|112
130|119
130|120
|130
|200
|300
|999

range between N following and unbounded following

 現在行値+Nから末尾値までの行をフレームとする。

select 
  group_concat(price) over (
    order by price 
    range between 10 following and unbounded following
  ), price
from T;
112,119,120,130,200,300,999|100
119,120,130,200,300,999|105
119,120,130,200,300,999|108
130,200,300,999|112
130,200,300,999|119
130,200,300,999|120
200,300,999|130
300,999|200
999|300
|999

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで