まずは、テーブルを用意
それぞれのユーザーが利用したECモールで1ヶ月当たりに使用した金額を表すec_mallsテーブルを用意します
・風土jp
・A
・R
3つのECモールがあるとして、それぞれ利用した場合の金額が格納されます
SELECT * FROM ec_malls;
user_id | mall_name | amount_spent_per_month
---------+-----------+----------------------
1 | 風土jp | 30000
1 | A | 8000
1 | R | 1000
2 | 風土jp | 5000
2 | A | 5000
3 | 風土jp | 10000
3 | R | 3000
4 | 風土jp | 25000
全てのECモールで5000円以上利用したユーザーを表現するには?
「全ての〜〜」をSQLで簡単に表現できれば良いのですが、
SQLには、全称量化子に対応する述語が存在しないみたいです。
なので、二重否定文へ変更することでSQLでは表現出来るようです
全てのECモールで5000円以上利用したユーザー
↓
全てのECモールで5000未満の利用のユーザーが存在しない
実際のSQL
SELECT DISTINCT user_id
FROM ec_malls ecm1
WHERE NOT EXISTS (
SELECT *
FROM ec_malls ecm2
WHERE ecm1.user_id = ecm2.user_id AND ecm2.amount_spent_per_month < 5000
);
user_id
---------
4
2
今回欲しいのはuser_idだけで、DISTINCTで重複行を除きます
WHERE NOT EXISTS()：1行ずつ評価していきますが、条件を満たさない行が見つかった時点で処理は止まります
HAVING句を使用した書き方でも書ける
SELECT DISTINCT user_id
FROM ec_malls
GROUP BY user_id
HAVING SUM(
CASE WHEN amount_spent_per_month >= 5000
THEN 1
ELSE 0
END
) = COUNT(user_id);
user_id 毎にグループ化したものに対して、それぞれ条件指定しています
COUNT(user_id)はGROUPごとのレコード数の合計を表しています。そのため、amount_spent_per_month >= 5000の条件を全て満たしていればSUM() = COUNT(user_id)は成立します
これは、EXISTSとは違って全ての行をなめる必要があります
そのため、パフォーマンスの観点で言うとEXISTSが勝るようです
今度は、風土jpで12000円以上、Rで5000円以下利用したユーザーを調べる
今度はモールを風土jp と Rに絞ったクエリを考えてみます
SELECT DISTINCT user_id
FROM ec_malls ecm1
WHERE mall_name in ('風土jp', 'R') AND NOT EXISTS (
SELECT *
FROM ec_malls ecm2
WHERE ecm1.user_id = ecm2.user_id AND 1 =
CASE WHEN mall_name = '風土jp' AND amount_spent_per_month < 12000 THEN 1
WHEN mall_name = 'R' AND amount_spent_per_month > 5000 THEN 1
ELSE 0
END
);
user_id
---------
1
4
WHERE mall_name in ('風土jp', 'R')でまず絞ります
そして、今回の条件である、「風土jpで12000円以上、Rで5000円以下利用した」は
風土jpで12000円以上、Rで5000円以下利用した
↓
風土jpで12000円未満、Rで5000円より大きい額の利用を満たさない
で表すことが出来ます
ここでは、CASE式を利用して条件を満たす場合は1、満たさない場合は0として比較します
風土jpもしくはRが存在しない場合は除外する
GROUP BY user_id HAVING count(mall_name) = 2を追加します
ユーザごとに、絞った2つのモールが存在しない場合は除外しています
SELECT user_id
FROM ec_malls ecm1
WHERE mall_name in ('風土jp', 'R') AND NOT EXISTS (
SELECT *
FROM ec_malls ecm2
WHERE ecm1.user_id = ecm2.user_id AND 1 =
CASE WHEN mall_name = '風土jp' AND amount_spent_per_month < 12000 THEN 1
WHEN mall_name = 'R' AND amount_spent_per_month > 5000 THEN 1
ELSE 0
END
)
GROUP BY user_id
HAVING count(mall_name) = 2;
user_id
---------
1
終わりに
帆立がたべたい
[pc]
https://www.hoodo.jp/pc/store/katsumaru/02300020