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

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

MENU

SQL中級者になる!(その09/12)

初めに

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

power-of-awareness.com

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

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

9. SQLで集合演算

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

9.1 導入

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

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

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のクエリは\mathrm{NULL}を含んでも問題なく、また列数や列名、データ型を指定せずに利用できる点が良い。
 またこのクエリは、任意の二項演算子*に対して任意のテーブルS

S*S=S

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

9.3 除算を表現する

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

  • \mathrm{NOT\ EXISTS}入れ子にする
  • \mathrm{HAVING}句を使った一対一対応を利用する
  • 割り算を引き算で表現する

 以下のテーブルがあったときに、必要なスキル(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 まとめ

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

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

プライバシーポリシー お問い合わせ