MySQL⑧ データの集計処理、サブクエリ

users_with_team テーブル

id	name	score	team
1	AAA	11	team-A
2	BBB	22	team-B
3	CCC	33	team-C
4	DDD	44	team-B

####データの個数を調べる　count()
select count() from users_with_team;
➡️４
####特定のカラムのデータの個数を調べる　count(カラム名)
select count(score) from users_with_team;
➡️４
####sum で合計、 min で最小値、 max で最大値、 avg で平均
select sum(カラム名) from users_with_team;　➡️１１０
select min(カラム名) from users_with_team;　➡️１１
select max(カラム名) from users_with_team;　➡️４４
select avg(カラム名) from users_with_team;　➡️２７.５

####重複したレコードを取り除いて取得する　distinct カラム名
select distinct team from users_with_team;

team
team-A
team-B
team-C

####重複したレコードの数を数える
select count(distinct team) from users_with_team;
➡️３

#グループ化する

####グループごとの合計をだす　group by グループ化の基準にするカラム名
select sum(score), team from users_with_team group by team;

####逆順にする　desc
select sum(score), team from users_with_team group by team desc;

####グループ化の後に条件をつける　group by グループ化の基準にするカラム名 having 条件
havingでは、グループ化したカラム、集計した値以外使えない
select sum(score), team from users_with_team group by team having sum(score) > 10.0;

####特定の条件でグループ化する　where 条件 group by グループ化の基準にするカラム名
where で条件付きで抽出した後にグループ化する
select sum(score), team from users_with_team where id > 3 group by team;

#一時的に使用するテーブル　サブクエリ

元のテーブル

id	name	score
1	AAA	11
2	BBB	22
3	CCC	33
4	DDD	44

ここから集計の為のテーブルを一時的に作成する

select sum(t.score), t.team from(
　　select　　　　　　　⬅️ここから
　　　　id,
　　　　name,
　　　　score,
　　　　case
　　　　　　when score > 8.0 then 'Team-A'
　　　　　　when score > 6.0 then 'Team-B'
　　　　　　else 'Team-C'
　　　　end as team
　　from users) as t　　　⬅️ここまでがサブクエリ
group by t.team;

#####サブクエリを使用しないと users_with_teamテーブルを作成してから集計する
create table users_with_team
select id, name, score,
　　　　case
　　　　　　when score > 8.0 then 'Team-A'
　　　　　　when score > 6.0 then 'Team-B'
　　　　　　else 'Team-C'
　　　　end as team
from users　

select sum(score), team from users_with_team group by team;

抽出条件を保存する view

viewはテーブルの様に扱われる
####viewを作成する　create view ビュー名 as 抽出条件
create view top3 as select * from users order by score desc limit 3;

####保存された抽出条件を確認する　show create view ビュー名;
show create view top3;

####viewを削除する
drop view top3;