はじめに
今回お仕事で初めて生のSQLを書くことになった新米デベロッパーです。
備忘録として残しますが、間違っていた場合指摘していただけると幸いです。
やりたいこと
今回はSequel Aceのクエリを利用して、複数のテーブルから複数の条件でデータを抽出したいです。
例として、ECサイトでユーザーのテーブルと買った商品のテーブルを用意しました。
テーブル
userテーブル
| id | name | created_at | updated_at |
|---|---|---|---|
| 1 | 戦場ヶ原 | 1988-07-07 22:22:22 | NULL |
| 2 | 小鳥遊 | 1997-10-24 11:11:11 | NULL |
| 3 | 孫 | 2000-05-09 00:00:00 | NULL |
よくあるユーザーさんが登録された時の情報が入ってるテーブルです。
今回必要なモノ以外は省いているので、とても質素。
user_buy_itemテーブル
| id | user_id | item_id | quantity | created_at | updated_at |
|---|---|---|---|---|---|
| 1 | 3 | 59 | 30 | 2000-08-08 12:34:56 | NULL |
| 2 | 1 | 74 | 4 | 2000-08-11 12:12:12 | NULL |
| 3 | 2 | 77 | 15 | 2000-09-24 01:20:55 | NULL |
買い物をした時に登録されるテーブルです。
idはインクリメント、user_idはuserテーブルのidと外部キー制約がされてます。
なので、userテーブルのidに存在しない値は入りません。
要件定義
「1990年以降に登録されたユーザーさんの中から、2000年8月に商品を15個以上購入された方のidを抽出してほしい」
と依頼されたとします（ポイント付与とかで使うんでしょう、きっと）。
この場合、2つのテーブルをまたいで複数の条件で抽出する必要があります。
抽出条件の洗い出し
まず、いきなりSQLを書き始めても出来ません（少なくとも僕は）。
なので、まずは依頼内容を見つつどのテーブルのどの列が関係してくるかを調べます。
依頼内容は、
1990年以降に登録されたユーザーさんの中から、2000年8月に商品を15個以上購入された方のid
なので
- 1990年以降に登録している
- 2000年8月に買い物をしている
- 15個以上の商品を買っている
という3つの条件が必要ですね。
抽出（SELECT）
次にそれぞれの条件をで抽出するには、どのようなSQLが必要か考えます。
抽出にはSELECTを使います。
SELECT [列名]
FROM [テーブル名]
で特定のテーブルから指定した列を取り出すことができます。
例えば、ただ単純にuserテーブルのnameを抜き出す時は以下のように
SELECT name
FROM user;
/* 実行結果 */
> 戦場ヶ原
> 小鳥遊
> 孫
こんな感じで返ってきます。
ですがこれだけでは条件式で抽出できていないので、WHEREを使って条件を追加しましょう。
/* idが2のユーザー名を抽出 */
SELECT name
FROM user
WHERE id = 2;
/* 実行結果 */
> 小鳥遊
上記のように、比較演算子を利用して条件式を加えることができます。
それぞれをSQLで書いてみる
では、実際に書いてみましょう。
① 1990年以降に登録している
SELECT id
FROM user
WHERE created_at >= '1990-01-01 00:00:00';
/* 実行結果 */
> 2
> 3
② 2000年8月に買い物をしている
ここが少し曲者で、期間を2000年の8月に限定しているので、8月1日 00:00:00 〜 8月31日23:59:59までを指定してあげます。
期間の指定には、BETWEENとANDを使います。
使い方は以下を参考に。
SELECT user_id
FROM user_buy_item
WHERE created_at BETWEEN '2000-08-01 00:00:00' AND '2000-08-31 23:59:59';
/* 実行結果 */
> 1
> 3
③ 15個以上の商品を買っている
SELECT user_id
FROM user_buy_item
WHERE quantity >= 15;
/* 実行結果 */
> 2
> 3
これでそれぞれの条件での抽出が終わりました。
全部に共通したidは3なので、「今回は3を抽出したいんだな」と思っていてください。
実際はもっとデータ数が多くなるので、このSQLを1つにまとめて書きましょう。
実際に全ての条件で抽出する
本題まで少し遠回りしてしまいましたが、これから今までやった2つのテーブルを跨いだ3つの条件で抽出していきます。
そのためには、2つのテーブルを繋げる必要があります。
テーブルの結合
複数のテーブルを繋げるにはJOINを使います。テーブルの結合はいくつか種類があるのですが、今回はINNER JOINを使用します。
またテーブル結合した場合、同じ名前の列が存在するものを指定するとエラーが出てしまうので、.（ドット）で繋いであげましょう。
それとASを使うことで、長いテーブル名を短縮できます。
ONで結合条件を決めます。
共通する列をここで一括りにしちゃいましょう。
/* userテーブルのidを指定 */
SELECT user.id
FROM user
INNER JOIN user_buy_item
/* user_buy_itemは長いので短縮 */
AS ubi
/* 左右が一致するものを抽出 */
ON user.id = ubi.user_id
いざ実践
ここからは要件である
1990年以降に登録し、2000年8月に15個以上の商品を購入したユーザーのid
を抽出します。
この記事で得た知識を総動員して、抽出していきましょう。
SQLは以下のようになります。
/* userテーブルのidを抽出 */
SELECT user.id
FROM user
/* userテーブルとuser_buy_itemテーブルを結合 */
INNER JOIN user_buy_item
/* 以下 user_buy_item は ubi とする */
AS ubi
/* 結合条件 */
ON user.id = ubi.user_id
/* 1990年以降に登録 */
AND user.created_at >= '1990-01-01 00:00:00'
AND ubi.created_at
/* 8月に購入 */
BETWEEN '2000-08-01 00:00:00'
AND '2000-8-31 23:59:59'
/* 商品を15個以上 */
AND ubi.quantity >= 15;
「WHERE使ってないじゃん！」
という方はこちらの記事をお読みください。
ONを1つの条件式と見なすことができます。
（実際僕の環境でも動きました）
一応、WHEREを使って書くと
SELECT user.id
FROM user
INNER JOIN user_buy_item
AS ubi
ON user.id = ubi.user_id
WHERE user.created_at >= '1990-01-01 00:00:00'
AND ubi.created_at
BETWEEN '2000-08-01 00:00:00'
AND '2000-8-31 23:59:59'
AND ubi.quantity >= 15;
こんな感じになると思います。
終わりに
長文・駄文でしたが読んでいただきありがとうございました。
間違った点や、「もっと簡単に書けるよ」などありましたら教えていただけると幸いです。
自分のようなSQL初心者に届け。