先日、仕事をしていてどうにもこうにもSQLの結果が思ったとおりにならなかったので、その時の苦悩をブログに残しておきます。
ちなみに、ググって、GPTして、なかなかゴールにたどり着けなかったので、その経緯も含めてお楽しみください。
今回の目的
とあるデータ一覧の中で、別テーブルに値がある場合のみのレコードを抽出したかったのですが、ちょっとこれだとわかりにくいので、次のような例だと理解してください。
とあるWebサイトの
アクセス一覧テーブルと、そのWebサイトにユーザー登録した
登録ユーザー一覧テーブルの２つがありました。
仮のデータは次のようなリストです。
登録ユーザー一覧テーブル
ユーザーがWebサイトにアカウント登録した際のデータテーブル。
| id |
name |
datetime |
| 1 |
hoge |
2024-01-01 00:00:00 |
| 2 |
huga |
2024-01-02 00:00:00 |
| 3 |
hage |
2024-01-03 00:00:00 |
テーブル作成コマンド
CREATE TABLE Regist (
id INTEGER PRIMARY KEY,
name TEXT,
datetime TIMESTAMP
);
データ登録コマンド
INSERT INTO Regist (name,datetime) VALUES ("hoge","2024-01-01 00:00:00");
INSERT INTO Regist (name,datetime) VALUES ("huga","2024-01-02 00:00:00");
INSERT INTO Regist (name,datetime) VALUES ("hage","2024-01-03 00:00:00");
アクセス一覧テーブル
ユーザーがWebサイトに（登録後に）アクセスした時のログデータテーブル。
| id |
name |
regist_id |
datetime |
| 1 |
https://example.com/a.html |
1 |
2024-01-01 01:00:00 |
| 2 |
https://example.com/a.html |
2 |
2024-01-02 01:00:00 |
| 3 |
https://example.com/a.html |
3 |
2024-01-03 01:00:00 |
| 1 |
https://example.com/a.html |
1 |
2024-01-03 01:00:00 |
| 2 |
https://example.com/a.html |
2 |
2024-01-03 01:00:00 |
テーブル作成コマンド
CREATE TABLE Access (
id INTEGER PRIMARY KEY,
url TEXT,
regist_id INTEGER,
datetime TIMESTAMP
);
データ登録コマンド
INSERT INTO Access (url,regist_id,datetime) VALUES ("https://example.com/a.html",1,"2024-01-01 01:00:00");
INSERT INTO Access (url,regist_id,datetime) VALUES ("https://example.com/b.html",2,"2024-01-02 01:00:00");
INSERT INTO Access (url,regist_id,datetime) VALUES ("https://example.com/c.html",3,"2024-01-03 01:00:00");
INSERT INTO Access (url,regist_id,datetime) VALUES ("https://example.com/d.html",1,"2024-01-02 01:00:00");
INSERT INTO Access (url,regist_id,datetime) VALUES ("https://example.com/e.html",2,"2024-01-03 01:00:00");
ストーリー解説
https://example.com/a.htmlというURLに、登録してアクセスするWebサイトがあり、
3名のユーザーが別々の日に登録して、その後何度かWebサイトにアクセスをしたログが書き込まれています。
この時に、2024-01-03にアクセスした人の中で、2024-01-02に登録した人のみのURL情報を取得したいというのが課題です。
最初に考えた方法
JOINして、Accessテーブルのresigt_idを別テーブル(Regist)のid値と対象の日時でマッチした列を連結すればいいと考えました。
SELECT * FROM Access
LEFT JOIN Regist ON Regist.id = Access.regist_id
WHERE
Access.datetime LIKE "2024-01-03%" AND
Regist.datetime LIKE "2024-01-02%";
- 実行結果
> 5|https://example.com/e.html|2|2024-01-03 01:00:00|2|huga|2024-01-02 00:00:00
この方法でもいいんですが、複雑なテーブルになった時に、JOINでのON区記述とWHERE区の記述が煩雑になるおそれがあります。
※実際はこの方式で行って、思った結果が返らなかったので、もっと確実な方法を検討しました。
ChatGPTからの提案
こんな時便利なのが、ChatGPT先生です。
この人（？）は、何でも確実に答えてくれます。（たまに的外れもありますが・・・）
そこで、次のように質問をしてみました。
- テーブル1の一覧に対して、テーブル2の特定カラムに存在する場合に取得するSQLを書いて
- ChatGPT
テーブル1とテーブル2の特定のカラムに基づいて結合するSQLを書いてみましょう。以下の例では、テーブル1の一覧を取得し、テーブル2の特定カラムに存在する場合のみ結果に含めます。
SELECT *
FROM テーブル1
INNER JOIN テーブル2 ON テーブル1.結合カラム = テーブル2.結合カラム;
このSQLでは、テーブル1とテーブル2をINNER JOINしています。結合カラムは、結合に使用するカラムのことです。テーブル1とテーブル2の結合カラムが一致する行のみが結果に含まれます。
- テーブル結合ではなく、別テーブルにその値があるかどうかを確認する方法は無いの？
- 別のテーブルに特定の値が存在するかどうかを確認する方法として、サブクエリを使用する方法があります。具体的には、SELECT文内で別のSELECT文を使用して、値の存在を確認することができます。
例えば、特定の値がテーブルBの特定のカラムに存在するかどうかを確認する場合、以下のようなSQLクエリを使用できます。
SELECT
CASE
WHEN EXISTS (
SELECT 1 FROM テーブルB WHERE 特定の条件
)
THEN '存在する'
ELSE '存在しない'
END as 存在確認
FROM テーブルA
WHERE 条件;
このクエリでは、テーブルAの特定の条件を満たす行に対して、サブクエリを使用してテーブルBから値の存在を確認しています。存在する場合は'存在する'、存在しない場合は'存在しない'という結果が表示されます。
ただし、この方法は効率的ではない場合があります。大きなデータセットでは、サブクエリの実行に時間がかかる可能性があるため、テーブル結合やインデックスの使用など、他の方法を検討することが重要です。
なるほど、CASE区を使えば良いのか・・・
完了した方法
SELECT
*,
CASE
WHEN EXISTS (
SELECT 1 FROM Regist WHERE Regist.id = Access.regist_id AND Regist.datetime LIKE "2024-01-02%"
)
THEN '存在する'
ELSE '存在しない'
END as 存在確認
FROM Access
WHERE
Access.datetime LIKE "2024-01-03%";
- 実行結果
> 3|https://example.com/c.html|3|2024-01-03 01:00:00|存在しない
> 5|https://example.com/e.html|2|2024-01-03 01:00:00|存在する
おお、これは悪くないね。
注意点として書かれていた、サブクエリなので、これもヘタなビッグテーブルなどの場合に、INDEX設定がされていないと、クソ遅くなりそうだけど、確実性はあるので、これを採用したところ、実際に誤差を無くすことができました。
ちみに、このSELECT文をFROM区のサブクエリにして、結果"存在する"の行のみを取得するようなクエリ文を書けば、いい感じの結果が得られます。
あとがき
なんともはや、SQLのクエリ文というのは、状況に応じて様々な対応をしなければいけないので、簡易な状態のみの確認で安心していては駄目だと言うことが今回よくわかりました。
もちろん、単純思考で考えることも重要ですが、ケースンバイケースという柔軟性も非常に重要ということですね。
ちなみに、今回のSQL検証は、Sqliteを使って行っているので、別のSQLで動作しなかったらごめんなさい。
自己責任でお願い致します。
