分析関数の衝撃（中編）

はじめに

　「分析関数の衝撃（前編）」に引き続き、CodeZineのミック氏の記事で記載されたSQLを、分析関数を使って記述していきます。

対象読者

• SQLの可読性を向上させたい方
• SQLのパフォーマンスを向上させたい方

　この記事では、「自己結合の使い方」と、「相関サブクエリで行と行を比較する」に記載されているSQLを分析関数を使って記述していきますので、先に読まれた方が理解がしやすいと思います。

必要な環境

　本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。ソースコードはDB2 V9.1でも動作確認しました。その他、

• Oracle9i以降
• DB2
• SQL Server 2005

　でも応用できます。

1. 部分的に不一致なキーの検索

　まずは部分的に不一致なキーを検索するSQLについてです。「自己結合の使い方」では、以下の自己非等値結合を使うSQLが提示されています。

SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1,
       Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

　これを分析関数で書き換えてみます。まずは、少しデータを変更した「Addresses」テーブルのデータと出力結果を考えます。

　手続き型の言語であれば、

1. family_idの昇順にソート
2. family_idの最小値からループ開始
3. family_idの値を変数に保存
4. addressの値を変数に保存
5. addressの値が違ったら出力
6. family_idがブレイクしたら、3へ
7. family_idがブレイクしなかったら、5へ

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

select name,address
from (select name,address,
      count(distinct address)
      over(partition by family_id) as distinctAddressCount
        from Addresses)
where distinctAddressCount > 1;

　partition by句に「family_id」を指定して、「family_id」が等しい部分集合でのcount(distinct address)を求めてます。partition by句を使ったSQLのイメージは、こうなります。

partition by句を使ったSQLのイメージ

　後は、count(distinct address)が、1より大きいことを、外側のselect文のwhere句で条件としてます。同じ「family_id」で、「address」が2通り以上あったら、出力対象になるという考え方です。

　DB2では、分析関数のcount関数でdistinctオプションが使えないので、下記のSQLとなります。

--逆ソートを使って、
--count(distinct address) over(partition by family_id)を求める方法
select name,address
  from (select name,address,
        -1+dense_rank()
           over(partition by family_id order by address asc)
          +dense_rank()
           over(partition by family_id order by address desc)
      as distinctAddressCount
        from Addresses) dummy
where distinctAddressCount > 1;

--最小値と最大値が異なれば、住所が2件以上あると考える方法
select name,address
  from (select name,address,
        max(address) over(partition by family_id) as MaxAddress,
        min(address) over(partition by family_id) as MinAddress
          from Addresses) dummy
where MaxAddress != MinAddress;