SQL指南書　HAVING句⑤　関係徐算でバスケット解析

SQL 指南書集目次 - ray88’s diary
SQL指南書外部結合⑨ 商集合（関係除算） - ray88’s diary
■バスケット解析
　・データマイニングの技術の一種で商品やアイテムが一緒に購入される傾向を分析する手法
　・様々な業務に以下なような用途でもバスケット手法が使用される
　　（例）医療分野で同時に複数の薬を併用する患者を探す場合
　　（例）社員の技術データベースからOracleとUNIXの両方に通じているプログラマを探す場合

■例題
　「item」テーブルのすべての商品を揃えている店舗を「shopitems」テーブルから選択する

■解答１（剰余を持った除算）
※商品マスタの全ての商品を揃えている店舗かつ商品マスタ以外の商品をおいている店舗も抽出されるパターン

--ビールと紙おむつと自転車すべてをおいている店舗を検索する：正しいSQL
SELECT SI.shop
	FROM shopitems SI,items I
	WHERE SI.item = I.item
	GROUP BY SI.shop
	HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM items);

【考え方】
STEP１：WHERE句で「items」テーブルに商品名が存在するレコードのみを抽出
STEP2：店舗ごとにグループ化してレコード数をカウント
STEP3：itemsテーブルのレコード総数（３件）と店舗ごとのレコード総数が一致する店舗のみを抽出

※「HAVING COUNT(SI.item) = COUNT(I.item)」とすると正しい結果が出力されない
　　　　結合の影響を受けてCOUNT(item)の値が元の「Items」テーブルの行数ではなくなっているため。
　　　　この条件だと仙台、東京、大阪の全店舗分が選択されてしまうので注意すること。
以下のSQLと出力結果が誤りのイメージ

/* COUNT(I.item) はもはや3 とは限らない(p.75) */
SELECT SI.shop, COUNT(SI.item), COUNT(I.item)
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop;

■解答２　厳密な関係除算
※商品マスタの全ての商品を揃えている店舗のみで商品マスタ以外の商品をおいている店舗は除外するパターン
※仙台は商品マスタにない商品「カーテン」をおいているので除外される

/* 厳密な関係除算：外部結合とCOUNT関数の利用(p.76) */
  SELECT SI.shop
    FROM ShopItems AS SI LEFT OUTER JOIN Items AS I
      ON SI.item=I.item
GROUP BY SI.shop
  HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)   /* 条件1 */
     AND COUNT(I.item)  = (SELECT COUNT(item) FROM Items);  /* 条件2 */

【考え方】
STEP1：「ShopItems」テーブルをマスタとして外部結合すると
　　　　「Items」テーブルには存在しない「カーテン」と「テレビ」はNULLとして「I.item」列に現れる
STEP2 ：SQL文の「条件１」により、COUNT(I.item) = 4 の仙台店が除外される
　　　　SQL文の「条件２」により、COUNT(I.item) = ２の大阪店が除外される（NULLはカウントされない）
STEP3：条件１と条件２に合致した東京店のみが抽出される
※通常、外部結合は商品マスタである「I.item」テーブルを主に結合する場合が多いが、ここではその主従をあえて逆転させている。

■応用例題
　「item」テーブルの全商品が揃っていない店舗について、
　どれくらいの品物が不足しているか一覧表示したい。
　列「my_item_cnt」に店舗の現在在庫数、列「diff_cnt」は足りない商品の数を抽出する
【解答】

/* 演習問題３：バスケット解析の一般化(p.294) */
SELECT SI.shop,
       COUNT(SI.item) AS my_item_cnt,
       (SELECT COUNT(item) FROM Items) - COUNT(SI.item) AS diff_cnt
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop;

【考え方】
STEP1：WHERE句で「Items」テーブルに商品名が存在するレコードのみを抽出
STEP2：「Items」テーブルの総件数３件より「ShopItemes」店舗ごとにグループ化して
　　　　カウントしたレコード数を引いて差異を求める
STEP3：STEP２でカウントした「ShopItems」のカウント数を「my_item_cnt」に、引き算して
　　　　求めた差異を「diff_cnt」に表示する
|
【参考】テーブル作成SQL

/* 関係除算でバスケット解析 */
CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
 
CREATE TABLE ShopItems
(shop VARCHAR(16),
 item VARCHAR(16),
    PRIMARY KEY(shop, item));

INSERT INTO Items VALUES('ビール');
INSERT INTO Items VALUES('紙オムツ');
INSERT INTO Items VALUES('自転車');

INSERT INTO ShopItems VALUES('仙台',  'ビール');
INSERT INTO ShopItems VALUES('仙台',  '紙オムツ');
INSERT INTO ShopItems VALUES('仙台',  '自転車');
INSERT INTO ShopItems VALUES('仙台',  'カーテン');
INSERT INTO ShopItems VALUES('東京',  'ビール');
INSERT INTO ShopItems VALUES('東京',  '紙オムツ');
INSERT INTO ShopItems VALUES('東京',  '自転車');
INSERT INTO ShopItems VALUES('大阪',  'テレビ');
INSERT INTO ShopItems VALUES('大阪',  '紙オムツ');
INSERT INTO ShopItems VALUES('大阪',  '自転車');