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

## はじめに

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

まずは用語の説明から

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

https://squareup.com/jp/ja/townsquare/market-basket-analysis#:~:text=%E3%83%90%E3%82%B9%E3%82%B1%E3%83%83%E3%83%88%E5%88%86%E6%9E%90%E3%81%A8%E3%81%AF%E3%80%81%E5%B0%8F%E5%A3%B2,%E8%A6%8B%E5%87%BA%E3%81%97%E3%81%A6%E3%81%84%E3%81%8F%E3%81%93%E3%81%A8%E3%81%A7%E3%81%99%E3%80%82

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

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

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

https://www.amazon.co.jp/dp/4798157821/

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

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

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

https://www.db-fiddle.com/

#### 用いるデータ

```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句を用います。

クエリ

```sql
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を返します

```sql
SELECT COUNT(name) FROM items
```

| COUNT(name) |
| ----------- |
| 3           |

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

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

クエリ

```sql
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`が選ばれたことがわかります。

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

```sql
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`テーブルの行数ではなくなったためです。

```sql
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を除外できるクエリができないか書いてみましょう。

クエリ

```sql
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`として現れます

![「」を編集_-_Qiita.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14191/ffa3fbee-6284-211d-52b6-25821e50342c.png)

- 条件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です。

本日は以上です。

## 参照

https://www.amazon.co.jp/dp/4798157821/

127 - 130p

133p

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

https://qiita.com/ryosuketter/items/713474dbd009870e4103