LoginSignup
191
225

More than 5 years have passed since last update.

@yasumon(安本 賢司)inK.S.ロジャース株式会社

MySQL10本ノック作ってみた！

Last updated at Posted at 2019-01-30

caspar-camille-rubin-224229-unsplash.jpg

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は削除しておく

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テーブルにて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'
;

結果

image.png

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
;

結果

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テーブルにて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
;

結果

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

191
225
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
191
225