SQL指南書 外部結合⑤ 掛け算として結合

SQL 指南書集 目次 - ray88’s diary
■例題
　次のような商品マスタ「items」と、商品の売り上げ履歴を管理するテーブル「SalesHistory」を使用し、商品ごとに総計でいくつ売れたかを調べる帳票を出力する

■解答例１
STEP1：売り上げ履歴管理テーブル「SalesHistory」より商品番号でグループ化し、item_noを主キーとする中間ビュー「SH」を作成する

STEP2：商品マスタ「items」とSTEP1で作成したテーブル「SH」を「item_no」をキーとして結合する

/* 答え　その１：結合の前に集約することで、一対一の関係を作る(p.93) */
SELECT I.item_no, SH.total_qty
  FROM Items I LEFT OUTER JOIN
       (SELECT item_no, SUM(quantity) AS total_qty
          FROM SalesHistory
         GROUP BY item_no) SH
    ON I.item_no = SH.item_no;

※解答例１の問題点
　パフォーマンスの観点からみると、中間ビューのSHデータを一度メモリ上に保存しなければならない、
　また、せっかく「item_no」で一意になっても、ＳＨ自身には主キーのインデックスが存在しないため、
　結合条件で利用することができない。

■解答例２
※「結合を掛け算としてみる」結合は、１対１でなくとも、1対多なら行数は（不当には）増えない
STEP1：商品マスタ「items」と商品売り上げ履歴テーブル「SalesHistory」を「item_no」をキーに LEFT JOINで結合する

STEP2：STEP1で出力したテーブルを「item_no」で集約する

/* 答え　その２：集約の前に一対多の結合を行う(p.94) */
SELECT I.item_no, SUM(SH.quantity) AS total_qty
  FROM Items I LEFT OUTER JOIN SalesHistory SH
    ON I.item_no = SH.item_no /* 一対多の結合 */
 GROUP BY I.item_no
ORDER BY item_no;

■テーブル作成SQL

/* 掛け算としての結合 */
CREATE TABLE Items
 (item_no INTEGER PRIMARY KEY,
  item    VARCHAR(32) NOT NULL);

INSERT INTO Items VALUES(10, 'FD');
INSERT INTO Items VALUES(20, 'CD-R');
INSERT INTO Items VALUES(30, 'MO');
INSERT INTO Items VALUES(40, 'DVD');

CREATE TABLE SalesHistory
 (sale_date DATE NOT NULL,
  item_no   INTEGER NOT NULL,
  quantity  INTEGER NOT NULL,
  PRIMARY KEY(sale_date, item_no));

INSERT INTO SalesHistory VALUES('2007-10-01',  10,  4);
INSERT INTO SalesHistory VALUES('2007-10-01',  20, 10);
INSERT INTO SalesHistory VALUES('2007-10-01',  30,  3);
INSERT INTO SalesHistory VALUES('2007-10-03',  10, 32);
INSERT INTO SalesHistory VALUES('2007-10-03',  30, 12);
INSERT INTO SalesHistory VALUES('2007-10-04',  20, 22);
INSERT INTO SalesHistory VALUES('2007-10-04',  30,  7);