SQLで重複があるデータに対して部分集合ごと、何らかの特徴に分けて集計する方法（類別）

## はじめに

- RDBで扱う集合は重複を認めています
    - 通常の集合論では認めていません
- テーブル定義の際に一位制約を付けられるならいいですが
    - 実際の業務では重複業のあるテーブルで集計する場合もあるでしょう
    - 今回は生産拠点ごとにストックしている資材管理するテーブルを見てみましょう

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

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

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

#### 用いるデータ

```sql
CREATE TABLE materials (
  id INT NOT NULL PRIMARY KEY auto_increment,
  center TEXT,
  receive_date DATETIME,
  material TEXT
);

INSERT INTO materials VALUES
  (1, 'tokyo', '2020-04-01', 'tin'),
  (2, 'tokyo', '2020-04-12', 'zinc'),
  (3, 'tokyo', '2020-05-17', 'aluminum'),
  (4, 'tokyo', '2020-05-20', 'zinc'),
  (5, 'osaka', '2020-04-20', 'tin'),
  (6, 'osaka', '2020-04-22', 'nickel'),
  (7, 'osaka', '2020-04-29', 'lead'),
  (8, 'nagoya', '2020-03-15', 'titanium'),
  (9, 'nagoya', '2020-04-01', 'carbon steel'),
  (10, 'nagoya', '2020-04-24', 'carbon steel'),
  (11, 'nagoya', '2020-05-02', 'magnesium'),
  (12, 'nagoya', '2020-05-10', 'titanium'),
  (13, 'fukuoka', '2020-05-10', 'zinc'),
  (14, 'fukuoka', '2020-05-28', 'tin');
```

| id  | center  | receive_date        | material     |
| --- | ------- | ------------------- | ------------ |
| 1   | tokyo   | 2020-04-01 00:00:00 | tin          |
| 2   | tokyo   | 2020-04-12 00:00:00 | zinc         |
| 3   | tokyo   | 2020-05-17 00:00:00 | aluminum     |
| 4   | tokyo   | 2020-05-20 00:00:00 | zinc         |
| 5   | osaka   | 2020-04-20 00:00:00 | tin          |
| 6   | osaka   | 2020-04-22 00:00:00 | nickel       |
| 7   | osaka   | 2020-04-29 00:00:00 | lead         |
| 8   | nagoya  | 2020-03-15 00:00:00 | titanium     |
| 9   | nagoya  | 2020-04-01 00:00:00 | carbon steel |
| 10  | nagoya  | 2020-04-24 00:00:00 | carbon steel |
| 11  | nagoya  | 2020-05-02 00:00:00 | magnesium    |
| 12  | nagoya  | 2020-05-10 00:00:00 | titanium     |
| 13  | fukuoka | 2020-05-10 00:00:00 | zinc         |
| 14  | fukuoka | 2020-05-28 00:00:00 | tin          |

#### 今回の例題

material（材料）が重複しているcenterを調べてください

- materialsテーブルを見ると
    - 1center1行になっていない
    - 1centerは複数行ある
    - つまりcenterは要素でなく集合
    - つまり、GROUP BY の部分集合で切り分けられる
- どんな条件であれば欲しい情報が得られるか
    - 日本語にすると
        - 重複を排除してカウントした要素の数が、重複除いてない要素数と異なる
            - これの真偽を確かめればいいですね

クエリで表現してみましょう。

```sql
SELECT center
FROM materials
GROUP BY 1
HAVING COUNT(material) <> COUNT(DISTINCT material)
```

結果

| center |
| ------ |
| nagoya |
| tokyo  |

- だんだんわかってきたかもしれませんが、HAVING句は（部分）集合の性質を調べる道具として使えるヤツです
- 特に集約関数やCASE式と組み合わせると強力です
- 集合を切り分ける際はベン図を書くと思考の補助になります

次に、CASE式を用いて、各centerごとに重複の有無のステータスが見れるようにもクエリを書くことができます。

```sql
SELECT
  center,
  CASE
    WHEN COUNT(material) <> COUNT(DISTINCT material) THEN '重複あり'  ELSE '重複なし'
    END AS status
FROM materials
GROUP BY 1
```

| center  | status |
| ------- | ------ |
| fukuoka | 重複なし   |
| nagoya  | 重複あり   |
| osaka   | 重複なし   |
| tokyo   | 重複あり   |

次に、最初に描いたクエリですが、HAVINGをEXISTSで書き換えることもできます。
なぜなら、EXISTSは「データの存在チェック」を行うためのものなので。

- この場合も以下のメリットを享受できます
    - 具体的に重複しているmaterialを見ることができる
    - パフォーマンスがいい
- WHERE EXISTS () の中に、以下の条件を書けばいいでしょう
    - 同じセンター名
    - 異なる日付
    - 同じmaterial名
- これは「相関副問合せ(相関サブクエリ)」という方法で
    - EXISTSをWHERE条件として記述し、主問合せのテーブル列を参照する方法が一般的です

クエリで表現してみましょう。

```sql
SELECT
  center, material
FROM materials M1
WHERE EXISTS (
  SELECT * FROM materials M2
  WHERE M1.center = M2.center
  AND M1.receive_date <> M2.receive_date
  AND M1.material = M2.material
)
```

| center | material     |
| ------ | ------------ |
| tokyo  | zinc         |
| tokyo  | zinc         |
| nagoya | titanium     |
| nagoya | carbon steel |
| nagoya | carbon steel |
| nagoya | titanium     |

ちなみに、`NOT EXISTS`にすればダブりのないmaterialとcenterを参照できます。

本日は以上です。

## 参照

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

123 - 127p

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

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