いまさらだけど、SQL MODEL句の話をしよう (Oracle)

## はじめに

表題の通り、SQLクエリでのOracle独自の拡張句であるMODELです。行間参照機能としては最強のアレです。覚えておくとなにかと便利なんですけど、ぜんぜん使われてないっていうかほとんど居ない子扱いですよね。うん、まぁMODEL句の文法を見てもらえばなんとなくわかると思いますけど、MODEL句って文法的にSELECTにくっついてるとはいえ、一般的なクエリ部分からは分離されてて、記述的にも動作的にもほとんどSQLじゃ無いんです。なので「こんなん追加で覚えるくらいならアプリ側で処理するわ」、...ってなっちゃうのも分かります。

```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句をつかった一番簡単なクエリです。

```sql
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'``に変わりました。

```sql
SELECT * FROM DUAL
MODEL DIMENSION BY (0 n) MEASURES ('A' c)
      RULES (c[0]='B');

         N C
---------- -
         0 B		<-- Cの値がBに変わった
```

では今度は、存在しないセル``c[1]``に値を代入してみましょう。

```sql

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``と記述することでこの動きを指定することができます。

```sql
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直後に記述します。

```sql
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でも行は追加されません。**

```sql
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は値を一つづつ処理していくのでそれぞれが一意値として処理されるのです。

```sql
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のどの配列でも使えますが**ネスト参照は一階層のみです**。(つまり配列のネストなかで配列のネストは使用不可)。ちなみにネスト参照は右辺でも使用できます。

```sql
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``で追加を行うというものです。うーん。わかりにくいですね。簡単な例を示します。

```sql
SELECT * FROM t;

        LN ITEM          QTY
---------- ------ ----------
       100 Apple          20
       200 Apple          20
       100 Orange         50
```

このテーブルに対して、LNとITEMを添え字に取りLNを評価式、ITEMを一意値として``UPSERT``代入をします。LNが100のときに適合してQTYが更新されていますが、評価式のため``UPSERT``でも行の追加はされません。

```sql
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は全く存在しない値なので行は追加されません。

```sql
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)を代入しています。結果はそのままですね。添字と同じ値になりました。

```sql
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
```

では、次は適当な値を持つ行をつくってから、そのカラムの値を二倍にしてみましょう。

```sql
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のような動きをしています。

```sql
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``は行間参照としてよく使われる方法ですが、添字となるカラムの値が抜けなく存在している必要があります。したがってこの手の計算を行う場合は、抜けのない行番号を別途作って添字として使用するのが一般的です。

```sql
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順で一つ前の値を取得しますが、最初のセルには最後のセルの値を持ってきて値をループさせてください。ただしテーブル行数は可変であるとすること。

```sql
        ID      VALUE       PREV
---------- ---------- ----------
         1        100        600	<-- ここに最大IDのVALUEの値が入る
         6        500        100
        10        300        500
        20        600        300
```

#### 解答1

やりかたはいろいろありますが、最初に行の最大数を計算しておけば``value[mc[cv()]]``で最大RNのセルの値にアクセスできますね。

```sql
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への代入で一つ前のセルを自己参照しようとしていますが、添字の処理順が指定されておらず結果が不定となるためエラーです。添字の値順で処理すると決まってるわけではないですからね。

```sql
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``句を使用します。これでランニング集計が正しく行われます。

```sql
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``ですね？

```sql
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
```

> 分析関数の詳細については、以下を参照してください。
>
> [分析関数（ウインドウ関数）をわかりやすく説明してみた](https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5)

またRULES内で分析関数を使用する場合いくつか制限があります。まず、左辺の添字に少なくとも１つ「評価式」を含まなければいけません。添字が一意値のみであった場合、エラーとなります。そしてさらに、分析関数の計算対象となるセルは左辺の評価式で適合した行のみです。この範囲を超えて計算することはできません。

以下の例では、集合関数、分析関数とも``id in (0, 1)``で最初の２行のみを代入更新対象としており、すべてのVALUEカラムセルに対してMAX関数を使用していますが、異なった計算結果となっています。

```sql
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と共に使用すると非常に有用です。

```sql

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句を実行します。

```sql
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を使って他テーブルにアクセスし、それで得たデータを使ってカンマ区切り文字列に戻す、、、みたいなことをやってみます。

```sql
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>``と宣言しなければなりません。

```sql
      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句で代入更新された行のみを返します。 |

```sql
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等の関数を使用する必要がなくなります。

```sql
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)のカラムで値がセルが一意に決まらない場合、デフォルトではエラーとなります。

```sql
 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`` オプションを使用すると重複添字が許可されます。

```sql
SELECT * FROM t
MODEL DIMENSION BY (n) MEASURES (item)
      UNIQUE SINGLE REFERENCE
      RULES (item[1] = 'Kiwi');

         N ITEM
---------- ------
         2 Apple
         1 Kiwi
         1 Kiwi
```

しかし、その場合右辺が複数セルを返してしまうとエラーとなります。

```sql
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
```

従ってそうならないように気をつけなければなりません。

```sql
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``では、代入式は上から下に記述順に実行されます。

```sql
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``になっています。便利な機能ではあるのですが、ある意味混乱の元です。オラクルに任せるよりは しっかり 依存関係を把握した上でコーディングすべきでしょう。まぁ、あるセルが書き換わったことを条件に意図的に順序を変更するなどというテクニックもあるのかもしれませんが、そんなコード書いたら保守が大変ですよね。

```sql
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で追加された行なので元から存在するセルとは見なされていません。

```sql

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等で用いられます。

```sql
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``を代入式に追加すると遅くなってしまうため除外しています。おそらくセルのネスト参照には多少のオーバーヘッドが伴うものと思われます。

> [素数を単一SQLクエリで計算するいくつかの方法とパフォーマンス](https://qiita.com/tlokweng/items/f33647e02feee92bfe46)

```sql
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の最も得意とするところです。

> [オラクルの単一SQLクエリでパスカルの三角形をいくつか](https://qiita.com/tlokweng/items/29c92e97c036682db3ab)

```sql
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;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179587/9de069e3-e84b-6a45-9bd4-784d64a9faed.png)


## おわりに

MODEL句を使うと本来SQLでできないようなことがチャッチャッとできるようになります。まぁ多くの場合再帰でも置き換えが効くようになりましたが、再帰よりは高速ですので覚えておくのも一興かと。

なんにせよ、MODEL句に関した記述はオラクルのマニュアルや他のサイトでもとても少なく、使いながら手探りで掴んで行くしかありません。そういうわけで、上記説明に間違いがあった場合はコメントしていただけるとありがたいです。

最後にMODELのシンタックスを置いておきます。

```sql

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>, ... ] )
```



以上です