#概要
相生葵の Advent Calendar 2020 3日目の記事です。
このページではサブクエリを用いたクエリの組み立て方を書きます。
目的のデータを取得することを目的にします。
チューニングについては書きません。
joinとか集計とかは書けるけど、サブクエリが書けないという状況を想定しています。
想定しているデータベース管理システムはMySQLです。
#想定状況
##テーブル
グループ対抗の運動会の景品を集計する状況を想定しています。
resultsテーブル
| group_id | competition_id | rank |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 1 | 2 | 3 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 1 | 3 | 2 |
| 2 | 3 | 3 |
| 3 | 3 | 1 |
※別にgroupテーブル（参加グループ）とcompetitionテーブル（競技）がある想定です。
scoresテーブル
| competition_id | rank | score |
|---|---|---|
| 1 | 1 | 100 |
| 1 | 2 | 70 |
| 1 | 3 | 50 |
| 2 | 1 | 200 |
| 2 | 2 | 100 |
| 2 | 3 | 70 |
| 3 | 1 | 150 |
| 3 | 2 | 130 |
| 3 | 3 | 100 |
rewardsテーブル
| score | name |
|---|---|
| 100 | ケーキ |
| 70 | ポテトチップス |
| 50 | オレンジジュース |
※scoreごとに1つnameの商品を獲得できます。
小数点は以下は切り上げます。
##集計するデータ
各グループ（group_id）の得点に応じた景品の数を集計します。
#クエリを作成していく
##①出したいものを明確にする
ここでは「各グループの得点に応じた景品の数」を集計します。
目的がはっきりしていると、考えやすくなります。
##②集計するものを出す工程を考える
いきなりクエリを作成しようとすると、「どのように出すか」と「クエリをどのように組み立てるか」を同時に考えることになり難しいので
まずは「どのように出すか」を考えます。
各グループの得点に応じた景品の数を出すには、
- 各グループの得点を集計する
- ①の結果をもとに景品の数を出す
という2工程で出せそうということが分かります。
##③工程にそってクエリを書いていく
「どのように出すか」を考えたので、「クエリをどのように組み立てるか」を考えていきます。
###1. 各グループの得点を集計する
select
r.group_id as group_id,
sum(s.score) as overrall_score
from
results as r
join
scores as s
on r.competition_id = s.competition_id
and r.rank = s.rank
group by
r.group_id
このクエリで各グループの総合得点を出します。
結果は以下のようになります。
| group_id | overrall_score |
|---|---|
| 1 | 300 |
| 2 | 370 |
| 3 | 300 |
###2. ①の結果をもとに景品の数を出す
1 で出したデータを一つのテーブルとみなしてクエリを作成します。
select
t1.group_id as group_id,
r.name as reward_name,
ceil((t1.overrall_score / r.score)) as quantity
from
(
select
r.group_id as group_id,
sum(s.score) as overrall_score
from
results as r
join
scores as s
on r.competition_id = s.competition_id
and r.rank = s.rank
group by
r.group_id
) as t1 /* 1 の結果データを「t1テーブル」として使用する*/
left join
rewards as r
on t1.overrall_score >= r.score
結果は以下になります。
| group_id | reward_name | reward_name |
|---|---|---|
| 1 | ケーキ | 3 |
| 2 | ケーキ | 4 |
| 3 | ケーキ | 3 |
| 1 | ポテトチップス | 5 |
| 2 | ポテトチップス | 6 |
| 3 | ポテトチップス | 5 |
| 1 | オレンジジュース | 6 |
| 2 | オレンジジュース | 8 |
| 3 | オレンジジュース | 6 |
これで目的のデータを集計することができました。
#まとめ
- 欲しいデータを得るための工程を「考えるフェーズ」と「クエリを組み立てるフェーズ」に分ける
- サブクエリは1つのテーブルとして扱う