MySQL10本ノック作ってみた！

![caspar-camille-rubin-224229-unsplash.jpg](https://qiita-image-store.s3.amazonaws.com/0/261265/425d0a5f-ddab-482d-9e06-d4f4c8ebcf3f.jpeg)

## 2019/02/28 更新
MySQL8バージョン作りました！
https://qiita.com/yasumon/items/33579a2cb85b320f262c

## はじめに

皆さん、どうもです。[ｋ．ｓ．ロジャース](https://www.wantedly.com/companies/ks-rogers)のやすもんです。
今回は、業務でそれなりによく使うMySQLを設問形式でまとめていこうと思います。
ちなみにバージョンはmysql5.7.19です。Window関数を使えないという不便な状況でがんばりましょう！
また、初級から上級編で用意しています。

## 事前準備
mysql公式のサンプルデータベースをimportする
http://downloads.mysql.com/docs/world.sql.gz
↑からファイルをダウンロードし、gzを展開して`world.sql`というSQLファイルを実行すると、worldというDBが生成され、以下のようなデータが格納される
今回はFK周りの設定は不要なので、cityとcountrylanguageに設定されているFKは削除しておく

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/a37a787d-6299-60cd-a82e-0c87683014b6.png)

ちなみに、Sequel Proを使っています

## 問題
### 1本目(初級)
cityテーブルにて`CountryCode=JPN`のレコードを抽出せよ

### 2本目(初級)
cityテーブルにて`CountryCode=JPN`のレコードを抽出し、`Popultion`の合計・平均・最大値・最小値を算出せよ

### 3本目(初級)
cityテーブルにて`CountryCode`単位で集計し、`Popultion`の平均を算出、`Popultion`の平均が高い順に10件取得せよ

### 4本目(中級)
cityテーブルとcountryテーブルを結合し、国名・大陸・シティ名を表示せよ

### 5本目(中級)
cityテーブルにて`CountryCode`ごとに`Population`の平均値を算出し、countryテーブルと結合、国名・大陸・平均Populationを表示せよ

### 6本目(中級)
cityテーブルにて`CountryCode`ごとに`Population`の平均値を算出した結果と、countryテーブルにて`GNP`と`GNPOld`を比較し、大きい値の方を`GNP_big`としてカラムに格納した結果を結合し、国名・大陸・平均Population・GNPBigを表示せよ

### 7本目(中級)
6本目の結果に対して、`continent_name`で集約を行い`avg_population`の最大値が10万以上、GNP_bigの平均値が10万以上の結果を抽出して表示せよ

### 8本目(上級)
`country`テーブルにidを振りcoutry_with_idテーブルを作成
その後、cityとcoutrylanguageのテーブルのCountryCodeカラムを↑で付与したidに更新せよ

### 9本目(上級)
countrylanguageテーブルにてPercentageにランキングを付与せよ

### 10本目(上級)
9本目のSQLを参考にして
countrylanguageテーブルにてCountryCode単位で最もPercentageが高い言語をcountry_with_idテーブルに結合し
cityテーブルにてCountryCode単位で最もPopulationが高いシティをcountry_with_idテーブルに結合
その後、PercentageとPopulationをキーに降順でソートせよ

## 正解
### 1本目(初級)
#### 問題
cityテーブルにて`CountryCode=JPN`のレコードを抽出せよ

#### 解説
純粋にwhere文で条件を指定するだけですね

#### 回答例
```sql
select *
from city
where CountryCode = 'JPN'
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/aa877a8e-7903-cf2f-aa74-2782d73ef00d.png)

### 2本目(初級)
#### 問題
cityテーブルにて`CountryCode=JPN`のレコードを抽出し、`Popultion`の合計・平均・最大値・最小値を算出せよ

#### 解説
`group by`を用いて集約させ、`sum`・`avg`・`max`・`min`で集計する感じですね

#### 回答例
```sql
select CountryCode, sum(Population), avg(Population), max(Population), min(Population)
from city
where CountryCode = 'JPN'
group by CountryCode
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/95721a04-7e4f-6614-9f19-5f5562af575e.png)

### 3本目(初級)
#### 問題
cityテーブルにて`CountryCode`単位で集計し、`Popultion`の平均を算出、`Popultion`の平均が高い順に10件取得せよ

#### 解説
`order by`を用いてソートさせます
よく自分も忘れるのですがASCが昇順でDESCが降順です
ちなみに、何も指定しないとASC(昇順)になります

#### 回答例
```sql
select CountryCode, avg(Population) as avg_Population
from city
group by CountryCode
order by avg_Population DESC
limit 10
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/a920d894-fd43-e032-d199-6c870ddf24b5.png)

### 4本目(中級)
#### 問題
cityテーブルとcountryテーブルを結合し、国名・大陸・シティ名を表示せよ

#### 解説
`left join`を用いてテーブルの結合を行います
他にも`inner join`などがありますが、`inner join`は紐付かなかったデータが出力されず、結果と照らし合わせて脳内デバッグがやりにくいため、個人的には`left join`のほうがわかりやすくて好きです(もちろん、要件によります)
`as`を用いてカラム名の変更を行うことで見やすくしてます
注意点としては、`name`などの別テーブルと被るカラム名は`country.name`のようにどのテーブルのカラム名かをわかるように記述しないとエラーが出ます

#### 回答例
```sql
select country.name as country_name
    , country.Continent as continent_name
    , city.name as city_name
from city 
    left join country 
    on city.CountryCode = country.Code
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/b59455a6-2da4-c110-2da9-104b42cf4ebd.png)

### 5本目(中級)
#### 問題
cityテーブルにて`CountryCode`ごとに`Population`の平均値を算出し、countryテーブルと結合、国名・大陸・平均Populationを表示せよ

#### 解説
基本的に、joinする前に集約系の処理はしたほうが良いと考えてます
joinするとデータ量が増える場合が多いので、join後の`group by`はあまりしたくないというモチベーションですね

#### 回答例
```sql
select country.name as country_name
    , country.Continent as continent_name
    , city_calc.avg_population as avg_population
from (
    select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
) as city_calc
    left join country 
    on city_calc.CountryCode = country.Code
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/7c886789-c5b1-e886-b975-897d3df88238.png)

### 6本目(中級)
#### 問題
cityテーブルにて`CountryCode`ごとに`Population`の平均値を算出した結果と、countryテーブルにて`GNP`と`GNPOld`を比較し、大きい値の方を`GNP_big`としてカラムに格納した結果を結合し、国名・大陸・平均Population・GNPBigを表示せよ

#### 解説
5本目と同様joinする前に必要な処理をしてしまいます
case文を用いることでプログラムのif文のような結果を得ることができます

#### 回答例
```sql
select country_calc.name as country_name
    , country_calc.Continent as continent_name
    , city_calc.avg_population as avg_population
    , country_calc.GNP_big as GNP_big
from (
    select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
) as city_calc
    left join (
        select Code
            , Name
            , Continent
            , case
                when GNP >= GNPOld then GNP
                when GNP < GNPOld then GNPOld
                when GNPOld is null then GNP
                else null
            end as GNP_big
        from country
    ) as country_calc
    on city_calc.CountryCode = country_calc.Code
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/ed22b420-b219-51e1-90fb-e2cdbebc7229.png)


### 7本目(中級)
#### 問題
6本目の結果に対して、`continent_name`で集約を行い`avg_population`の最大値が10万以上、GNP_bigの平均値が10万以上の結果を抽出して表示せよ

#### 解説
`group by`を行った後の条件での絞り込みはhavingを用います
ちなみに、MySQLの実行順序は
from→join→where→group by→sumなど→having→select→order by→limit
という感じです

#### 回答
```sql
select country_calc.Continent as continent_name
    , max(city_calc.avg_population) as max_avg_population
    , avg(country_calc.GNP_big) as avg_GNP_big
from (
    select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
) as city_calc
    left join (
        select Code
            , Name
            , Continent
            , case
                when GNP >= GNPOld then GNP
                when GNP < GNPOld then GNPOld
                when GNPOld is null then GNP
                else null
            end as GNP_big
        from country
    ) as country_calc
    on city_calc.CountryCode = country_calc.Code
group by country_calc.Continent
having max_avg_population >= 100000 and avg_GNP_big >= 100000
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/f6378ad1-ca9a-2203-9223-66adbf4d0082.png)


### 8本目(上級)
#### 問題
`country`テーブルにidを振りcoutry_with_idテーブルを作成
その後、cityとcoutrylanguageのテーブルのCountryCodeカラムを↑で付与したidに更新せよ

#### 解説
まず、row_numberをidとしてふったテーブルを作成
その後、update文を用いて各テーブルの値を更新します
この時、joinなどは基本的に不要です

#### 回答例
```sql
set @id:=0;

create table country_with_id as (
    select *, @id:=@id+1 as id
    from country
);

update
    country_with_id as t1,
    city as t2
set
    t2.CountryCode = t1.id
where
    t1.Code = t2.CountryCode
;

update
    country_with_id as t1,
    countrylanguage as t2
set
    t2.CountryCode = t1.id
where
    t1.Code = t2.CountryCode
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/f5610c34-c560-b0f7-a716-5b5dee27dc92.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/a9cc539d-e82b-cee9-cf71-4901e5f14ff3.png)

### 9本目(上級)
#### 問題
countrylanguageテーブルにてPercentageにランキングを付与せよ

#### 解説
`rank() over(partition by CountryCode order by Percentage desc) as language_rank`
のようにwindow関数がかければ簡単ですが、残念ながらmysql5.6ではそのような便利な関数がないので、クセのある記述方式で書いていきます

処理的にはrow_number用の変数を定義してgroupid=CountryCodeのときに`no`を+1していくような感じです
ちなみに見慣れない`<>`こいつは`!=`とほぼ同義です

#### 回答
```sql
set @no:=0;
set @groupid:=null;

select *
    , if(@groupid <> CountryCode, @no:=1, @no:=@no+1) as rank
    , @groupid:=CountryCode
from countrylanguage
order by CountryCode, Percentage DESC
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/007054d0-c4ac-7b3b-b59d-4a49ef278cd3.png)

### 10本目(上級)
#### 問題
9本目のSQLを参考にして
countrylanguageテーブルにてCountryCode単位で最もPercentageが高い言語をcountry_with_idテーブルに結合し
cityテーブルにてCountryCode単位で最もPopulationが高いシティをcountry_with_idテーブルに結合
その後、PercentageとPopulationをキーに降順でソートせよ

#### 解説
副問合せでmaxを使って抽出したり、自己結合することによっても同様の結果を得られますが、今回は9本目で行ったrankをつけてから抽出する方針で処理を行います
ポイントとしては7本目で解説したとおり、MySQLの実行順序は
from→join→where→group by→sumなど→having→select→order by→limit
となっているので、row_numberをふってるセクションにてwhere文でrow_numberの条件を指定できないというところです
手間ですが、副問合せでrow_numberを振り、その結果に対してwhere文で条件を指定しましょう

#### 回答例
```sql
set @city_no:=0;
set @city_groupid:=null;
set @language_no:=0;
set @language_groupid:=null;

select Code, Name, Continent, Language as popular_language, city_name as popular_city
from country_with_id
left join 
(
    select CountryCode, Language, Percentage
    from (
        select *
            , if(@language_groupid <> CountryCode, @language_no:=1, @language_no:=@language_no+1) as rank
            , @language_groupid:=CountryCode
        from countrylanguage
        order by CountryCode, Percentage DESC
    ) as lang_calc
    where rank = 1
) as lang_rank1
on lang_rank1.CountryCode = country_with_id.id
left join 
(
    select CountryCode, Name as city_name, Population
    from (
        select *
            , if(@city_groupid <> CountryCode, @city_no:=1, @city_no:=@city_no+1) as rank
            , @city_groupid:=CountryCode
        from city
        order by CountryCode, Population DESC
    ) as city_calc
    where rank = 1
) as city_rank1
on city_rank1.CountryCode = country_with_id.id
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/9baaf9e4-c2b5-5a0a-760f-6c8043e1458f.png)

## さいごに
いかがでしたでしょうか、今回は前職の経験を活かし、メンバー育成用も兼ねてMySQL10本ノックを作成しました。
個人的にはwindow関数が使えるようになれば一人前かな〜と考えてます。
前職ではBigQueryを使ってデータ分析をゴリゴリしてたので
今後、MySQLだけでなくBigQueryとかの10本ノックも作っていきたいな〜と思ってたり(弊社はコスト面からBQは普段あまり使いませんが。。。)
Mysql8系でwindow関数を使った簡単な書き方を紹介してもいいかなーとも思ったりしてます！
また、SQLの回答例ですが、あくまで例です。もちろん、今回記述した内容以外の書き方もありますので、ご了承くださいmm

### Wantedlyでもブログ投稿してます
Techブログに加えて会社ブログなどもやっているので、気になった方はぜひ覗いてみてください。
https://www.wantedly.com/companies/ks-rogers