「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。今月(2022年10月)からは多忙につき、日々の投稿数を減らします。

MENU

SQL中級者になる!(その08/X)

はじめに

 データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を


power-of-awareness.com


を基に学んできた。
 この知識をより昇華させてSQL中級者になりたい!ため、同じ著者の

を参考に、更なるSQLの知識を拡充していこう。

8. 外部結合の使い方

8.1 はじめに

 \mathrm{SQL}は帳票作成のための言語という誤解があるが、\mathrm{SQL}はあくまでもデータ探索を目的とした言語である。
 しかし近年はそうした用途に対応すべくさまざまな機能が取り入れられた。
 ここでは外部結合を用いたフォーマット成型、集合演算の観点から見た外部結合を扱う。

8.2. 外部結合による行列変換

 ここでは外部結合によるクロス表を考える。具体的には、以下の表から\mathrm{name}を行に、\mathrm{course}を列に持つクロス表を作る。

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


 今後は列から行へ変換する。

  • \mathrm{Personnel}
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


 ここで問題なのは、\mathrm{age_class}2であるデータが存在しないことである。これにより、単純な集計では2に相当するデータ行が無視され、定型フォーマットを用いている場合、そのフォーマットに上手く収まらなくなる。これはテーブルを結合した際に2に対応する値が\mathrm{NULL}になるため、その結合結果に更に別の結果を結合すると対応する値が\mathrm{UNKNOWN}になってしまうことが原因である。そこで結合を一度で済ますことに気を付ける。

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 掛け算としての結合

 \mathrm{SQL}における結合は乗算に相当する。そこで一方のテーブルのキー列を基準にもう一方と「一対多」の結合に持ち込むことができれば、コードを簡潔に記述でき、また中間ビューを無くすことでパフォーマンスを向上できる。「多対多」の結合では、一方を集約して「一対多」の問題に書き換えることを考える。

8.5 完全外部結合

 標準\mathrm{SQL}では以下の3種類の外部結合構文が定義されている:

  • 左外部結合(\mathrm{LEFT\ OUTER\ JOIN})
  • 右外部結合(\mathrm{RIGHT\ OUTER\ JOIN})
  • 完全外部結合(\mathrm{FULL\ OUTER\ JOIN})

 左(右)外部結合が集合で言う共通部分(\mathrm{intersection})に相当する一方で、完全外部結合は和集合(\mathrm{union})に相当する。そのため、完全外部結合は情報を欠落させないという特徴がある。

8.5.1 外部結合による差集合
  • \mathrm{Class\_ A}
id(識別子)
name(名前)
1 田中
2 鈴木
3 伊集院
  • \mathrm{Class\_ B}
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 まとめ

  • \mathrm{SQL}は帳票作成のための言語ではないため、フォーマット成型には不向きである。
  • 必要に迫られた際には外部結合と\mathrm{CASE}式を駆使する。
  • 入れ子の表側を作成する際はマスタとなる直積を作ってから結合を一回することで済ませる。
  • 行数に着目した場合、結合は「掛け算」として考えることができる。そのため一対多の結合では結果の行数は増えない。
  • 外部結合は集合演算と類比的に感がられる。その観点から様々な集合演算を表現できる。
プライバシーポリシー お問い合わせ