SQL 関係除算でバスケット解析

はじめに

関係除算でバスケット解析をどうやるか、見ていきます。

まずは用語の説明から

バスケット分析とは、小売店でお客様がレジに持っていく買い物かご（バスケット）の中身を、マーケティングデータとして解析する手法です。

SQLを用いた業務では活用シーンが少なくないでしょう。例えば

複数の研修を受けた社員をデータを探し出す場合とかですかね。

次に「関係除算」の説明については、下記の書籍の133pをご覧ください

これらをSQLでどう表現するかを見ていきます。

今回の例題にて用いるデータ

実行結果はこちらで確認できます。

用いるデータ

CREATE TABLE shops (
	id INT NOT NULL PRIMARY KEY auto_increment,
	name TEXT
);

CREATE TABLE items (
	id INT NOT NULL PRIMARY KEY auto_increment,
	name TEXT
);

CREATE TABLE shop_items (
	id INT NOT NULL PRIMARY KEY auto_increment,
	shop_id INT,
   	item_id INT
);

INSERT INTO shops VALUES (1, 'sendai'), (2, 'tokyo'), (3, 'osaka');
INSERT INTO items VALUES (1, 'beer'), (2, 'paper diaper'), (3, 'car');

INSERT INTO shop_items VALUES
	(1, 1, 1),
	(2, 1, 2),
	(3, 1, 3),
	(4, 1, 4),
	(5, 2, 1),
	(6, 2, 2),
	(7, 2, 3),
	(8, 3, 5),
	(9, 3, 2),
	(10, 3, 3);

お題1

beerとpaper diaperとcarを全て置いている店舗を抽出してください。

shop_itemsテーブルのうちshop_idごとの部分集合の中で、それぞれの条件（beerとpaper diaperとcarを全て置いている）の真偽を確かめたい。集合に対する条件なので、HAVING句を用います。

クエリ

SELECT S.name
FROM shop_items AS SI
INNER JOIN items AS I ON SI.item_id = I.id
INNER JOIN shops AS S ON SI.shop_id = S.id
GROUP BY S.name
HAVING COUNT(SI.item_id) = (SELECT COUNT(name) FROM items)

結果

name
sendai
tokyo

HAVING句のサブクエリの中の以下のクエリは定数3を返します

SELECT COUNT(name) FROM items

COUNT(name)
3

なので、 COUNT(SI.item_id) が 3になる店舗だけが選ばれます。

なぜなら、今更かもしれませんが、HAVING句はグループ化したデータを1件（1集合）取得しようとするたびに条件式(where_condition)を評価して、結果が TRUEのデータを取得するので

クエリ

SELECT S.name, COUNT(SI.item_id)
FROM shop_items AS SI
INNER JOIN items AS I ON SI.item_id = I.id
INNER JOIN shops AS S ON SI.shop_id = S.id
GROUP BY S.name

結果

name	COUNT(SI.item_id)
sendai	3
tokyo	3
osaka	2

これを見ると、結果としてsendaiとtokyoが選ばれたことがわかります。

ちなみに、以下のクエリの場合は正しく集計できません。

SELECT S.name
FROM shop_items AS SI
INNER JOIN items AS I ON SI.item_id = I.id
INNER JOIN shops AS S ON SI.shop_id = S.id
GROUP BY S.name
HAVING COUNT(SI.item_id) = COUNT(I.id)

結果を見るとosakaが選ばれていますね。

name
sendai
tokyo
osaka

なぜなら、JOINした結果、COUNT(I.id)の値が元々のitemsテーブルの行数ではなくなったためです。

SELECT *
FROM shop_items AS SI
INNER JOIN items AS I ON SI.item_id = I.id
INNER JOIN shops AS S ON SI.shop_id = S.id

id	shop_id	item_id	id	name	id	name
1	1	1	1	beer	1	sendai
2	1	2	2	paper diaper	1	sendai
3	1	3	3	car	1	sendai
5	2	1	1	beer	2	tokyo
6	2	2	2	paper diaper	2	tokyo
7	2	3	3	car	2	tokyo
9	3	2	2	paper diaper	3	osaka
10	3	3	3	car	3	osaka

お題2

前のお題では、beerとpaper diaperとcarを全て置いている店舗を抽出する。でしたが、
今回は、itemsテーブルにないshop_items.item_id = 4を置いている仙台shopを除外できるクエリができないか書いてみましょう。

クエリ

SELECT S.name
FROM shop_items AS SI
LEFT JOIN items AS I ON SI.item_id = I.id
LEFT JOIN shops AS S ON SI.shop_id = S.id
GROUP BY S.name
HAVING COUNT(SI.item_id) = (SELECT COUNT(name) FROM items) -- 条件1
AND COUNT(I.id) = (SELECT COUNT(name) FROM items)  -- 条件2

結果

name
tokyo

shop_itemsテーブルをマスタとして外部結合すると、itemsテーブルにないデータはNULLとして現れます

• 条件1によって、COUNT(SI.id) = 4になって、定数3と比較してFALSEになりHAVING句から除外されます
• 条件2によって、COUNT(I.id) = 2になって、定数3と比較してFALSEになりHAVING句から除外されます
• よって、tokyoだけが選ばれます

id	shop_id	item_id	id	name	id	name
1	1	1	1	beer	1	sendai
2	1	2	2	paper diaper	1	sendai
3	1	3	3	car	1	sendai
4	1	4			1	sendai
5	2	1	1	beer	2	tokyo
6	2	2	2	paper diaper	2	tokyo
7	2	3	3	car	2	tokyo
8	3	5			3	osaka
9	3	2	2	paper diaper	3	osaka
10	3	3	3	car	3	osaka

外部結合というと、商品マスタであるitemsテーブルを主にする結合が多いかもですが、shop_itemsを主にしているところがPOINTです。

本日は以上です。

参照

127 - 130p

133p

アウトプット100本ノック実施中