はじめに
データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を
を基に学んできた。
この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の
を参考に、更なるSQLの知識を拡充していこう。
前回
8. 外部結合の使い方
8.1 はじめに
は帳票作成のための言語という誤解があるが、
はあくまでもデータ探索を目的とした言語である。
しかし近年はそうした用途に対応すべくさまざまな機能が取り入れられた。
ここでは外部結合を用いたフォーマット成型、集合演算の観点から見た外部結合を扱う。
8.2. 外部結合による行列変換
ここでは外部結合によるクロス表を考える。具体的には、以下の表からを行に、
を列に持つクロス表を作る。
name(受講者) |
course(講座) |
|---|---|
| 赤井 | SQL入門 |
| 赤井 | UNIX基礎 |
| 鈴木 | SQL入門 |
| 工藤 | SQL入門 |
| 工藤 | Java中級 |
| 吉田 | UNIX基礎 |
| 渡辺 | SQL入門 |
-- 方法(1)：外部結合（列が少ないからできる方法） 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):スカラサブクエリ(方法(1)よりは修正が楽。ただし高コスト) 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式の入れ子 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
今後は列から行へ変換する。
employee(社員) |
child_1(子ども1) |
child_2(子ども2) |
child_3(子ども3) |
|---|---|---|---|
| 赤井 | 一郎 | 二郎 | 三郎 |
| 工藤 | 春子 | 夏子 | |
| 鈴木 | 夏子 | ||
| 吉田 |
-- まずは行形式に変換 SELECT employee,child_1 AS child FROM Personnel UNION ALL SELECT employee, child_3 AS child FROM Personnel UNION ALL SELECT employee, child_2 AS child FROM Personnel -- CREATE VIEW Children(child) AS SELECT child_1 FROM Personnel UNION SELECT child_2 FROM Personnel UNION SELECT child_3 FROM Personnel 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)
8.3 クロス表で入れ子の表側を作成
以下のデータからクロス表を作成する。
- 年齢階級マスタ：
age_class(年齢階級) |
age_range(年齢) |
|---|---|
1 |
21-30歳 |
2 |
31-40歳 |
3 |
41-50歳 |
- 性別マスタ：
sex_cd(性別コード) |
sex(性別) |
|---|---|
m |
男 |
f |
女 |
- 人口構成テーブル：
pref_name(県名) |
age_class(年齢階級) |
sex_cd(性別コード) |
population(人口) |
|---|---|---|---|
秋田 |
1 |
m |
400 |
秋田 |
3 |
m |
1000 |
秋田 |
1 |
f |
800 |
秋田 |
3 |
f |
1000 |
青森 |
1 |
m |
700 |
青森 |
1 |
f |
500 |
青森 |
3 |
f |
800 |
東京 |
1 |
m |
900 |
東京 |
1 |
f |
1500 |
東京 |
3 |
f |
1200 |
千葉 |
1 |
m |
900 |
千葉 |
1 |
f |
1000 |
千葉 |
3 |
f |
900 |
ここで問題なのは、が
であるデータが存在しないことである。これにより、単純な集計では
に相当するデータ行が無視され、定型フォーマットを用いている場合、そのフォーマットに上手く収まらなくなる。これはテーブルを結合した際に
に対応する値が
になるため、その結合結果に更に別の結果を結合すると対応する値が
になってしまうことが原因である。そこで結合を一度で済ますことに気を付ける。
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, FROM TblAge CROSS JOIN TblSex ) MASTER -- ここでクロス積を取るのがミソ！ LEFT OUTER JOIN ( 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 ON MASTER.age_class = DATA.age_class AND MASTER.sex_cd = DATA.sex_cd
8.4 掛け算としての結合
における結合は乗算に相当する。そこで一方のテーブルのキー列を基準にもう一方と「一対多」の結合に持ち込むことができれば、コードを簡潔に記述でき、また中間ビューを無くすことでパフォーマンスを向上できる。「多対多」の結合では、一方を集約して「一対多」の問題に書き換えることを考える。
8.5 完全外部結合
標準では以下の3種類の外部結合構文が定義されている：
- 左外部結合(
)
- 右外部結合(
)
- 完全外部結合(
)
左(右)外部結合が集合で言う共通部分()に相当する一方で、完全外部結合は和集合(
)に相当する。そのため、完全外部結合は情報を欠落させないという特徴がある。
8.5.1 外部結合による差集合
id(識別子) |
name(名前) |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 伊集院 |
id(識別子) |
name(名前) |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 4 | 西園寺 |
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.nameがNULLとなるようにあえて外部結合する -- 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 --A.nameがNULLとなるようにあえて外部結合する
8.5.2 完全外部結合による排他的和集合
SELECT COALESCE(A.id,B.id) AS id -- COALESCE()は、与えられた引数のうちNULLでない最初の引数を返す 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
8.6 まとめ
は帳票作成のための言語ではないため、フォーマット成型には不向きである。
- 必要に迫られた際には外部結合と
式を駆使する。
- 入れ子の表側を作成する際はマスタとなる直積を作ってから結合を一回することで済ませる。
- 行数に着目した場合、結合は「掛け算」として考えることができる。そのため一対多の結合では結果の行数は増えない。
- 外部結合は集合演算と類比的に感がられる。その観点から様々な集合演算を表現できる。
