やってみる

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

📌 ？
トップ > SQLite3 > SQLite3集約関数（over 句）

SQLite3集約関数（over 句）

SQLite3

　集約関数でorver句を使ってみた。

成果物

データ

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

　9ある集約関数にover句を使ってみる。

avg

select 
  avg(point) over (order by class), 
  class, name, point 
  from student_grades;

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

　できた。クラスごとの平均点。なぜかpartition by classしなくてもいけた。

count

select 
  count(*) 
    filter(where 80<=point) 
    over (order by class), 
  class, name, point 
  from student_grades;

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

　80点を超えた人数をクラスごとに、と思ったのだがB組おかしい。3人でなく2人では？

　やはりパーティションをしっかりクラスにすべきか。

select 
  count(*) filter(where 80<=point) over (partition by class order by point desc), 
  class, name, point 
  from student_grades;

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

　できた。一応、クラスごとに全レコードを含めてみる。

select 
  count(*) 
    filter(where 80<=point) 
    over (
      partition by class 
      order by point desc 
      groups between unbounded preceding and unbounded following
    ), 
  class, name, point 
  from student_grades;

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

　同じ。いまいちフレームの使い方がわからん。これ使わなくても集計できたのだが。

group_concat(X),(X,Y)

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

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

　自分より前にある行の点数を結合したようだ。あ、はい、そうなるんですか。へぇ。よくわからんけど。いつ使うと、どう有効なの？

　

たぶん木構造とかで役立つ？　と思ったがわからん。

create table T(id integer primary key, name text, parent integer);
insert into T values 
  (1, 'root', 0),
  (2, 'n1',   1),
  (3, 'n2',   1),
  (4, 'n11',  2);
select 
  id, name, 
  group_concat(name, '/') 
    over (order by id)
  from T;

1|root|root
2|n1|root/n1
3|n2|root/n1/n2
4|n11|root/n1/n2/n11

　ぜんぜん違う。親が同じのをピアにしても仕方ないし。

　idとparentが一致したら親子。それを抽出できないか？

select 
  id, name, 
  group_concat(name, '/') 
    filter (id = parent)
    over (order by id)
  from T;

1|root|
2|n1|
3|n2|
4|n11|

　違いますね、はい。これはたぶん同一行のidとparentの一致確認しているのだろう。ダメだ、まるで理解できてない。

max

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

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

min

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

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

　おや？　それぞれ自分の行が出ている。たぶんフレームだな。

select 
  min(point) 
    over (
      partition by class 
      order by point desc
      groups between unbounded preceding and unbounded following
    ), 
  class, name, point 
  from student_grades;

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

　並び順が昇順、降順、どちらであっても、クラス内の最大・最小を求めたいなら、フレーム定義しておくべき。ということかな？

sum(), total()

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

93|A|Uehara|93
146|A|Adati|53
167|A|Itou|21
98|B|Kida|98
180|B|Kato|82
330|B|Kei|75
330|B|Koi|75
396|B|Kurihara|66

　またおかしい。フレームを指定してみる。

select 
  sum(point) 
    over (
      partition by class 
      order by point desc
      groups between unbounded preceding and unbounded following
    ), 
  class, name, point 
  from student_grades;

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

　できた。やはりフレームの定義を適切にしておくべき場合があるようだ。省略したら現在行からそれ以前までの行になるのだろう。

　もっとも、今回のようにクラス内合計点など求めても、あまり意味はないかもしれないが。

所感

　窓関数の使い方や全体像のイメージがつかめない。使いこなせる気がまったくしない。フレームがよくわかってない。

対象環境

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

前回まで