サブクエリを使用するクエリの組み立て方

#概要
[相生葵の Advent Calendar 2020 3日目](https://adventar.org/calendars/5480)の記事です。

このページではサブクエリを用いたクエリの組み立て方を書きます。
目的のデータを取得することを目的にします。
チューニングについては書きません。

joinとか集計とかは書けるけど、サブクエリが書けないという状況を想定しています。
想定しているデータベース管理システムはMySQLです。

#想定状況
##テーブル
グループ対抗の運動会の景品を集計する状況を想定しています。

<!--
create table result(
　group_id integer,
  competition_id integer,
  `rank` integer
);
create table score(
　competition_id integer,
  `rank` integer,
  score integer
);
create table reward(
　score integer,
  name varchar(50)
);

insert into result
values
(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);

insert into score
values
(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);

insert into reward
values
(100,'ケーキ'),
(70,'ポテトチップス'),
(50,'オレンジジュース');
-->

<b>resultsテーブル</b>

|  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テーブル（競技）がある想定です。

<b>scoresテーブル</b>

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

<b>rewardsテーブル</b>

|  score  |  name  |
| ---- | ---- |
|  100  |  ケーキ  |
|  70  |  ポテトチップス  |
|  50  |  オレンジジュース  |

※scoreごとに1つnameの商品を獲得できます。
小数点は以下は切り上げます。

##集計するデータ
各グループ（group_id）の得点に応じた景品の数を集計します。

#クエリを作成していく
##①出したいものを明確にする
ここでは「各グループの得点に応じた景品の数」を集計します。
目的がはっきりしていると、考えやすくなります。

##②集計するものを出す工程を考える
いきなりクエリを作成しようとすると、「どのように出すか」と「クエリをどのように組み立てるか」を同時に考えることになり難しいので
まずは「どのように出すか」を考えます。

各グループの得点に応じた景品の数を出すには、

1. 各グループの得点を集計する
2. ①の結果をもとに景品の数を出す

という2工程で出せそうということが分かります。

##③工程にそってクエリを書いていく
「どのように出すか」を考えたので、「クエリをどのように組み立てるか」を考えていきます。

###1. 各グループの得点を集計する
```sql

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 で出したデータを一つのテーブルとみなしてクエリを作成します。

```sql

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つのテーブルとして扱う