MySQL10本ノック作ってみた！

2019/02/28 更新

MySQL8バージョン作りました！
https://qiita.com/yasumon/items/33579a2cb85b320f262c

はじめに

皆さん、どうもです。ｋ．ｓ．ロジャースのやすもんです。
今回は、業務でそれなりによく使う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は削除しておく

ちなみに、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文で条件を指定するだけですね

回答例

select *
from city
where CountryCode = 'JPN'
;

結果

2本目(初級)

問題

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

解説

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

回答例

select CountryCode, sum(Population), avg(Population), max(Population), min(Population)
from city
where CountryCode = 'JPN'
group by CountryCode
;

結果

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
;

結果

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
;

結果

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
;

結果

6本目(中級)

問題

cityテーブルにてCountryCodeごとにPopulationの平均値を算出した結果と、countryテーブルにてGNPとGNPOldを比較し、大きい値の方を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
;

結果

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
;

結果

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
;

結果

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

結果

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

結果

さいごに

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

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

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