【SQL】JOINのKEYに不等号は使える!?
JOINのKEYに不等号?
通常、SQLでJOINを実行する際に用いる結合KEYは、
ユーザーIDや日付などが利用されます。
その際にはuserID=userID
といったように、
結合したいもの同士をイコール(=)で結びます。
SQLのJOINに関して、少し不安かなという方は、
こちらの書籍をお勧めしますので、ぜひ読んでみてください。
さて今回の本題に入りまして、結合する際にイコールではなく、
不等号を使ってみたいと思います。
不等号を使って結合
例えば、以下のようなユーザーの日毎PV数が格納されたテーブルを想定しましょう。
userID | date | pv |
---|---|---|
A | 5/1 | 3 |
A | 5/2 | 6 |
A | 5/3 | 2 |
A | 5/4 | 5 |
B | 10/3 | 2 |
B | 10/4 | 1 |
B | 10/5 | 8 |
このデータを用いて、その日までの累計PV数を算出してみてください。
ユーザーAの場合
- 5/1は3PV
- 5/2は9PV (5/1の3PV + 5/2の6PV)
- 5/3は11PV (5/1の3PV + 5/2の6PV + 5/3の2PV)
といったように、日毎に過去のPV数を足し合わせて、
累計のPV数を算出したい場合に、不等号を利用したJOINが大変有用です。
手順①:userIDとdateで結合
SELECT t1.userID, t1.date, t1.pv, t2.userID as t2_userID t2.date as t2_date, t2.pv as t2_pv FROM sample_table as t1 INNER JOIN sample_table as t2 ON t1.userID = t2.userID AND t1.date >= t2.date
実行結果
userID | date | pv | t2_userID | t2_date | t2_pv |
---|---|---|---|---|---|
A | 5/1 | 3 | A | 5/1 | 3 |
A | 5/2 | 6 | A | 5/1 | 3 |
A | 5/2 | 6 | A | 5/2 | 6 |
A | 5/3 | 2 | A | 5/1 | 3 |
A | 5/3 | 2 | A | 5/2 | 6 |
A | 5/3 | 2 | A | 5/3 | 2 |
A | 5/4 | 5 | A | 5/1 | 3 |
A | 5/4 | 5 | A | 5/2 | 6 |
A | 5/4 | 5 | A | 5/3 | 2 |
A | 5/4 | 5 | A | 5/4 | 5 |
B | 10/3 | 2 | B | 10/3 | 2 |
B | 10/4 | 1 | B | 10/3 | 2 |
B | 10/4 | 1 | B | 10/4 | 1 |
B | 10/5 | 8 | B | 10/3 | 2 |
B | 10/5 | 8 | B | 10/4 | 1 |
B | 10/5 | 8 | B | 10/5 | 8 |
手順①実行クエリの解説
今回のクエリで重要なところは、
t1.date <= t2.date
こちらの部分になるでしょう。
これは、date
を不等号により比較して、
指定の日よりも小さい場合は結合をしております。
例えば、ユーザーAの場合、5/1は最も小さな日付です。
そのため<=
で比較すると5/1のデータしかひも付きません。
次に、5/2は5/1と5/2にひも付きます。
このようにして、date
に格納されている日付を参照し、
自分よりも小さな日付のデータどんどん結合していっているのです。
手順②:日毎の累計PV数を算出
SELECT t1.userID, t1.date, SUM(t2_pv) as total_pv FROM ( SELECT t1.userID, t1.date, t1.pv, t2.userID as t2_userID t2.date as t2_date, t2.pv as t2_pv FROM sample_table as t1 INNER JOIN sample_table as t2 ON t1.userID = t2.userID AND t1.date >= t2.date ) GROUP BY t1.userID, t1.date
実行結果
userID | date | total_pv |
---|---|---|
A | 5/1 | 3 |
A | 5/2 | 9 |
A | 5/3 | 11 |
A | 5/4 | 16 |
B | 10/3 | 2 |
B | 10/4 | 3 |
B | 10/5 | 11 |
このように、その日までの累計PV数を算出することができました。
留意点
不等号でJOINする非常に便利な方法ですが、一点注意することがあります。
それは、INNER JOINで使えるがLEFT JOINなどでは使えません。
そのため、不等号でJOINをしたいとなった場合は、
INNER JOINで利用するときに限定してください。