【SQL学習記録】group by復習

SQLのgroup by句がなんとなくしかわかってなかったので改めて勉強しました。

まだまだ理解は浅いですが、少しずつ知見を深めるためにアウトプットしていきます。

group byは集約関数と呼ばれ、DBテーブルの内容をグループに分けて集計できる。

集計する操作としては最大値を取得するmax()、最小値を取得するmin()、平均を取得するavg()、合計するsum()、行数を数えるcount()などがある。

練習用に架空の音楽チームのメンバーのデータを持つteam_memberテーブルを以下のSQLで作成しました。構造は練習用なので適当です。

testdb1=> create table team_member (
id integer 
, name text
, inst text
, height integer
, gender varchar(2)
);
CREATE TABLE  

testdb1=> insert into team_member (id, name, inst, height, gender)
values  
(1, "member1", "gt", 180, "M")
,(2, 'member2', 'ba', 170, 'F')
,(3, 'member3', 'ba', 171, 'F')
,(4, 'member4', 'dr', 160, 'F')
 ;
INSERT 0 3
testdb1=> select * from team_member
;
 id |  name   | inst | height | gender 
----+---------+------+--------+--------
  1 | member1 | gt   |    180 | M
  2 | member2 | ba   |    170 | F
  3 | member3 | ba   |    171 | F
  4 | member4 | dr   |    160 | F

最大の身長を取得したい場合、グループ分けの仕方によって集計結果は変わる。

たとえば、genderでグループ分けすると、M(男性)の最大は180、F(女性)の最大は171となる。
このレコード件数だと目視ですぐわかるが何千行もある場合はSQLでしっかり書けないと現実的に集計は厳しい。

SQLでgenderごとに最大のheightを取得する場合はgroup by genderと書く。

testdb1=> select max(height), gender from team_member group by gender;
 max | gender 
-----+--------
 180 | M
 171 | F

また、このDB構造だとinstでグループ分けして最大の身長を取得することもできる。

testdb1=> select max(height), inst from team_member group by inst;
 max | inst 
-----+------
 160 | dr
 171 | ba
 180 | gt

ちなみに、特にグループ分けをしない場合は単純にteam_memberテーブルで一番高いheightの180が取得できる。

testdb1=> select max(height) from team_member;
 max 
-----
 180

max()で最大値を取得する他に、count()でinstごとの人数を取得することができる。

testdb1=> select count(*), inst from team_member group by inst;
 count | inst 
-------+------
     1 | dr
     2 | ba
     1 | gt

genderでグループ分けする場合

testdb1=> select count(*), gender from team_member group by gender;
 count | gender 
-------+--------
     1 | M
     3 | F

genderとinstでグループ分けする場合

testdb1=> select count(*), gender, inst from team_member group by inst, gender;
 count | gender | inst 
-------+--------+------
     1 | M      | gt
     1 | F      | dr
     2 | F      | ba

instがbaでgenderがMのレコードもあった方が練習になると思ったのでレコードを追加

testdb1=> insert into team_member (id, name, inst, height, gender)
testdb1-> values
testdb1-> (5, 'member5', 'ba', 190, 'M');
INSERT 0 1

testdb1=> select * from team_member order by id;
 id |  name   | inst | height | gender 
----+---------+------+--------+--------
  1 | member1 | gt   |    180 | M
  2 | member2 | ba   |    170 | F
  3 | member3 | ba   |    171 | F
  4 | member4 | dr   |    160 | F
  5 | member5 | ba   |    190 | M

このデータでinstでグループ分けするSQLとinstとgenderでグループ分けするSQLをそれぞれ実行する

testdb1=> select count(*), inst from team_member group by inst;
 count | inst 
-------+------
     1 | dr
     3 | ba
     1 | gt

testdb1=> select count(*), gender, inst from team_member group by inst, gender;
 count | gender | inst 
-------+--------+------
     1 | M      | ba
     1 | M      | gt
     1 | F      | dr
     2 | F      | ba

instだけでグループ分けした場合はgenderは考慮しないのでbaは3人だが、その3人の中に男性と女性がいるのでgenderをgroup byに加えるとinstがbaのレコードもgenderで別れて集計されることがわかる。

まとめ

もう少し複雑なデータを用意するともっと勉強になるかも知れませんが、まずは基礎を固める目的がこれぐらいの量のデータから手を動かして検証していくのが勉強になりそうな気がしました。

その他のSQL構文も不慣れなものはブログにアウトプットしながら地道に勉強していきます。