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

はじめに

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

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

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

用いるデータ

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 の部分集合で切り分けられる
• どんな条件であれば欲しい情報が得られるか
  • 日本語にすると
    • 重複を排除してカウントした要素の数が、重複除いてない要素数と異なる
      • これの真偽を確かめればいいですね

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

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

結果

center
nagoya
tokyo

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

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

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条件として記述し、主問合せのテーブル列を参照する方法が一般的です

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

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を参照できます。

本日は以上です。

参照

123 - 127p

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