PostgreSQLの分析関数の衝撃3
（数列を扱うSQLとrange指定）

Lag関数とLead関数の使用例

　2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本稿では、『分析関数の衝撃3（後編）』をPostgreSQL 8.4用にリニューアルした内容を扱います。

はじめに

　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が提示されています。

人数分の空席を探す　その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 <> '空' );

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

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

　『分析関数の衝撃3 （後編）』では、count(nullif(status,'占')) over(order by seat Rows between current row and 2 following)を使いましたが、 PostgreSQL 8.4では文法エラーになりますので、答えは下記となります。

window関数で書き換えたSQL1

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のイメージは、下記となります。

SQLのイメージ

　Lead関数で、2行後のseatをSeatEndとして求めてます。また、3つの検索case式でstatus='空'の行の数を足し算で求め、それが3であることを、外側のselect文のwhere句で条件としてます。

　PostgreSQLは、select文の値としてのboolean型をサポートしますので、下記のselect文でもよいです。

window関数で書き換えたSQL2

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でもよいです。

window関数を使わないSQL1

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;

window関数を使わないSQL2

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;