LoginSignup
5
6

More than 5 years have passed since last update.

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

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

Last updated at Posted at 2019-02-27

caspar-camille-rubin-224229-unsplash.jpg

はじめに

皆さん、どうもです。ｋ．ｓ．ロジャースのやすもんです。
前回の記事 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

image.png

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

image.png

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

image.png

※不安定？なのか調べていたら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
;

結果

image.png

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
;

結果

image.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

前回の回答例

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
;

結果

image.png

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
;

結果

image.png

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

前回の回答例

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

結果

image.png

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

結果

image.png

さいごに

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

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

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

5
6
0

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
5
6