Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(中編)

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

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

ダウンロード ソースコード (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テーブル
namefamily_idaddress
前田 義明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上田城
出力結果
nameaddress
前田 義明港区虎ノ門3-2-29
前田 由美港区虎ノ門3-2-92
徳川 家康関ヶ原
松平 忠吉関ヶ原
徳川 秀忠上田城

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

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

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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