はじめに
「分析関数の衝撃（中編）」に引き続き、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が提示されています。
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 <> '空' );
これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
| 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でも似たような考え方を使います。
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のイメージはこうなります。
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となります。
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;
