分析関数の衝撃

分析関数の衝撃（後編）

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

山岸賢治[著]

2007/09/14 14:00

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

SQLを使う業務では、分析関数を使いこなすと、生産性と、SQLの可読性とパフォーマンスを、大きく向上させることができます。後編では、order by句でrangeを使った分析関数などを取り上げます。

はじめに

　「分析関数の衝撃（中編）」に引き続き、CodeZineのミック氏の記事で記載されたSQLを、分析関数を使って記述していきます。また、分析関数のorder by句でのrangeの使用例も解説します。

対象読者

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

　この記事では、「SQLで数列を扱う」と「相関サブクエリで行と行を比較する」に記載されているSQLを、分析関数を使って記述していきますので、先に読んでおくと理解しやすいと思います。

必要な環境

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

Oracle9i以降
DB2
SQL Server 2005

　でも応用できます。

3人なんですけど座れますか？ - その1：行の折り返しを考慮しない

　まずは人数分の空席を探すSQL（行の折り返しを考慮しない）についてです。「SQLで数列を扱う」では、以下のSQLが提示されています。

人数分の空席を探す　その1：行の折り返しを考慮しない

SELECT S1.seat   AS start_seat, '～' , S2.seat AS end_seat
  FROM Seats S1, Seats S2
 WHERE S2.seat = S1.seat + (:head_cnt -1)  --始点と終点を決める
   AND NOT EXISTS
          (SELECT *
             FROM Seats S3
            WHERE S3.seat BETWEEN S1.seat AND S2.seat
              AND S3.status <> '空' );

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

seatsテーブル

seat	status
1	占
2	占
3	空
4	空
5	空
6	占
7	空
8	空
9	空
10	空
11	空
12	占
13	占
14	空
15	空

出力結果

SeatStart	SeatEnd
3	5
7	9
8	10
9	11

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

seatの昇順にソート
各値を、配列に保存
配列の添字の最小値からループ
配列の添字を元に、3つの連続した空席を探す

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

分析関数で書き換えたSQL

select SeatStart,SeatEnd
from (select seat as SeatStart,
      Lead(seat,3-1) over(order by seat) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and (3-1) following) as SeatCount
      from Seats)
where SeatCount = 3;

　インラインビューの中のselect文にstatus列を追加した、SQLのイメージはこうなります。

SQLのイメージ

　Lead関数では、2行後の「seat」をSeatEndとして求めてます。count関数では、引数にnullif((status,'占'))を指定しています。集合関数のcount関数と同様に、分析関数のcount関数も、nullを数えません。このことと、「status」は占または空であることを使って、nullif関数で「status」が占ならnullを返すようにして、「status」が空の行を数えるようにしてます。

　count関数とnullif関数の組み合わせは、「HAVING句の力」の、nullを含まない集合を探す、と同じような考え方と言えるでしょう。

　count関数のwindowing_clauseは、下記のように解釈すると分かりやすいと思います。

order by seat         --seatの昇順で、
 Rows between         --行の範囲は、
current row           --小さいほうは、現在の行から
  and (3-1) following --大きいほうは、2行後まで

　後は、count関数の結果であるSeatCountが3であることを、外側のselect文のwhere句で条件としてます。

　DB2では、下記のSQLとなります。

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

select SeatStart,SeatEnd
from (select seat as SeatStart,
      max(seat)
      over(order by seat
           Rows between 2 following
                    and 2 following) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and 2 following) as SeatCount
      from Seats) dummy
where SeatCount = 3;

--これでも可
select SeatStart,SeatEnd
from (select seat as SeatStart,
      max(seat)
      over(order by seat
           Rows between 2 following
                    and 2 following) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows 2 following) as SeatCount
      from Seats) dummy
where SeatCount = 3;