Oracleのmodel句

Oracleのmodel句（2）
（行の補完）

rules句の繰り返し機能

山岸賢治[著]

2010/05/28 14:00

ポスト

ダウンロード SourceCode (1.1 KB)

3. 空き番号一覧の作成

　次は、『達人に学ぶ SQL徹底指南書』の162ページおよび、『SQLで数列を扱う』で扱われている「欠番を全部求める」をふまえて、空き番号一覧を作成するSQLをmodel句で作成してみます。テーブルのデータと出力結果は下記となります。

SeqTbl

seq
3
5
7
9
10
11
15

　SeqTblの最小値から最大値までの空き番号一覧を作成します。

出力結果

seq

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

minusで差集合演算を行う方法

select soeji as seq
  from SeqTbl
 model return updated rows
dimension by(0 as soeji)
measures(min(seq) as minV,max(seq) as maxV,0 as dummy)
rules(dummy[for soeji from minV[0] to maxV[0] INCREMENT 1] = 0)
minus
select seq from SeqTbl;

　処理のイメージを解説すると、まず、model句のdimension by(0 as soeji)とmeasures(min(seq) as minV,max(seq) as maxV,0 as dummy)によって、数値型のsoejiを添字として、SeqTblテーブルのseqの最小値であるmin(seq)を要素名minVとして、SeqTblテーブルのseqの最大値であるmax(seq)を要素名maxVとして持つ配列を用意してます。

　select文の評価順序においてmodel句は、having句の後に評価されるので、model句のdimension by句やmeasures句でmin関数やmax関数などの集約関数を使用できるのです。

　次に、model句のrules句でのdummy[for soeji from minV[0] to maxV[0] INCREMENT 1] = 0によって、soejiを添字とした配列の、増分値を1としてminV[0]からmaxV[0]までの添字のdummyに0をupsert（あればupdate,なければinsert）してます。このデータであれば、soejiを添字とした配列の、3から15までの添字のdummyに0がupsert（あればupdate,なければinsert）されます。

　model return updated rowsを指定してますので、rules句で広義の更新（updateかinsert）が行われた配列の要素のみが出力対象となります。もし、model return updated rowsを指定しなかったら、soejiが0の要素が出力されてしまいます。

　最後に、minus集合演算によって空き番号一覧を求めてます。SQLのイメージは、下のようになります。dimension by(0 as soeji)に対応する配列の添字を紫色で表現してます。

SQLのイメージ

Lead関数の結果を使ってforコンストラクトを使う方法

　別の方法として、下記のようにLead関数の結果を使ってforコンストラクトを使う方法もあります。

Lead関数の結果を使ってforコンストラクトを使う方法

select soeji as seq
from (select seq,Lead(seq) over(order by seq) as LeadV
        from SeqTbl)
 where seq+1 < LeadV
 model return updated rows
partition by(RowNum as PID)
dimension by(0 as soeji)
measures(seq+1 as startV,LeadV-1 as EndV,0 as dummy)
rules(
dummy[for soeji from startV[0] to EndV[0] INCREMENT 1] = 0)
order by seq;

　処理のイメージを解説すると、まず、インラインビューでLead関数を使って、seqの昇順で1行後の行のseqを列別名LeadVとして取得して、where seq+1 < LeadVによって、ValとLeadVの間に空き番号がある行を抽出してます。なお、select文の評価順序においてmodel句は、where句の後で評価されます。

　次のmodel句のpartition by(RowNum as PID)によって、RowNum擬似列でパーティションを切ってパーティションごとにrules句が適用されるようにしてます。RowNum擬似列は、ユニークな連番を振るものですから、1行ごとにパーティションを切って、rules句が適用されるイメージとなります。

　次に、dimension by(0 as soeji)とmeasures(seq+1 as startV,LeadV-1 as EndV,0 as dummy)によって、数値型のsoejiを添字として、seq+1を要素名startVとして、LeadV-1を要素名EndVとして、0をdummy要素名として持つ配列を用意してます。startVが空き番号の開始番号で、EndVが空き番号の終了番号となります。

　次に、model句のrules句でのdummy[for soeji from startV[0] to EndV[0] INCREMENT 1] = 0によって、soejiを添字とした配列の、増分値を1として、startV[0]からEndV[0]までの添字のdummyに0をupsert（あればupdate,なければinsert）してます。

　SQLのイメージは下図のようになります。最初にpartition by(RowNum as PID)で赤線で、次にdimension by(0 as soeji)に対応する配列の添字を紫色で表現してます。

SQLのイメージ

model句を使わない方法

　model句での行の補完で、補完の範囲を自テーブルから動的に取得すると、上記のような複雑な記述になってしまうため、下記のように、再帰with句を使う方法や、all_objectsやall_catalogやdictといった特に権限がなくてもアクセスできるデータディクショナリを使う方法などと使い分けるといいでしょう。

再帰with句を使う方法

with rec(seq,LeadV) as(
select seq+1,LeadV
from (select seq,Lead(seq) over(order by seq) as LeadV
        from SeqTbl)
where seq+1 < LeadV
union all
select seq+1,LeadV
  from rec
where seq+1 < LeadV)
select seq from rec order by seq;

dictデータディクショナリを使う方法

select a.seq
  from (select RowNum as seq from dict) a
  Join (select min(seq) as minV,max(seq) as maxV from SeqTbl) b
    on a.seq between b.minV and b.maxV
minus
select seq from SeqTbl;