分析関数の衝撃（後編）

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

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

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

人数分の空席を探す　その2：行の折り返しも考慮する

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

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

Seats2テーブル

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

出力結果

row_id	SeatStart	SeatEnd
A	3	5
B	8	10
C	11	13

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

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

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

分析関数で書き換えたSQL

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

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

SQLのイメージ

　SQL自体は前の、「3人なんですけど座れますか？ その1：行の折り返しを考慮しない」で使った分析関数に、partition by句で「Row_ID」を指定して、「Row_ID」でパーティションを切っただけとなります。SQLのイメージを比較すると分かりやすいと思います。

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

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

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

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