SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

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

Lag関数とLead関数の使用例


  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (2.3 KB)

3. 「最大何人まで座れますか?」

 次に最大何人まで座れるかを求める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);

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

Seats3
seat status
1
2
3
4
5
6
7
8
9
10
出力結果
start_seat end_seat seat_cnt
2 5 4

 答えは、下記となります。

window関数で書き換えた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) a
            where status = '空') a
      group by makeGroup) a
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

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

次に内側のselect文の結果
seat makeGroup
2 1
3 1
4 1
5 1
7 2
9 3
10 3

 where句でstatus='空'の行を抽出して、window関数の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を求め、window関数のmax関数でcount(*)の最大値を求めてます。

 『PostgreSQLの分析関数の衝撃1』で、最頻値(モード)を求めたSQLと同じような考え方です。

最も外側のselect文の結果
start_seat end_seat seat_cnt
2 5 4

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

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

 ちなみに別解として、『分析関数の衝撃5 (総集編)』で扱った旅人算の感覚を使ってもよいです。

次のページ
4. 「2日前からの累計」

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2695 2009/08/18 19:53

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング