分析関数の衝撃

分析関数の衝撃（後編）

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

山岸賢治[著]

2007/09/14 14:00

ポスト

ダウンロードソースコード (3.7 KB)

最大何人まで座れますか？

　次に最大何人まで座れるかを求めるSQLについてです。「SQLで数列を扱う」では、以下のSQLが提示されています。

第1段階：すべてのシーケンスを保持するビューを作る。

CREATE VIEW Sequences (start_seat, end_seat, seat_cnt) AS
SELECT S1.seat  AS start_seat,
       S2.seat  AS end_seat,
       S2.seat - S1.seat + 1 AS seat_cnt
  FROM Seats3 S1, Seats3 S2
 WHERE S1.seat <= S2.seat  --ステップ1：始点と終点の組み合わせを作る
   AND NOT EXISTS
      --ステップ2：シーケンス内のすべての行が満たすべき条件を記述する
       (SELECT *
          FROM Seats3 S3
         WHERE (     S3.seat BETWEEN S1.seat AND S2.seat
                 AND S3.status <> '空')  --条件1の否定
            OR  (S3.seat = S2.seat + 1 AND S3.status = '空' )
                                                         --条件2の否定
            OR  (S3.seat = S1.seat - 1 AND S3.status = '空' ));
                                                         --条件3の否定

第2段階：最大のシーケンスを求める

SELECT start_seat, '～', end_seat, seat_cnt
  FROM Sequences
 WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);

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

Seats3テーブル

seat	status
1	占
2	空
3	空
4	空
5	空
6	占
7	空
8	占
9	空
10	空

出力結果

SeatStart	SeatEnd	Seat_Cnt
2	5	4

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

仮の最大値を保存する変数を用意
カウンタ変数を用意
seatの昇順にループ
各値を、配列に保存
seatが空なら、カウンタ変数をインクリメント
seatが空でなかったら、仮の最大値の更新処理

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

分析関数で書き換えたSQL

select start_seat,end_seat,seat_cnt
from (select min(seat) as start_seat,
      max(seat) as end_seat,
      count(*) as seat_cnt,
      max(count(*)) over() as maxSeat_cnt
      from (select seat,
            sum(willSum) over(order by seat) as makeGroup
            from (select seat,status,
                  case when Lag(status) over(order by seat) = '空'
                       then 0 else 1 end as willSum
                  from Seats3)
            where status = '空')
      group by makeGroup)
where seat_cnt = maxSeat_cnt;

　4つのselect文がありますが、最も内側のselect文の結果イメージから、解説していきます。

最も内側のselect文

seat	status	willSum
1	占	1
2	空	1
3	空	0
4	空	0
5	空	0
6	占	0
7	空	1
8	占	0
9	空	1
10	空	0

　case式で、Lag関数を使って、「seat」の昇順での1行前のstatusを求めて、それが空ならば0、そうでなければ1とし、willSumを列別名としてます。willSumの列別名の通り、合計（累計）を求めるのに使います。

次に内側のselect文

seat	makeGroup
2	1
3	1
4	1
5	1
7	2
9	3
10	3

　where句でstatus='空'の行を抽出して、分析関数のsum関数で、「seat」の昇順でのwillSumの累計を求め、makeGroupを列別名としてます。makeGroupの列別名の通り、group by句で使い、グループ化するのに使います。

次に内側のselect文

start_seat	end_seat	seat_cnt	maxSeat_cnt
2	5	4	4
7	7	1	4
9	10	2	4

　makeGroupでグループ化して、集合関数のmin関数でstart_seatを求め、集合関数のmax関数でend_seatを求め、集合関数のcount(*)でseat_cntを求め、分析関数のmax関数でcount(*)の最大値を求めてます。

　「分析関数の衝撃（前編）」で、最頻値（モード）を求めたSQLと同じような考え方です。

最も外側のselect文

start_seat	end_seat	seat_cnt
2	5	4

　where句で、seat_cnt=maxSeat_cntの行を抽出してます。

　まとめると、最初にcase式で、「seat」の昇順でstatus='空'になった（コントロールブレイクした）行を求めて、willSumとして累計を求めるのが1つめのポイントで、willSumの累計でグループ化してから集合関数と分析関数を使うのが2つめのポイントとなります。

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

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

select start_seat,end_seat,seat_cnt
from (select min(seat) as start_seat,
      max(seat) as end_seat,
      count(*) as seat_cnt,
      max(count(*)) over() as maxSeat_cnt
      from (select seat,
            sum(willSum) over(order by seat) as makeGroup
            from (select seat,status,
                  case max(status)
                       over(order by seat
                            Rows between 1 preceding
                                     and 1 preceding)
                       when '空' then 0 else 1 end as willSum
                  from Seats3) dummy
            where status = '空') dummy
      group by makeGroup)  dummy
where seat_cnt = maxSeat_cnt;