SQL中級者になる！（その09/12）

初めに

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

power-of-awareness.com

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

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

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

前回

power-of-awareness.com

9.　SQLで集合演算

　は集合論に立脚した言語である。しかし集合論に則った側面は長らく無視されてきた。それは集合演算子が長らく実装されてこなかったからであった。
　しかし今では集合演算子が相当に実装されたため、応用が可能になっている。

9.1　導入

　の集合演算子には数学的な演算とは異なり、いくつか注意点がある。

• では重複する要素がある。やといった集合演算子はデフォルトで重複を削除する。重複削除が不要ならばのようにオプションを付ける。
• およびに対してが優先される。そのため必要に応じて括弧を付ける。
• に応じて集合演算子の実装状況にバラツキがあるため、互換性が無い場合がある。
• 除算の標準定義が無い。

9.2　集合の相等性チェック

　2つのテーブルが等しいかを比較する。

-- 方法1：UNIONを取って行数が元の集合の行数と一致すれば等しい
SELECT COUNT(*) AS row_cnt
FROM (SELECT *
             FROM tbl_A
             UNION
             SELECT *
             FROM tbl_B
             ) TMP
-- 方法2：和集合と共通部分が一致するならば、両者は等しい
SELECT CASE WHEN COUNT(*)=0 THEN '等しい' ELSE '相違' END AS result
FROM ((SELECT * FROM tbl_A
              UNION SELECT * FROM tbl_B)
              EXCEPT
            (SELECT * FROM tbl_A
              INTERCEPT SELECT * FROM tbl_B)) TMP

方法1のクエリはを含んでも問題なく、また列数や列名、データ型を指定せずに利用できる点が良い。
　またこのクエリは、任意の二項演算子*に対して任意のテーブルが

S*S=S

を満たす場合、その演算子には冪等性があるというが、は冪等性を持つ*1。

9.3　除算を表現する

　集合の除算は標準実装されていないため、自前で定義する。以下のような方法がある：

• を入れ子にする
• 句を使った一対一対応を利用する
• 割り算を引き算で表現する

　以下のテーブルがあったときに、必要なスキル(skillテーブル)をすべて持つ従業員を探す。

skill(技術)
Oracle
UNIX
Java

emp(社員)	skill(技術)
相田	Oracle
相田	UNIX
相田	Java
相田	C#
神崎	Oracle
神崎	UNIX
神崎	Java
平井	UNIX
平井	Oracle
平井	PHP
平井	Perl
平井	C++
若田部	Perl
渡来	Oracle

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
 FROM Skills
 EXCEPT
 SELECT skill
 FROM EmpSkills ES2
 WHERE ES1.emp = ES2.emp)

9.4　等しい部分集合を見つける

sup(供給業者)	part(部品)
A	ボルト
A	ナット
A	パイプ
B	ボルト
B	パイプ
C	ボルト
C	ナット
C	パイプ
D	ボルト
D	パイプ
E	ヒューズ
E	ナット
E	パイプ
F	ヒューズ

以上から、数も種類も全く同じ部品を取り扱う供給業者のペアを探す。

SELECT SP1.sup AS s1,SP2.sup AS s2
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)

9.5　重複行の削除方法

　重複行を削除する方法に相関サブクエリがあるが、相関サブクエリはパフォーマンスが悪い。そこで、補集合の考え方を活用した以下の2つの方法だとより高速に実行できる。

-- 補集合をEXCEPTで求める
DELETE FROM Products
WHERE rowid IN (
    SELECT rowid
    FROM Products
    EXCEPT
    SELECT MAX(rowid)
    FROM Products
    GROUP BY name, price)
-- 補集合をNOT INで求める
DELETE FROM Products
WHERE rowid NOT IN (
    SELECT MAX(rowid)
    FROM Products
    GROUP BY name, price)

9.6　まとめ

• は集合演算機能の整備が遅れていた。今でもによって実装状況が相違する。
• 集合演算子はオプションを付けないと重複排除を行ない、その際にソートも行なうため、パフォーマンスが悪い。
• は冪等性を持つ。他方では持たない。
• 集合の除算は自前で作る必要がある。
• 集合の相等性を調べるには、冪等性または全単射を用いる。
• を用いると補集合を簡単に表現できる。

次回

power-of-awareness.com

*1:プログラムでは、繰り返し処理をしても一度だけ実行したときと同じ結果になることを冪等性があるという。