#事始め
ニートから新卒として働き出し早2週間の僕
研修課題としてある問題が与えられた時に初心者SQL見習いにありがちなミスを犯してしまったので世の中のSQL見習い同志に共有したいと思い久々に記事をかくう。
#sakilaデータ準備
mysqlのホームページに行ってsakila databaseをダウンロードしよう!
sakila databaseのダウンロードはここをクリック!
zipファイルのダウンロードが終わったら解答して下記の作業に移ろう
先ほど解凍したファイルが存在するディレクトリでコマンドラインからmysqlサーバに繋ごう
mysql -u root -p
次にデータベースの骨格となるスキーマを流し込もう
mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql
次にデータベースのデータを流しこもう
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;
USE sakila;
Database changed
SHOW TABLES;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
22 rows in set (0.00 sec)
テーブルがたくさん表示されたら成功だ！
このデータベースはSQL操作の練習でよく使われるのでみんなも練習で使うといいと思う。
では問題に移ろう。
問題
sakilaスキーマのcustomerテーブルとrentalテーブルを⽤い、2005年6⽉15⽇〜6⽉19
⽇のレンタル本数をユーザごとに集計せよ。
ただし同期間に⼀度も購⼊していないユーザのレンタル本数はゼロとして出⼒すること。
僕の解答[1]
select
c.customer_id
,c.first_name
,count(distinct r.rental_id) as "合計レンタル本数"
from customer c
inner JOIN
rental r
on
c.customer_id = r.customer_id
where
(r.rental_date >='2005-06-15 00:00:00'
and
r.rental_date <'2005-06-20 00:00:00')
group by c.customer_id
order by customer_id
limit 10;
customer tableにrental tableをinnerで joinしてwhereで借りられた時の期間を指定している。
+-------------+------------+--------------------------+
| customer_id | first_name | 合計レンタル本数 |
+-------------+------------+--------------------------+
| 1 | MARY | 6 |
| 2 | PATRICIA | 1 |
| 3 | LINDA | 4 |
| 4 | BARBARA | 6 |
| 5 | ELIZABETH | 4 |
| 6 | JENNIFER | 4 |
| 7 | MARIA | 3 |
| 8 | SUSAN | 2 |
| 10 | DOROTHY | 4 |
| 11 | LISA | 2 |
+-------------+------------+--------------------------+
10 rows in set (0.01 sec)
inner joinにしようがouter joinにしようが9が表示されない。
僕がやってしまったバカな解答[2]
SELECT
c.customer_id,
c.first_name
,count(distinct r.rental_id) as "合計レンタル本数"
from
customer c
inner JOIN
rental r
on c.customer_id = r.customer_id
where
(r.rental_date >='2005-06-15 00:00:00'
and
r.rental_date <'2005-06-20 00:00:00')
group by c.customer_id
UNION
SELECT
customer_id ,
first_name ,0
from customer
WHERE
customer_id
not in
(select
c.customer_id
from
customer c
LEFT outer JOIN rental r on
c.customer_id = r.customer_id
where
r.rental_date >='2005-06-15 00:00:00'
and
r.rental_date <'2005-06-20 00:00:00'
group by c.customer_id
)
group by customer_id
order by customer_id
limit 20;
customer_id |9| がほしいために上記のSQLに加えて上記のsqlでない(not in)customer_idをわざわざサブクエリを使って持ってきて,それらを連結(UNION)しているのだ。
+-------------+------------+--------------------------+
| customer_id | first_name | 合計レンタル本数 |
+-------------+------------+--------------------------+
| 1 | MARY | 6 |
| 2 | PATRICIA | 1 |
| 3 | LINDA | 4 |
| 4 | BARBARA | 6 |
| 5 | ELIZABETH | 4 |
| 6 | JENNIFER | 4 |
| 7 | MARIA | 3 |
| 8 | SUSAN | 2 |
| 9 | MARGARET | 0 |
| 10 | DOROTHY | 4 |
| 11 | LISA | 2 |
| 12 | NANCY | 4 |
| 13 | KAREN | 2 |
| 14 | BETTY | 1 |
| 15 | HELEN | 1 |
| 16 | SANDRA | 2 |
| 17 | DONNA | 2 |
| 18 | CAROL | 3 |
| 19 | RUTH | 2 |
| 20 | SHARON | 3 |
+-------------+------------+--------------------------+
20 rows in set (0.02 sec)
結果としては行けそうだが、select文を三回実行していることからも察するように
これは実はほぼ同じクエリを2回実行しており無駄に読みにくくリソースの無駄でパフォーマンスが良くない冗長的なSQLを書いていることになる。
だから僕と同じSQL初心者には気をつけてもらいたい。
UNIONを使う前に考えてほしいこと
- SELECT句の中で分岐させられないか
- JOINの条件に内包できないか
- CASE式で条件分岐できないか
これらを一度考えてからUNIONを使うかを考慮してほしい。
#最終解答[3]
select
c.customer_id
,c.first_name
,count(distinct r.rental_id) as "合計レンタル本数"
from
customer c
left outer JOIN
rental r
on
c.customer_id = r.customer_id
and
(r.rental_date >='2005-06-15 00:00:00'
and
r.rental_date <'2005-06-20 00:00:00')
group by c.customer_id
order by customer_id
limit 15;
+-------------+------------+--------------------------+
| customer_id | first_name | 合計レンタル本数 |
+-------------+------------+--------------------------+
| 1 | MARY | 6 |
| 2 | PATRICIA | 1 |
| 3 | LINDA | 4 |
| 4 | BARBARA | 6 |
| 5 | ELIZABETH | 4 |
| 6 | JENNIFER | 4 |
| 7 | MARIA | 3 |
| 8 | SUSAN | 2 |
| 9 | MARGARET | 0 |
| 10 | DOROTHY | 4 |
| 11 | LISA | 2 |
| 12 | NANCY | 4 |
| 13 | KAREN | 2 |
| 14 | BETTY | 1 |
| 15 | HELEN | 1 |
+-------------+------------+--------------------------+
15 rows in set (0.06 sec)
僕の最終解答はcustomer_idを彈いてたrentalテーブルのrental_dateの条件をleft outerの形でcustomerテーブルとの結合条件に含めたことだ。
以下の箇所
customer c
left outer JOIN
rental r
on
c.customer_id = r.customer_id
and
(r.rental_date >='2005-06-15 00:00:00'
and
r.rental_date <'2005-06-20 00:00:00')
僕のはじめの解答[1]はcustomer_id(1,2,3,4,5,6,7,8,9,10)全てテーブルに含まれていたが、whereの条件抽出により9がFalseだったので、それ以外の条件に当てはまっていた値だけが取り出された形だった。
そして、解答[3]ではouter joinを使うことで複製し結合するだけ(つまり抽出も縮小もない)前提のテーブルなので1~10全て出力される。
もしここでinner joinを使った場合
条件にあうテーブルしか結合されないので解答[1]と同じ結果になることに沖しよう。
まとめ
久々にブログを書いたので変かも知れないが見てくれる人がいたらありがとう。
今までニートだったことを乗り越えてこれかバカなりにもできることをしっかりしていこうと思う。
ブログもできるだけ書こうと思う。
また、SQLを舐めすぎていた。web系を少しやっていた時はあまり長いSQLを書かなかったがデータ抽出の観点からだとまた違う深みがあるので日々精進していこうと思う。
###今回の失敗を通して
UNIONは簡単だからこそ使いそうになるがそこには大きな罠があることを忘れては行けないということを学んだ。