ホーム ＞ リレーショナル・データベースの世界 ＞
『達人に学ぶ SQL徹底指南書』サポートページ
このサイトは、拙著『達人に学ぶ SQL徹底指南書』
（翔泳社, 2008）のサポートページです。主な内容としては、本文中に掲載されていないテーブル定義文やサンプルデータ、および実装に依存するコードについての注意や書籍には載っていない別解の紹介、そして各章を書いた動機やその当時考えていたことなんかの四方山ばなしです（最後のが一番多い）。
疑問、間違いの指摘、新たな解法のアイデアなども随時受け付けております。メール、ゲストブック、ブログのいずれからでもどうぞ。
注意事項：
- 翔泳社のサイトには、本書の内容に関するQ & Aが掲載されています。こちらも参照のほど。
1-1．CASE式のススメ
初出は私のサイト。何度か改訂を繰り返した（私が自分のサイトに書くテキストはみんなそうだけど）のですが、履歴によれば作成日は2005年3月。CodeZineに最初に掲載していただいた記事でもあり（CodeZineの初出は2006年6月）、その意味で、やはり記念すべき冒頭に持ってくるにふさわしい。
最初に編集者の方から「記事を書かないか」という話を受けたときは、連載か単発かも不分明だったので、一回こっきりで終わってもいいよう、これだけは世に出しておきたい、というものを選びました。だから内容に関しても不満はなし。手続き型言語で IF 文がなくてはやっていけないように、SQL は CASE 式がなくてはやっていけません。SQL における分岐が「式」のレベルで行われるという点を理解することで、SQL の極めて抽象性の高い柔軟な表現力（それは関数型言語へ接近する）が見えてきます。
素晴らしき SQL とデータベースの世界へ、ようこそ。
サンプルデータ
/* 既存のコード体系を新しい体系に変換して集計 */
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
population INTEGER NOT NULL);
INSERT INTO PopTbl VALUES('徳島', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('愛媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福岡', 300);
INSERT INTO PopTbl VALUES('佐賀', 100);
INSERT INTO PopTbl VALUES('長崎', 200);
INSERT INTO PopTbl VALUES('東京', 400);
INSERT INTO PopTbl VALUES('群馬', 50);
/* 異なる条件の集計を1つのSQLで行う */
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
sex CHAR(1) NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(pref_name, sex));
INSERT INTO PopTbl2 VALUES('徳島', '1', 60 );
INSERT INTO PopTbl2 VALUES('徳島', '2', 40 );
INSERT INTO PopTbl2 VALUES('香川', '1', 100);
INSERT INTO PopTbl2 VALUES('香川', '2', 100);
INSERT INTO PopTbl2 VALUES('愛媛', '1', 100);
INSERT INTO PopTbl2 VALUES('愛媛', '2', 50 );
INSERT INTO PopTbl2 VALUES('高知', '1', 100);
INSERT INTO PopTbl2 VALUES('高知', '2', 100);
INSERT INTO PopTbl2 VALUES('福岡', '1', 100);
INSERT INTO PopTbl2 VALUES('福岡', '2', 200);
INSERT INTO PopTbl2 VALUES('佐賀', '1', 20 );
INSERT INTO PopTbl2 VALUES('佐賀', '2', 80 );
INSERT INTO PopTbl2 VALUES('長崎', '1', 125);
INSERT INTO PopTbl2 VALUES('長崎', '2', 125);
INSERT INTO PopTbl2 VALUES('東京', '1', 250);
INSERT INTO PopTbl2 VALUES('東京', '2', 150);
/* CHECK制約で複数の列の条件関係を定義する */
CREATE TABLE TestSal
(sex CHAR(1) ,
salary INTEGER,
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 ));
INSERT INTO TestSal VALUES(1, 200000);
INSERT INTO TestSal VALUES(1, 300000);
INSERT INTO TestSal VALUES(1, NULL);
INSERT INTO TestSal VALUES(2, 200000);
INSERT INTO TestSal VALUES(2, 300000); --error
INSERT INTO TestSal VALUES(2, NULL);
INSERT INTO TestSal VALUES(1, 300000);
/* 条件を分岐させたUPDATE */
CREATE TABLE SomeTable
(p_key CHAR(1) PRIMARY KEY,
col_1 INTEGER NOT NULL,
col_2 CHAR(2) NOT NULL);
INSERT INTO SomeTable VALUES('a', 1, 'あ');
INSERT INTO SomeTable VALUES('b', 2, 'い');
INSERT INTO SomeTable VALUES('c', 3, 'う');
/* テーブル同士のマッチング */
CREATE TABLE CourseMaster
(course_id INTEGER PRIMARY KEY,
course_name VARCHAR(32) NOT NULL);
INSERT INTO CourseMaster VALUES(1, '経理入門');
INSERT INTO CourseMaster VALUES(2, '財務知識');
INSERT INTO CourseMaster VALUES(3, '簿記検定');
INSERT INTO CourseMaster VALUES(4, '税理士');
CREATE TABLE OpenCourses
(month INTEGER ,
course_id INTEGER ,
PRIMARY KEY(month, course_id));
INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);
/* CASE式の中で集約関数を使う */
CREATE TABLE StudentClub
(std_id INTEGER,
club_id INTEGER,
club_name VARCHAR(32),
main_club_flg CHAR(1),
PRIMARY KEY (std_id, club_id));
INSERT INTO StudentClub VALUES(100, 1, '野球', 'Y');
INSERT INTO StudentClub VALUES(100, 2, '吹奏楽', 'N');
INSERT INTO StudentClub VALUES(200, 2, '吹奏楽', 'N');
INSERT INTO StudentClub VALUES(200, 3, 'バドミントン','Y');
INSERT INTO StudentClub VALUES(200, 4, 'サッカー', 'N');
INSERT INTO StudentClub VALUES(300, 4, 'サッカー', 'N');
INSERT INTO StudentClub VALUES(400, 5, '水泳', 'N');
INSERT INTO StudentClub VALUES(500, 6, '囲碁', 'N');
本文中のコード
/* 県コードを地方コードに再分類する(p.11) */
SELECT CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END;
/* 人口階級ごとに都道府県を分類する(p.11) */
SELECT CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END;
/* 地方単位にコードを再分類する その２：CASE 式を一箇所にまとめる(p.12) */
SELECT CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;
/* 異なる条件の集計を1 つのSQL で行なう(p.14) */
SELECT pref_name,
/* 男性の人口 */
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
/* 女性の人口 */
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;
/* CHECK 制約で複数の列の条件関係を定義する */
/* 条件法(p.15) */
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )
/* 論理積(p.16) */
CONSTRAINT check_salary CHECK
( sex = '2' AND salary <= 200000 )
/* 条件を分岐させたUPDATE(p.17) */
UPDATE Personnel
SET salary = CASE WHEN salary >= 300000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary * 1.2
ELSE salary END;
/* CASE 式で主キーを入れ替える(p.19) */
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
/* テーブルのマッチング：IN 述語の利用(p.21) */
SELECT CM.course_name,
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN '○'
ELSE '×' END AS "200706",
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN '○'
ELSE '×' END AS "200707",
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;
/* テーブルのマッチング：EXISTS 述語の利用(p.21) */
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200706",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200707",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;
/* CASE 式の中で集約関数を使う(p.23) */
SELECT std_id,
CASE WHEN COUNT(*) = 1 /* 一つのクラブに専念する学生の場合 */
THEN MAX(club_id)
ELSE MAX(CASE WHEN main_club_flg = 'Y'
THEN club_id
ELSE NULL END)
END AS main_club
FROM StudentClub
GROUP BY std_id;
CASE 式の使い方に関してポイントはいくつかあります。SELECT 句で使うと効果的なことや、集約関数の中にも外にも書ける柔軟さについても、書籍中で述べたとおりです。しかし、それらは全て大本の一つの要点から派生したものばかりです。CASE 式に関して、全てに勝る最も重要なこと、それは、CASE 式が「式」であって「文」ではないこと。これがアルファにしてオメガです。お忘れなきよう。
1-2．自己結合の使い方
初出は CodeZine 2006年7月31日。編集の斉木さんから「CASE式」がまずまず好評だったのでこの調子で次も一つよろしく、と言われて気をよくして同じような路線で書いたのがこれ。「同じような路線」とはつまり、
- 重要なのだけどあまり広く理解されていないことを選択的に拾う。重要でかつ広く理解されている、あるいは取り上げられる機会の多いトピックは扱わない（例えばパフォーマンス・チューニング）。「私が書かなければあと10年誰も書くまい」というような話を先に出す。
- 単なる Tips の列挙に終わるのではなく、裏に潜む数学と論理（SQL の場合は集合論と述語論理）にまで届くような「深さ」を持たせる。したがって、実装依存の機能は極力使わない（といいつつ、最初から行ポインタの話してるじゃないの）。
- 10人中10人が「役に立った」と言われるよりは、10人中2, 3人に「へー、SQLって面白れーな」と感じてもらえばいいや。
いま振り返れば、ぽっと出の新米ライターとしてはかまえすぎです。でも一方で、自分が勝負できるスタンスは結局これしかなかったわけだし、と思うのも事実。私はコミュニティやベンダーに属してもいないし、特定の製品の機能に詳しいわけでもないし、時事的なニュースにも疎い化石みたいな人間です。だから勝負したくてもそういうフィールドでは勝負できない。犬には犬の、猫には猫の喧嘩の仕方がある、というところでしょう。
サンプルデータ
CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
price INTEGER NOT NULL);
--重複順列・順列・組み合わせ
INSERT INTO Products VALUES('りんご', 100);
INSERT INTO Products VALUES('みかん', 50);
INSERT INTO Products VALUES('バナナ', 80);
--部分的に不一致なキーの検索
CREATE TABLE Addresses
(name VARCHAR(32),
family_id INTEGER,
address VARCHAR(32),
PRIMARY KEY(name, family_id));
INSERT INTO Addresses VALUES('前田 義明', '100', '東京都港区虎ノ門3-2-29');
INSERT INTO Addresses VALUES('前田 由美', '100', '東京都港区虎ノ門3-2-92');
INSERT INTO Addresses VALUES('加藤 茶', '200', '東京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('加藤 勝', '200', '東京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('ホームズ', '300', 'ベーカー街221B');
INSERT INTO Addresses VALUES('ワトソン', '400', 'ベーカー街221B');
--ランキング
DELETE FROM Products;
INSERT INTO Products VALUES('りんご', 50);
INSERT INTO Products VALUES('みかん', 100);
INSERT INTO Products VALUES('ぶどう', 50);
INSERT INTO Products VALUES('スイカ', 80);
INSERT INTO Products VALUES('レモン', 30);
INSERT INTO Products VALUES('バナナ', 50);
--非集約テスト用
DELETE FROM Products;
INSERT INTO Products VALUES('みかん', 100);
INSERT INTO Products VALUES('ぶどう', 50);
INSERT INTO Products VALUES('スイカ', 80);
INSERT INTO Products VALUES('レモン', 30);
本文中のコード
/* 重複順列を得るSQL(p.29) */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2;
/* 順列を得るSQL(p.30) */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name <> P2.name;
/* 組み合わせを得るSQL(p.31) */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;
/* 重複行を削除するSQL その1：極値関数の利用(p.33) */
DELETE FROM Products P1
WHERE rowid < ( SELECT MAX(P2.rowid)
FROM Products P2
WHERE P1.name = P2. name
AND P1.price = P2.price ) ;
/* 重複行を削除するSQL その2：非等値結合の利用(p.34) */
DELETE FROM Products P1
WHERE EXISTS ( SELECT *
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price
AND P1.rowid < P2.rowid );
/* 同じ家族だけど、住所が違うレコードを検索する(p.35) */
SELECT DISTINCT A1.name, A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;
/* 同じ値段だけど、商品名が違うレコードを検索する (p.36) */
SELECT DISTINCT P1.name, P1.price
FROM Products P1, Products P2
WHERE P1.price = P2.price
AND P1.name <> P2.name;
/* ランキング算出：OLAP 関数の利用(p.37) */
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rank_1,
DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
FROM Products;
/* ランキング 1 位から始まる。同順位が続いた後は不連続(p.37) */
SELECT P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM Products P2
WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1;
/* ランキングを求める：自己結合の利用(p.40) */
SELECT P1.name,
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM Products P1 LEFT OUTER JOIN Products P2
ON P1.price < P2.price
GROUP BY P1.name;
/* ランキングを求める：内部結合に変えると・・・(p.41) */
SELECT P1.name,
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM Products P1 INNER JOIN Products P2
ON P1.price < P2.price
GROUP BY P1.name;
この章の最大のポイントは、ランキング算出の自己相関サブクエリが、自然数を構成するためにノイマンが考案した同心円的な再帰集合のアイデアを源泉としている、という意外なリンクにあります。これには是非おどろいてほしい。
この方法自体は、経験豊かな DB エンジニアの間では昔から知られていたものですが、ノイマンにまで遡って解説した人物は、セルコ以外に知りません。さすがに背景知識がないと唐突過ぎてわかりにくいだろうな、と思って書いたのが第2部の「SQL と再帰集合」です。
何気ない道端に突如こういう「穴」が空いているのが、SQL という言語のいいところ。ほんとに。
なお、重複行を削除する自己相関サブクエリは、MySQL では動作しません。MySQL は、そもそも DELETE や UPDATE でのサブクエリの使用を認めていないようです（非相関なサブクエリであっても許されないという、かなり厳しい制限です）。他の DB では問題なく動作します。
1-3．3値論理とNULL
初出は CodeZine 2006年10月2日。「自己結合」の回も（意外に？）ウケたので、次もどんどん行きましょう、という編集部のお言葉を頂いて、それじゃあとさらに調子に乗って書きました。実は、これの原案自体は自分のサイトで2002年から既に公開していたものなので、その意味では出自は本書の中で一番古いものです。多分、サイトを作り始めた頃に書いた最初期のテキストの一つです。昔は、こういう基礎理論よりのものを好んで書いていました。多くは若書き以外の何ものでもなく、今となっては恥ずかしくて読むに堪えないので HD の奥に死蔵するか、原形をとどめないほどに変えてしまっているかのどちらかです。
内容は、「重要なのだけどあまり広く理解されていないものばかり拾う」という方向性を地で行くのですが（このテーマにいたっては、私が書かねば日本ではあと20年、誰も取り上げなかったでしょう）、それだけに引き受けてくれるメディアもないだろうと諦めていました。CodeZine は、こういう得体の知れないテキストを二つ返事で引き受けてくれる得がたいメディアで、「いやあ、いいですねえ。マニアックで」といってほとんどそのままのっけてしまった。Web という媒体のフットワークのよさと、編集部の寛容な方針が幸運に作用した結果でしょう。
感謝。
サンプルデータ
/* 実践編：１. 比較述語とNULL その１：排中律が成立しない */
CREATE TABLE Students
(name VARCHAR(16) PRIMARY KEY,
age INTEGER );
INSERT INTO Students VALUES('ブラウン', 22);
INSERT INTO Students VALUES('ラリー', 19);
INSERT INTO Students VALUES('ジョン', NULL);
INSERT INTO Students VALUES('ボギー', 21);
/* 実践編：３. NOT IN とNOT EXISTS は同値ではない */
CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
age INTEGER,
city VARCHAR(16) NOT NULL );
CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
age INTEGER,
city VARCHAR(16) NOT NULL );
INSERT INTO Class_A VALUES('ブラウン', 22, '東京');
INSERT INTO Class_A VALUES('ラリー', 19, '埼玉');
INSERT INTO Class_A VALUES('ボギー', 21, '千葉');
INSERT INTO Class_B VALUES('斎藤', 22, '東京');
INSERT INTO Class_B VALUES('田尻', 23, '東京');
INSERT INTO Class_B VALUES('山田', NULL, '東京');
INSERT INTO Class_B VALUES('和泉', 18, '千葉');
INSERT INTO Class_B VALUES('武田', 20, '千葉');
INSERT INTO Class_B VALUES('石川', 19, '神奈川');
/* 実践編：4．限定述語とNULL */
DELETE FROM Class_A;
INSERT INTO Class_A VALUES('ブラウン', 22, '東京');
INSERT INTO Class_A VALUES('ラリー', 19, '埼玉');
INSERT INTO Class_A VALUES('ボギー', 21, '千葉');
DELETE FROM Class_B;
INSERT INTO Class_B VALUES('斎藤', 22, '東京');
INSERT INTO Class_B VALUES('田尻', 23, '東京');
INSERT INTO Class_B VALUES('山田', 20, '東京');
INSERT INTO Class_B VALUES('和泉', 18, '千葉');
INSERT INTO Class_B VALUES('武田', 20, '千葉');
INSERT INTO Class_B VALUES('石川', 19, '神奈川');
/* 実践編：5．限定述語と極値関数は同値ではない */
DELETE FROM Class_B;
INSERT INTO Class_B VALUES('和泉', 18, '千葉');
INSERT INTO Class_B VALUES('武田', 20, '千葉');
INSERT INTO Class_B VALUES('石川', 19, '神奈川');
本文中のコード
/* 年齢が20 歳か、20 歳でない生徒を選択せよ(p.52) */
SELECT *
FROM Students
WHERE age = 20
OR age <> 20;
/* 第3 の条件を追加：「年齢が20 歳か、20 歳でないか、または年齢が分からない」(p.53) */
SELECT *
FROM Students
WHERE age = 20
OR age <> 20
OR age IS NULL;
/* B クラスの東京在住の生徒と年齢が一致しないA クラスの生徒を選択するSQL？ (p.55) */
SELECT *
FROM Class_A
WHERE age NOT IN ( SELECT age
FROM Class_B
WHERE city = '東京' );
/* 正しいSQL：ラリーとボギーが選択される(p.57) */
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT *
FROM Class_B B
WHERE A.age = B.age
AND B.city = '東京' );
/* B クラスの東京在住の誰よりも若いA クラスの生徒を選択する(p.59) */
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age
FROM Class_B
WHERE city = '東京' );
/* 東京在住の生徒の平均年齢より若いA クラスの生徒を選択するSQL？ (p.61) */
SELECT *
FROM Class_A
WHERE age < ( SELECT AVG(age)
FROM Class_B
WHERE city = '東京' );
本文でも口をすっぱくして書いてますが、基本的に3値論理という体系は使わぬが身のためです。コッド博士がなんと言っても、ね。そのためには、とにかくテーブル設計の時点で一つでも多くの列に NOT NULL 制約を付加しておくことです。そうすれば、意外なところで足をすくわれる危険をかなり軽減できます。でも、自分の裁量でテーブル設計を自由にできるとは限りませんし、また NULL は排除しようと躍起になっても根絶は難しいので、やはり3値論理の動作については熟知しておかねばなりません。遠巻きに眺める分には面白いのだけど、いざ付き合うとなると面倒なやつ。それが3値論理。
1-4．HAVING句の力
初出は CodeZine 2006年11月2日。これは、私が SQL について書いたテキストの中で一番のヒット作。その評価は、嬉しいことに独りよがりのものではないようで、CodeZine 誌上でも反響が高かった。実用性と面白さのバランスをうまくとれたのが勝因でしょう。
サンプルデータ
/* データの歯抜けを探す */
CREATE TABLE SeqTbl
(seq INTEGER PRIMARY KEY,
name VARCHAR(16) NOT NULL);
INSERT INTO SeqTbl VALUES(1, 'ディック');
INSERT INTO SeqTbl VALUES(2, 'アン');
INSERT INTO SeqTbl VALUES(3, 'ライル');
INSERT INTO SeqTbl VALUES(5, 'カー');
INSERT INTO SeqTbl VALUES(6, 'マリー');
INSERT INTO SeqTbl VALUES(8, 'ベン');
/* HAVING 句でサブクエリ：最頻値を求める(メジアンも同じサンプルを使用) */
CREATE TABLE Graduates
(name VARCHAR(16) PRIMARY KEY,
income INTEGER NOT NULL);
INSERT INTO Graduates VALUES('サンプソン', 400000);
INSERT INTO Graduates VALUES('マイク', 30000);
INSERT INTO Graduates VALUES('ホワイト', 20000);
INSERT INTO Graduates VALUES('アーノルド', 20000);
INSERT INTO Graduates VALUES('スミス', 20000);
INSERT INTO Graduates VALUES('ロレンス', 15000);
INSERT INTO Graduates VALUES('ハドソン', 15000);
INSERT INTO Graduates VALUES('ケント', 10000);
INSERT INTO Graduates VALUES('ベッカー', 10000);
INSERT INTO Graduates VALUES('スコット', 10000);
/* NULL を含まない集合を探す */
CREATE TABLE Students
(student_id INTEGER PRIMARY KEY,
dpt VARCHAR(16) NOT NULL,
sbmt_date DATE);
INSERT INTO Students VALUES(100, '理学部', '2005-10-10');
INSERT INTO Students VALUES(101, '理学部', '2005-09-22');
INSERT INTO Students VALUES(102, '文学部', NULL);
INSERT INTO Students VALUES(103, '文学部', '2005-09-10');
INSERT INTO Students VALUES(200, '文学部', '2005-09-22');
INSERT INTO Students VALUES(201, '工学部', NULL);
INSERT INTO Students VALUES(202, '経済学部', '2005-09-25');
/* 関係除算でバスケット解析 */
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('大阪', '自転車');
本文中のコード
/* 結果が返れば歯抜けあり(p.65) */
SELECT '歯抜けあり' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
/* 歯抜けの最小値を探す(p.67) */
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
/* 最頻値を求めるSQL その1：ALL述語の利用(p.68) */
SELECT income
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
FROM Graduates
GROUP BY income);
/* 最頻値を求めるSQL その2：極値関数の利用(p.69) */
SELECT income
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ( SELECT MAX(cnt)
FROM ( SELECT COUNT(*) AS cnt
FROM Graduates
GROUP BY income) TMP) ;
/* メジアンを求めるSQL：自己非等値結合をHAVING句で使う(p.70) */
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income
/* S1の条件 */
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2
/* S2の条件 */
AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2 ) TMP;
/* 提出日にNULLを含まない学部を選択する その１：COUNT 関数の利用(p.73) */
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
/* 提出日にNULLを含まない学部を選択する その２：CASE 式の利用(p.73) */
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
THEN 1
ELSE 0 END);
/* ビールと紙オムツと自転車をすべて置いている店舗を検索する：間違ったSQL(p.74) */
SELECT DISTINCT shop
FROM ShopItems
WHERE item IN (SELECT item FROM Items);
/* ビールと紙オムツと自転車をすべて置いている店舗を検索する：正しい SQL(p.75) */
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);
/* 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 */
この章で紹介する技術はどれもこれも面白いのですが、個人的なみどころとしては、関係除算の解説です。実務的にも応用のきく技術であるのはもちろん、関係という集合が「体」となりうるかがこの演算にかかっているという理論的な重要性にまで話が広がるのが面白い。
1-5．外部結合の使い方
初出は CodeZine 2006年12月10日。この頃から「月1ペースぐらいの連載」というのが暗黙の了解になってきて、一つ脱稿したら次のネタを探す、というサイクルにも慣れてきました。
外部結合は必ず取り上げなければならない、とは前々から思っていたのですが、通り一遍の解説をしてもつまらないので、外部結合の使い方としては半ば邪道に属するものばかり集めてみようという狙いで書きました。だから他の章と比べると、ちょっと小手先の話が多い。しかし、見慣れた道具をちょっと違う使い方をすることで、また新たな一面が発見できる面白さはあると思います。
幸運にも、これも意外に好評を得て、前回の「HAVING」についで人気の出た回でした。単に外部結合について勉強したい人が多いだけ、という気がしなくもないけど。
サンプルデータ
/* 外部結合で行列変換 その1（行→列）：クロス表を作る */
CREATE TABLE Courses
(name VARCHAR(32),
course VARCHAR(32),
PRIMARY KEY(name, course));
INSERT INTO Courses VALUES('赤井', 'SQL入門');
INSERT INTO Courses VALUES('赤井', 'UNIX基礎');
INSERT INTO Courses VALUES('鈴木', 'SQL入門');
INSERT INTO Courses VALUES('工藤', 'SQL入門');
INSERT INTO Courses VALUES('工藤', 'Java中級');
INSERT INTO Courses VALUES('吉田', 'UNIX基礎');
INSERT INTO Courses VALUES('渡辺', 'SQL入門');
/* 外部結合で行列変換 その2（列→行）：繰り返し項目を1 列にまとめる */
CREATE TABLE Personnel
(employee varchar(32),
child_1 varchar(32),
child_2 varchar(32),
child_3 varchar(32),
PRIMARY KEY(employee));
INSERT INTO Personnel VALUES('赤井', '一郎', '二郎', '三郎');
INSERT INTO Personnel VALUES('工藤', '春子', '夏子', NULL);
INSERT INTO Personnel VALUES('鈴木', '夏子', NULL, NULL);
INSERT INTO Personnel VALUES('吉田', NULL, NULL, NULL);
/* クロス表で入れ子の表側を作る */
CREATE TABLE TblSex
(sex_cd char(1),
sex varchar(5),
PRIMARY KEY(sex_cd));
CREATE TABLE TblAge
(age_class char(1),
age_range varchar(30),
PRIMARY KEY(age_class));
CREATE TABLE TblPop
(pref_name varchar(30),
age_class char(1),
sex_cd char(1),
population integer,
PRIMARY KEY(pref_name, age_class,sex_cd));
INSERT INTO TblSex (sex_cd, sex ) VALUES('m', '男');
INSERT INTO TblSex (sex_cd, sex ) VALUES('f', '女');
INSERT INTO TblAge (age_class, age_range ) VALUES('1', '21〜30歳');
INSERT INTO TblAge (age_class, age_range ) VALUES('2', '31〜40歳');
INSERT INTO TblAge (age_class, age_range ) VALUES('3', '41〜50歳');
INSERT INTO TblPop VALUES('秋田', '1', 'm', 400 );
INSERT INTO TblPop VALUES('秋田', '3', 'm', 1000 );
INSERT INTO TblPop VALUES('秋田', '1', 'f', 800 );
INSERT INTO TblPop VALUES('秋田', '3', 'f', 1000 );
INSERT INTO TblPop VALUES('青森', '1', 'm', 700 );
INSERT INTO TblPop VALUES('青森', '1', 'f', 500 );
INSERT INTO TblPop VALUES('青森', '3', 'f', 800 );
INSERT INTO TblPop VALUES('東京', '1', 'm', 900 );
INSERT INTO TblPop VALUES('東京', '1', 'f', 1500 );
INSERT INTO TblPop VALUES('東京', '3', 'f', 1200 );
INSERT INTO TblPop VALUES('千葉', '1', 'm', 900 );
INSERT INTO TblPop VALUES('千葉', '1', 'f', 1000 );
INSERT INTO TblPop VALUES('千葉', '3', 'f', 900 );
/* 掛け算としての結合 */
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);
/* 完全外部結合 */
CREATE TABLE Class_A
(id char(1),
name varchar(30),
PRIMARY KEY(id));
CREATE TABLE Class_B
(id char(1),
name varchar(30),
PRIMARY KEY(id));
INSERT INTO Class_A (id, name) VALUES('1', '田中');
INSERT INTO Class_A (id, name) VALUES('2', '鈴木');
INSERT INTO Class_A (id, name) VALUES('3', '伊集院');
INSERT INTO Class_B (id, name) VALUES('1', '田中');
INSERT INTO Class_B (id, name) VALUES('2', '鈴木');
INSERT INTO Class_B (id, name) VALUES('4', '西園寺');
本文中のコード
/* クロス表を求める水平展開 その1：外部結合の利用(p.83) */
SELECT C0.name,
CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL入門",
CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX基礎",
CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java中級"
FROM (SELECT DISTINCT name FROM Courses) C0
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'SQL入門' ) C1
ON C0.name = C1.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'UNIX基礎' ) C2
ON C0.name = C2.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Java中級' ) C3
ON C0.name = C3.name;
/* 水平展開 その2：スカラ・サブクエリの利用(p.84) */
SELECT C0.name,
(SELECT '○'
FROM Courses C1
WHERE course = 'SQL入門'
AND C1.name = C0.name) AS "SQL入門",
(SELECT '○'
FROM Courses C2
WHERE course = 'UNIX基礎'
AND C2.name = C0.name) AS "UNIX基礎",
(SELECT '○'
FROM Courses C3
WHERE course = 'Java中級'
AND C3.name = C0.name) AS "Java中級"
FROM (SELECT DISTINCT name FROM Courses) C0;
/* 水平展開 その3：CASE式を入れ子にする(p.85) */
SELECT name,
CASE WHEN SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE NULL END) >= 1
THEN '○' ELSE NULL END AS "SQL入門",
CASE WHEN SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE NULL END) >= 1
THEN '○' ELSE NULL END AS "UNIX基礎",
CASE WHEN SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE NULL END) >= 1
THEN '○' ELSE NULL END AS "Java中級"
FROM Courses
GROUP BY name;
/* 列から行への変換：UNION ALLの利用(p.86) */
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;
/* 社員の子供リストを得るSQL（子供のいない社員も出力する）(p.86) */
SELECT EMP.employee, CHILDREN.child
FROM Personnel EMP
LEFT OUTER JOIN
(SELECT child_1 AS child FROM Personnel
UNION
SELECT child_2 AS child FROM Personnel
UNION
SELECT child_3 AS child FROM Personnel) CHILDREN
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
/* 子供マスタのビュー(p.87) */
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel
UNION
SELECT child_3 FROM Personnel;
/* 社員の子供リストを得るSQL（子供のいない社員も出力する）(p.87) */
SELECT EMP.employee, CHILDREN.child
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
/* 外部結合で入れ子の表側を作る：間違ったSQL(p.89) */
SELECT MASTER1.age_class AS age_class,
MASTER2.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('東京', '千葉')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1 /* 外部結合1：年齢階級マスタと結合 */
ON MASTER1.age_class = DATA.age_class
RIGHT OUTER JOIN TblSex MASTER2 /* 外部結合2：性別マスタと結合 */
ON MASTER2.sex_cd = DATA.sex_cd;
/* 最初の外部結合で止めた場合：年齢階級「2」も結果に現れる(p.90) */
SELECT MASTER1.age_class AS age_class,
DATA.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('東京', '千葉')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1
ON MASTER1.age_class = DATA.age_class;
/* 外部結合で入れ子の表側を作る：正しいSQL(p.91) */
SELECT
MASTER.age_class AS age_class,
MASTER.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM
(SELECT
age_class,
sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('東京', '千葉')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN
(SELECT age_class, sex_cd
FROM TblAge
CROSS JOIN
TblSex ) MASTER
ON MASTER.age_class = DATA.age_class
AND MASTER.sex_cd = DATA.sex_cd;
/* 答え その１：結合の前に集約することで、一対一の関係を作る(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;
/* 答え その２：集約の前に一対多の結合を行う(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;
/* 完全外部結合は情報を「完全」に保存する(p.95) */
SELECT COALESCE(A.id, B.id) AS id,
A.name AS A_name,
B.name AS B_name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id;
/* 完全外部結合が使えない環境での代替方法(p.96) */
SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id;
/* 外部結合で差集合を求める：A-B(p.97) */
SELECT A.id AS id, A.name AS A_name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
WHERE B.name IS NULL;
/* 外部結合で差集合を求める：B-A(p.98) */
SELECT B.id AS id, B.name AS B_name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL;
/* 完全外部結合で排他的和集合を求める(p.98) */
SELECT COALESCE(A.id, B.id) AS id,
COALESCE(A.name , B.name ) AS name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL
OR B.name IS NULL;
/* 外部結合で関係除算：差集合の応用(p.99) */
SELECT DISTINCT shop
FROM ShopItems SI1
WHERE NOT EXISTS
(SELECT I.item
FROM Items I LEFT OUTER JOIN ShopItems SI2
ON SI1.shop = SI2.shop
AND I.item = SI2.item
WHERE SI2.item IS NULL) ;
先に「邪道」と否定的な言葉を使いましたが、実はそういう技術ほどけっこう実務で役に立ってしまうのも、また否定できない事実です。それはそれでいいのですが、でもそういうのはあくまでこそっと隠れて後ろめたい気持ちをもって使うものだ、という節度は、忘れないでください。老婆心からのお説教です。
1-6．相関サブクエリで行と行を比較する
初出は CodeZine 2007年2月8日。外部結合と並んで初級者の躓きの石となる相関サブクエリの解説です。この技術が何のために SQL に導入されたのか、という理由は、日本では本当に、全く理解されていません。それは、一言でいって「ループの代用」なのですが（だから相関サブクエリはずばり「ループ・クエリ」とも呼ばれる）、この手続き型の道具から離れることはなかなか困難で、よって相関サブクエリもその真価を知られないままになっています。しかし、特に OLAP 関数をまだ備えていない PostgreSQL や MySQL などの DB では、行間比較の方法としては相関サブクエリに頼るしかないので、これらの DB をメインに使っている方はぜひとも理解してください。
見所としては、やはり「移動累計と移動平均」での集合の作り方でしょう。SQLでノイマン型の再帰集合を作れることを初めて知ったときは、本当に驚いたものです。いずれ OLAP 関数が普及すれば、このやり方は廃れていくのでしょうけど、この原理的というか、真理的なものに触れたときの驚きと感動は、できれば多くの DB エンジニアに味わってもらいたい。データベースの仕事をしていてこの感覚を知らずに終わるなんて、人生もったいないでっせ。
サンプルデータ
--成長・後退・現状維持
CREATE TABLE Sales
(year INTEGER NOT NULL ,
sale INTEGER NOT NULL ,
PRIMARY KEY (year));
INSERT INTO Sales VALUES (1990, 50);
INSERT INTO Sales VALUES (1991, 51);
INSERT INTO Sales VALUES (1992, 52);
INSERT INTO Sales VALUES (1993, 52);
INSERT INTO Sales VALUES (1994, 50);
INSERT INTO Sales VALUES (1995, 50);
INSERT INTO Sales VALUES (1996, 49);
INSERT INTO Sales VALUES (1997, 55);
--時系列に歯抜けがある場合：直近と比較
CREATE TABLE Sales2
(year INTEGER NOT NULL ,
sale INTEGER NOT NULL ,
PRIMARY KEY (year));
INSERT INTO Sales2 VALUES (1990, 50);
INSERT INTO Sales2 VALUES (1992, 50);
INSERT INTO Sales2 VALUES (1993, 52);
INSERT INTO Sales2 VALUES (1994, 55);
INSERT INTO Sales2 VALUES (1997, 55);
--移動累計と移動平均
CREATE TABLE Accounts
(prc_date DATE NOT NULL ,
prc_amt INTEGER NOT NULL ,
PRIMARY KEY (prc_date)) ;
INSERT INTO Accounts VALUES ('2006-10-26', 12000 );
INSERT INTO Accounts VALUES ('2006-10-28', 2500 );
INSERT INTO Accounts VALUES ('2006-10-31', -15000 );
INSERT INTO Accounts VALUES ('2006-11-03', 34000 );
INSERT INTO Accounts VALUES ('2006-11-04', -5000 );
INSERT INTO Accounts VALUES ('2006-11-06', 7200 );
INSERT INTO Accounts VALUES ('2006-11-11', 11000 );
--オーバーラップする期間を調べる
CREATE TABLE Reservations
(reserver VARCHAR(30) PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL);
INSERT INTO Reservations VALUES('木村', '2006-10-26', '2006-10-27');
INSERT INTO Reservations VALUES('荒木', '2006-10-28', '2006-10-31');
INSERT INTO Reservations VALUES('堀', '2006-10-31', '2006-11-01');
INSERT INTO Reservations VALUES('山本', '2006-11-03', '2006-11-04');
INSERT INTO Reservations VALUES('内田', '2006-11-03', '2006-11-05');
INSERT INTO Reservations VALUES('水谷', '2006-11-06', '2006-11-06');
--山本氏の投宿日が4日の場合
DELETE FROM Reservations WHERE reserver = '山本';
INSERT INTO Reservations VALUES('山本', '2006-11-04', '2006-11-04');
本文中のコード
/* 前年と年商が同じ年度を求める その1：相関サブクエリの利用(p.106) */
SELECT year,sale
FROM Sales S1
WHERE sale = (SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1)
ORDER BY year;
/* 前年と年商が同じ年度を求める その2：自己結合の利用(p.106) */
SELECT S1.year, S1.sale
FROM Sales S1,
Sales S2
WHERE S2.sale = S1.sale
AND S2.year = S1.year - 1
ORDER BY year;
/* 成長、後退、現状維持を一度に求める その1：相関サブクエリの利用(p.107) */
SELECT S1.year, S1.sale,
CASE WHEN sale =
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '→' /* 横ばい */
WHEN sale >
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '↑' /* 成長 */
WHEN sale <
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '↓' /* 後退 */
ELSE '-' END AS var
FROM Sales S1
ORDER BY year;
/* 成長、後退、現状維持を一度に求める その2：自己結合の利用(p.108)
※本文の二つ目の分岐は、「↑」ではなく「↓」の間違いです。 */
SELECT S1.year, S1.sale,
CASE SIGN(sale -
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) )
WHEN 0 THEN '→'
WHEN 1 THEN '↑'
WHEN -1 THEN '↓'
ELSE '-' END AS var
FROM Sales S1
ORDER BY year;
/* 直近の年度と同じ年商の年度を選択する(p.109) */
SELECT year, sale
FROM Sales2 S1
WHERE sale =
(SELECT sale
FROM Sales2 S2
WHERE S2.year =
(SELECT MAX(year) /* 条件2：条件1を満たす年度の中で最大 */
FROM Sales2 S3
WHERE S1.year > S3.year)) /* 条件1：自分より過去である */
ORDER BY year;
/* 直近の年度と同じ年商の年度を選択する：自己結合と併用(p.109) */
SELECT S2.year AS pre_year,
S1.year AS now_year
FROM Sales2 S1, Sales2 S2
WHERE S1.sale = S2.sale
AND S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year;
/* 累積差を求める その1：開始時点は結果に含まれない(p.109) */
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year;
/* 累積差を求める その2：自己外部結合の利用。開始時点も結果に含まれる(p.110) */
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1 LEFT OUTER JOIN Sales2 S2
ON S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year;
/* 累計を求める：OLAP 関数の利用(p.111) */
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
FROM Accounts;
/* 累計を求める：ノイマン型再帰集合の利用(p.111) */
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;
/* 移動累計を求める その１：OLAP 関数の利用(p.113) */
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date
ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;
/* 移動累計を求める その2：3行に満たない期間も出力する(p.113) */
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) <= 3 ) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;
/* 移動累計を求める その3：3行に満たない期間は無効扱い(p.114) */
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) <= 3
HAVING COUNT(*) =3) AS mvg_sum /* 3行未満は非表示 */
FROM Accounts A1
ORDER BY prc_date;
/* オーバーラップする期間を求める(p.117) */
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver /* 自分以外の客と比較する */
AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date /* 条件(1)：開始日が他の期間内にある */
OR R1.end_date BETWEEN R2.start_date AND R2.end_date)); /* 条件(2)：終了日が他の期間内にある */
/* 応用版：相手を完全に含む期間も出力する(p.118) */
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver
AND ( ( R1.start_date BETWEEN R2.start_date AND R2.end_date
OR R1.end_date BETWEEN R2.start_date AND R2.end_date)
OR ( R2.start_date BETWEEN R1.start_date AND R1.end_date
AND R2.end_date BETWEEN R1.start_date AND R1.end_date)));
相関サブクエリは、使いこなせば、CASE式、HAVING句、外部結合などに劣らぬ大変強力な武器となります。しかし、「おわりに」でも述べたように、可読性とパフォーマンスの面で欠点を抱えてもいます。その点には、よく注意を払っておきましょう。
1-7．SQLで集合演算
初出は CodeZine 2007年5月25日。わりと玄人ごのみの、個人的にはよく書けた一本だと思います。SQL の集合演算の特徴をうまく掘り下げられたと思う。原理との架橋という点でも、「冪等性」の概念についても話をスムーズにつなげられて、奥行きも出た。やはり自分の勝負できる路線はこれだろうな、ということを再確認した回でした。
サンプルデータ
/* テーブル同士のコンペア 集合の相等性チェック */
CREATE TABLE Tbl_A
(keycol CHAR(1) PRIMARY KEY,
col_1 INTEGER ,
col_2 INTEGER,
col_3 INTEGER);
CREATE TABLE Tbl_B
(keycol CHAR(1) PRIMARY KEY,
col_1 INTEGER,
col_2 INTEGER,
col_3 INTEGER);
/* 等しいテーブル同士のケース */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', 2, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', 5, 1, 6);
DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', 2, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', 5, 1, 6);
/* 「B」の行が相違するケース */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', 2, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', 5, 1, 6);
DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', 2, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 8);
INSERT INTO Tbl_B VALUES('C', 5, 1, 6);
/* NULLを含むケース（等しい） */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', NULL, NULL, NULL);
DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', NULL, NULL, NULL);
/* NULLを含むケース（「C」の行が異なる） */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', NULL, NULL, NULL);
DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', 0, NULL, NULL);
/* 3. 差集合で関係除算を表現する */
CREATE TABLE Skills
(skill VARCHAR(32),
PRIMARY KEY(skill));
CREATE TABLE EmpSkills
(emp VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill));
INSERT INTO Skills VALUES('Oracle');
INSERT INTO Skills VALUES('UNIX');
INSERT INTO Skills VALUES('Java');
INSERT INTO EmpSkills VALUES('相田', 'Oracle');
INSERT INTO EmpSkills VALUES('相田', 'UNIX');
INSERT INTO EmpSkills VALUES('相田', 'Java');
INSERT INTO EmpSkills VALUES('相田', 'C#');
INSERT INTO EmpSkills VALUES('神崎', 'Oracle');
INSERT INTO EmpSkills VALUES('神崎', 'UNIX');
INSERT INTO EmpSkills VALUES('神崎', 'Java');
INSERT INTO EmpSkills VALUES('平井', 'UNIX');
INSERT INTO EmpSkills VALUES('平井', 'Oracle');
INSERT INTO EmpSkills VALUES('平井', 'PHP');
INSERT INTO EmpSkills VALUES('平井', 'Perl');
INSERT INTO EmpSkills VALUES('平井', 'C++');
INSERT INTO EmpSkills VALUES('若田部', 'Perl');
INSERT INTO EmpSkills VALUES('渡来', 'Oracle');
/* 4. 等しい部分集合を見つける */
CREATE TABLE SupParts
(sup CHAR(32) NOT NULL,
part CHAR(32) NOT NULL,
PRIMARY KEY(sup, part));
INSERT INTO SupParts VALUES('A', 'ボルト');
INSERT INTO SupParts VALUES('A', 'ナット');
INSERT INTO SupParts VALUES('A', 'パイプ');
INSERT INTO SupParts VALUES('B', 'ボルト');
INSERT INTO SupParts VALUES('B', 'パイプ');
INSERT INTO SupParts VALUES('C', 'ボルト');
INSERT INTO SupParts VALUES('C', 'ナット');
INSERT INTO SupParts VALUES('C', 'パイプ');
INSERT INTO SupParts VALUES('D', 'ボルト');
INSERT INTO SupParts VALUES('D', 'パイプ');
INSERT INTO SupParts VALUES('E', 'ヒューズ');
INSERT INTO SupParts VALUES('E', 'ナット');
INSERT INTO SupParts VALUES('E', 'パイプ');
INSERT INTO SupParts VALUES('F', 'ヒューズ');
/* 5. 重複行を削除する高速なクエリ
PostgreSQLでは「with oids」をCREATE TABLE文の最後に追加すること */
CREATE TABLE Products
(name CHAR(16),
price INTEGER);
INSERT INTO Products VALUES('りんご', 50);
INSERT INTO Products VALUES('みかん', 100);
INSERT INTO Products VALUES('みかん', 100);
INSERT INTO Products VALUES('みかん', 100);
INSERT INTO Products VALUES('バナナ', 80);
本文中のコード
/* テーブル同士のコンペア：基本編(p.125) */
SELECT COUNT(*) AS row_cnt
FROM ( SELECT *
FROM tbl_A
UNION
SELECT *
FROM tbl_B ) TMP;
/* テーブル同士のコンペア：応用編（Oracleでは通らない）(p.128) */
SELECT DISTINCT CASE WHEN COUNT(*) = 0
THEN '等しい'
ELSE '異なる' END AS result
FROM ((SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B)
EXCEPT
(SELECT * FROM tbl_A
INTERSECT
SELECT * FROM tbl_B)) TMP;
/* テーブルに対するdiff：排他的和集合を求める(p.128) */
(SELECT * FROM tbl_A
EXCEPT
SELECT * FROM tbl_B)
UNION ALL
(SELECT * FROM tbl_B
EXCEPT
SELECT * FROM tbl_A);
/* 差集合で関係除算（剰余を持った除算）(p.130) */
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp);
/* 等しい部分集合を見つける(p.134) */
SELECT SP1.sup, SP2.sup
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup /* 業者の組み合わせを作る */
AND SP1.part = SP2.part /* 条件１．同じ種類の部品を扱う */
GROUP BY SP1.sup, SP2.sup
HAVING COUNT(*) = (SELECT COUNT(*) /* 条件２．同数の部品を扱う */
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
AND COUNT(*) = (SELECT COUNT(*)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);
/* 重複行を削除する高速なクエリ１：補集合をEXCEPTで求める(p.136) */
DELETE FROM Products
WHERE rowid IN ( SELECT rowid
FROM Products
EXCEPT
SELECT MAX(rowid)
FROM Products
GROUP BY name, price);
/* 重複行を削除する高速なクエリ２：補集合をNOT IN で求める(p.137) */
DELETE FROM Products
WHERE rowid NOT IN ( SELECT MAX(rowid)
FROM Products
GROUP BY name, price);
なお、初出の際には、脱線話が過ぎると思って名前の紹介だけにとどめた包含演算子 CONTAINS 述語（集合論の⊃に相当する）ですが、本書では構文も紹介してちょっと詳しく触れました。これがあったら何かと便利な局面は多いと思うんだけどなー。そのうち本当に実装されないかな。
1-8．EXISTS述語の使い方
この章は、本書のための書き下ろし。EXISTS述語もいつか取り上げなければな・・・とずっと思っていたのですが、述語論理の基礎や二階の述語の特性まで含めて Web 上の一つの記事で解説するにはかなり工夫が必要で、ヘビーな内容のため後回しにしてしまっていたのですが、書籍の出版というちょうどよい機会を得てうまく収められました。
サンプルデータ
/* テーブルに存在「しない」データを探す */
CREATE TABLE Meetings
(meeting CHAR(32) NOT NULL,
person CHAR(32) NOT NULL,
PRIMARY KEY (meeting, person));
INSERT INTO Meetings VALUES('第１回', '伊藤');
INSERT INTO Meetings VALUES('第１回', '水島');
INSERT INTO Meetings VALUES('第１回', '坂東');
INSERT INTO Meetings VALUES('第２回', '伊藤');
INSERT INTO Meetings VALUES('第２回', '宮田');
INSERT INTO Meetings VALUES('第３回', '坂東');
INSERT INTO Meetings VALUES('第３回', '水島');
INSERT INTO Meetings VALUES('第３回', '宮田');
/* 全称量化 その１：肯定⇔二重否定の変換に慣れよう */
CREATE TABLE TestScores
(student_id INTEGER,
subject VARCHAR(32) ,
score INTEGER,
PRIMARY KEY(student_id, subject));
INSERT INTO TestScores VALUES(100, '算数',100);
INSERT INTO TestScores VALUES(100, '国語',80);
INSERT INTO TestScores VALUES(100, '理科',80);
INSERT INTO TestScores VALUES(200, '算数',80);
INSERT INTO TestScores VALUES(200, '国語',95);
INSERT INTO TestScores VALUES(300, '算数',40);
INSERT INTO TestScores VALUES(300, '国語',90);
INSERT INTO TestScores VALUES(300, '社会',55);
INSERT INTO TestScores VALUES(400, '算数',80);
/* 全称量化 その２：集合VS 述語――凄いのはどっちだ？ */
CREATE TABLE Projects
(project_id VARCHAR(32),
step_nbr INTEGER ,
status VARCHAR(32),
PRIMARY KEY(project_id, step_nbr));
INSERT INTO Projects VALUES('AA100', 0, '完了');
INSERT INTO Projects VALUES('AA100', 1, '待機');
INSERT INTO Projects VALUES('AA100', 2, '待機');
INSERT INTO Projects VALUES('B200', 0, '待機');
INSERT INTO Projects VALUES('B200', 1, '待機');
INSERT INTO Projects VALUES('CS300', 0, '完了');
INSERT INTO Projects VALUES('CS300', 1, '完了');
INSERT INTO Projects VALUES('CS300', 2, '待機');
INSERT INTO Projects VALUES('CS300', 3, '待機');
INSERT INTO Projects VALUES('DY400', 0, '完了');
INSERT INTO Projects VALUES('DY400', 1, '完了');
INSERT INTO Projects VALUES('DY400', 2, '完了');
/* 列に対する量化：オール１の行を探せ */
CREATE TABLE ArrayTbl
(keycol CHAR(1) PRIMARY KEY,
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 INTEGER,
col5 INTEGER,
col6 INTEGER,
col7 INTEGER,
col8 INTEGER,
col9 INTEGER,
col10 INTEGER);
--オールNULL
INSERT INTO ArrayTbl VALUES('A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('B', 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
--オール1
INSERT INTO ArrayTbl VALUES('C', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
--少なくとも一つは9
INSERT INTO ArrayTbl VALUES('D', NULL, NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('E', NULL, 3, NULL, 1, 9, NULL, NULL, 9, NULL, NULL);
本文中のコード
/* 欠席者だけを求めるクエリ その１：存在量化の応用(p.146) */
SELECT DISTINCT M1.meeting, M2.person
FROM Meetings M1 CROSS JOIN Meetings M2
WHERE NOT EXISTS
(SELECT *
FROM Meetings M3
WHERE M1.meeting = M3.meeting
AND M2.person = M3.person);
/* 欠席者だけを求めるクエリ その２：差集合演算の利用(p.146) */
SELECT M1.meeting, M2.person
FROM Meetings M1, Meetings M2
EXCEPT
SELECT meeting, person
FROM Meetings;
/* 全称量化その１：肯定⇔二重否定の変換に慣れよう(p.147) */
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS /* 以下の条件を満たす行が存在しない */
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.score < 50); /* 50 点未満の教科 */
/* 全称量化その１：肯定⇔二重否定の変換に慣れよう(p.149) */
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE subject IN ('算数', '国語')
AND NOT EXISTS
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND 1 = CASE WHEN subject = '算数' AND score < 80 THEN 1
WHEN subject = '国語' AND score < 50 THEN 1
ELSE 0 END);
/* 全称量化その１：肯定⇔二重否定の変換に慣れよう(p.149) */
SELECT student_id
FROM TestScores TS1
WHERE subject IN ('算数', '国語')
AND NOT EXISTS
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND 1 = CASE WHEN subject = '算数' AND score < 80 THEN 1
WHEN subject = '国語' AND score < 50 THEN 1
ELSE 0 END)
GROUP BY student_id
HAVING COUNT(*) = 2; /* 必ず2 教科揃っていること */
/* 工程1 番まで完了のプロジェクトを選択：集合指向的な解答(p.150) */
SELECT project_id
FROM Projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND status = '完了' THEN 1
WHEN step_nbr > 1 AND status = '待機' THEN 1
ELSE 0 END);
/* 工程1 番まで完了のプロジェクトを選択：述語論理的な解答(p.151) */
SELECT *
FROM Projects P1
WHERE NOT EXISTS
(SELECT status
FROM Projects P2
WHERE P1.project_id = P2. project_id /* プロジェクトごとに条件を調べる */
AND status <> CASE WHEN step_nbr <= 1 /* 全称文を二重否定で表現する */
THEN '完了'
ELSE '待機' END);
/* 列方向への全称量化：芸のない答え(p.153) */
SELECT *
FROM ArrayTbl
WHERE col1 = 1
AND col2 = 1
AND col3 = 1
AND col4 = 1
AND col5 = 1
AND col6 = 1
AND col7 = 1
AND col8 = 1
AND col9 = 1
AND col10 = 1;
/* 列方向への全称量化：芸のある答え(p.153) */
SELECT *
FROM ArrayTbl
WHERE 1 = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
/* 列方向への存在量化：その１(p.154) */
SELECT *
FROM ArrayTbl
WHERE 9 = ANY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
/* 列方向への存在量化：その２(p.154) */
SELECT *
FROM ArrayTbl
WHERE 9 IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
/* オールNULL の行を探す：間違った答え(p.154) */
SELECT *
FROM ArrayTbl
WHERE NULL = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
/* オールNULL の行を探す：正しい答え(p.154) */
SELECT *
FROM ArrayTbl
WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;
なお、この章については一つ注意事項の追記があります。「列に対する量化：オール１の行を探せ」（p.152）で、ALL 述語と ANY 述語の後に (col1, col2, ...) のようにカンマ区切りのリストを記述する構文は、Oracle では認められていますが、他の DB ではエラーとなります。本文中に記載するべきでしたが、漏れてしまいました。申し訳ありません。
ただ、ALL と ANY の後にカンマ区切りのリストを引数に与えることは、標準 SQL で認められている適法な構文です（『標準SQLガイド 改訂第4版』「12.6 限定条件」参照）。ALL と ANY はテーブル式を引数に取るよう標準で定められています。そして、カンマ区切りのリストはテーブル式の一種だからです（従って、Oracle が勝手に独自拡張しているわけではない）。
Oracle 以外で同じことやる場合は・・・うーん、どうしましょうね。何かいいアイデアありませんか。
1-9．SQLで数列を扱う
初出は CodeZine 2007年3月20日。EXISTS 述語の応用編の練習問題なので、本当は EXISTS の解説をした後に持ってくるのが筋なのですが、上でも書いたように EXISTS の解説を後延ばしにしてしまったのと、これ自体内容として面白いものだったので、我慢できずにこっちを先に出してしまいました。今回、書籍化にあたり然るべき順番に配置できて助かりました。本書の中ではかなりパズル的な傾向の強い、遊び心あふれる章です。
サンプルデータ
--連番を作ろう
CREATE TABLE Digits
(digit INTEGER PRIMARY KEY);
INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);
INSERT INTO Digits VALUES (2);
INSERT INTO Digits VALUES (3);
INSERT INTO Digits VALUES (4);
INSERT INTO Digits VALUES (5);
INSERT INTO Digits VALUES (6);
INSERT INTO Digits VALUES (7);
INSERT INTO Digits VALUES (8);
INSERT INTO Digits VALUES (9);
--欠番を全部求める
CREATE TABLE SeqTbl
(seq INTEGER PRIMARY KEY);
INSERT INTO SeqTbl VALUES (1);
INSERT INTO SeqTbl VALUES (2);
INSERT INTO SeqTbl VALUES (4);
INSERT INTO SeqTbl VALUES (5);
INSERT INTO SeqTbl VALUES (6);
INSERT INTO SeqTbl VALUES (7);
INSERT INTO SeqTbl VALUES (8);
INSERT INTO SeqTbl VALUES (11);
INSERT INTO SeqTbl VALUES (12);
--3人なんですけど、座れますか？
CREATE TABLE Seats
(seat INTEGER NOT NULL PRIMARY KEY,
status CHAR(2) NOT NULL
CHECK (status IN ('空', '占')) );
INSERT INTO Seats VALUES (1, '占');
INSERT INTO Seats VALUES (2, '占');
INSERT INTO Seats VALUES (3, '空');
INSERT INTO Seats VALUES (4, '空');
INSERT INTO Seats VALUES (5, '空');
INSERT INTO Seats VALUES (6, '占');
INSERT INTO Seats VALUES (7, '空');
INSERT INTO Seats VALUES (8, '空');
INSERT INTO Seats VALUES (9, '空');
INSERT INTO Seats VALUES (10, '空');
INSERT INTO Seats VALUES (11, '空');
INSERT INTO Seats VALUES (12, '占');
INSERT INTO Seats VALUES (13, '占');
INSERT INTO Seats VALUES (14, '空');
INSERT INTO Seats VALUES (15, '空');
--折り返しも考慮
CREATE TABLE Seats2
( seat INTEGER NOT NULL PRIMARY KEY,
row_id CHAR(1) NOT NULL,
status CHAR(2) NOT NULL
CHECK (status IN ('空', '占')) );
INSERT INTO Seats2 VALUES (1, 'A', '占');
INSERT INTO Seats2 VALUES (2, 'A', '占');
INSERT INTO Seats2 VALUES (3, 'A', '空');
INSERT INTO Seats2 VALUES (4, 'A', '空');
INSERT INTO Seats2 VALUES (5, 'A', '空');
INSERT INTO Seats2 VALUES (6, 'B', '占');
INSERT INTO Seats2 VALUES (7, 'B', '占');
INSERT INTO Seats2 VALUES (8, 'B', '空');
INSERT INTO Seats2 VALUES (9, 'B', '空');
INSERT INTO Seats2 VALUES (10,'B', '空');
INSERT INTO Seats2 VALUES (11,'C', '空');
INSERT INTO Seats2 VALUES (12,'C', '空');
INSERT INTO Seats2 VALUES (13,'C', '空');
INSERT INTO Seats2 VALUES (14,'C', '占');
INSERT INTO Seats2 VALUES (15,'C', '空');
--最大何人まで座れますか？
CREATE TABLE Seats3
( seat INTEGER NOT NULL PRIMARY KEY,
status CHAR(2) NOT NULL
CHECK (status IN ('空', '占')) );
INSERT INTO Seats3 VALUES (1, '占');
INSERT INTO Seats3 VALUES (2, '空');
INSERT INTO Seats3 VALUES (3, '空');
INSERT INTO Seats3 VALUES (4, '空');
INSERT INTO Seats3 VALUES (5, '空');
INSERT INTO Seats3 VALUES (6, '占');
INSERT INTO Seats3 VALUES (7, '空');
INSERT INTO Seats3 VALUES (8, '占');
INSERT INTO Seats3 VALUES (9, '空');
INSERT INTO Seats3 VALUES (10, '空');
--単調増加と単調減少
CREATE TABLE MyStock
(deal_date DATE PRIMARY KEY,
price INTEGER );
INSERT INTO MyStock VALUES ('2007-01-06', 1000);
INSERT INTO MyStock VALUES ('2007-01-08', 1050);
INSERT INTO MyStock VALUES ('2007-01-09', 1050);
INSERT INTO MyStock VALUES ('2007-01-12', 900);
INSERT INTO MyStock VALUES ('2007-01-13', 880);
INSERT INTO MyStock VALUES ('2007-01-14', 870);
INSERT INTO MyStock VALUES ('2007-01-16', 920);
INSERT INTO MyStock VALUES ('2007-01-17', 1000);
本文中のコード
/* 連番を求める その1：0〜99 (p.161)*/
SELECT D1.digit + (D2.digit * 10) AS seq
FROM Digits D1, Digits D2
ORDER BY seq;
/* 連番を求める その2：1〜542を求める (p.161) */
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seq
FROM Digits D1, Digits D2, Digits D3
WHERE D1.digit + (D2.digit * 10) + (D3.digit * 100) BETWEEN 1 AND 542
ORDER BY seq;
/* シーケンス・ビューを作る（0〜999までをカバー）(p.162) */
CREATE VIEW Sequence (seq)
AS SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
FROM Digits D1, Digits D2, Digits D3;
/* シーケンス・ビューから1〜100まで取得 (p.162) */
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 100
ORDER BY seq;
/* 欠番を全部求める：EXCEPTバージョン (p.163) */
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 12
EXCEPT
SELECT seq FROM SeqTbl;
/* 欠番を全部求める：NOT INバージョン (p.163) */
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 12
AND seq NOT IN (SELECT seq FROM SeqTbl);
/* 連番の範囲を動的に決定するクエリ (p.163) */
SELECT seq
FROM Sequence
WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT
SELECT seq FROM SeqTbl;
/* 人数分の空席を探す その1：行の折り返しを考慮しない (p.165) */
SELECT S1.seat AS start_seat, '〜' , S2.seat AS end_seat
FROM Seats S1, Seats S2
WHERE S2.seat = S1.seat + (:head_cnt -1) /* 始点と終点を決める */
AND NOT EXISTS
(SELECT *
FROM Seats S3
WHERE S3.seat BETWEEN S1.seat AND S2.seat
AND S3.status <> '空' )
ORDER BY start_seat;
/* 人数分の空席を探す その2：行の折り返しも考慮する (p.167) */
SELECT S1.seat AS start_seat, '〜' , S2.seat AS end_seat
FROM Seats2 S1, Seats2 S2
WHERE S2.seat = S1.seat + (:head_cnt -1) --始点と終点を決める
AND NOT EXISTS
(SELECT *
FROM Seats2 S3
WHERE S3.seat BETWEEN S1.seat AND S2.seat
AND ( S3.status <> '空'
OR S3.row_id <> S1.row_id))
ORDER BY start_seat;
/* 第1段階：すべてのシーケンスを保持するビューを作る (p.169) */
CREATE VIEW Sequences (start_seat, end_seat, seat_cnt) AS
SELECT S1.seat AS start_seat,
S2.seat AS end_seat,
S2.seat - S1.seat + 1 AS seat_cnt
FROM Seats3 S1, Seats3 S2
WHERE S1.seat <= S2.seat /* ステップ1：始点と終点の組み合わせを作る */
AND NOT EXISTS /* ステップ2：シーケンス内のすべての行が満たすべき条件を記述する */
(SELECT *
FROM Seats3 S3
WHERE ( S3.seat BETWEEN S1.seat AND S2.seat
AND S3.status <> '空') /* 条件1の否定 */
OR (S3.seat = S2.seat + 1 AND S3.status = '空' ) /* 条件2の否定 */
OR (S3.seat = S1.seat - 1 AND S3.status = '空' )); /* 条件3の否定 */
/* 第2段階：最大のシーケンスを求める(p.170) */
SELECT start_seat, '〜', end_seat, seat_cnt
FROM Sequences
WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);
/* 単調増加する期間を求めるクエリ：部分集合も出力する(p.172) */
SELECT S1.deal_date AS start_date,
S2.deal_date AS end_date
FROM MyStock S1, MyStock S2
WHERE S1.deal_date < S2.deal_date /* ステップ1：始点と終点の組み合わせを作る */
AND NOT EXISTS /* ステップ2：期間内のすべての行が満たすべき条件を記述する */
( SELECT *
FROM MyStock S3, MyStock S4
WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S3.deal_date < S4.deal_date
AND S3.price >= S4.price)
ORDER BY start_date, end_date;
--部分集合を除外して、最大範囲の期間だけｯを取る
SELECT MIN(start_date) AS start_date, /* 始点を最大限まえに伸ばす */
end_date
FROM (SELECT S1.deal_date AS start_date,
MAX(S2.deal_date) AS end_date /* 終点を最大限うしろに伸ばす */
FROM MyStock S1, MyStock S2
WHERE S1.deal_date < S2.deal_date
AND NOT EXISTS
(SELECT *
FROM MyStock S3, MyStock S4
WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S3.deal_date < S4.deal_date
AND S3.price >= S4.price)
GROUP BY S1.deal_date) TMP
GROUP BY end_date
ORDER BY start_date;
本章での解法は、EXISTS 述語で全称量化を応用するものを中心に取り上げました。いわば SQL の述語論理的な側面に力点をおいた解説になっています。一方、演習問題では、HAVING を使った集合指向的なアプローチを紹介しています。この二つの方法論は、是非じっくり見比べていただきたい。SQL の言語としての豊穣さが見えてくるはずです。
そうだ、あともう一つ。SQL で連番を作る方法は、この章で紹介したクロス結合以外にまだまだ沢山あります。どうやればいいか、考えてみてださい。これを宿題にしておきましょう。
2008/07/29補記：クロス結合による連番作成のヴァリエーションとして、読者の方から面白い方法を教えていただきました。
/* 連番を作ろう（2進数から10進数へ変換）*/
CREATE TABLE Digits
(digit INTEGER PRIMARY KEY);
INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);
/* 0〜31まで取得 */
SELECT D1.digit + D2.digit * 2 + D3.digit * 4 + D4.digit * 8 + D5.digit * 16 AS seq
FROM Digits D1, Digits D2, Digits D3, Digits D4, Digits D5
ORDER BY seq;
これは、いわば2進数版の連番作成方法です。2進数においては、0 と 1 だけを用意すれば全ての数を表現できるという簡潔さがエレガントです。
同じ数を作るためには10進数の場合よりクロス結合の回数が増えるので、パフォーマンスに関しては劣るのではないか、と思いますが、こんな発想があるという視点の転換が面白い。komamitsu さんより教えていただきました。
参照：komamitsu.log
1-10．帰ってきたHAVING句
初出は CodeZine 2007年7月11日。タイトルとして「柳の下のHAVING句」も考えていたけど、品がなかったのでこっちを採用。「HAVING句の力」では導入部分しか語りきれなかった HAVING 句の魅力を語り尽くそうという、書いていてまことに楽しいテキストでした。やろうと思えばまだまだ続編もいけそう。読まされる方はおなか一杯かもしれないけど。
サンプルデータ
--各隊、総員点呼！
CREATE TABLE Teams
(member CHAR(12) NOT NULL PRIMARY KEY,
team_id INTEGER NOT NULL,
status CHAR(8) NOT NULL);
INSERT INTO Teams VALUES('ジョー', 1, '待機');
INSERT INTO Teams VALUES('ケン', 1, '出動中');
INSERT INTO Teams VALUES('ミック', 1, '待機');
INSERT INTO Teams VALUES('カレン', 2, '出動中');
INSERT INTO Teams VALUES('キース', 2, '休暇');
INSERT INTO Teams VALUES('ジャン', 3, '待機');
INSERT INTO Teams VALUES('ハート', 3, '待機');
INSERT INTO Teams VALUES('ディック', 3, '待機');
INSERT INTO Teams VALUES('ベス', 4, '待機');
INSERT INTO Teams VALUES('アレン', 5, '出動中');
INSERT INTO Teams VALUES('ロバート', 5, '休暇');
INSERT INTO Teams VALUES('ケーガン', 5, '待機');
--一意集合と多重集合
CREATE TABLE Materials
(center CHAR(12) NOT NULL,
receive_date DATE NOT NULL,
material CHAR(12) NOT NULL,
PRIMARY KEY(center, receive_date));
INSERT INTO Materials VALUES('東京' ,'2007-4-01', '錫');
INSERT INTO Materials VALUES('東京' ,'2007-4-12', '亜鉛');
INSERT INTO Materials VALUES('東京' ,'2007-5-17', 'アルミニウム');
INSERT INTO Materials VALUES('東京' ,'2007-5-20', '亜鉛');
INSERT INTO Materials VALUES('大阪' ,'2007-4-20', '銅');
INSERT INTO Materials VALUES('大阪' ,'2007-4-22', 'ニッケル');
INSERT INTO Materials VALUES('大阪' ,'2007-4-29', '鉛');
INSERT INTO Materials VALUES('名古屋', '2007-3-15', 'チタン');
INSERT INTO Materials VALUES('名古屋', '2007-4-01', '炭素鋼');
INSERT INTO Materials VALUES('名古屋', '2007-4-24', '炭素鋼');
INSERT INTO Materials VALUES('名古屋', '2007-5-02', 'マグネシウム');
INSERT INTO Materials VALUES('名古屋', '2007-5-10', 'チタン');
INSERT INTO Materials VALUES('福岡' ,'2007-5-10', '亜鉛');
INSERT INTO Materials VALUES('福岡' ,'2007-5-28', '錫');
--欠番を探せ：発展版
CREATE TABLE SeqTbl
( seq INTEGER NOT NULL PRIMARY KEY);
--歯抜けなし：開始値が1
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1);
INSERT INTO SeqTbl VALUES(2);
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
--歯抜けあり：開始値が1
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1);
INSERT INTO SeqTbl VALUES(2);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
INSERT INTO SeqTbl VALUES(8);
--歯抜けなし：開始値が1ではない
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
INSERT INTO SeqTbl VALUES(6);
INSERT INTO SeqTbl VALUES(7);
--歯抜けあり：開始値が1ではない
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(7);
INSERT INTO SeqTbl VALUES(8);
INSERT INTO SeqTbl VALUES(10);
--集合にきめ細かな条件を設定する
CREATE TABLE TestResults
(student CHAR(12) NOT NULL PRIMARY KEY,
class CHAR(1) NOT NULL,
sex CHAR(1) NOT NULL,
score INTEGER NOT NULL);
INSERT INTO TestResults VALUES('001', 'A', '男', 100);
INSERT INTO TestResults VALUES('002', 'A', '女', 100);
INSERT INTO TestResults VALUES('003', 'A', '女', 49);
INSERT INTO TestResults VALUES('004', 'A', '男', 30);
INSERT INTO TestResults VALUES('005', 'B', '女', 100);
INSERT INTO TestResults VALUES('006', 'B', '男', 92);
INSERT INTO TestResults VALUES('007', 'B', '男', 80);
INSERT INTO TestResults VALUES('008', 'B', '男', 80);
INSERT INTO TestResults VALUES('009', 'B', '女', 10);
INSERT INTO TestResults VALUES('010', 'C', '男', 92);
INSERT INTO TestResults VALUES('011', 'C', '男', 80);
INSERT INTO TestResults VALUES('012', 'C', '女', 21);
INSERT INTO TestResults VALUES('013', 'D', '女', 100);
INSERT INTO TestResults VALUES('014', 'D', '女', 0);
INSERT INTO TestResults VALUES('015', 'D', '女', 0);
本文中のコード
/* 全称文を述語で表現する(p.177) */
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS
(SELECT *
FROM Teams T2
WHERE T1.team_id = T2.team_id
AND status <> '待機' );
/* 全称文を集合で表現する：その1(p.178) */
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待機'
THEN 1
ELSE 0 END);
/* 全称文を集合で表現する：その2(p.180) */
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待機'
AND MIN(status) = '待機';
/* 総員スタンバイかどうかをチームごとに一覧表示(p.180) */
SELECT team_id,
CASE WHEN MAX(status) = '待機' AND MIN(status) = '待機'
THEN '総員スタンバイ'
ELSE '隊長！ メンバーが足りません' END AS status
FROM Teams
GROUP BY team_id;
/* 資材のだぶっている拠点を選択する(p.182) */
SELECT center
FROM Materials
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);
/* ダブりの有無を一覧表示(p.182) */
SELECT center,
CASE WHEN COUNT(material) <> COUNT(DISTINCT material)
THEN 'ダブり有り'
ELSE 'ダブり無し' END AS status
FROM Materials
GROUP BY center;
/* ダブりのある集合：EXISTS の利用(p.183) */
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);
/* 結果が返れば歯抜けあり：数列の連続性のみ調べる(p.185) */
SELECT '歯抜けあり' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1;
/* 欠番があってもなくても一行返す(p.185) */
SELECT CASE WHEN COUNT(*) = 0
THEN 'テーブルが空です'
WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1
THEN '歯抜けあり'
ELSE '連続' END AS gap
FROM SeqTbl;
/* 歯抜けの最小値を探す：テーブルに1がない場合は、1を返す(p.185) */
SELECT CASE WHEN MIN(seq) > 1 /* 下限が1でない場合→1を返す */
THEN 1
ELSE (SELECT MIN(seq +1) /* 下限が1の場合→最小の欠番を返す */
FROM SeqTbl S1
WHERE NOT EXISTS
(SELECT *
FROM SeqTbl S2
WHERE S2.seq = S1.seq + 1))
END AS min_gap
FROM SeqTbl;
/* クラスの75%以上が80点以上をとったクラス(p.187) */
SELECT class
FROM TestResults
GROUP BY class
HAVING COUNT(*) * 0.75
<= SUM(CASE WHEN score >= 80
THEN 1
ELSE 0 END) ;
/* 50点以上を取った生徒のうち、男子の数が女子の数より多いクラス(p.187) */
SELECT class
FROM TestResults
GROUP BY class
HAVING SUM(CASE WHEN score >= 50 AND sex = '男'
THEN 1
ELSE 0 END)
> SUM(CASE WHEN score >= 50 AND sex = '女'
THEN 1
ELSE 0 END) ;
/* 男子と女子の平均点を比較するクエリ その1：空集合に対するAVG を0で返す(p.188) */
SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男'
THEN score
ELSE 0 END)
< AVG(CASE WHEN sex = '女'
THEN score
ELSE 0 END) ;
/* 男子と女子の平均点を比較するクエリ その2：空集合に対する平均をNULLで返す(p.189) */
SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男'
THEN score
ELSE NULL END)
< AVG(CASE WHEN sex = '女'
THEN score
ELSE NULL END);
HAVING 句は行ではなく行の集合という一段高いレベルの存在に条件を設定する高次の道具です。その原理を理解することは、すなわち SQL を理解するということ。それを、どうか忘れないでおいてください。
1-11．SQLを速くするぞ
初出は私のサイト。これも古いテキストで、「3値論理とNULL」と同時期に書かれた初期のテキスト群の一つ。実装非依存で SQL を見直すだけでできるお手軽なチューニング・トリックを集めたものです。従ってこの章に関しては、私の独創と呼べるような見解は一つもありません。なのであまり話すこともなし。さらっと目を通してくれればかまいません。
1-12．SQLプログラミング作法
これも初出は私のサイト（2006年1月）。SQL のコーディング・スタイルについての情報というと、アメリカではセルコの『SQL Programming Style』という名著があるのですが、日本には皆無と言っていいでしょう。従って SQL 文の書き方もプログラマによってバラバラで、そのため意思疎通のための余計なオーバーヘッドを多く発生させているのが現状です。この状況も何とか変えなければ、と考えて書きました。まだ実験的な私案にとどまるものなので、生煮え／突飛な見解も少なからず含まれていると思いますが、今後の議論の叩き台としていただければ、と思います。
何にせよ、一人でプログラミングを楽しむ日曜プログラマならともかく、それを生業とする私たちプロにとって、プログラミングとは何よりも人と人とのコミュニケーションの一環であり、である以上、やはりコミュニケーションの作法は、避けては通れない重要な問題です。よく SE やプログラマはコミュニケーションが下手だという風評がたちますし、事実そうであるケースも多いのですが、どんな分野にせよ本当のプロはコミュニケーションをおろそかにはしないものです。
っと。柄にもなく説教こいちゃいましたね。
2-1．リレーショナル・データベースの歴史
以下、第2部に収録のテキストの初出は全部、私のサイトです。これも古くて、2002年10月にお目見え。デイトが Web マガジンの連載企画でリレーショナル・データベースの誕生史を振り返っているのを読んで、それが面白かったので要約を作ったのが始まりです。
私は、何かを理解しようとするならば、論理的な理解と歴史的・文化誌的な理解の両方が必要だと考えています。それは、一見すると後者は必要ないように見えるプログラミングにおいても例外ではありません。
自分が関与している世界を歴史的に捉えるということは、自分が時間という垂直軸のどこに位置するのかを知ること、そして世界を文化誌的に捉えるということは、社会という水平軸のどこに位置するのかを知ること。その二つの軸によって自分の立ち位置をマッピングすることは、あらゆる人間にとって不可欠なことです。ビジネスの世界で生きていこうとする人間にとってはなおさら。なぜなら、自分の立ち位置を把握できて初めて、自分の担うべき役割が分かるし、またどのように立ち回ればよいかも見えてくるからです。最近はやりの言葉で言えば「空気を読む」というのだってこのマッピング作業の一種です（皆さん空気読める方ですか？ 私はけっこうだめ）。
システムの仕事をしている人は論理的な理解には熱心だけど、文化誌的なセンスはからっきし、という人が多い。ほかに「こういうこと」書く人もあまりいないし、というわけで、私のこのだらだらコラム群にも多少の意味はあろう、と、こう思うわけですわ。
ちなみに、コッドの1970年の論文は、英語ならば Web 上でも読めます。次を参照。
2-2．なぜ"関係"モデルという名前なの？
初出は2003年12月。
「関係」という言葉はなかなか厄介なものです。語義も多いし、日常的にもよく使われる言葉なので、みんななんとなく「人間関係」とか「影響関係」のような意味と類推を働かせてしまいがちですが、そういう日常的な意味合いとリレーショナル・データベースにおける「関係」は、（まったくとは言わないまでも）ほとんど"関係"ありません。なのでそういう含意はすっぱり忘れていただきたい。
数学の集合論を学ぶとすぐに出てくる概念なので、ちょっとでも齧ったことある人なら迷うことはないのですが、いかんせん、日本の高等教育では集合論はほとんど扱われないので、DB エンジニアでも数学的背景から理解できる人が少ない、というのがさびしい現状です。その溝をちょっとでも埋めようと書いた一文。でも私の悪い癖で、色んな話題を詰め込もうとするし、すぐに高度なところへ飛躍しようとするので、あまりおさまりがよくない。初期に書いたテキストは特にその傾向が強い。
2-3．関係に始まり関係に終わる
草稿は古いけど、今回の書籍化にあたってかなり手を加えたので実質的には書き下ろしに近いものです。そのためなかなかまとまりもいい。
演算や操作という観点から集合の特性を調べる群論など抽象代数の発想は、（名前の通り）その抽象性の高さから門外漢には敷居が高く感じるのですが、でも抽象度を高めることがいかに絶大な威力を発揮するか、ということの一端を伝えられたなら幸いです。リレーショナル・データベースや UNIX のような優れたシステムは、何かしらの方法で抽象性（あるいはアメリカ人にはそれは「自由」として理解されているのではないか）を確保する工夫がなされています。きっともっと他の領域とも話を繋げられると思う。私の力不足で実現しなかったけど。
そんなわけで、短いながらお気に入りの一本。
2-4．アドレス、この巨大な怪物
初出は2002年10月。今でもよく覚えていますが、コッドとバッカスの ACM 賞受賞講演を読んで、ぜひオマージュを捧げたくなって書いたテキストです。二人の講演はどちらも素晴らしく感動的なので、是非みなさんにも読んでほしい（書籍の形で手に入りずらいのが残念）。語り口は平明でアイデアは斬新。昔のコンピュータの世界って色々な可能性が手付かずで残っていて、面白い時代だったのだろうな、と思わされます。
私たちは、最初にコンピュータやプログラミング（特にC言語）について教わるときに、ごく当然のようにアドレスによるデータ管理方式についても教えられます ―― あたかもそれ以外の方法は存在しないかのように、ごく「自然」に。でも本当にそれはそんなに「自然」で「ベスト」な方法論なんだろうか？ 本当はアドレスなんて無い方がみんな幸せになれるんじゃなかろうか？ 少なくともプログラマやユーザにアドレスを意識させることのメリットは本当にあるのか？
こういう物事の淵源にまで遡って問い、かつそれに答えてしまう豪腕の持ち主たちを、私たちは知的な人と呼びます。知性というのは、半分は度胸です。
二人の講演に感動しすぎて無意味に肩に力が入った文章になってしまったのが、欠点といえば欠点。でもしょうがない。まだ若かったんですもの（でもこの文章書いてても力んでしまった。ハハハ。修行が足りない）。
なおバッカスの講演は、原文をスタンフォード大学のサイトで読むことができます。
2-5．GROUP BYとPARTITION BY
初出は2007年6月。きっかけは、セルコの『SQL Programming Style』を読んでいたら「GROUP BY はテーブルを類にカットする機能を持つ」という一文がさらっと書かれているのを見て、「言われてみればその通りだ」と思ったこと。そして「それ言ったら、PARTITION BY だって似たようなものだよな」と思ってそのまんまの内容を書きました。OLAP 関数を習い始めての人が読むと、PARTITION BY のイメージがつかみやすくていいかも。
サンプルデータ
CREATE TABLE Teams
(member VARCHAR(32) PRIMARY KEY,
team CHAR(1) NOT NULL,
age INTEGER NOT NULL);
INSERT INTO Teams VALUES('大木', 'A', 28);
INSERT INTO Teams VALUES('逸見', 'A', 19);
INSERT INTO Teams VALUES('新藤', 'A', 23);
INSERT INTO Teams VALUES('山田', 'B', 40);
INSERT INTO Teams VALUES('久本', 'B', 29);
INSERT INTO Teams VALUES('橋田', 'C', 30);
INSERT INTO Teams VALUES('野々宮', 'D', 28);
INSERT INTO Teams VALUES('鬼塚', 'D', 28);
INSERT INTO Teams VALUES('加藤', 'D', 24);
INSERT INTO Teams VALUES('新城', 'D', 22);
CREATE TABLE Natural
(num INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Natural VALUES(0);
INSERT INTO Natural VALUES(1);
INSERT INTO Natural VALUES(2);
INSERT INTO Natural VALUES(3);
INSERT INTO Natural VALUES(4);
INSERT INTO Natural VALUES(5);
INSERT INTO Natural VALUES(6);
INSERT INTO Natural VALUES(7);
INSERT INTO Natural VALUES(8);
INSERT INTO Natural VALUES(9);
INSERT INTO Natural VALUES(10);
本文中のコード
/* PARTITION BY のイメージをつかむ(p.251) */
SELECT member, team, age ,
RANK() OVER(PARTITION BY team ORDER BY age DESC) rn,
DENSE_RANK() OVER(PARTITION BY team ORDER BY age DESC) dense_rn,
ROW_NUMBER() OVER(PARTITION BY team ORDER BY age DESC) row_num
FROM Teams
ORDER BY team, rn;
/* 1 から10 までを、3 を法とする剰余類に分類(p.251) */
SELECT MOD(num, 3) AS modulo,
num
FROM Natural
ORDER BY modulo, num;
2-6．手続き型から宣言型・集合指向へ頭を切り替える７箇条
初出は2007年6月。SQL を習得する上での心構えについて、普段から漠然とは思っているけど、あまり明確な言葉になっていなかったポイントをまとめました。
2-7．SQLと再帰集合
初出は2007年6月。なぜさっきからこの月に初出が集中しているかというと、仕事のサイクル上、夏がヒマだから。
ま、それはともかく、この章はいい内容です。別に私の文章が巧いとかそういうわけではなく、テーマそのものが面白いので、このテーマを選んだ時点で面白いことは確定済みなのです。フレーゲ、ノイマン、ツェルメロ、ペアノ、チャーチといった20世紀の数学とコンピュータの基礎を築いたビッグネームの方々にもちょこちょこっと登場していただきました。
2-8．神のいない論理
初出は2002年12月。SQL を歴史的・文化誌的に理解してみよう、という試みの一つ。これもまたテーマ自体が大変面白い。3値論理という、それだけ見ているとケッタイな論理体系が何の理由によって誕生し、また発展してきたのか、というその根幹を問いたかった。多くの人が真理値というと何のためらいもなく真と偽だけだと思っている、まさにその常識を衝くウカシェヴィッツの大胆さは、ノイマン型コンピュータの仕様に挑んだバッカスとコッドの蛮勇に匹敵するでしょう。
3値論理は、今でこそかなり異端の体系扱いされていますが、1950年代ぐらいはかなり多くの論理学者や数学者が研究していたメジャーなテーマだったことも、一応言い添えておきましょう。思いつくままにあげてみると、ノイマン、ポスト、ゲーデル、クリーネ、ライヘンバッハといった錚々たる面子。一時期はけっこう流行の分野だったのです。
自分が当たり前として受け入れている考え方の枠組み（スキーム）を、こうやって相対化していく効果が、歴史にはあります。自らの持つ与件を疑うのは知的負荷が高くてしんどいけど、それもまた大事なことなのです。
2-9．NULL撲滅委員会
初出は2005年8月。「3値論理とNULL」が理論編ならこっちは実践編です。何でこういう文体で書こうと思ったのは、よく覚えていません。多分、気合の入った決起文を書こうと思って失敗したのでしょう。まあ何にせよ、とりあえず NOT NULL 制約はできるだけ付けといてください。それだけでだいぶ違いますから。
2-10．SQLにおける存在の階層
これは書き下ろし。「EXISTS述語の使い方」で、述語論理の面から見たオーダーの概念を説明したので、今度は集合論の観点から見た場合にどうなるか、説明しておこうと思いました。なかなか面白い出来になった。SQL で集約を行うと、オリジナルのテーブルの列を集約キー以外では参照できなくなる、という禁則があるのですが（MySQLは例外。あまりよくない）、これがなぜそういうルールになっているのかは、SQL に厳然とした存在の階層社会が構築されていることに気付かないと分かりません。
本文中のコード
/* チーム単位に集約するクエリ(p.280) */
SELECT team, AVG(age)
FROM Teams
GROUP BY team;
/* チーム単位に集約するクエリ？ (p.280) */
SELECT team, AVG(age), age
FROM Teams
GROUP BY team;
/* エラー(p.282) */
SELECT team, AVG(age), member
FROM Teams
GROUP BY team;
/* 正しい(p.282) */
SELECT team, MAX(age),
(SELECT MAX(member)
FROM Teams T2
WHERE T2.team = T1.team
AND T2.age = MAX(T1.age)) AS oldest
FROM Teams T1
GROUP BY team;
なお、サンプルデータは2-5のものを使用
3-1-1．演習解答：CASE式のススメ
「1-1：複数列の最大値」と「1-2：合計と再掲を表頭に出力する行列変換」は、必ず解けるようにしておいてください。1-3 のトリックは、あんまり頼り過ぎないように。なるべくテーブル設計の側で吸収しましょう。
サンプルデータ
/* 演習問題1-1：複数列の最大値（1-3とも兼用） */
CREATE TABLE Greatests
(key CHAR(1) PRIMARY KEY,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
z INTEGER NOT NULL);
INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);
本文中のコード
/* x と y の最大値(p.286) */
SELECT key,
CASE WHEN x < y THEN y
ELSE x END AS greatest
FROM Greatests;
/* x と y と z の最大値(p.286) */
SELECT key,
CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
THEN z
ELSE CASE WHEN x < y THEN y ELSE x END
END AS greatest
FROM Greatests;
/* 行持ちに変換してMAX 関数(p.287) */
SELECT key, MAX(col) AS greatest
FROM (SELECT key, x AS col FROM Greatests
UNION ALL
SELECT key, y AS col FROM Greatests
UNION ALL
SELECT key, z AS col FROM Greatests) TMP
GROUP BY key;
/* OracleとMySQLのみ(p.287) */
SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
FROM Greatests;
/* 演習問題２：合計と再掲を表頭に出力する行列変換(p.287) */
SELECT sex,
SUM(population) AS total,
SUM(CASE WHEN pref_name = '徳島' THEN population ELSE 0 END) AS col_1,
SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
SUM(CASE WHEN pref_name = '愛媛' THEN population ELSE 0 END) AS col_3,
SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
SUM(CASE WHEN pref_name IN ('徳島', '香川', '愛媛', '高知')
THEN population ELSE 0 END) AS saikei
FROM PopTbl2
GROUP BY sex;
/* 演習問題３：ORDER BY でソート列を作る(p.288) */
SELECT *
FROM Greatests
ORDER BY CASE key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END;
/* ソート列も結果に含める(p.288) */
SELECT key,
CASE key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END AS sort_col
FROM Greatests
ORDER BY sort_col;
これだけみっちりやれば、皆さんももう CASE 式マスターです。くれぐれも CASE「文」なんて呼ばないでね。
3-1-2．演習解答：自己結合の使い方
本文にも書いたように、自己結合は非等値結合と組み合わせたときにその真価を発揮します。私たちは（等号に比べれば）あまり学校教育の中で不等号の重要性を習ってこなかったのですが、オトナの世界では予想以上に不等号の演算が幅を利かせています。それは、システムの世界でも同様。非等値結合のイメージをうまく描けるようになったら、SQL が一つ、分かるようになります。
サンプルデータ
/* 演習問題2-2：地域ごとのランキング */
CREATE TABLE DistrictProducts
(district VARCHAR(16) NOT NULL,
name VARCHAR(16) NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY(district, name));
INSERT INTO DistrictProducts VALUES('東北', 'みかん', 100);
INSERT INTO DistrictProducts VALUES('東北', 'りんご', 50);
INSERT INTO DistrictProducts VALUES('東北', 'ぶどう', 50);
INSERT INTO DistrictProducts VALUES('東北', 'レモン', 30);
INSERT INTO DistrictProducts VALUES('関東', 'レモン', 100);
INSERT INTO DistrictProducts VALUES('関東', 'パイン', 100);
INSERT INTO DistrictProducts VALUES('関東', 'りんご', 100);
INSERT INTO DistrictProducts VALUES('関東', 'ぶどう', 70);
INSERT INTO DistrictProducts VALUES('関西', 'レモン', 70);
INSERT INTO DistrictProducts VALUES('関西', 'スイカ', 30);
INSERT INTO DistrictProducts VALUES('関西', 'りんご', 20);
/* 演習問題2-3：ランキングの更新 */
CREATE TABLE DistrictProducts2
(district VARCHAR(16) NOT NULL,
name VARCHAR(16) NOT NULL,
price INTEGER NOT NULL,
ranking INTEGER,
PRIMARY KEY(district, name));
INSERT INTO DistrictProducts2 VALUES('東北', 'みかん', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('東北', 'りんご', 50 , NULL);
INSERT INTO DistrictProducts2 VALUES('東北', 'ぶどう', 50 , NULL);
INSERT INTO DistrictProducts2 VALUES('東北', 'レモン', 30 , NULL);
INSERT INTO DistrictProducts2 VALUES('関東', 'レモン', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('関東', 'パイン', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('関東', 'りんご', 100, NULL);
INSERT INTO DistrictProducts2 VALUES('関東', 'ぶどう', 70 , NULL);
INSERT INTO DistrictProducts2 VALUES('関西', 'レモン', 70 , NULL);
INSERT INTO DistrictProducts2 VALUES('関西', 'スイカ', 30 , NULL);
INSERT INTO DistrictProducts2 VALUES('関西', 'りんご', 20 , NULL);
本文中のコード
/* 演習問題１：重複組み合わせ(p.289) */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name >= P2.name;
/* 演習問題２：地域ごとのランキング(p.290) */
SELECT district, name,
RANK() OVER(PARTITION BY district
ORDER BY price DESC) AS rank_1
FROM DistrictProducts;
/* 演習問題２：相関サブクエリ(p.290) */
SELECT P1.district, P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM DistrictProducts P2
WHERE P1.district = P2.district /* 同じ地域内で比較する */
AND P2.price > P1.price) + 1 AS rank_1
FROM DistrictProducts P1;
/* 演習問題２：自己結合(p.290) */
SELECT P1.district, P1.name,
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM DistrictProducts P1 LEFT OUTER JOIN DistrictProducts P2
ON P1.district = P2.district
AND P1.price < P2.price
GROUP BY P1.district, P1.name;
/* 演習問題３：ランキングの更新(p.291) */
UPDATE DistrictProducts2 P1
SET ranking = (SELECT COUNT(P2.price) + 1
FROM DistrictProducts2 P2
WHERE P1.district = P2.district
AND P2.price > P1.price);
/* 演習問題３：DB2のみ(p.291) */
UPDATE DistrictProducts2
SET ranking = RANK() OVER(PARTITION BY district
ORDER BY price DESC);
/* 演習問題３：Oracle、SQL Server、PostgreSQL (p.291) */
UPDATE DistrictProducts2
SET ranking =
(SELECT P1.ranking
FROM (SELECT district , name ,
RANK() OVER(PARTITION BY district
ORDER BY price DESC) AS ranking
FROM DistrictProducts2) P1
WHERE P1.district = DistrictProducts2.district
AND P1.name = DistrictProducts2.name);
ノイマン型再帰集合の構造はなんど見ても美しい。さすが天才よのう。
3-1-4．演習解答：HAVING句の力
サンプルデータは全て本文中のものを使うので省略。
本文中のコード
/* 演習問題１：常に結果を一行返す欠番チェック(p.292) */
SELECT ' 歯抜けあり' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)
UNION ALL
SELECT ' 歯抜けなし' AS gap
FROM SeqTbl
HAVING COUNT(*) = MAX(seq);
/* 演習問題１：常に結果を一行返す欠番チェック(p.292) */
SELECT CASE WHEN COUNT(*) <> MAX(seq)
THEN '歯抜けあり'
ELSE '歯抜けなし' END AS gap
FROM SeqTbl;
/* 演習問題２：特性関数の練習(p.293)
全員が9 月中に提出済みの学部を選択する その１：BETWEEN 述語の利用 */
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-01' AND '2005-09-30'
THEN 1 ELSE 0 END);
/* 演習問題２：特性関数の練習(p.293)
全員が9 月中に提出済みの学部を選択する その２：EXTRACT 関数の利用 */
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT (YEAR FROM sbmt_date) = 2005
AND EXTRACT (MONTH FROM sbmt_date) = 09
THEN 1 ELSE 0 END);
/* 演習問題３：バスケット解析の一般化(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;
3-1-5．演習解答：外部結合の使い方
サンプルデータは全て本文中のものを使うので省略。
本文中のコード
/* 演習問題１：結合が先か、集約が先か？ (p.294)
インライン・ビューを一つ削除した修正版 */
SELECT MASTER.age_class AS age_class,
MASTER.sex_cd AS sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('東京', '千葉')
THEN population ELSE NULL END) AS pop_kanto
FROM (SELECT age_class, sex_cd
FROM TblAge CROSS JOIN TblSex) MASTER
LEFT OUTER JOIN TblPop DATA /* DATA はTblPop そのものであるのがミソ。 */
ON MASTER.age_class = DATA.age_class
AND MASTER.sex_cd = DATA.sex_cd
GROUP BY MASTER.age_class, MASTER.sex_cd;
/* 演習問題２：子供の数にご用心(p.295) */
SELECT EMP.employee, COUNT(*) AS child_cnt /* COUNT(*) は使ってはダメ！ */
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;
/* 演習問題２：子供の数にご用心(p.295) */
SELECT EMP.employee, COUNT(CHILDREN.child) AS child_cnt
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;
/* 演習問題３：完全外部結合とMERGE 文(p.296) */
MERGE INTO Class_A A
USING (SELECT *
FROM Class_B ) B
ON (A.id = B.id)
WHEN MATCHED THEN
UPDATE SET A.name = B.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (B.id, B.name);
3-1-6．演習解答：相関サブクエリで行と行を比較する
サンプルデータは全て本文中のものを使うので省略。
本文中のコード
/* 演習問題１：行間比較の簡略化(p.297) */
SELECT S1.year, S1.sale,
CASE SIGN(sale -
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) )
WHEN 0 THEN '→' /* 横ばい */
WHEN 1 THEN '↑' /* 成長 */
WHEN -1 THEN '↓' /* 後退 */
ELSE '-' END AS var
FROM Sales S1
ORDER BY year;
/* 演習問題２：OVERLAPS で期間の重複を調べる(p.297) */
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver /* 自分以外の客と比較する */
AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date));
SELECT R1.reserver, R1.start_date, R1.end_date
FROM Reservations R1, Reservations R2
WHERE R1.reserver <> R2.reserver /* 自分以外の客と比較する */
AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date);
3-1-7．演習解答：SQLで集合演算
サンプルデータは全て本文中のものを使うので省略。
本文中のコード
/* 演習問題１：UNION だけを使うコンペアの改良(p.299) */
SELECT CASE WHEN COUNT(*) = (SELECT COUNT(*) FROM tbl_A )
AND COUNT(*) = (SELECT COUNT(*) FROM tbl_B )
THEN '等しい'
ELSE '異なる' END AS result
FROM ( SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B ) TMP;
/* 演習問題２：厳密な関係除算(p.300) */
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp)
AND NOT EXISTS
(SELECT skill
FROM EmpSkills ES3
WHERE ES1.emp = ES3.emp
EXCEPT
SELECT skill
FROM Skills );
/* 演習問題２：厳密な関係除算(p.300) */
SELECT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp)
GROUP BY emp
HAVING COUNT(*) = (SELECT COUNT(*) FROM Skills);
3-1-8．演習解答：EXISTS述語の使い方
3問ともなかなか面白い問題だと思います。特に最後の素数問題は、じっくり考えてみてください。これが解けたら、あなたの EXISTS の使い方に対する理解には太鼓判を押しましょう。
サンプルデータ
/* 8-1：配列テーブル――行持ちの場合 */
CREATE TABLE ArrayTbl2
(key CHAR(1) NOT NULL,
i INTEGER NOT NULL,
val INTEGER,
PRIMARY KEY (key, i));
/* AはオールNULL、Bは一つだけ非NULL、Cはオール非NULL */
INSERT INTO ArrayTbl2 VALUES('A', 1, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('A',10, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 1, 3);
INSERT INTO ArrayTbl2 VALUES('B', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('B',10, NULL);
INSERT INTO ArrayTbl2 VALUES('C', 1, 1);
INSERT INTO ArrayTbl2 VALUES('C', 2, 1);
INSERT INTO ArrayTbl2 VALUES('C', 3, 1);
INSERT INTO ArrayTbl2 VALUES('C', 4, 1);
INSERT INTO ArrayTbl2 VALUES('C', 5, 1);
INSERT INTO ArrayTbl2 VALUES('C', 6, 1);
INSERT INTO ArrayTbl2 VALUES('C', 7, 1);
INSERT INTO ArrayTbl2 VALUES('C', 8, 1);
INSERT INTO ArrayTbl2 VALUES('C', 9, 1);
INSERT INTO ArrayTbl2 VALUES('C',10, 1);
本文中のコード
/* 演習問題１：配列テーブル――行持ちの場合(p.301)
間違った答え */
SELECT DISTINCT key
FROM ArrayTbl2 AT1
WHERE NOT EXISTS
(SELECT *
FROM ArrayTbl2 AT2
WHERE AT1.key = AT2.key
AND AT2.val <> 1);
/* 正しい答え(p.301) */
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE NOT EXISTS
(SELECT *
FROM ArrayTbl2 A2
WHERE A1.key = A2.key
AND (A2.val <> 1 OR A2.val IS NULL));
/* 別解１：ALL 述語の利用(p.303) */
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE 1 = ALL
(SELECT val
FROM ArrayTbl2 A2
WHERE A1.key = A2.key);
/* 別解２：HAVING 句の利用(p.303) */
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;
/* 別解その３：HAVING 句で極値関数を利用する(p.303) */
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING MAX(val) = 1
AND MIN(val) = 1;
/* 演習問題２：ALL 述語による全称量化(p.304)
工程1 番まで完了のプロジェクトを選択：ALL 述語による解答 */
SELECT *
FROM Projects P1
WHERE '○' = ALL
(SELECT CASE WHEN step_nbr <= 1 AND status = '完了' THEN '○'
WHEN step_nbr > 1 AND status = '待機' THEN '○'
ELSE '×' END
FROM Projects P2
WHERE P1.project_id = P2. project_id);
/* 演習問題３：素数を求める(p.305)
SELECT num AS prime
FROM Numbers Dividend
WHERE num > 1
AND NOT EXISTS
(SELECT *
FROM Numbers Divisor
WHERE Divisor.num <= Dividend.num / 2 /* 自分以外の約数は自分の半分以下にしか存在しない */
AND Divisor.num <> 1 /* 1 は約数に含まない */
AND MOD(Dividend.num, Divisor.num) = 0) /*「割り切れない」の否定条件なので「割り切れる」 */
ORDER BY prime;
3-1-9．演習解答：SQLで数列を扱う
サンプルデータは全て本文中のものを使うので省略。
本文中のコード
/* 演習問題１：欠番を全て求める――NOT EXISTS と外部結合(p.305)
NOT EXISTS バージョン */
SELECT seq
FROM Sequence N
WHERE seq BETWEEN 1 AND 12
AND NOT EXISTS
(SELECT *
FROM SeqTbl S
WHERE N.seq = S.seq );
/* NOT EXISTS バージョン */
SELECT N.seq
FROM Sequence N LEFT OUTER JOIN SeqTbl S
ON N.seq = S.seq
WHERE N.seq BETWEEN 1 AND 12
AND S.seq IS NULL;
/* 演習問題２：シーケンスを求める――集合指向的発想(p.307) */
SELECT S1.seat AS start_seat, '〜' , S2.seat AS end_seat
FROM Seats S1, Seats S2, Seats S3
WHERE S2.seat = S1.seat + (:head_cnt -1)
AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '空' THEN 1 ELSE 0 END);
/* 行に折り返しがある場合(p.307) */
SELECT S1.seat AS start_seat, ' 〜 ' , S2.seat AS end_seat
FROM Seats2 S1, Seats2 S2, Seats2 S3
WHERE S2.seat = S1.seat + (:head_cnt -1)
AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '空'
AND S3.row_id = S1.row_id THEN 1 ELSE 0 END);
/* 演習問題３：シーケンスを全て求める――集合指向的発想(p.308) */
SELECT S1.seat AS start_seat,
S2.seat AS end_seat,
S2.seat - S1.seat + 1 AS seat_cnt
FROM Seats3 S1, Seats3 S2, Seats3 S3
WHERE S1.seat <= S2.seat /* ステップ1：始点と終点の組み合わせを作る */
AND S3.seat BETWEEN S1.seat - 1 AND S2.seat + 1
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.seat BETWEEN S1.seat AND S2.seat
AND S3.status = '空' THEN 1 /* 条件1 */
WHEN S3.seat = S2.seat + 1 AND S3.status = '占' THEN 1 /* 条件2 */
WHEN S3.seat = S1.seat - 1 AND S3.status = '占' THEN 1 /* 条件3 */
ELSE 0 END);
3-1-10．演習解答：帰ってきたHAVING句
HAVING句の使い方は、ここまで練習したら十分でしょう。1-10 の問題で深く悩んでしまった方、ごめんなさい。
サンプルデータ
/* 10-1：一意集合と多重集合の一般化 */
CREATE TABLE Materials2
(center VARCHAR(32) NOT NULL,
receive_date DATE NOT NULL,
material VARCHAR(32) NOT NULL,
orgland VARCHAR(32) NOT NULL,
PRIMARY KEY(center, receive_date, material));
INSERT INTO Materials2 VALUES('東京', '2007-04-01', '錫', 'チリ');
INSERT INTO Materials2 VALUES('東京', '2007-04-12', '亜鉛', 'タイ');
INSERT INTO Materials2 VALUES('東京', '2007-05-17', 'アルミニウム', 'ブラジル');
INSERT INTO Materials2 VALUES('東京', '2007-05-20', '亜鉛', 'タイ');
INSERT INTO Materials2 VALUES('大阪', '2007-04-20', '銅', 'オーストラリア');
INSERT INTO Materials2 VALUES('大阪', '2007-04-22', 'ニッケル', '南アフリカ');
INSERT INTO Materials2 VALUES('大阪', '2007-04-29', '鉛', 'インド');
INSERT INTO Materials2 VALUES('名古屋', '2007-03-15', 'チタン', 'ボリビア');
INSERT INTO Materials2 VALUES('名古屋', '2007-04-01', '炭素鋼', 'チリ');
INSERT INTO Materials2 VALUES('名古屋', '2007-04-24', '炭素鋼', 'アルゼンチン');
INSERT INTO Materials2 VALUES('名古屋', '2007-05-02', 'マグネシウム', 'チリ');
INSERT INTO Materials2 VALUES('名古屋', '2007-05-10', 'チタン', 'タイ');
INSERT INTO Materials2 VALUES('福岡', '2007-05-10', '亜鉛', 'アメリカ');
INSERT INTO Materials2 VALUES('福岡', '2007-05-28', '錫', 'ロシア');
/* 演習問題10-2：行によって条件が異なる特性関数 */
CREATE TABLE TestScores
(student_id INTEGER NOT NULL,
subject VARCHAR(16) NOT NULL,
score INTEGER NOT NULL,
PRIMARY KEY (student_id, subject));
INSERT INTO TestScores VALUES(100, '算数', 100);
INSERT INTO TestScores VALUES(100, '国語', 80);
INSERT INTO TestScores VALUES(100, '理科', 80);
INSERT INTO TestScores VALUES(200, '算数', 80);
INSERT INTO TestScores VALUES(200, '国語', 95);
INSERT INTO TestScores VALUES(300, '算数', 40);
INSERT INTO TestScores VALUES(300, '国語', 50);
INSERT INTO TestScores VALUES(300, '社会', 55);
INSERT INTO TestScores VALUES(400, '算数', 80);
本文中のコード
/* 演習問題１：一意集合と多重集合の一般化(p.309)
（資材, 原産国）でダブリのある拠点を選択する */
SELECT center
FROM Materials2
GROUP BY center
HAVING COUNT(material || orgland) <> COUNT(DISTINCT material || orgland);
/* 演習問題２：行によって条件が異なる特性関数(p.310) */
SELECT student_id
FROM TestResults2
WHERE subject IN ('算数', '国語')
GROUP BY student_id
HAVING SUM(CASE WHEN subject = '算数' AND score >= 80 THEN 1
WHEN subject = '国語' AND score >= 50 THEN 1
ELSE 0 END) = 2;
1-10 の解答について、補足しておくと、p.309 の解説でも述べたように、COUNT(列1, 列2 ...) という構文は、確かに標準 SQL では認められていないのですが、実は MySQL は独自拡張としてこの構文を使えます。あくまで方言ですが、私はこれは、MySQL の方が標準 SQL より筋の通った、良い構文だと思います。
なぜかというと、複数列を引数に取るときは、IN だって ... (foo, bar) IN (SELECT foo, bar ...) と書きますし、SELECT 句で 普通に DISTINCT 使うときの構文とも同じです。統一感があって覚えやすいし、数値型や日付型の列が含まれている場合にも、型変換の手間とオーバーヘッドを気にしなくていいのがメリットです。文字列連結をするときは、型変換が結構手間になります。
標準SQLは、多分「COUNT関数の引数は一列に決まっておる」という思い込みでこんな仕様になってしまったのでしょうけど、少しもったいない。
このことについて改めて考えさせてくれたのは、Kiske さんでした。ありがとうございます。
本書をレビューしてくれたブログなど
以下、本書の感想を書いていただいたブログなどです。もし以下のリストに漏れていて、載せてかまわないという方はブログ、掲示板、メールなど方法は何でも構いませんのでご一報ください。なお、はてなで書かれたブログ限定ですが、ここにもまとめられています。
さて、長らく本書にお付き合いいただき、ありがとうございました。楽しんでいただけたでしょうか。それでは最後に、「あとがき」を読んでいただいた方は既にご存知でしょう、山形浩生さんの素晴らしい言葉を紹介して、お別れの言葉に代えさせていただきます。ご縁がありましたら、またどこかでお目にかかりましょう。お元気で。
作成者：ミック
作成日：2008/02/07
最終更新日：2010/07/09
この作品は、クリエイティブ・コモンズ・ライセンスの下でライセンスされています。
