[SQL向上計画] 複数のJOINを扱う場合の注意点

化け物クラスの容量を持つデータベースで開発を行う時、嫌になりますか？身震いしてワクワクしますか？ 多くの場合は、自分にとってのリスキーな状態を嫌うと思いますが、モチベーションの達人であるユゲタは、ワクワクします。 まるで、自分よりも強い敵が目の前にいた時に、「オラ、ワクワクすっぞ！」というあのキャラクターの感じですね。 SQLは、大ボリュームデータの扱いと、複数テーブルのリレーションをいかに効率的に行うかという事で、そのSQLコードの美しさ、スピード、発展効率背などをクオリティとします。 そんなSQLで、複数JOINをした時に、思った通りの値が返らなかった時に対応したテクニックを紹介します。

複数テーブルをJOINするトラブル

簡単なマスターテーブルのJOINで、1対1の構成であれば、何の心配もいらず、その辺のサイトに書かれているJOIN区を使って、お茶を飲んで、ゲームをしながら、Youtubeを見て、お菓子でも食ってりゃいいんですが、 実際の現場で扱うデータベースは、おそらくたくさんのテーブルが対象になるケースも多いでしょう。 例として、次のような複数のテーブルを使って、それらをJOINしてみたいと思います。

テーブルサンプル「アクセステーブル」

id	date	url_id	browser_id
1	2000-01-01	1	1
2	2000-01-02	2	2
3	2000-01-03	3	3

テーブルサンプル「url」

id	name
1	index.html
2	about.html
3	contact.html

テーブルサンプル「browser」

id	name
1	chrome
2	safari
3	firefox

JOINいろいろ

Accessテーブルの一覧を普通に表示

sqlite> SELECT * FROM Access;

1	2000-01-01	1	1
2	2000-01-02	2	2
3	2000-01-03	3	3

Access情報の対応IDを名前に変換してみる

sqlite> SELECT Access.id, Access.date, Url.name as url_name , Browser.name as browser_name FROM Access JOIN Url ON Access.url_id = Url.id JOIN Browser ON Access.url_id = Browser.id;

1	2000-01-01	|index.html	chrome
2	2000-01-02	about.html	safari
3	2000-01-03	contact.html	firefox

連結テーブルの数を算出してみる

sqlite> SELECT Access.date, Url.name as url_name, count(Url.id) as browser_count , Browser.name as browser_name, count(Browser.id) as browser_count FROM Access JOIN Url ON Access.url_id = Url.id JOIN Browser ON Access.url_id = Browser.id GROUP BY date, Url.id, Browser.id;

1	2000-01-01	|index.html	1	chrome	1
2	2000-01-02	about.html	1	safari	1
3	2000-01-03	contact.html	1	firefox	1

ここからトラブルのはじまり

１個しかなくてわかりにくいので、Accessデータを追加してみる

2000-01-01に、同じURLで、別のブラウザでのアクセスデータを追加してみます。 sqlite> INSERT INTO Access (date, url_id, browser_id) VALUES ("2000-01-01", 1, 2);

連結テーブルの数を算出してみる（上記と同じ）

sqlite> SELECT Access.date, Url.name as url_name, count(Access.url_id) as browser_count , Browser.name as browser_name, count(Access.browser_id) as browser_count FROM Access JOIN Url ON Access.url_id = Url.id JOIN Browser ON Access.url_id = Browser.id GROUP BY date, Url.id, Browser.id;

1	2000-01-01	|index.html	2	chrome	2
2	2000-01-02	about.html	1	safari	1
3	2000-01-03	contact.html	1	firefox	1

ん？ 2000-01-01のURL数が2と、ブラウザ数が2になっている・・・ URLは1にならないとイケないのに・・・

countにDISTINCTをセットすると良い

sqlite> SELECT Access.date, Url.name as url_name, count(DISTINCT Access.url_id) as browser_count , Browser.name as browser_name, count(DISTINCT Access.browser_id) as browser_count FROM Access JOIN Url ON Access.url_id = Url.id JOIN Browser ON Access.url_id = Browser.id GROUP BY date, Url.id, Browser.id;

1	2000-01-01	|index.html	1	chrome	2
2	2000-01-02	about.html	1	safari	1
3	2000-01-03	contact.html	1	firefox	1

count()内にDISTINCTという命令を入れると、ユニーク処理をしてくれるようになります。 ちょっとしたテクニックで、正解値になることがわかります。

（おまけ） SQLiteでのテーブル操作コマンド集

テーブル作成

$ sqlite3 sample.sql # Accessテーブル作成 sqlite> CREATE TABLE Access (id INTEGER PRIMARY KEY, date TEXT, url_id INTEGER, browser_id INTEGER); # URLテーブル作成 sqlite> CREATE TABLE Url (id INTEGER PRIMARY KEY, name TEXT); # Browserテーブル作成 sqlite> CREATE TABLE Browser (id INTEGER PRIMARY KEY, name TEXT);

データ登録

#Urlテーブル INSERT INTO Url (name) VALUES ("index.html"); INSERT INTO Url (name) VALUES ("about.html"); INSERT INTO Url (name) VALUES ("contact.html"); # Browserテーブル INSERT INTO Browser (name) VALUES ("chrome"); INSERT INTO Browser (name) VALUES ("safari"); INSERT INTO Browser (name) VALUES ("firefox"); # Accessテーブル INSERT INTO Access (date, url_id, browser_id) VALUES ("2000-01-01", 1, 1); INSERT INTO Access (date, url_id, browser_id) VALUES ("2000-01-02", 2, 2); INSERT INTO Access (date, url_id, browser_id) VALUES ("2000-01-03", 3, 3);

あとがき

SQLを筆頭にデータベース操作は、プログラミングとは別に、システム構築で絶対に必要になるスキルでもあります。 設計ができる、実データを扱う、登録更新などの効率や、排他処理などの基礎知識、効率データのためのインデックス処理などなど、 たくさんの知識と経験が求められるでしょう。 でも、SQLは、必ず答えが出るジャンルなので、数学的な論理思考でプログラミングすれば、どんどん自分のスキルがアップするのが体感できる領域だとも思います。 SQLを制するものは、システムを制す。