Edited at

MySQL10本ノック作ってみた!

caspar-camille-rubin-224229-unsplash.jpg


2019/02/28 更新

MySQL8バージョン作りました!

https://qiita.com/yasumon/items/33579a2cb85b320f262c


はじめに

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

今回は、業務でそれなりによく使う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

ちなみに、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テーブルにてGNPGNPOldを比較し、大きい値の方を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文で条件を指定するだけですね


回答例

select *

from city
where CountryCode = 'JPN'
;


結果

image.png


2本目(初級)


問題

cityテーブルにてCountryCode=JPNのレコードを抽出し、Popultionの合計・平均・最大値・最小値を算出せよ


解説

group byを用いて集約させ、sumavgmaxminで集計する感じですね


回答例

select CountryCode, sum(Population), avg(Population), max(Population), min(Population)

from city
where CountryCode = 'JPN'
group by CountryCode
;


結果

image.png


3本目(初級)


問題

cityテーブルにてCountryCode単位で集計し、Popultionの平均を算出、Popultionの平均が高い順に10件取得せよ


解説

order byを用いてソートさせます

よく自分も忘れるのですがASCが昇順でDESCが降順です

ちなみに、何も指定しないとASC(昇順)になります


回答例

select CountryCode, avg(Population) as avg_Population

from city
group by CountryCode
order by avg_Population DESC
limit 10
;


結果

image.png


4本目(中級)


問題

cityテーブルとcountryテーブルを結合し、国名・大陸・シティ名を表示せよ


解説

left joinを用いてテーブルの結合を行います

他にもinner joinなどがありますが、inner joinは紐付かなかったデータが出力されず、結果と照らし合わせて脳内デバッグがやりにくいため、個人的にはleft joinのほうがわかりやすくて好きです(もちろん、要件によります)

asを用いてカラム名の変更を行うことで見やすくしてます

注意点としては、nameなどの別テーブルと被るカラム名はcountry.nameのようにどのテーブルのカラム名かをわかるように記述しないとエラーが出ます


回答例

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


5本目(中級)


問題

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


解説

基本的に、joinする前に集約系の処理はしたほうが良いと考えてます

joinするとデータ量が増える場合が多いので、join後のgroup byはあまりしたくないというモチベーションですね


回答例

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


6本目(中級)


問題

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


解説

5本目と同様joinする前に必要な処理をしてしまいます

case文を用いることでプログラムのif文のような結果を得ることができます


回答例

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


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

という感じです


回答

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


8本目(上級)


問題

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

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


解説

まず、row_numberをidとしてふったテーブルを作成

その後、update文を用いて各テーブルの値を更新します

この時、joinなどは基本的に不要です


回答例

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

image.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していくような感じです

ちなみに見慣れない<>こいつは!=とほぼ同義です


回答

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


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文で条件を指定しましょう


回答例

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


さいごに

いかがでしたでしょうか、今回は前職の経験を活かし、メンバー育成用も兼ねてMySQL10本ノックを作成しました。

個人的にはwindow関数が使えるようになれば一人前かな〜と考えてます。

前職ではBigQueryを使ってデータ分析をゴリゴリしてたので

今後、MySQLだけでなくBigQueryとかの10本ノックも作っていきたいな〜と思ってたり(弊社はコスト面からBQは普段あまり使いませんが。。。)

Mysql8系でwindow関数を使った簡単な書き方を紹介してもいいかなーとも思ったりしてます!

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


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

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

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