CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(完結編)

CodeZineに掲載されたSQLを分析関数で記述する 4

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/07/22 14:00

ダウンロード ソースコード (4.6 KB)

目次

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

 次に等しい部分集合を見つける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テーブル
suppart
Aボルト
Aナット
Aパイプ
Bボルト
Bパイプ
Cボルト
Cナット
Cパイプ
Dボルト
Dパイプ
Eヒューズ
Eナット
Eパイプ
Fヒューズ
出力結果
s1s2
AC
BD

 手続き型の言語であれば、次のような手順となるでしょう。

  1. SupPartsテーブルの各値を、supの昇順にソート
  2. partを配列に保存
  3. supの昇順にループ
  4. 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のイメージ
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));

  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2022 Shoeisha Co., Ltd. All rights reserved. ver.1.5