やってみる

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

📌 ？
トップ > SQLite3 > SQLite3構文　窓関数

SQLite3構文　窓関数

SQLite3

　窓関数とは、ある区間だけを抽出し、それを元に計算する処理のこと。だと思う。

情報源

窓関数とは

　窓関数とは、ある有限区間（台）以外で0となる関数。

用途

　分析。窓関数で、ある区間だけを抽出し、それを元に計算する。

構文

名前 概要
パーティション 指定した列の値が同じならピア行とみなす
フレーム 集計窓関数で読み取るか否かを決める

構文制限

  • distinct不可
  • 窓関数は結果セットとselect文のorder by句にのみ現れる

窓関数

窓定義

　名前を付けて呼び出せる。window句を使う。

CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');

SELECT x, y, row_number() OVER win1, rank() OVER win2 
FROM t0 
WINDOW win1 AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       win2 AS (PARTITION BY y ORDER BY x)
ORDER BY x;

1|aaa|1|1
2|ccc|3|1
3|bbb|2|1

説明用データ

　以降のSQL文では、以下のテーブルを用いる。

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'  );

ウインドウチェーン

　ウインドウチェーンは、1つのウィンドウを別のウィンドウで定義できるようにする短縮形

SELECT group_concat(b, '.') OVER (
  win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1
WINDOW win AS (PARTITION BY a ORDER BY c);

A
B
C
D
E
F
G

filter句

where句

　式が真の行のみ、窓フレームに含まれる。

SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
  ORDER BY a
) AS group_concat
FROM t1 ORDER BY a;

one|1|A|A
two|2|B|A
three|3|C|A.C
one|4|D|A.C.D
two|5|E|A.C.D
three|6|F|A.C.D.F
one|7|G|A.C.D.F.G

　group_concat列にはc列値=twoのb列値が出てこない。filter句のwhere条件式によって除外されているから。

over句

　集計窓関数。over句を付与するとそれになる。集計関数と異なる点は、行数が変わらないこと。行が集約されることがない。

　over句内にorder by, partition by, フレームを含めることで集計窓関数とする。

SELECT a, b, group_concat(b, '.') OVER (
  ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS group_concat FROM t1;

1|A|A.B
2|B|A.B.C
3|C|B.C.D
4|D|C.D.E
5|E|D.E.F
6|F|E.F.G
7|G|F.G

窓名

　これが何だかよくわからん。構文ツリーにはbase-window-nameと書いてあるヤツ。

partition by

　partition by句は、指定した列の値が同じレコードを同一パーティションとするよう指示する。パーティションとは、レコードのグループを決める基準やその境界である。

　窓関数を計算するためには結果セットを1つ以上のパーティションに分ける必要がある。もしpartition by句がなければ、結果セットすべてを1つのパーティションとする。

SELECT c, a, b, group_concat(b, '.') OVER (
  PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;

one|1|A|A.D.G
one|4|D|D.G
one|7|G|G
three|3|C|C.F
three|6|F|F
two|2|B|B.E
two|5|E|E

　上記は、PARTITION BY cにより、c列の値でパーティション分けしている。one,two,threeの3つに分かれる。

order by

　結果がパーティション順にソートされている必要はない。selectのorder byとoverのorder byは別である。

SELECT c, a, b, group_concat(b, '.') OVER (
  PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY a;

one|1|A|A.D.G
two|2|B|B.E
three|3|C|C.F
one|4|D|D.G
two|5|E|E
three|6|F|F
one|7|G|G

　フレームをなくすと全レコードが対象となる。

SELECT a, b, 
  group_concat(b, '.') 
    OVER (ORDER BY a) 
      AS group_concat 
FROM t1;

1|A|A
2|B|A.B
3|C|A.B.C
4|D|A.B.C.D
5|E|A.B.C.D.E
6|F|A.B.C.D.E.F
7|G|A.B.C.D.E.F.G

フレーム

　フレームは、出力行が集計窓関数によって読み取られるかを決定する。

　フレームは以下の4つで構成される。

  • フレームタイプ: range,rows,groups
  • 開始フレーム境界
  • 終了フレーム境界
  • exclude句

　特徴は以下。

  • フレームは省略できる。省略するとcurrent rowになる
  • peers(ピア,仲間)
    • フレームタイプがrange/groupsのとき、すべてのorder by式で同じ値をもつ行はpeers(ピア,仲間)とみなす
    • order by式がないとき、すべての行はピアである
    • ピアは常に同一フレーム内にある
SELECT a, b, c, 
       group_concat(b, '.') OVER (ORDER BY c) AS group_concat 
FROM t1 ORDER BY a;

1|A|one|A.D.G
2|B|two|A.D.G.C.F.B.E
3|C|three|A.D.G.C.F
4|D|one|A.D.G
5|E|two|A.D.G.C.F.B.E
6|F|three|A.D.G.C.F
7|G|one|A.D.G

range/rows/groups

フレームタイプ 意味
rows 現在の行を基準にして個々の行をカウントすることにより、フレームの開始および終了境界が決定される
groups 開始および終了境界が、現在のグループに関連する「グループ」をカウントすることによって決定される
range ORDER BY句に厳密に1つの用語Xが必要。パーティションの全行に対して式Xの値を計算し、Xの値が現在の行のXの値の特定の範囲内にある行をフレーミングすることにより、フレームの要素が決定される。詳細

　rows,groupsは似ている。現在の行を基準にしてカウントする点が。違いはrowが個々の行をカウントするのに対し、groupsはピアグループをカウントすること。

　rangeは、現在の行に相対的な値の範囲内にある式の値を探すことにより、フレームの範囲を決定する。

フレーム境界 意味
unbounded preceding 境界はパーティションの最初の行である
式 preceding 式は負でない整数の定数数値式であること。境界は現在行とその前にある式行まで。0 preceding=current row
式 preceding(フレームタイプ: rows) 境界は現在行とその前にある式行まで。
式 preceding(フレームタイプ: groups) グループはピア行のセットである。order by句の語が同値の行。境界は現在行とそのグループの前の式グループ。
式 preceding(フレームタイプ: range) order by句には単一の語Xが必要。Xiをi番目にあるX列値、Xcを現在行のX列値とする。境界は、XiがXcの式内にある最初の行である。
  1. Xi,Xcいずれかが非数値なら、境界はXi is Xcがtrueとなる最初の行である
  2. それ以外の場合、order byがascなら、境界はXi >= Xc-式となる最初の行である
  3. それ以外の場合、order byがdescなら、境界は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 現在行はフレームの一部だが、現在行のピアは除外する。

対象環境

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

前回まで