SQL修行vol.10

つくし

2024年3月28日 15:54

『達人に学ぶSQL徹底指南書』ー9.SQLで集合演算

3回目の送別会（全部自分の）が終わり、やっと転職の実感が沸いてきたなぁと同時に、SQLは集合指向言語であるということの意味がやっとわかってきた気がする。今日はそんなSQLと集合演算についてのまとめ。

導入

🐣注意１
SQLの扱う集合は重複行を許す多重集合であるため、ALLオプションがある
集合論での集合：重複する要素は認められない
RDBのテーブル：重複行を認める多重集合

例：UNION・INTERSECT
そのまま使う→結果から重複行が排除される（重複排除のためにソートが発生する）
ALLを使う　 →結果から重複行は排除されない（ソートは発生しない）

🐣注意２
演算の順番に優先順位がある
INTERSECT→UNION、EXCEPT

🐣注意３
DBMSごとに集合演算子の実装状況にバラツキがある

🐣注意４
除算の標準的な定義がない
和：UNION
差：EXCEPT
積：CROSS JOIN
商：（なし）

活用ケース１：テーブル同士のコンペアー集合の相等性チェック

例：名前が違うだけで中身が同じ２つのテーブルを比較する

--方法1
SELECT
   COUNT(*) AS row_cnt
FROM
   ( SELECT *
     FROM tbl_A
     UNION
     SELECT *
     FROM tbl_B
   ) TMP;

上記のクエリ結果がtbl_Aとtbl_Bの行数と一致すれば両者は等しいテーブルである。
∵UNIONにALLオプションがないので重複行が排除され、等しいテーブルであればきれいに重なり合う。

この方法では、事前に２つのテーブルの行数を調べておくことが必要である。

--方法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
     INTERSECT
     SELECT * FROM tbl_B)
   ) TMP;

∵ ( A INTERSECT B ) ⊆ ( A UNION B )なので、 ( A UNION B ) EXCEPT ( A INTERSECT B ) の結果が空集合であれば、A = B。

例：２つのテーブル間で相違した行を具体的に表示する（つまり排他的和集合）

(SELECT * FROM tbl_A
 EXCEPT
 SELECT * FROM tbl_B)
UNION ALL
(SELECT * FROM tbl_B
 EXCEPT
 SELECT * FROM tbl_A)

活用ケース２：差集合で関係除算を表現する

🐣除算の実現方法

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

例：EmpSkillsテーブルからSkillsテーブルの技術全てに精通した社員を探す

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

活用ケース３：等しい部分集合を見つける

例：供給業者が取り扱っている部品の一覧から数も種類も全く同じ部品を取り扱う供給業者のペアを見つける

SELECT
   SP1.sup AS s1,
   SP2.sup AS s2
FROM
   SupParts SP1,
   SUpParts SP2
WHERE
   SP1.sup < SP2.sup
   AND
   SP1.part = S2.part
GROUP BY
   SP1.sup,
   Sp2.sup
HAVING
   COUNT(*) = (SELECT COUNT(*) 
                FROM SupParts S3
               WHERE S3.sup = SP1.sup)
   AND
   COUNT(*) = (SELECT COUNT(*) 
                FROM SupParts S4
               WHERE S4.sup = SP1.sup)

活用ケース４：重複行を削除する高速なクエリ

例：重複行のある商品マスタから重複行を削除する

--補集合を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);