やってみる

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

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

SQLite3窓関数

SQLite3

　11関数ある。window function。分析関数、OLAP機能ともいう。

成果物

一覧

窓関数 概要
row_number()
行番号を返す。現在のパーティション内の行番号。ウィンドウ定義のORDER BY句で定義された順序（またはそれ以外の任意の順序）で、1から始まる番号が付けられる。
rank()
順位を返す。同ランクが複数あれば欠番が生じる。各グループの最初のピアのrow_number()-ギャップのある現在の行のランク。ORDER BY句がない場合、すべての行はピアと見なされ、この関数は常に1を返す。
dense_rank()
順位を返す。同ランクが複数あっても欠番が生じない。パーティション内の現在の行のピアグループの番号-ギャップのない現在の行のランク。パーティションには、ウィンドウ定義のORDER BY句で定義された順序で1から始まる番号が付けられる。ORDER BY句がない場合、すべての行はピアと見なされ、この関数は常に1を返す。
percent_rank()
順位を返す。0.0〜1.0の値で。(rank-1)/(partition-rows-1) 。rankは組込ウィンドウ関数rank()で返される値で、partition-rowsはパーティション内の行の総数。パーティションに含まれる行が1つだけの場合、この関数は0.0を返す。
cume_dist()
累積分布。計算 行数/パーティション列数、行番号がグループとの最後のピアのrow_number()によって返される値であるパーティション列パーティション内の行の数。
ntile(N)
引数N個のグループに分けて、その番号を振る。引数Nは整数として扱われる。この関数は、できるだけ均等にN個のグループにパーティションを分割し、1との間の整数割り当てNをORDER BY句によって定義された順序で、各グループに、または他の任意の順序。必要に応じて、大きなグループが最初に発生する。この関数は、現在の行が属するグループに割り当てられた整数値を返す。
lag(expr)
(expr, offset)
(expr, offset, default)
前の行を返す。関数の最初の形式は、パーティション内の前の行に対して式exprを評価した結果を返す。または、前の行がない場合（現在の行が最初であるため）NULL。

　オフセット引数が提供される場合、それは負でない整数でなければならない。この場合、返される値は、パーティション内の現在の行の前の行オフセット行に対してexprを評価した結果である。 offsetが0の場合、exprは現在の行に対して評価される。現在の行の前に行オフセット行がない場合、NULLを返す。

　場合はオフセット引数が提供され、それは非負の整数でなければなりません。この場合、返される値は、パーティション内の現在の行の前の行オフセット行に対してexprを評価した結果です。場合オフセット 0である場合、 exprは、現在の行に対して評価されます。現在の行の前に行オフセット行がない場合 、NULLが返されます。

　デフォルトも指定されている場合、offsetで識別される行が存在しない場合、NULLの代わりにデフォルトが返される。
lead(expr)
(expr, offset)
(expr, offset, default)
次の行を返す。lead()関数の最初の形式は、パーティション内の次の行に対して式exprを評価した結果を返します。または、次の行がない場合（現在の行が最後であるため）、NULL。

　オフセット引数が提供される場合、それは負でない整数でなければなりません。この場合、返される値は、パーティション内の現在の行の後の行オフセット行に対してexprを評価した結果です。 offsetが0の場合、exprは現在の行に対して評価されます。現在の行の後に行オフセット行がない場合、NULLが返されます。

　デフォルトも指定されている場合、offsetで識別される行が存在しない場合、NULLの代わりにデフォルトが返されます。
first_value(expr)
最初の行を返す。各行のウィンドウフレームの最初の行に対して評価されたexprの値を返す。集計ウィンドウ関数と同じ方法で各行のウィンドウフレームを計算する。
last_value(expr)
最後の行を返す。各行のウィンドウフレームの最後の行に対して評価されたexprの値を返す。集計ウィンドウ関数と同じ方法で各行のウィンドウフレームを計算する。
nth_value(expr, N)
指定した行位置の行を返す。ウィンドウフレームの行Nに対して評価されたexprの値を返す。行はORDER BY句が存在する場合はORDER BY句で定義された順序で、それ以外の場合は任意の順序で、1から始まるウィンドウフレーム内で番号が付けられる。パーティションにN番目の行がない場合、NULLが返される。集計ウィンドウ関数と同じ方法で各行のウィンドウフレームを計算する。

例

create table student_grades(
  class text, 
  name text,
  point integer
);
insert into student_grades values 
  ('A', 'Adati',    53),
  ('A', 'Itou',     21),
  ('A', 'Uehara',   93),
  ('B', 'Kato',     82),
  ('B', 'Kida',     98),
  ('B', 'Kurihara', 66),
  ('B', 'Kei',      75),
  ('B', 'Koi',      75);

row_number

select row_number() over (order by rowid) from (select 'A' value union select 'B');

1
2
select row_number() over(order by rowid) from student_grades;

1
2
3
4
5
6
7
8
select row_number() over(order by rowid) rownum, name from student_grades;

1|Adati
2|Itou
3|Uehara
4|Kato
5|Kida
6|Kurihara
7|Kei
8|Koi

　partition by classによりクラスごとに番号が割り振られるようになった。これぞ窓関数。

select 
  class,
  row_number() over(partition by class order by rowid) rownum, 
  name 
from student_grades;

A|1|Adati
A|2|Itou
A|3|Uehara
B|1|Kato
B|2|Kida
B|3|Kurihara
B|4|Kei
B|5|Koi

rank()

　pointでランク付け。

select rank() over (order by point desc), point, name from student_grades;

1|98|Kida
2|93|Uehara
3|82|Kato
4|75|Kei
4|75|Koi
6|66|Kurihara
7|53|Adati
8|21|Itou

　pointでランク付け。（クラスごと）

select 
  rank() over (partition by class order by point desc), 
  class, point, name 
from student_grades;

1|A|93|Uehara
2|A|53|Adati
3|A|21|Itou
1|B|98|Kida
2|B|82|Kato
3|B|75|Kei
3|B|75|Koi
5|B|66|Kurihara

dense_rank()

　pointでランク付け。

select dense_rank() over (order by point desc), point, name from student_grades;

1|98|Kida
2|93|Uehara
3|82|Kato
4|75|Kei
4|75|Koi
5|66|Kurihara
6|53|Adati
7|21|Itou

　pointでランク付け。（クラスごと）

select 
  dense_rank() over (partition by class order by point desc), 
  class, point, name 
from student_grades;

1|A|93|Uehara
2|A|53|Adati
3|A|21|Itou
1|B|98|Kida
2|B|82|Kato
3|B|75|Kei
3|B|75|Koi
4|B|66|Kurihara

　同ランクが複数あっても欠番が生じない。

percent_rank()

　pointでランク付け。

select percent_rank() over (order by point desc), point, name from student_grades;

0.0|98|Kida
0.142857142857143|93|Uehara
0.285714285714286|82|Kato
0.428571428571429|75|Kei
0.428571428571429|75|Koi
0.714285714285714|66|Kurihara
0.857142857142857|53|Adati
1.0|21|Itou

　pointでランク付け。（クラスごと）

select 
  percent_rank() over (partition by class order by point desc), 
  class, point, name 
from student_grades;

0.0|A|93|Uehara
0.5|A|53|Adati
1.0|A|21|Itou
0.0|B|98|Kida
0.25|B|82|Kato
0.5|B|75|Kei
0.5|B|75|Koi
1.0|B|66|Kurihara

　ランキング全体のどのあたりにいるかがわかる。

cume_dist()

　pointで累積分布。

select cume_dist() over (order by point desc), point, name from student_grades;

0.125|98|Kida
0.25|93|Uehara
0.375|82|Kato
0.625|75|Kei
0.625|75|Koi
0.75|66|Kurihara
0.875|53|Adati
1.0|21|Itou

　pointでランク付け。（クラスごと）

select 
  cume_dist() over (partition by class order by point desc), 
  class, point, name 
from student_grades;

0.333333333333333|A|93|Uehara
0.666666666666667|A|53|Adati
1.0|A|21|Itou
0.2|B|98|Kida
0.4|B|82|Kato
0.8|B|75|Kei
0.8|B|75|Koi
1.0|B|66|Kurihara

ntile(N)

　結果セットを複数のバケットに分けて、その番号を振る。

select ntile(3) over (order by point desc), point, name from student_grades;

1|98|Kida
1|93|Uehara
1|82|Kato
2|75|Kei
2|75|Koi
2|66|Kurihara
3|53|Adati
3|21|Itou

　pointでソートし、クラスごとで分け、さらに2人ずつ分ける。

select 
  ntile(2) over (partition by class order by point desc), 
  class, point, name 
from student_grades;

1|A|93|Uehara
1|A|53|Adati
2|A|21|Itou
1|B|98|Kida
1|B|82|Kato
1|B|75|Kei
2|B|75|Koi
2|B|66|Kurihara

　勝組と負組に分けられた。恐ろしい。特にA組のItouさん居場所なし。

lag(expr),(expr, offset),(expr, offset, default)

　前のレコードを取得する。

select lag(point) over (order by point desc), point, name from student_grades;

|98|Kida
98|93|Uehara
93|82|Kato
82|75|Kei
75|75|Koi
75|66|Kurihara
66|53|Adati
53|21|Itou

　2つ前を取る。

select lag(point,2) over (order by point desc), point, name from student_grades;

|98|Kida
|93|Uehara
98|82|Kato
93|75|Kei
82|75|Koi
75|66|Kurihara
75|53|Adati
66|21|Itou

　2つ前を取る。ないときは-1。

select lag(point,2,-1) over (order by point desc), point, name from student_grades;

-1|98|Kida
-1|93|Uehara
98|82|Kato
93|75|Kei
82|75|Koi
75|66|Kurihara
75|53|Adati
66|21|Itou

lead(expr),(expr, offset),(expr, offset, default)

　1つ次のレコードを取得する。

select lead(point) over (order by point desc), point, name from student_grades;

93|98|Kida
82|93|Uehara
75|82|Kato
75|75|Kei
66|75|Koi
53|66|Kurihara
21|53|Adati
|21|Itou

　2つ次を取る。

select lead(point,2) over (order by point desc), point, name from student_grades;

82|98|Kida
75|93|Uehara
75|82|Kato
66|75|Kei
53|75|Koi
21|66|Kurihara
|53|Adati
|21|Itou

　2つ次を取る。ないときは-1。

select lead(point,2,-1) over (order by point desc), point, name from student_grades;

82|98|Kida
75|93|Uehara
75|82|Kato
66|75|Kei
53|75|Koi
21|66|Kurihara
-1|53|Adati
-1|21|Itou

first_value(expr)

　先頭レコードを取得する。

select first_value(point) over (order by point desc), point, name from student_grades;

98|98|Kida
98|93|Uehara
98|82|Kato
98|75|Kei
98|75|Koi
98|66|Kurihara
98|53|Adati
98|21|Itou

　クラスの先頭レコードを取得する。

select 
  first_value(point) over (partition by class order by point desc), 
  point, name 
from student_grades;

93|93|Uehara
93|53|Adati
93|21|Itou
98|98|Kida
98|82|Kato
98|75|Kei
98|75|Koi
98|66|Kurihara

last_value(expr)

　末尾レコードを取得する。

select last_value(point) over (order by point desc), point, name from student_grades;

98|98|Kida
93|93|Uehara
82|82|Kato
75|75|Kei
75|75|Koi
66|66|Kurihara
53|53|Adati
21|21|Itou

　クラスの末尾レコードを取得する。

select 
  last_value(point) over (partition by class order by point desc), 
  point, name 
from student_grades;

93|93|Uehara
53|53|Adati
21|21|Itou
98|98|Kida
82|82|Kato
75|75|Kei
75|75|Koi
66|66|Kurihara

　末尾レコード取得。（範囲指定）

select 
  last_value(point) over (
    order by point desc
    range between unbounded preceding and unbounded following), 
  point, name 
from student_grades;

21|98|Kida
21|93|Uehara
21|82|Kato
21|75|Kei
21|75|Koi
21|66|Kurihara
21|53|Adati
21|21|Itou

nth_value(expr, N)

　点数が2番目のレコードを取得する。

select nth_value(point, 2) over (order by point desc), point, name from student_grades;

|98|Kida
93|93|Uehara
93|82|Kato
93|75|Kei
93|75|Koi
93|66|Kurihara
93|53|Adati
93|21|Itou

　クラスの末尾レコードを取得する。

select 
  nth_value(point,2) over (partition by class order by point desc), 
  point, name 
from student_grades;

|93|Uehara
53|53|Adati
53|21|Itou
|98|Kida
82|82|Kato
82|75|Kei
82|75|Koi
82|66|Kurihara

対象環境

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

前回まで