Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(後編)

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

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

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

SQLを使う業務では、分析関数を使いこなすと、 生産性と、SQLの可読性とパフォーマンスを、大きく向上させることができます。 後編では、order by句でrangeを使った分析関数などを取り上げます。

目次

はじめに

 「分析関数の衝撃(中編)」に引き続き、CodeZineのミック氏の記事で記載されたSQLを、分析関数を使って記述していきます。また、分析関数のorder by句でのrangeの使用例も解説します。

対象読者

  • SQLの可読性を向上させたい方
  • SQLのパフォーマンスを向上させたい方

 この記事では、「SQLで数列を扱う」と「相関サブクエリで行と行を比較する」に記載されているSQLを、分析関数を使って記述していきますので、先に読んでおくと理解しやすいと思います。

必要な環境

 本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。ソースコードはDB2 V9.1でも動作確認しました。その他、

  • Oracle9i以降
  • DB2
  • SQL Server 2005

 でも応用できます。

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 <> '空' );

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

seatsテーブル
seatstatus
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
出力結果
SeatStartSeatEnd
35
79
810
911

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

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

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

分析関数で書き換えたSQL
select SeatStart,SeatEnd
from (select seat as SeatStart,
      Lead(seat,3-1) over(order by seat) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and (3-1) following) as SeatCount
      from Seats)
where SeatCount = 3;

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

SQLのイメージ
SQLのイメージ

 Lead関数では、2行後の「seat」をSeatEndとして求めてます。count関数では、引数にnullif((status,'占'))を指定しています。集合関数のcount関数と同様に、分析関数のcount関数も、nullを数えません。このことと、「status」は占または空であることを使って、nullif関数で「status」が占ならnullを返すようにして、「status」が空の行を数えるようにしてます。

 count関数とnullif関数の組み合わせは、「HAVING句の力」の、nullを含まない集合を探す、と同じような考え方と言えるでしょう。

 count関数のwindowing_clauseは、下記のように解釈すると分かりやすいと思います。

order by seat         --seatの昇順で、
 Rows between         --行の範囲は、
current row           --小さいほうは、現在の行から
  and (3-1) following --大きいほうは、2行後まで

 後は、count関数の結果であるSeatCountが3であることを、外側のselect文のwhere句で条件としてます。

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

分析関数で書き換えたSQL(DB2)
select SeatStart,SeatEnd
from (select seat as SeatStart,
      max(seat)
      over(order by seat
           Rows between 2 following
                    and 2 following) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and 2 following) as SeatCount
      from Seats) dummy
where SeatCount = 3;

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

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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