はじめに
表題の通り、SQLクエリでのOracle独自の拡張句であるMODELです。行間参照機能としては最強のアレです。覚えておくとなにかと便利なんですけど、ぜんぜん使われてないっていうかほとんど居ない子扱いですよね。うん、まぁMODEL句の文法を見てもらえばなんとなくわかると思いますけど、MODEL句って文法的にSELECTにくっついてるとはいえ、一般的なクエリ部分からは分離されてて、記述的にも動作的にもほとんどSQLじゃ無いんです。なので「こんなん追加で覚えるくらいならアプリ側で処理するわ」、...ってなっちゃうのも分かります。
SELECT <columns>
FROM <tables>
WHERE <conditions>
MODEL
PARTITION BY (<columns>)
DIMENSION BY (<columns>)
MEASURES (<columns>)
RULES (<actions>)
でもね、MODEL使うとちょっとした行間参照クエリなんかは、アプリ側でわざわざプログラムを組まなくともSQLクエリ内でササッと終わらせることができるで、かる～くでも動きを覚えておくととっても便利なんですよ。ホントに。あと殆ど使われてないとはいえ、れっきとしたOracleのSELECTの正式な文法ですからね。ときには思い出して、、、じゃなくて使わない理由はないのです。
じゃあ、MODELを使うと一体なにができるの？って話ですが、「行間参照」、「ループ」、「カラム毎の処理」ってとこですかね。特に行間参照が秀逸。なぜかっていうとMODEL句はデータに行でアクセスするのではなく、配列でアクセスするからです。たとえば二次元配列であればエクセルのように縦横の添字をつかってピンポイントでデータにアクセスできます。もちろん三次元以上の配列もOK。なんかもう、その時点でそりゃSQLじゃないよってツッコミが入りそうですが、そのとおりです。でもOracleがそんなSQL作っちゃったんだからしょうが無い。活用しましょう (^^)。
MODELの文法
ではまずは文法から。すでに記載しましたが、MODELは基本４つの句で構成されます。このうち最初のPARTITION BYだけは省略可能で、あとは必須です。ただしRULESは存在さえすれば空でもOKです。
| 句 | 必須？ | |
|---|---|---|
| PARTITION BY | 分析関数のPARTITION BYと同じ様に区切って処理する。同じ値をもつ行だけでそれぞれRULESの代入式を実行する。 | |
| DIMENSION BY | 必須 | 配列の添字となるテーブルカラムの定義。テーブルにないカラムも可。 |
| MEASURES | 必須 | 配列として扱われるテーブルカラムの定義。テーブルにないカラムも可。 |
| RULES | 必須 | 配列への値の代入を記述する。行毎ではなくカラム毎の記述。空も可。 |
配列と添字
ということでMODEL句をつかった一番簡単なクエリです。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c) RULES ();
N C
---------- -
0 A <--- C[0] = 'A'
DIMENSION BY句で添字を定義し、MEASURE句で配列を定義します。ここでは、添字Nと配列Cを作っており、配列Cの添字0におけるセルデータが'A'という初期値で定義されています。そして配列のデータには、最後のRULES句においてC[<添字>]という形でアクセスできるようになります。つまりC[0]を参照すると'A'が得られるということですね。これをセルと呼びます。
ちなみに、上記のように配列や添字は元テーブルに存在しなくともMODEL句内で勝手に定義してかまいません。逆にMODEL句で参照されなかったカラムはクエリから消えてしまいます。上の例で言えば、DUALにはDUMMYというカラムが存在しますが、MODEL内で使われていないためSELECT *でも出力されません。
RULES句の代入式
そして最後のRULES句でMODELの動きを定義します。ただしここでできる動きというのは「配列のセルへの代入」のみです。つまりMODELはクエリの途中で配列のセルを書き換えながら欲しいデータを取得する動きをするのです。そしてその書き換えはカラムのセル単位です。一般のSQLが行単位でアクセスしながらデータを得るのに対して、MODELはカラム単位でセルを書き換えながらクエリを進め欲しいデータを作ります。
代入式の動き (UPSERTとUPDATE)
ではもっとも簡単な代入式の例をみてみましょう。MODEL句はDIMENSION BYが配列の添字で、MEASURESが配列でしたね？したがって先程のクエリでは配列cに添え字nでアクセスできます。RULES内でc[0]に値'B'を代入してみます。結果、Cのカラムの値が'A'から'B'に変わりました。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[0]='B');
N C
---------- -
0 B <-- Cの値がBに変わった
では今度は、存在しないセルc[1]に値を代入してみましょう。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[1]='C');
N C
---------- -
0 A
1 C <-- N=1が存在しなかったので追加された
なんと、行が増えました。そうです、"n=1"は存在しなかったので作られました。行が存在するときは更新し、無いときは追加するこの動きをUPSERTと呼びます。これがRULES句のデフォルトの動きです。UPSERTでない動きはUPDATEです。UPDATEは行を一切追加しません。代入の直前にUPDATEと記述することでこの動きを指定することができます。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (UPDATE c[1]='C', -- 一つの代入式だけがUPDATE
c[2]='X'); -- デフォルトのUPSERT
N C
---------- -
0 A
2 X <-- UPSERTのみ追加
上記のように代入の前に記述すると直後の代入のみに影響を与えます。RULES全体のデフォルトの動きを変えたい場合は以下のようにRULES直後に記述します。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES UPDATE (c[1]='C', -- RULES内のすべての代入式がUPDATEとなる
c[2]='X');
N C
---------- -
0 A
そして、さらにもう一つUPSERT ALLという動きがあります。これを説明するは、まず添字の「評価式」を説明する必要があります。
添字の評価式 (左辺)
配列の添字は、一意の値を指定する方法と評価の式を記述する方法があります。オラクルのマニュアルでは、前者をPositional reference、後者をSymbolic Referenceと呼んでいます。たとえば、上記C[1]の添字は一意値でしたが、以下の例の添字はBETWEENを使った評価式です。評価式の場合は、適合した行が更新されるだけでUPSERTでも行は追加されません。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[n BETWEEN 0 and 1]='C');
N C
---------- -
0 C
比較演算を含め、以下の記述はすべて評価式です。
| 評価式 | 説明 |
|---|---|
| c[ n = 1 ] = | nが1の時のみ |
| c[ n in (0, 1) ] = | nが0または1の時のみ |
| c[ ANY ] = | 存在するすべてのセル。 n IS ANYの省略形 |
| c[ n ] = | n = nの省略形。ANYと同等 |
| c[ n + 1 ] = | n = n + 1の省略形。なにも適合しない |
| c[ n IS ANY ] = | 存在するすべてのセル。 ANYと同等 |
添字の一意値 (左辺)
では次にすでに使用している一意値です。c[1]のように指定する値ですね。一行追加するクエリはすでに見ましたが、複数行を一度に追加する書き方もあります。
一意値のFOR
以下のようにFORを使うと複数の値を一度に一意値として扱えます。FORは値を一つづつ処理していくのでそれぞれが一意値として処理されるのです。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[FOR n IN (0, 1, 2)]='C');
N C
---------- -
0 C
1 C
2 C
FORにはいくつかの書式があります。ここでMODELでない普通のサブクエリが使えるのが面白いところですね。
| FORの書式 | 説明 |
|---|---|
| FOR n IN (1, 2, 3) | INリスト |
| FOR n FROM 1 TO 3 INCREMENT 1 | 繰り返し |
| FOR n IN (SELECT col FROM table) | サブクエリ |
ネスト参照
また配列のネストによる参照は、値が一意に決まる限り使えます。MEASURESのどの配列でも使えますがネスト参照は一階層のみです。(つまり配列のネストなかで配列のネストは使用不可)。ちなみにネスト参照は右辺でも使用できます。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[DECODE(c[0], 'A' ,1, 2)]='C'); -- セル値によって添字を変更
N C
---------- -
0 A
1 C
代入式の動き (UPSERT ALL)
さて、話をUPSERT ALLに戻します。UPSERT ALLは、二次元以上の配列の添字が上記の一意値と評価式が混在しているときに、評価式の値が指定された一意値になくとも他行にあれば、UPSERTで追加を行うというものです。うーん。わかりにくいですね。簡単な例を示します。
SELECT * FROM t;
LN ITEM QTY
---------- ------ ----------
100 Apple 20
200 Apple 20
100 Orange 50
このテーブルに対して、LNとITEMを添え字に取りLNを評価式、ITEMを一意値としてUPSERT代入をします。LNが100のときに適合してQTYが更新されていますが、評価式のためUPSERTでも行の追加はされません。
SELECT * FROM t
MODEL DIMENSION BY (ln, item) MEASURES (10 qty)
RULES (UPSERT qty[ln in (100, 200, 300), 'Orange'] = 100); <-- UPSERT
LN ITEM QTY
---------- ------ ----------
100 Apple 10
200 Apple 10
100 Orange 100 <-- 存在する行でQTYの更新のみ
これをUPSERT ALLで実行すると、LN=200が"Orange"以外の他の行に存在するため追加されます。ただし、LN=300は全く存在しない値なので行は追加されません。
SELECT * FROM t
MODEL DIMENSION BY (ln, item) MEASURES (10 qty)
RULES (UPSERT ALL qty[ln in (100, 200, 300), 'Orange'] = 100); <-- UPSERT ALL
LN ITEM QTY
---------- ------ ----------
100 Apple 10
200 Apple 10
100 Orange 100 <-- 存在する行でQTYの更新
200 Orange 100 <-- LN=200が追加された
<-- ただし、LN=300は追加されない
代入式の動きがでそろったのでまとめておきます。
| コマンド | 動き |
|---|---|
| UPDATE | 適合する行を更新する。追加はしない。 |
| UPSERT (default) | 適合する行を更新する。添字が一意値のとき存在しない行を追加する。 |
| UPSERT ALL | 適合する行を更新する。添字が一意値と評価値が混在している時、評価式の値が指定された一意値になくとも他行にあれば追加する。 |
代入式のCV関数 (右辺)
今度は代入式の右辺に着目します。これまで見てきたとおり一つの代入式で複数の行を一度に処理することができます。とすると代入式の右辺では、いまどの添字値で計算しているのか知ることが重要になってきます。このためにCV関数が用意されています。CV()は処理中の添字の値を返す関数で、引数に添字カラムをとります。以下の例では、CのそれぞれのセルにCV(n)を代入しています。結果はそのままですね。添字と同じ値になりました。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
RULES (c[FOR n IN (0, 1, 5)] = CV(n));
N C
---------- -
0 0
1 1
5 5
では、次は適当な値を持つ行をつくってから、そのカラムの値を二倍にしてみましょう。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 doubled)
RULES (value[0] = 100,
value[1] = 500,
value[2] = 300,
value[3] = 600,
doubled[ANY] = value[CV()] * 2)
ORDER BY ID;
ID VALUE DOUBLED
---------- ---------- ----------
0 100 200
1 500 1000
2 300 600
3 600 1200
どうでしょう？ CV関数の使い方が納得できましたか？ここでは、CV関数が配列の中で使われています。この場合添字のカラムは明らかなので引数は不要です。
CV関数は処理中の添字をちゃんと値としてとってきます。したがってその値に対する計算等も可能です。以下では、添字をCV() - 1として一つ前の値を取ってきており、分析関数のLAGのような動きをしています。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 prev)
RULES (value[0] = 100,
value[1] = 500,
value[2] = 300,
value[3] = 600,
prev[ANY] = value[CV() - 1])
ORDER BY ID;
ID VALUE PREV
---------- ---------- ----------
0 100
1 500 100
2 300 500
3 600 300
上記のCV() - 1は行間参照としてよく使われる方法ですが、添字となるカラムの値が抜けなく存在している必要があります。したがってこの手の計算を行う場合は、抜けのない行番号を別途作って添字として使用するのが一般的です。
CREATE TABLE t(id, value)
AS SELECT 1, 100 FROM DUAL UNION ALL SELECT 6, 500 FROM DUAL UNION ALL
SELECT 10, 300 FROM DUAL UNION ALL SELECT 20, 600 FROM DUAL;
ID VALUE -- IDが飛び飛び
---------- ----------
1 100
6 500
10 300
20 600
SELECT * FROM t
MODEL DIMENSION BY (ROW_NUMBER() OVER(ORDER BY id) rn) -- 新たに添字用カラムを作る
MEASURES (id, value, 0 prev)
RULES (prev[ANY] = value[CV() - 1]);
RN ID VALUE PREV
---------- ---------- ---------- ----------
1 1 100
2 6 500 100
3 10 300 500
4 20 600 300
問題1
さて、ここで問題です。上記のテーブルを使って同様にID順で一つ前の値を取得しますが、最初のセルには最後のセルの値を持ってきて値をループさせてください。ただしテーブル行数は可変であるとすること。
ID VALUE PREV
---------- ---------- ----------
1 100 600 <-- ここに最大IDのVALUEの値が入る
6 500 100
10 300 500
20 600 300
解答1
やりかたはいろいろありますが、最初に行の最大数を計算しておけばvalue[mc[cv()]]で最大RNのセルの値にアクセスできますね。
SELECT id, value, prev FROM t
MODEL DIMENSION BY (ROW_NUMBER() OVER(ORDER BY id) rn)
MEASURES (COUNT(*) OVER () mc, id, value, 0 prev) -- mcに行数を保存しておく
RULES (prev[ANY] = value[CV() - 1],
prev[1] = value[mc[CV()]]) -- 最大RNのセルの値を得る
ORDER BY id;
代入式でのセルの処理順 (左辺)
さて、上記のように一つの代入式で複数のセルを一度に更新する方法が理解できたと思います。となると場合によってはセルの処理の順番によって計算結果がかわってしまうことがあることも理解できるはずです。たとえば、以前処理した自分自身のカラムのセルを参照して代入を行う場合ですね。そのような場合は処理の順番が確定できないとエラーとなります。
以下の例では、run_sumへの代入で一つ前のセルを自己参照しようとしていますが、添字の処理順が指定されておらず結果が不定となるためエラーです。添字の値順で処理すると決まってるわけではないですからね。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 run_sum)
RULES (value[0] = 100,
value[1] = 500,
value[2] = 300,
value[3] = 600,
run_sum[ANY] = NVL(run_sum[CV() - 1], 0) + value[CV()])
ORDER BY id;
ORA-32637: Self cyclic rule in sequential order MODEL
セルの処理順を指定するには、以下のように左辺でORDER BY句を使用します。これでランニング集計が正しく行われます。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 run_sum)
RULES (value[0] = 100,
value[1] = 500,
value[2] = 300,
value[3] = 600,
run_sum[ANY] ORDER BY id -- ORDER BYでセルの処理順を指定
= NVL(run_sum[CV() - 1], 0) + value[CV()])
ORDER BY id;
ID VALUE RUN_SUM
---------- ---------- ----------
0 100 100
1 500 600
2 300 900
3 600 1500
ちなみに、RULESの前にリファレンスオプションのIGNORE NAVをつけると、参照先セルが存在しない場合にNULLでなく0で計算してくれるので、上記のNVLが不要になります。オプションの詳細については後述します。
集合関数と分析関数 (右辺)
代入式の計算（右辺）に集合関数や分析関数をつかうこともできます。以下では、集合関数(run_sum1)、分析関数(run_sum2)のどちらもランニング集計を行っています。集合関数では、集合関数のあとに添え字をつけて範囲指定します。分析関数では当然ウインドウで範囲指定するので添字は付きません。OVER句にORDER BYがある場合のデフォルト範囲は、ROWS UNBOUNDED PRECEDING AND CURRENT ROWですね？
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 run_sum1, 0 run_sum2)
RULES (value[0] = 100,
value[1] = 500,
value[2] = 300,
value[3] = 600,
run_sum1[ANY] = SUM(value)[id <= CV()],
run_sum2[ANY] = SUM(value) OVER (ORDER BY id))
ORDER BY id;
ID VALUE RUN_SUM1 RUN_SUM2
---------- ---------- ---------- ----------
0 100 100 100
1 500 600 600
2 300 900 900
3 600 1500 1500
分析関数の詳細については、以下を参照してください。
またRULES内で分析関数を使用する場合いくつか制限があります。まず、左辺の添字に少なくとも１つ「評価式」を含まなければいけません。添字が一意値のみであった場合、エラーとなります。そしてさらに、分析関数の計算対象となるセルは左辺の評価式で適合した行のみです。この範囲を超えて計算することはできません。
以下の例では、集合関数、分析関数ともid in (0, 1)で最初の２行のみを代入更新対象としており、すべてのVALUEカラムセルに対してMAX関数を使用していますが、異なった計算結果となっています。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 id) MEASURES (0 value, 0 max1, 0 max2)
RULES (value[0] = 100, value[1] = 500, value[2] = 300, value[3] = 600,
max1[id IN (0, 1)] = MAX(value)[ANY], -- 集合関数、計算対象全部
max2[id IN (0, 1)] = MAX(value) OVER ()) -- 分析関数、計算対象全部（ただしウインドウ内）
ORDER BY id;
ID VALUE MAX1 MAX2
---------- ---------- ---------- ----------
0 100 600 500
1 500 600 500
2 300
3 600
PARTITION BY句で分割
RULES句内の代入式についてはあらかた説明はおわりました。次は、MODEL直下の句でありながら説明を飛ばしていたPARTITION BYについてです。
前述したとおり、MODELのPARTITION BYは同じ名前の分析関数のPARTITION BYと同等の動きをします。「指定したカラムの値が同じ行だけでそれぞれ計算を行う」ってことです。MODELにおいては、指定したカラムの値が同じ行だけで、RULES句を実行することになり、その場合パーティション外の行はRULES内で全く参照することができなくなります。逆にいえば特定のカラムの値を超えて行間参照しないのであれば、PARTITION BYを活用するとRULES句がかなり簡素化されます。
以下、簡単な例としてITEM毎にランニング集計を行っています。PARTITION BYを使わなくてもできますが、その場合DIMENSIONを増やす必要があります。この程度だとあまり有効性を感じませんが、後述のITERATEと共に使用すると非常に有用です。
SELECT * FROM t;
ID VALUE ITEM
---------- ---------- ----------
1 100 Apple
6 500 Apple
10 300 Orange
20 600 Orange
SELECT * FROM t
MODEL PARTITION BY (item)
DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY item ORDER BY id) rn)
MEASURES (value, 0 run_sum)
RULES (run_sum[ANY] = NVL(run_sum[CV() - 1], 0) + value[CV()])
;
ITEM RN VALUE RUN_SUM
---------- ---------- ---------- ----------
Apple 1 100 100
Apple 2 500 600
Orange 1 300 300
Orange 2 600 900
RULES句の繰り返し (ITERATE)
これまで見てきたように、RULES句内の代入式は上から順に一度だけ実行されます。RULES句内で代入式そのものの繰り返しや分岐などの制御はできません。だだし、RULES句の記述全部を任意回数繰り返すことはできます。それがITERATE句です。ITERATE句とPARTITION BYを組み合わせると行毎に処理して行を増やしていく処理などが可能となります。
以下はテーブルtのカラムsに格納されたカンマ区切り文字列を展開しています。まずPARTITION BYでID毎、つまり行毎の処理とし、ITERATEでループしてカンマ区切りからREGEXP_SUBSTRで各ITEMを取得しています。ITERATEでループするとITERATION_NUMBERというループカウンタの疑似変数が使用できます。ITERATION_NUMBERはゼロからスタートしループ毎にカウントアップします。ITERATEの引数は最大ループ回数で、リテラル値のみ設定でき変数等でダイナミックに変更することができませんが、UNTIL句でループを打ち切ることができます。UNTILの記述はRULES句の記述に先行していますが、名前の通りループの終了のチェックを行うのは毎回RULES句処理後です(ITERATION_NUMBERがカウントアップする前)。したがって、必ず一回はRULES句を実行します。
CREATE TABLE t(id, s)
AS SELECT 10, 'Apple,Orange,Kiwi' FROM DUAL UNION ALL
SELECT 20, 'Banana,Orange' FROM DUAL;
ID S
---------- -----------------
10 Apple,Orange,Kiwi
20 Banana,Orange
SELECT id, n, item FROM t
MODEL PARTITION BY (id)
DIMENSION BY (0 n)
MEASURES (s,
CAST('' AS VARCHAR2(10)) item,
REGEXP_COUNT(s, ',') + 1 num)
RULES ITERATE (2000) -- 繰り返し最大回数(リテラル値のみ)
UNTIL (ITERATION_NUMBER + 1 >= num[0])
(item[ITERATION_NUMBER]
= REPLACE(REGEXP_SUBSTR(s[0], '\w+(,|$)',
1, ITERATION_NUMBER + 1),
',', ''))
ORDER BY id, n;
ID N ITEM
---------- ---------- ----------
10 0 Apple
10 1 Orange
10 2 Kiwi
20 0 Banana
20 1 Orange
REFERENCE句の参照用配列
これまで説明した通りMODEL句は配列と添字でセルにデータを取得するというSQLとしては非常に特殊なアクセス方法をとっているため、そのままでは、処理の途中で他のテーブルと結合して新しいデータを取得するなどということができません。そこでMODEL句内では参照用の配列を事前に準備することができるようになっています。REFERECE句を用いて事前に他テーブルのデータを配列にして用意しておくとMODELの処理の途中においても他テーブルにアクセスできるようになります。これがテーブル結合の代替となります。
ちなみに、事前にテーブルを結合してデータを得ておけばよいのでは？とおもうかもしれませんが、MODELの処理中でしか得られないデータもあるので、その場合は事前にテーブル結合することができません。まぁMODELで処理した後のデータでテーブル結合してまたMODELに戻るという方法もなくはないですが、SQLがとても冗長になります。
簡単な例として、上記のカンマ区切り文字列を展開して得たITEMを使って他テーブルにアクセスし、それで得たデータを使ってカンマ区切り文字列に戻す、、、みたいなことをやってみます。
SELECT * FROM t;
ID S
---------- -----------------
10 Apple,Orange,Kiwi
20 Banana,Orange
SELECT * FROM list;
ITEM COUNTRY
--------------- ---------------
Apple Japan
Orange USA
Kiwi Aussie
Banana India
SELECT id, s FROM t
MODEL RETURN UPDATED ROWS
REFERENCE r ON (SELECT item, country FROM list)
DIMENSION BY (item)
MEASURES (country)
MAIN M
PARTITION BY (id)
DIMENSION BY (0 n)
MEASURES (CAST(s AS VARCHAR2(50)) s,
CAST('' AS VARCHAR2(30)) item,
REGEXP_COUNT(s, ',') + 1 num)
RULES ITERATE (2000)
UNTIL (ITERATION_NUMBER + 1 >= num[0])
(item[NULL] = REPLACE(REGEXP_SUBSTR(s[0], '\w+(,|$)',
1, ITERATION_NUMBER + 1),
',', ''),
s[NULL] = NULLIF(s[NULL] || ',', ',')
|| item[NULL]
|| '(' || r.country[item[NULL]] || ')'
)
ORDER BY id, n;
ID S
---------- --------------------------------------------------
10 Apple(Japan),Orange(USA),Kiwi(Aussie)
20 Banana(India),Orange(USA)
新しいのは以下の部分ですね。事前に参照配列を用意しておいて、RULES句の中からアクセスしています。参照配列は複数作ることもできます。その場合は、REFRENCE句をもう一度繰り返します（ex. REFRENCE r2 ON ...)。ここで、注意すべきは、REFERENCE句ではDIMENSION BYやMEASURESの定義でテーブルカラムを使用しなければならない(新しいカラムを作ることはできない)ことです。ただサブクエリ部分で自由にカラムを作れるので問題ではないはずです。
REFERENCE r ON (SELECT item, country FROM list)
DIMENSION BY (item)
MEASURES (country)
また、REFRENCE句を使用した場合は、メインの処理部分はMAIN <alias>と宣言しなければなりません。
MAIN M
PARTITION BY (id)
DIMENSION BY (0 n)
...
ちなみに、RETURN UPDATED ROWSはグローバルリファレンスオプションで、最終的に変更されたセルのある行だけを出力するというオプションです。ここでは、添字がNULLのセルのみ変更されているのでその行だけが返されています。
MODELのオプション
MODEL句には動きを変えるオプションがいくつか用意されています。オプションには３つの種類があり、それぞれ「グローバルリファレンスオプション」、「リファレンスオプション」、「ルールオプション」で、以下のように記述する位置と影響範囲が異なります。
| 種類 | 位置 | 範囲 |
|---|---|---|
| グローバルリファレンスオプション | MODEL直後 | MAIN句およびREFERENCE句両方 |
| リファレンスオプション | MAIN句はMEASURES定義後、REFERECE句はREFERENCE定義後 | MAIN句またはREFERENCE句それぞれ |
| ルールオプション | RULES直後 | RULES句 |
グローバルリファレンスオプション
MODEL直後に記述しリファレンス句およびメイン句に影響を与えます。グローバスオプションとして指定できるのは、後述のリファレンスオプション+以下のRETURNオプションです。混在して記述する場合は、なぜかRETURNオプションを最後に記述しなければならないようです。
- RETURNオプション
| オプション | デフォルト | 説明 |
|---|---|---|
| RETURN ALL ROWS | YES | すべての行を返します。 |
| RETURN UPDATED ROWS | MODELのRULES句で代入更新された行のみを返します。 |
SELECT * FROM t;
ID S
---------- -----------------
10 Apple,Orange,Kiwi
20 Banana,Orange
SELECT * FROM t
MODEL RETURN UPDATED ROWS -- 更新したセルのある行だけ返す
DIMENSION BY (id)
MEASURES (s)
RULES (s[10]='Grape');
ID S
---------- -----------------
10 Grape
リファレンスオプション
MAIN句に対するオプションはMEASURES定義後(RULES句直前)、REFERENCE句に対するオプションはそれぞれのREFERENCE句定義後に記述します。グローバルリファレンスオプションと異なる設定がなされた場合はこちらが優先されます。
- NAVオプション
| オプション | デフォルト | 説明 |
|---|---|---|
| KEEP NAV | YES | 存在しない数値セルの値をNULLとして処理する |
| IGNORE NAV | 存在しない数値セルの値0として処理する |
存在しない数値セルを参照した場合にNULLを返すか、0を返すかを変更できます。NULLを含んだ計算はすべてNULLになってしまうので、0を返すようにしておけば、いちいちNVL等の関数を使用する必要がなくなります。
SELECT * FROM DUAL
MODEL DIMENSION BY (1 n) MEASURES (10 qty, 0 sum)
RULES (qty[1]=qty[2],
sum[1]=SUM(qty)[ANY])
;
N QTY SUM
---------- ---------- ----------
1
SELECT * FROM DUAL
MODEL DIMENSION BY (1 n) MEASURES (10 qty, 0 sum)
IGNORE NAV -- 存在しないセルは0を返す
RULES (qty[1]=qty[2],
sum[1]=SUM(qty)[ANY])
;
N QTY SUM
---------- ---------- ----------
1 0 0
- UNIQUEオプション
| オプション | デフォルト | 説明 |
|---|---|---|
| UNIQUE DIMENSION | YES | 添字はユニークでなければならない。 |
| UNIQUE SINGLE REFERENCE | 添字の重複を許可するが、重複セルに右辺でアクセスする場合は複数セルを返さないよう気をつけなければならない。 |
以下の通り、DIMENSION BY (およびPARTITION BY)のカラムで値がセルが一意に決まらない場合、デフォルトではエラーとなります。
SELECT * FROM t;
N ITEM
---------- ------
1 Apple <-- ITEM[1]が２つある
1 Orange <--
2 Apple
SELECT * FROM t
MODEL DIMENSION BY (n) MEASURES (item)
RULES (item[1] = 'Kiwi');
ERROR:
ORA-32638: Non unique addressing in MODEL dimensions
UNIQUE SINGLE REFERENCE オプションを使用すると重複添字が許可されます。
SELECT * FROM t
MODEL DIMENSION BY (n) MEASURES (item)
UNIQUE SINGLE REFERENCE
RULES (item[1] = 'Kiwi');
N ITEM
---------- ------
2 Apple
1 Kiwi
1 Kiwi
しかし、その場合右辺が複数セルを返してしまうとエラーとなります。
SELECT * FROM t
MODEL DIMENSION BY (n) MEASURES (item)
UNIQUE SINGLE REFERENCE
RULES (item[2] = item[1]);
ERROR at line 1:
ORA-32638: Non unique addressing in MODEL dimensions
従ってそうならないように気をつけなければなりません。
SELECT * FROM t
MODEL DIMENSION BY (n) MEASURES (item)
UNIQUE SINGLE REFERENCE
RULES (item[2] = MAX(item)[1]);
N ITEM
---------- ------
1 Apple
1 Orange
2 Orange
ルールオプション
RULES句の直下に記述できるオプションです。RULESの動き全体に影響します。
- ORDERオプション
| オプション | デフォルト | 説明 |
|---|---|---|
| SEQUENTIAL ORDER | YES | RULES句内の代入式は、記述順に実行される。 |
| AUTOMATIC ORDER | RULES句内の代入式は、依存関係順に実行される |
デフォルトのSEQUENTIAL ORDERでは、代入式は上から下に記述順に実行されます。
SELECT * FROM t
MODEL DIMENSION BY (id) MEASURES (s)
RULES (s[20] = s[10],
s[10] = 'UPDATED');
ID S
---------- -----------------
10 UPDATED
20 Apple,Orange,Kiwi
しかし、AUTOMATIC ORDERにすると、Oracleが依存関係をしらべて実行順を変更します。以下のクエリではその結果s[20]=s[10]の前にs[10]='UPDATED'が実行され、両方のセルがUPDATEDになっています。便利な機能ではあるのですが、ある意味混乱の元です。オラクルに任せるよりは しっかり 依存関係を把握した上でコーディングすべきでしょう。まぁ、あるセルが書き換わったことを条件に意図的に順序を変更するなどというテクニックもあるのかもしれませんが、そんなコード書いたら保守が大変ですよね。
SELECT * FROM t
MODEL DIMENSION BY (id) MEASURES (s)
RULES AUTOMATIC ORDER
(s[20] = s[10],
s[10] = 'UPDATED');
ID S
---------- -----------------
10 UPDATED
20 UPDATED <-- 先に2番めのs[10]への代入が実行された
- 代入オプション
| オプション | デフォルト | 説明 |
|---|---|---|
| UPDATE | 存在するセルを更新する | |
| UPSERT | YES | 存在するセルを更新し、存在しないセルを追加する |
| UPSERT ALL | 添字に一意値と評価式が混在ている場合、指定の一意値で存在していないセルでも他の一意値で存在している場合はセルを追加する。 |
前述の通り、代入オプションはRULES句内の代入式それぞれに付与することもできます。
元から存在しているセルかどうかの確認
どういった場面で使用するのかいまいち判然としませんが、MODELにはセルが元から存在したものかUPSERTで追加されたものかを判別する関数や演算子が用意されています。元から存在するセルというのは、DIMENSION BYやMEASURESが定義されたときに存在するセルであり、定義時に作られたセルも含まれます。
- PRESENTV ( ＜Cell＞, ＜Value to return when true＞, ＜Value to return when false＞ )
PRESENTV()はセルが元から存在するかどうか確認する関数です。セルが元から存在するものであるときは第2引数の値を返し、そうでなければ第3引数の値を返します。
以下の例でわかりますが、n=0はMIMENSIONの定義時に作られているので元から存在するセルとして認識されますが、それ以外はUPSERTで追加された行なので元から存在するセルとは見なされていません。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES (CAST('A' as VARCHAR2(10)) s)
RULES (s[1] = 'B',
s[FOR n IN (0, 1, 2)] = PRESENTV(s[CV()], 'YES', 'NO'));
N S
---------- ----------
0 YES
1 NO
2 NO
- PRESENTNNV ( ＜Cell＞, ＜Value to return when true＞, ＜Value to return when false＞ )
PRESENTNNV()は、PRESENTVと同じくセルが元から存在するかどうか確認する関数ですが、セル元からが存在しかつ値がNULLでないときだけ第2引数の値を返します。それ以外は第3引数の値を返します。
- IS PRESENT
IS PRESENTは、セルが元から存在するかどうか確認する演算子です。CASE文やITERATEのUNTIL等で用いられます。
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES (CAST('A' as VARCHAR2(10)) s)
RULES (s[1] = 'B',
s[FOR n IN (0, 1, 2)] = CASE WHEN s[CV()] IS PRESENT
THEN 'YES'
ELSE 'NO'
END);
N S
---------- ----------
0 YES
1 NO
2 NO
MODELの簡単な使用例(手抜き)
MODELのうまい実際例がちょっと思いつかないので、いままでに投稿したMODEL使ったコードを挙げてみます。:-)
素数計算
まずは素数を計算するクエリ。エラトステネスの篩 (厳密には違う)で高速化してます。
最初に素数でないときにチェックを入れるための補助テーブルnumsを作ります。1 - 100000までの行を生成してますが、素数でないと確定している不要な行は除外しています。5以上の素数はかならず6の倍数の前後にあるらしいので。そのあと、MODELのITERATEで1から最大値のルートSQRT(MAX(n))までループし、各値で割り切れる数値にはフラグを立てていくと残った数が素数になります。
本来エラトステネスの篩 だとすでに素数でないと判定された数の評価はスキップすべきですが、その条件である評価式flg[ITERATION_NUMBER + 2] = 0を代入式に追加すると遅くなってしまうため除外しています。おそらくセルのネスト参照には多少のオーバーヘッドが伴うものと思われます。
WITH nums
AS (SELECT LEVEL + 1 n
FROM DUAL
WHERE MOD (LEVEL + 1, 6) in (1, 5) OR LEVEL + 1 IN (2, 3)
CONNECT BY LEVEL < 100000)
SELECT n
FROM (SELECT n, flg
FROM nums
MODEL REFERENCE r ON (SELECT SQRT (MAX (n)) mx, 0 i FROM nums)
DIMENSION BY (i) MEASURES (mx)
MAIN m
DIMENSION BY (n) MEASURES (0 flg)
RULES UPDATE
ITERATE (1000) UNTIL (ITERATION_NUMBER + 3 > mx[0])
(flg [MOD (n, ITERATION_NUMBER + 2) = 0
AND n > ITERATION_NUMBER + 2] = 1)
)
WHERE flg = 0;
パスカルの三角形
次はパスカルの三角形です。この手の動きはMODELの最も得意とするところです。
SELECT LPAD(' ', TRUNC((MAX(LENGTH(path)) OVER () - LENGTH(path))/2), ' ') || path triangle
FROM (SELECT * FROM DUAL
MODEL DIMENSION BY (1 n, 1 r)
MEASURES (1 value, CAST('1' AS VARCHAR2(200)) path)
RULES ITERATE (19) -- 深さ20まで計算
(value [ITERATION_NUMBER + 2, FOR r FROM 1 TO ITERATION_NUMBER + 2 INCREMENT 1] =
NVL(value [ITERATION_NUMBER + 1, CV() - 1], 0)
+ NVL(value [ITERATION_NUMBER + 1, CV() ], 0),
path [ITERATION_NUMBER + 2, ANY] ORDER BY r DESC =
value [ITERATION_NUMBER + 2, CV()]
|| NULLIF('-' || path [ITERATION_NUMBER + 2, CV() + 1], '-')))
WHERE r = 1
ORDER BY n;
おわりに
MODEL句を使うと本来SQLでできないようなことがチャッチャッとできるようになります。まぁ多くの場合再帰でも置き換えが効くようになりましたが、再帰よりは高速ですので覚えておくのも一興かと。
なんにせよ、MODEL句に関した記述はオラクルのマニュアルや他のサイトでもとても少なく、使いながら手探りで掴んで行くしかありません。そういうわけで、上記説明に間違いがあった場合はコメントしていただけるとありがたいです。
最後にMODELのシンタックスを置いておきます。
MODEL
[ <Global reference options> ]
[ REFERENCE <Name> ON ( <Sub query> )
DIMENSION BY ( <Cols> )
MEASURES ( <Cols> )
[ <Referece options> ]
... ]
[ MAIN <Name> ]
[ PARTITION BY ( <Cols> ) ]
DIMENSION BY ( <Cols> )
MEASURES ( <Cols> )
[ <reference options> ]
RULES [ <Rule options> ]
[ ITERATE ( <Num> )[ UNTIL ( <Condition> ) ] ]
( [ <Rule>, ... ] )
以上です