等しい部分集合を見つける
次に等しい部分集合を見つけるSQLについて説明します。「SQLで集合演算」では、以下のSQLが提示されています。
等しい部分集合を見つける
SELECT SP1.sup AS s1, SP2.sup AS s2 FROM SupParts SP1, SupParts SP2 WHERE SP1.sup < SP2.sup --業者の組み合わせを作る AND SP1.part = SP2.part --条件1．同じ種類の部品を扱う GROUP BY SP1.sup, SP2.sup HAVING COUNT(*) = (SELECT COUNT(*) --条件2．同数の部品を扱う FROM SupParts SP3 WHERE SP3.sup = SP1.sup) AND COUNT(*) = (SELECT COUNT(*) FROM SupParts SP4 WHERE SP4.sup = SP2.sup);
これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
SupPartsテーブル
| sup | part |
| A | ボルト |
| A | ナット |
| A | パイプ |
| B | ボルト |
| B | パイプ |
| C | ボルト |
| C | ナット |
| C | パイプ |
| D | ボルト |
| D | パイプ |
| E | ヒューズ |
| E | ナット |
| E | パイプ |
| F | ヒューズ |
出力結果
| s1 | s2 |
| A | C |
| B | D |
手続き型の言語であれば、次のような手順となるでしょう。
- SupPartsテーブルの各値を、supの昇順にソート
- partを配列に保存
- supの昇順にループ
- supごとに、全てのpartを持つ別のsupを探す
分析関数を使ったSQLでも似たような考え方を用います。
分析関数で書き換えたSQL
select distinct s1,s2 from (select a.sup as s1,b.sup as s2, count(distinct a.part) over(partition by a.sup,b.sup) as SupCount1, count(distinct b.part) over(partition by a.sup,b.sup) as SupCount2, count(decode(a.part,b.part,1)) over(partition by a.sup,b.sup) as ExistSum from SupParts a,SupParts b where a.sup < b.sup) where ExistSum = all(SupCount1,SupCount2);
インラインビュー内のselect文に記載されたwhere句を評価した後、SQLのイメージ（24行目以降は割愛）は次のようになります。
SQLのイメージ
まず、自己結合from SupParts a,SupParts bによって業者の組み合わせを作った後、partition by a.sup,b.supの部分で業者の組み合わせ毎にパーティションを切っています。その後、distinctオプションを指定したcount関数によって、パーティション内における業者ごとの部品数を求め、count(decode(a.part,b.part,1))で一致した部品の数を求めています。最終的には外側のwhere句で、ExistSum = all(SupCount1,SupCount2)を条件にレコードの絞込みを行っています。
なお、ここでは前述した論理式を利用しています。
（集合Aと集合Bの要素数が等しい） かつ （A ⊇ B） ⇔ A = B
上記のSQLは、3つの分析関数で指定しているpartition by句が、全てpartition by a.sup,b.supで、select句も同じくa.sup,b.supで、なおかつ、distinctを指定しているので、group byを使ったSQLに書き換え可能です。
group byを使ったSQL
select a.sup as s1,b.sup as s2 from SupParts a,SupParts b where a.sup < b.sup group by a.sup,b.sup having count(decode(a.part,b.part,1)) = all(count(distinct a.part),count(distinct b.part));
