分析関数の衝撃

分析関数の衝撃（中編）

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

山岸賢治[著]

更新日: 2007/09/04
公開日: 2007/07/19

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

SQLを使う業務では、分析関数を使いこなすと、生産性と、SQLの可読性とパフォーマンスを、大きく向上させることができます。分析関数を使う際の、考え方と、処理のイメージを解説します。

はじめに

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

対象読者

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

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

必要な環境

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

Oracle9i以降
DB2
SQL Server 2005

　でも応用できます。

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

　まずは部分的に不一致なキーを検索するSQLについてです。「自己結合の使い方」では、以下の自己非等値結合を使う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」テーブルのデータと出力結果を考えます。

Addressesテーブル

name	family_id	address
前田義明	100	港区虎ノ門3-2-29
前田由美	100	港区虎ノ門3-2-92
加藤茶	200	新宿区西新宿2-8-1
加藤勝	200	新宿区西新宿2-8-1
ホームズ	300	ベーカー街221B
ワトソン	400	ベーカー街221B
織田信長	500	京都
織田信忠	500	京都
織田長益	500	京都
徳川家康	600	関ヶ原
松平忠吉	600	関ヶ原
徳川秀忠	600	上田城

出力結果

name	address
前田義明	港区虎ノ門3-2-29
前田由美	港区虎ノ門3-2-92
徳川家康	関ヶ原
松平忠吉	関ヶ原
徳川秀忠	上田城

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

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

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

分析関数で書き換えた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となります。

分析関数で書き換えたSQL（DB2）

--逆ソートを使って、
--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;