Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

Lag関数とLead関数の使用例

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

ダウンロード SourceCode (2.3 KB)

 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のイメージ
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;

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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