やりたいこと
SQLの苦手意識をなくすためにサンプルDBを使ってクエリをばしばし書いていきたい。
目指せ圧倒的成長。
環境
今回の環境は以下のものとなります。
- RDBMS...PostgreSQL(バージョン10.5)
- サンプルDB...Sakila
- SQLクライアント...DBeaver
前置き
SQLの入門書と格闘して何とか読破しても、レベル的にはスライムに
HPを赤ゲージにしながら何とか勝てるかどうか。
現場という名の魔王城に放り込まれたところで当然勝ち目なんてありません。
全滅したところで魔王討伐からは逃れられないので、レベルを上げて物理で殴るための
修行を積まなければなりません。
何か良いものは無いかと探し回っていたところ、Sakilaさんという素晴らしいサンプルDBを見つけました。
有名どころが出してるヤツなんだからsqlzooさんみたいな感じで色々練習問題とか出回っているだろうし、これでしばらくは課題に困りそうにないな〜、と思っていました。
Sakilaさんの練習問題全然無いんですけど問題
Sakilaさんを導入しました、的な記事はぽろぽろ出てくるのですが、肝心の練習問題のようなものは全く見つからず。
無いならつくるしかないじゃない、というアレです。
こういうのは続けるのが大事なので、月一ぐらいで数個のクエリ+解法を投稿するのを半年ぐらい続けられたらいいかなー、と低めのハードルを設定しておきます。
頑張れたらデータ追加をストアドでやるとかやってみたい。
Sakilaとは
実際にクエリを書いていく前にSakilaさんについても軽く触れておきます。
といっても、全体像や個々のテーブルの説明については公式さんに詳しく書かれているので、そちらを見て頂ければざっくりとは分かるかと思います。
レンタルDVDショップでお客さんが映画のDVDをレンタルしていくやーつです。
導入についてはMySQLさんとは最近仲がいまいちよろしくないので、PostgreSQLで行いました。
リンクのサイトを見ていまいちピンとこなくても、実際に導入してクエリを何度か叩いていればなるほどねってなると思います、きっと。
いざ実戦
とりあえず今回はSakilaさんの導入やらなんやらでMySQLさんと喧嘩して疲れてしまったので、軽めのクエリからやっていきたいと思います。
進め方としては、
問題
↓
解き方
↓
実際のクエリ
という流れで進めていきます。
1.Nickさんが登場する映画
今回やるべきことは、「filmテーブルを出演者の名前で絞り込み取得する」ということです。
filmテーブルは出演者の情報を保持していないので、「where 出演者 = Nick」というような書き方はできません。
一つの映画には複数の出演者が存在し、一人の出演者は複数の映画へ出演する、ということから、filmテーブルとactorテーブルは多対多の関係を持っています。
多対多の関係をDB上で表現するために、film_actorという中間テーブルが存在します。
film_actorはidのみを保持し、出演者や映画に関する情報は持っていないので、結合でくっ付けることによって情報を渡してあげます。
映画のタイトルは結果に必要なので、filmテーブルとは結合を行います。しかし、出演者の情報については、映画の絞り込みに使うだけで取得はしません。
Nickさんが出てくる「映画」が欲しいのです。
具体的には、「first_nameが『Nick』となっている出演者のactor_id」の集合によって絞り込んでいきます。
ここで、「IN句」を利用することで、参照中の値が集合に存在する場合のみ結果として取得することができます。
ここまでの情報をもとに実際にクエリを書くと以下のようになります。
select fi.title
from film_actor fa
inner join film fi
on fa.film_id = fi.film_id
where actor_id in (
select actor_id
from actor
where first_name = 'Nick'
)
クエリの実行結果は以下のようになります。
図1: Nickさんが登場する映画のタイトル
2.日本に住んでいるお客さんの名前
お客さんの情報を持つcustomerテーブルにはaddressという項目が存在しています。ここにはaddress_idが格納されており、address-city-countryというように紐づけられています。
ここではcustomerのaddressについて、問1と同じようにcountryが日本となっているaddress_idの集合で絞り込みをかけることで必要な結果を取得することができます。
が、同じことを繰り返していても勉強にはならないので、今回はついでに国名も一緒に取得してあげます。
他のテーブルの情報を取得するためには結合を利用していくのですが、上述の通りaddressテーブルには国名の情報は無く、customerをaddress_idをキーにaddressと結合し、更に結合することによって取得したcity_idをキーに(以下省略)
というように複数回の結合が必要になります。
複数回の結合とはいっても、結合の度にテーブルの横に新しい情報がくっついて、それを利用してどんどんテーブルを横に広げていっているだけなので、そう難しいことはないと思います。
実際の複数回の結合によるクエリは以下のようになります。
select concat(cu.first_name, ' ', cu.last_name) customer_name, co.country
from customer cu
inner join address ad
on cu.address_id = ad.address_id
inner join city ci
on ad.city_id = ci.city_id
inner join country co
on ci.country_id = co.country_id
and co.country = 'Japan'
クエリの実行結果は以下のようになります。
図2: 日本に住んでいるお客さんの名前
3.2005年5月26日にレンタルされた映画のカテゴリ別のレンタル回数ランキング
急に複雑そうな感じになりました。
難しそうなクエリを書くときは、欲しいデータをまず日本語で記述し、そこから段階的に進めていく、というように考えていくとやりやすいかと思います。
今回だと、
- rentalテーブルから2005年5月26日のレコードを取得
- rentalテーブルのinventory_idからinventoryテーブル→film_categoryテーブルと結合して対応するカテゴリのidを取得
- category_idをキーにcategoryテーブルと結合し、該当日付のレンタルごとのカテゴリ名を取得
- カテゴリ名ごとでテーブルをグループへ分割し、レコード数で集計することでカテゴリ別のレンタル数を取得
- カテゴリ別レンタル数をソートすることでランキング情報を取得
という流れになります。
実際にどのようなクエリを記述していくか、ステップごとに見ていきます。
rentalテーブルから2005年5月26日のレコードを取得
まずは該当日付のレコードをrentalテーブルから取得します。
レンタルされた日付の情報はrental_dateというカラムに格納されています。
rental_dateは日付だけでなく時刻の情報も保持していますが、
絞り込み時には「rental_date >= '2005-05-26'」とすることで、2005年5月26日の0時丁度以降のレコードを取得することができます。
このことから実際にクエリを書くと以下のようになります。
select * from rental
where rental_date >= '2005-05-26'
and rental_date < '2005-05-27'
該当日付のレンタル情報を取得することはできましたが、現状あるのは在庫を表すinventory_idの情報だけなのでカテゴリの情報にたどり着くにはもう少し頑張る必要があります。
レンタル情報と対応したcategory_idを取得
レンタル情報と対応したcategory_idを取得するには、これまでの問でやってきたように、結合を利用します。rentalテーブルはinventory_idをキーにinventory-filmという対応づけがされていますが、これについても2回結合してあげればOKです。
実際のクエリを見た方がイメージが掴みやすいかと思います。
select fc.category_id from rental re
inner join inventory inv
on re.inventory_id = inv.inventory_id
inner join film_category fc
on inv.film_id = fc.category_id
where rental_date >= '2005-05-26'
and rental_date < '2005-05-27'
これにより各々のレンタルと対応した在庫情報、そして映画の情報を経てカテゴリのidを取得することに成功しました。
集計してソートして表示
残った課題は、categoryテーブルと結合してカテゴリー名を取得し、カテゴリー毎にグループ化して集計、そしてソートして表示ということになります。
これらについては「GROUP BY」でカテゴリ名ごとのグループでテーブルを切り出し、グループ毎に「COUNT」で集計した結果を「ORDER BY」によってソートすることで実現することができます。
実際にクエリで記述した結果及び今回の問の全体像は以下のようになります。
select ca.name, count(*) rental_count from rental re
inner join inventory inv
on re.inventory_id = inv.inventory_id
inner join film_category fc
on inv.film_id = fc.film_id
inner join category ca
on fc.category_id = ca.category_id
where rental_date >= '2005-05-26'
and rental_date < '2005-05-27'
group by ca.name
order by rental_count desc
そして、実行結果は以下の図となります。
図3: 2005年5月26日のカテゴリ別レンタル数ランキング
以下、余談
疲れてしまったので今回はここまでとなります。
解説口調で色々と書いていますが、私はSQLに関してはスライム3匹同時に相手にするのがやっとのレベルのひよっこなので、間違いとかもっといい書き方がございましたら、やさしく教えて頂けるととても嬉しいです。
問題を考えるのも一人だと面ｄ...ワンパターンになってしまうかもしれないですし、この問題解いてみろよォ！的な感じで投げて頂けたら頑張って解きます。
結合関係とか集計クエリがいまいち苦手に感じてしまっているので、ちょっとずつ練習して慣れていけるように頑張りたいです。
今回の問題やクエリについてはGitHubにまとめてありますのでよろしければ。