MySQL8でMySQL10本ノックを解いてみる！

![caspar-camille-rubin-224229-unsplash.jpg](https://qiita-image-store.s3.amazonaws.com/0/261265/502b2582-12f5-9e2e-1005-957338da7603.jpeg)
## はじめに

皆さん、どうもです。[ｋ．ｓ．ロジャース](https://www.wantedly.com/companies/ks-rogers)のやすもんです。
前回の記事 [MySQL10本ノック作ってみた！](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2) が想像以上に好評だったので、その続きでmysql8版を書いてみます！
docker使って動かすところからおこない
前回の [MySQL10本ノック作ってみた！](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2) からMySQL8で記述方式が変わる設問を参照し、参考回答を記述していこうと思います

## DockerでMySQL8環境構築
Dockerのインストールなどは割愛します
適当にworkフォルダ作成して準備します

```
$ mkdir sand_box
$ cd sand_box
$ mkdir mysql8
$ cd mysql8
$ mkdir volumes
```

docker-compose.ymlを作成します

```
version: '2'
services:
  db:
    image: mysql:latest
    container_name: mysql8
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: hoge
    ports:
      - "3317:3306"
    volumes:
      - $PWD/volumes:/var/lib/mysql
```

docker-composeでたち上げます

```
$ docker compose up -d
$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
cc448a369afb        mysql:latest        "docker-entrypoint.s…"   11 hours ago        Up 11 hours         33060/tcp, 0.0.0.0:3317->3306/tcp   mysql8
```

## SequelProをMySQL8に対応させる
アプリケーションの中にあるSequelProを適当にリネーム

以下のコマンドでSequelProのnightlyバージョンをインストール

```
$ brew cask install homebrew/cask-versions/sequel-pro-nightly
```

<img width="244" alt="image.png" src="https://qiita-image-store.s3.amazonaws.com/0/261265/940e1129-6ba8-df9f-5fa6-a9d0ef1c5967.png">

nightlyバージョンのSequelProを起動して、先程dockerで立ち上げたMySQL8に接続

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/3d92d1b2-fea1-d781-17c6-6c50891a61a1.png)

前回の [事前準備](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2#%E4%BA%8B%E5%89%8D%E6%BA%96%E5%82%99) を元にサンプルデータをダウンロードし、DBにimportする

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/cfd07b06-0f48-0686-050e-b3625918d88d.png)

**※不安定？なのか調べていたらSequelProのnightlyバージョンでもMySQL8動かないという記事もチラホラあったので、これで動かない場合はコメント下さい！(解決できるかはわかりませんが。。。)**

## 問題
[前回の問題](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2#%E5%95%8F%E9%A1%8C) 1~4本はMySQL8でも記述方式変わらないので、飛ばします

### 5本目
cityテーブルにてCountryCodeごとにPopulationの平均値を算出し、countryテーブルと結合、国名・大陸・平均Populationを表示せよ
また、上記の内容をwith句を用いて実現せよ

### 6本目
cityテーブルにてCountryCodeごとにPopulationの平均値を算出した結果と、countryテーブルにてGNPとGNPOldを比較し、大きい値の方をGNP_bigとしてカラムに格納した結果を結合し、国名・大陸・平均Population・GNPBigを表示せよ
また、上記の内容をwith句を用いて実現せよ

### 7本目
6本目の結果に対して、continent_nameで集約を行いavg_populationの最大値が10万以上、GNP_bigの平均値が10万以上の結果を抽出して表示せよ
また、上記の内容をwith句を用いて実現せよ

### 8本目
countryテーブルにidを振りcoutry_with_idテーブルを作成
その後、cityとcoutrylanguageのテーブルのCountryCodeカラムを↑で付与したidに更新せよ
また、idははwindow関数を用いて付与せよ

### 9本目
countrylanguageテーブルにてPercentageにランキングを付与せよ
また、上記のランキングをwindow関数を用いて付与せよ

### 10本目
9本目のSQLを参考にして
countrylanguageテーブルにてCountryCode単位で最もPercentageが高い言語をcountry_with_idテーブルに結合し
cityテーブルにてCountryCode単位で最もPopulationが高いシティをcountry_with_idテーブルに結合
その後、PercentageとPopulationをキーに降順でソートせよ
また、上記のランキングをwindow関数を用いて付与し、with句を用いて実現せよ

## 正解
[前回の問題](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2#%E5%95%8F%E9%A1%8C) 1~4本はMySQL8でも記述方式変わらないので、飛ばします

### 5本目
#### 問題
cityテーブルにてCountryCodeごとにPopulationの平均値を算出し、countryテーブルと結合、国名・大陸・平均Populationを表示せよ
また、上記の内容をwith句を用いて実現せよ

#### 解説
MySQL8の新機能のひとつwith句です！
HQLやBigQueryなどでは当然のように使えるこの機能ですが、MySQLでは最近まで使えませんでした。。。
挙動的にはWITHで書かれた部分が最初に実行されテンポラリテーブルとしてメモリに保存され、別のfrom句で利用できるという形です
良い点としては、viewでwith句を使う場合はメモリに結果を保持するので、再度クエリを回したときにDBへの問い合わせが発生しないです。副問合せの場合は毎回問い合わせする感じですね。そして、何より可読性が高い！
悪い点としては、メモリリソースを消費するので、めちゃくちゃでかいデータを処理する場合にはメモリ枯渇に気をつけないといけません。ということもあり、Hadoopなどは副問合せを使われることが多いですね。

#### 前回の回答例
```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
;
```
#### 今回の回答例
```sql
WITH city_calc (CountryCode, avg_population) AS (
	select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
)
select country.name as country_name
    , country.Continent as continent_name
    , city_calc.avg_population as avg_population
from city_calc left join country
	on city_calc.CountryCode = country.Code
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/d492d1db-8080-9dc4-9ca9-36f88701f919.png)

### 6本目
#### 問題
cityテーブルにてCountryCodeごとにPopulationの平均値を算出した結果と、countryテーブルにてGNPとGNPOldを比較し、大きい値の方をGNP_bigとしてカラムに格納した結果を結合し、国名・大陸・平均Population・GNPBigを表示せよ
また、上記の内容をwith句を用いて実現せよ

#### 解説
5本目と同様にwith句を使い、わかりやすく記述します
with句が複数あってもカンマでつなげるだけです。めちゃめちゃ簡単！

#### 前回の回答例
```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
;
```

#### 今回の回答例
```sql
WITH city_calc (CountryCode, avg_population) AS (
	select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
),
country_calc (Code, Name, Continent, GNP_big) AS (
	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
)
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 city_calc left join country_calc
	on city_calc.CountryCode = country_calc.Code
;
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/7baa4c64-92ac-e780-f929-ee0d6eb5fda3.png)

### 7本目
#### 問題
6本目の結果に対して、continent_nameで集約を行いavg_populationの最大値が10万以上、GNP_bigの平均値が10万以上の結果を抽出して表示せよ
また、上記の内容をwith句を用いて実現せよ

#### 解説
6本目とほぼ同じです
ちなみに、with句の実行順序も通常のSQLと同じです
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
;
```

#### 今回の回答例
```sql
WITH city_calc (CountryCode, avg_population) AS (
	select CountryCode, avg(Population) as avg_population
    from city
    group by CountryCode
),
country_calc (Code, Name, Continent, GNP_big) AS (
	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
)
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 city_calc left join 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/02f87cef-4e4d-6253-7339-be43edc5b6af.png)

### 8本目
#### 問題
countryテーブルにidを振りcoutry_with_idテーブルを作成
その後、cityとcoutrylanguageのテーブルのCountryCodeカラムを↑で付与したidに更新せよ
また、idははwindow関数を用いて付与せよ

#### 解説
MySQLの新機能window関数です！
こちらも、HQLやBigQueryなどでは当然のように使える関数ですが、MySQLでは最近まで使えませんでした。。。
以前、書いたrow_numberをふるSQLが1行でかけちゃいます。感激！

#### 前回の回答例
```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
;
```

#### 今回の回答例
```sql
create table country_with_id as (
    select *
    	, row_number() over () 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/c806a5c9-e81b-5cf9-9542-9ad21b9f321e.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/8801d180-7a7d-3a7d-f05a-477155a0c34f.png)

### 9本目
#### 問題
countrylanguageテーブルにてPercentageにランキングを付与せよ
また、上記のランキングをwindow関数を用いて付与せよ

#### 解説
こちらも、window関数の`rank()`を用いることで、鬼のように簡単に書くことができます
ちなみに、window関数一覧は以下のような感じです
rank、lag、lead、fist_value、row_numberはよく使いますね

* CUME_DIST()：累積分布値
* DENSE_RANK()：そのパーティション内の現在の行のランク(順位が飛ばされない)
* FIRST_VALUE()：ウィンドウフレームの最初の値を取得
* LAG()：パーティション内の現在の行の次の値
* LAST_VALUE()：ウィンドウフレームの最後の値を取得
* LEAD()：パーティション内の現在の行の前の値
* NTH_VALUE()：ウィンドウ枠のN番目の行の値を取得
* NTILE()：パーティション内でバケット分割を行う
* PERCENT_RANK()：パーセントランクを計算して表示
* RANK()：パーティション内の現在の行のランクを取得
* ROW_NUMBER()：パーティション内の現在の行の数を取得

`partition by`はいわゆる`group by`みたいなものですね
`order by`はwindow内の並び順を意味しています

#### 前回の回答例
```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
;
```

#### 今回の回答例
```sql
select *
    , rank() over (partition by CountryCode order by CountryCode, Percentage desc) as ranking
from countrylanguage
```

#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/1598e2f4-923a-f66b-aeaa-bd59d7d929a2.png)

### 10本目
#### 問題
9本目のSQLを参考にして
countrylanguageテーブルにてCountryCode単位で最もPercentageが高い言語をcountry_with_idテーブルに結合し
cityテーブルにてCountryCode単位で最もPopulationが高いシティをcountry_with_idテーブルに結合
その後、PercentageとPopulationをキーに降順でソートせよ
また、上記のランキングをwindow関数を用いて付与し、with句を用いて実現せよ

#### 解説
もちろん、with句の中で別のwith句で実行した内容をfrom句で呼び出せます
[前回のSQL](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2#%E5%9B%9E%E7%AD%94%E4%BE%8B-7)よりとても可読性が上がってると思います


#### 前回の回答例
```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
;
```
#### 今回の回答例
```sql
WITH lang_calc AS (
	select *
		, rank() over (partition by CountryCode order by CountryCode, Percentage desc) as ranking
	from countrylanguage
),
lang_rank1 AS (
    select CountryCode, Language, Percentage
    from lang_calc
    where ranking = 1
), 
city_calc AS (
    select *
		, rank() over (partition by CountryCode order by CountryCode, Population desc) as ranking
        from city
), 
city_rank1 AS (
    select CountryCode, Name as city_name, Population
    from city_calc
    where ranking = 1
)
select Code, Name, Continent, Language as popular_language, city_name as popular_city
from country_with_id
left join lang_rank1
	on lang_rank1.CountryCode = country_with_id.id
left join city_rank1
	on city_rank1.CountryCode = country_with_id.id
```
#### 結果
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261265/e11067ba-b4bb-3c81-8c06-1f385aa99ff8.png)

## さいごに
いかがでしたでしょうか、前回の [MySQL10本ノック作ってみた！](https://qiita.com/yasumon/items/a11b0e5d93e9bebec7a2) では、MySQL5.7だったのもありrow_numberを無理やりふっていましたが、今回MySQL8になって専用の関数が用意されたことでとても簡単にrow_numberなどが扱えるようになりました。with句もとてもありがたいです！
window関数が難しそうだな。。。と感じてる人もこれを機に挑戦してみてはいかがでしょうか！使いこなせたらとても便利ですよ！
また、SQLの回答例ですが、あくまで例です。もちろん、今回記述した内容以外の書き方もありますので、ご了承くださいmm

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