はじめに
2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズをPostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃3 （後編）』をPostgreSQL 8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
『SQLで数列を扱う』と 『相関サブクエリで行と行を比較する』に記載されているSQLをwindow関数を使って記述していきますので、『SQLで数列を扱う』と『相関サブクエリで行と行を比較する』を読まれてからのほうが理解しやすいと思います。
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 「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 <> '空' );
これをwindow関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
| 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 |
『分析関数の衝撃3 （後編）』では、count(nullif(status,'占')) over(order by seat Rows between current row and 2 following)を使いましたが、 PostgreSQL 8.4では文法エラーになりますので、答えは下記となります。
select SeatStart,SeatEnd
from (select seat as SeatStart,
Lead(seat,2) over(order by seat) as SeatEnd,
case when status='空' then 1 else 0 end+
case when Lead(status) over(order by seat) ='空'
then 1 else 0 end+
case when Lead(status,2) over(order by seat) ='空'
then 1 else 0 end as SeatCount
from Seats) a
where SeatCount = 3
order by SeatStart;
インラインビューの中のselect文にstatus列を追加したSQLのイメージは、下記となります。
Lead関数で、2行後のseatをSeatEndとして求めてます。また、3つの検索case式でstatus='空'の行の数を足し算で求め、それが3であることを、外側のselect文のwhere句で条件としてます。
PostgreSQLは、select文の値としてのboolean型をサポートしますので、下記のselect文でもよいです。
select SeatStart,SeatEnd
from (select seat as SeatStart,
Lead(seat,2) over(order by seat) as SeatEnd,
status='空'
and Lead(status='空') over(order by seat)
and Lead(status='空',2) over(order by seat) as willOut
from Seats) a
where willOut
order by SeatStart;
Lead関数を何度も使いたくないのであれば、window関数を使わない下記のSQLでもよいです。
select a.seat as start_seat,max(b.seat) as end_seat from Seats a,Seats b where b.seat between a.seat and a.seat+(3-1) group by a.seat having count(nullif(b.status,'占')) = 3 order by a.seat;
select seat as start_seat,seat+(3-1) as end_seat
from Seats a
where exists(select 1 from Seats b
where b.seat between a.seat and a.seat+(3-1)
having count(nullif(b.status,'占')) = 3)
order by seat;
