Edited at

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


はじめに

皆さん、どうもです。k.s.ロジャースのやすもんです。

前回の記事 MySQL10本ノック作ってみた! が想像以上に好評だったので、その続きでmysql8版を書いてみます!

docker使って動かすところからおこない

前回の MySQL10本ノック作ってみた! から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

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

前回の 事前準備 を元にサンプルデータをダウンロードし、DBにimportする

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


問題

前回の問題 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句を用いて実現せよ


正解

前回の問題 1~4本はMySQL8でも記述方式変わらないので、飛ばします


5本目


問題

cityテーブルにてCountryCodeごとにPopulationの平均値を算出し、countryテーブルと結合、国名・大陸・平均Populationを表示せよ

また、上記の内容をwith句を用いて実現せよ


解説

MySQL8の新機能のひとつwith句です!

HQLやBigQueryなどでは当然のように使えるこの機能ですが、MySQLでは最近まで使えませんでした。。。

挙動的にはWITHで書かれた部分が最初に実行されテンポラリテーブルとしてメモリに保存され、別のfrom句で利用できるという形です

良い点としては、viewでwith句を使う場合はメモリに結果を保持するので、再度クエリを回したときにDBへの問い合わせが発生しないです。副問合せの場合は毎回問い合わせする感じですね。そして、何より可読性が高い!

悪い点としては、メモリリソースを消費するので、めちゃくちゃでかいデータを処理する場合にはメモリ枯渇に気をつけないといけません。ということもあり、Hadoopなどは副問合せを使われることが多いですね。


前回の回答例

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
;


今回の回答例

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
;


結果


6本目


問題

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

また、上記の内容をwith句を用いて実現せよ


解説

5本目と同様にwith句を使い、わかりやすく記述します

with句が複数あってもカンマでつなげるだけです。めちゃめちゃ簡単!


前回の回答例

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
;


今回の回答例

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
;


結果


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


前回の回答例

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
;


今回の回答例

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
;


結果


8本目


問題

countryテーブルにidを振りcoutry_with_idテーブルを作成

その後、cityとcoutrylanguageのテーブルのCountryCodeカラムを↑で付与したidに更新せよ

また、idははwindow関数を用いて付与せよ


解説

MySQLの新機能window関数です!

こちらも、HQLやBigQueryなどでは当然のように使える関数ですが、MySQLでは最近まで使えませんでした。。。

以前、書いたrow_numberをふるSQLが1行でかけちゃいます。感激!


前回の回答例

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
;


今回の回答例

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
;


結果


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内の並び順を意味しています


前回の回答例

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
;


今回の回答例

select *

, rank() over (partition by CountryCode order by CountryCode, Percentage desc) as ranking
from countrylanguage


結果


10本目


問題

9本目のSQLを参考にして

countrylanguageテーブルにてCountryCode単位で最もPercentageが高い言語をcountry_with_idテーブルに結合し

cityテーブルにてCountryCode単位で最もPopulationが高いシティをcountry_with_idテーブルに結合

その後、PercentageとPopulationをキーに降順でソートせよ

また、上記のランキングをwindow関数を用いて付与し、with句を用いて実現せよ


解説

もちろん、with句の中で別のwith句で実行した内容をfrom句で呼び出せます

前回の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
;


今回の回答例

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


結果


さいごに

いかがでしたでしょうか、前回の MySQL10本ノック作ってみた! では、MySQL5.7だったのもありrow_numberを無理やりふっていましたが、今回MySQL8になって専用の関数が用意されたことでとても簡単にrow_numberなどが扱えるようになりました。with句もとてもありがたいです!

window関数が難しそうだな。。。と感じてる人もこれを機に挑戦してみてはいかがでしょうか!使いこなせたらとても便利ですよ!

また、SQLの回答例ですが、あくまで例です。もちろん、今回記述した内容以外の書き方もありますので、ご了承くださいmm


Wantedlyでもブログ投稿してます

Techブログに加えて会社ブログなどもやっているので、気になった方はぜひ覗いてみてください。

https://www.wantedly.com/companies/ks-rogers