OracleSQLパズル   明智重蔵のブログ   明智重蔵のTwitter   記事(OTN)   記事(CodeZine)

分析関数とmodel句


概要

Oracle OpenWorld 2009 Tokyo の"Unconference"の、4月23日(木)の15:00-15:30の枠
で私が、オーガナイザ(講師)をさせていただきまして、そのまとめページです。

OTN-JapanUS-OTNのフォーラムでのSQLの問題を解くのに、使うことの多い、
分析関数とmodel句について、私の思考法や、脳内のイメージを解説しました。

2007年にLaurent Schneiderさんがスピーカーをされた"SQL Model"を意識した内容と、
分析関数の衝撃のまとめ的な内容を扱いました。単純なサンプル中心のセッションにしました。


プログラム

第1部 分析関数
 1. 分析関数とは
 2. 分析関数の例1 select文の件数取得
 3. 分析関数の例2 最大値の行の取得
 4. 分析関数の例3 順位を付ける
 5. 分析関数の例4 前後の行の値
 6. 分析関数の例5 累計
 7. 分析関数の例6 移動累計
 8. 分析関数の例7 連続範囲の最小値と最大値
 9. 分析関数の例8 全称肯定,全称否定,存在肯定,存在否定
10. 分析関数の参考リソース

第2部 model句
 1. model句とは
 2. HelloWorldなどの簡単なサンプル
 3. model句の例1 集計行の追加
 4. model句の例2 pivotをあえてmodel句で
 5. model句の例3 all_objectsやall_catalogやdictの代用
 6. model句の例4 Partitioned Outer Joinもどき
 7. model句の例5 count(distinct Val) over(order by SortKey)の代用
 8. model句の例6 wmsys.wm_concatの代用
 9. model句の例7 group_concatもどき
10. model句の参考リソース


第1部   1. 分析関数とは

select句とorder by句で使うことができる。(一応、model句でも分析関数は使えます)
order by句で使うことは、ほとんどない。

Oracle9iから(厳密には、8.1.6 EnterpriseEdition)から使用可能
もちろん、Oracle10g,Oracle11g,OracleXEでも使用可能

SQLServer2005以降
DB2 Express
PostgreSQL8.4以降
などでも使用可能

分析関数は標準SQLなので、いずれは、他のDBでも使えるようになるはずです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数のメリット

Oracle8iまでは、自己結合や相関サブクエリを使ったり、親言語(JavaやVB6など)やPL/SQLで、
求めていた結果を、SQLで容易に求めることができるようになります。
帳票作成やデータ分析で特に使います。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数の使用頻度

最頻出
count   max   min   sum   Row_Number

頻出
Lag   Lead   Rank   dense_rank

たまに
avg   Last_Value   First_Value   wmsys.wm_concat

レア
Ratio_To_Report   median   NTile   regr_count


分析関数の例1 select文の件数取得

select文の結果の件数が欲しいといったことは、結構あります。そんな時に使うのが、分析関数のcount関数です。

create table TestTable(ID,Val) as
select 1,10 from dual union
select 1,20 from dual union
select 2,10 from dual union
select 2,30 from dual union
select 2,50 from dual;

-- OLAPSample1
select ID,Val,
count(*) over() as recordCount
  from TestTable;

-- OLAPSample1のサブクエリを使った代替方法
select ID,Val,
(select count(*) from TestTable b) as recordCount
  from TestTable a;

ID  Val  recordCount
--  ---  -----------
 1   10            5
 1   20            5
 2   10            5
 2   30            5
 2   50            5

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
-- OLAPSample2
select ID,Val,
count(*) over() as recordCount
  from TestTable
 where Val in(10,20);

 ID  Val  recordCount
---  ---  -----------
  1   10            3
  1   20            3
  2   10            3

-- OLAPSample3
select ID,max(Val),
count(*) over() as recordCount
  from TestTable
group by ID;

ID  MAX(VAL)  recordCount
--  --------  -----------
 1        20            2
 2        50            2


分析関数の例2 最大値の行の取得

実によく見かける、定番問題です。IDごとに、Valが最大値の行を取得します。

create table TestTable2(ID,Val,extraCol) as
select 1,10,'A' from dual union all
select 1,20,'B' from dual union all
select 2,10,'C' from dual union all
select 2,30,'D' from dual union all
select 2,50,'E' from dual union all
select 3,70,'F' from dual union all
select 3,70,'G' from dual;

-- 単純に、IDごとのValの最大値が欲しいなら、これで可
select ID,max(Val)
  from TestTable2
group by ID;

-- OLAPSample4
select ID,Val,extraCol
from (select ID,Val,extraCol,
      max(Val) over(partition by ID) as maxVal
        from TestTable2)
 where Val = maxVal;

-- OLAPSample4の相関サブクエリを使った代替方法
select ID,Val,extraCol
  from TestTable2 a
 where Val = (select max(b.Val)
                from TestTable2 b
               where b.ID = a.ID);

ID  Val  extraCol
--  ---  --------
 1   20  B
 2   50  E
 3   70  F
 3   70  G

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数が使えるのは、select句かorder by句です。
なので、分析関数の結果をwhere句で使うには、インラインビューを使う必要があります。

脳内のイメージは、下のようになります。partition by は、脳内で赤線を引くと分かりやすいです。



分析関数の例3 順位を付ける

順位や連番を付けたい時に使うのが、分析関数のRow_Number関数,rank関数,dense_rank関数です。

create table TestTable3(ID,score) as
select 1,100 from dual union all
select 1,100 from dual union all
select 1, 90 from dual union all
select 1, 80 from dual union all
select 2,100 from dual union all
select 2, 70 from dual union all
select 2, 70 from dual;

-- OLAPSample5
select ID,score,
Row_Number() over(partition by ID order by score desc) as "Row_Number",
      rank() over(partition by ID order by score desc) as "rank",
dense_rank() over(partition by ID order by score desc) as "dense_rank"
  from TestTable3
order by ID,score desc;

ID  score  Row_Number  rank  dense_rank
--  -----  ----------  ----  ----------
 1    100           1     1           1
 1    100           2     1           1
 1     90           3     3           2
 1     80           4     4           3
 2    100           1     1           1
 2     70           2     2           2
 2     70           3     2           2

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Row_Number関数は、RowNum擬似列と名前が似ていることから分かるとおり
1から始まって、必ず連番になります。

-- RowNum擬似列の使用例
select ID,score,RowNum
from (select ID,score from TestTable3
      order by score desc)
order by score desc;

ID  score  ROWNUM
--  -----  ------
 1    100       1
 1    100       2
 2    100       3
 1     90       4
 1     80       5
 2     70       6
 2     70       7

rank関数は、同点があると順位が飛びます。
dense_rank関数は、同点があっても順位が飛びません。denseは、形容詞で、密集したという意味です。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


分析関数の例4 前後の行の値

指定したソートキーでの、前後の行の値が欲しい時に使われるのが、Lag関数とLead関数です。

create table TestTable4(ID,sortKey,Val) as
select 'AA',1,10 from dual union all
select 'AA',3,20 from dual union all
select 'AA',5,60 from dual union all
select 'AA',7,30 from dual union all
select 'BB',2,40 from dual union all
select 'BB',4,80 from dual union all
select 'BB',6,50 from dual;

-- OLAPSample6
select ID,sortKey,Val,
Lag(Val)  over(partition by ID order by sortKey) as Prev,
Lead(Val) over(partition by ID order by sortKey) as Next
  from TestTable4
order by ID,sortKey;

ID  sortKey  Val  Prev  Next
--  -------  ---  ----  ----
AA        1   10  null    20
AA        3   20    10    60
AA        5   60    20    30
AA        7   30    60  null
BB        2   40  null    80
BB        4   80    40    50
BB        6   50    80  null

-- OLAPSample6の相関サブクエリを使った代替方法
以降、割愛します。興味がある方は、MySQLで分析関数を模倣シリーズを御覧になって下さい。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

Lag関数とLead関数は、キーブレイクの事前検知にも使えます。
ID列,sortKey列をソートキー、
ID列をブレークキーとして、
キーブレイクを検知してみます。
(case式を使うのが定番ですが、これは、case式を使わなくていいパターンです)

-- case式を使う定番の方法
select ID,sortKey,Val,
case when ID = Lag(ID)  over(order by ID,SortKey) then 0 else 1 end as IsKeyBreaked,
case when ID = Lead(ID) over(order by ID,SortKey) then 0 else 1 end as willKeyBreak
  from TestTable4
order by ID,sortKey;

-- OLAPSample7
select ID,sortKey,Val,
Lag (0,1,1) over(partition by ID order by sortKey) as IsKeyBreaked,
Lead(0,1,1) over(partition by ID order by sortKey) as willKeyBreak
  from TestTable4
order by ID,sortKey;

ID  sortKey  Val  IsKeyBreaked  willKeyBreak
--  -------  ---  ------------  ------------
AA        1   10             1             0
AA        3   20             0             0
AA        5   60             0             0
AA        7   30             0             1
BB        2   40             1             0
BB        4   80             0             0
BB        6   50             0             1

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
キーブレイクの事前検知は、分析関数のcount関数を使ってもいいですが、
否定条件となってしまったため、分かりにくい気がします。

-- OLAPSample8
select ID,sortKey,Val,
count(*) over(partition by ID order by sortKey
              Rows between 1 preceding
                       and 1 preceding) as IsNotKeyBreaked,
count(*) over(partition by ID order by sortKey
              Rows between 1 following
                       and 1 following) as willNotKeyBreak
  from TestTable4
order by ID,sortKey;

ID  sortKey  Val  IsNotKeyBreaked  willNotKeyBreak
--  -------  ---  ---------------  ---------------
AA        1   10                0                1
AA        3   20                1                1
AA        5   60                1                1
AA        7   30                1                0
BB        2   40                0                1
BB        4   80                1                1
BB        6   50                1                0

windowing_clause(ウィンドウイング_クロウズ)は、下記のように解釈すると分かりやすいと思います。

order by sortKey -- sortKeyの昇順で、
Rows between     -- 行の範囲は、
1 preceding      -- 1行前から
and 1 preceding  -- 1行前まで

order by sortKey -- sortKeyの昇順で、
Rows between     -- 行の範囲は、
1 following      -- 1行先から
and 1 following  -- 1行先まで


分析関数の例5 累計

帳票で累計を求めたい時に使うのが、order byを指定した、分析関数のsum関数です。

create table ValTable(ID,sortKey,Val) as
select 'AA',1,10 from dual union all
select 'AA',2,20 from dual union all
select 'AA',3,40 from dual union all
select 'AA',4,80 from dual union all
select 'BB',1,10 from dual union all
select 'BB',2,30 from dual union all
select 'BB',3,90 from dual union all
select 'CC',1,50 from dual union all
select 'CC',2,60 from dual union all
select 'CC',2,60 from dual;

-- OLAPSample9
select ID,sortKey,Val,
sum(Val) over(partition by ID order by sortKey) as runSum
  from ValTable
order by ID,sortKey;

ID  sortKey  Val  runSum
--  -------  ---  ------
AA        1   10      10
AA        2   20      30
AA        3   40      70
AA        4   80     150
BB        1   10      10
BB        2   30      40
BB        3   90     130
CC        1   50      50
CC        2   60     170
CC        2   60     170

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
order byを指定して、windowing_clauseを省略すると、デフォルトの、
Range between unbounded preceding
          and current rowになります。

windowing_clause(ウィンドウイング_クロウズ)は、下記のように解釈すると分かりやすいと思います。

order by sortKey    -- sortKeyの昇順で、
Range between       -- 値の範囲は、
unbounded preceding -- 前方は、際限なく、
and current row     -- 後方は、カレント行まで

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


分析関数の例6 移動累計

OracleSQLパズルのアクセス解析で、移動平均で検索してくる方は、結構多いです。4日移動累計を求めてみましょう。

create table idouT(day1,Val) as
select date '2009-04-16', 10 from dual union all
select date '2009-04-17', 20 from dual union all
select date '2009-04-20', 40 from dual union all
select date '2009-04-21', 80 from dual union all
select date '2009-04-22',160 from dual union all
select date '2009-04-23',320 from dual union all
select date '2009-04-24',640 from dual;

col BaseOfMoveSum1 for a18
col BaseOfMoveSum2 for a18

-- OLAPSample10
select to_char(day1,'yyyy-mm-dd') as day1,Val,
sum(Val) over(order by day1 rows  3 preceding) as moveSum1,
sum(Val) over(order by day1 range 3 preceding) as moveSum2,
wmsys.wm_concat(to_char(day1,'dd'))
over(order by day1 rows  3 preceding) as BaseOfMoveSum1,
wmsys.wm_concat(to_char(day1,'dd'))
over(order by day1 range 3 preceding) as BaseOfMoveSum2
  from idouT
order by day1;

day1        Val  moveSum1  moveSum2  BaseOfMoveSum1  BaseOfMoveSum2
----------  ---  --------  --------  --------------  --------------
2009-04-16   10        10        10  16              16
2009-04-17   20        30        30  16,17           16,17
2009-04-20   40        70        60  16,17,20        17,20
2009-04-21   80       150       120  16,17,20,21     20,21
2009-04-22  160       300       280  17,20,21,22     20,21,22
2009-04-23  320       600       600  20,21,22,23     20,21,22,23
2009-04-24  640      1200      1200  21,22,23,24     21,22,23,24

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数のwmsys.wm_concatは、Oracle11gのマニュアルにすら載ってないのですが、
開発時のデバッグやテストなどで使い道はあります。

windowing_clause(ウィンドウイング_クロウズ)の省略時の仕様として、
order by day1 rows 3 precedingは、
order by day1 rows between 3 preceding
                       and current row
と同じ扱いとなります。

同様に、
order by day1 range 3 precedingは、
order by day1 range between 3 preceding
                        and current row
と同じ扱いとなります。

rows指定とrange指定の違いは、
rowsは、ソートキーで並べた時の、前もしくは後ろの行数(Rows)の指定であり、
rangeは、ソートキーが、どれだけ前もしくは後ろかの指定であることです。

それぞれ、下記のように解釈すると分かりやすいと思います。

order by day1   -- day1の昇順で、
rows between    -- 行の範囲は、
3 preceding     -- 3行前から
and current row -- カレント行まで

order by day1   -- day1の昇順で、
range between   -- 値の範囲は、
3 preceding     -- 3日前から
and current row -- カレント行まで

なお、day1はdate型ですので、
sum(Val) over(order by day1 range 3 preceding) as moveSum2
は、下記のように
sum(Val) over(order by day1 range interval '3' day preceding) as moveSum2
とも記述できます。

-- OLAPSample11
select to_char(day1,'yyyy-mm-dd') as day1,Val,
sum(Val) over(order by day1 range interval '3' day preceding) as moveSum2
  from idouT
order by day1;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


分析関数の例7 連続範囲の最小値と最大値

US-OTNでよく見かける問題である、連続範囲の最小値と最大値を求めるSQLです。
分析関数の応用例として有名なものだと思います。

create table seqTable(ID,seq) as
select 'AA',1 from dual union all
select 'AA',2 from dual union all
select 'AA',3 from dual union all
select 'AA',6 from dual union all
select 'AA',7 from dual union all
select 'BB',1 from dual union all
select 'BB',3 from dual union all
select 'BB',4 from dual union all
select 'BB',6 from dual union all
select 'BB',7 from dual union all
select 'CC',1 from dual union all
select 'CC',5 from dual union all
select 'DD',1 from dual union all
select 'DD',5 from dual;

-- OLAPSample12 Lag関数でシーケンス開始を検知
select ID,min(seq) as minSeq,max(seq) as maxSeq
from (select ID,seq,sum(willSum) over(partition by ID order by seq) as GID
      from (select ID,seq,
            case when Lag(seq) over(partition by ID order by seq) = seq-1
                 then 0 else 1 end as willSum
              from seqTable))
group by ID,GID
order by ID,GID;

-- OLAPSample13 旅人算の感覚を使う
select ID,min(seq) as minSeq,max(seq) as maxSeq
from (select ID,seq,
      seq-Row_Number() over(partition by ID order by seq) as distance
        from seqTable)
group by ID,distance
order by ID,distance;

ID  minSeq  maxSeq
--  ------  ------
AA       1       3
AA       6       7
BB       1       1
BB       3       4
BB       6       7
CC       1       1
CC       5       5
DD       1       1
DD       5       5

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLのロジックの解説は、割愛します。興味がある方は、下記を御覧になって下さい。
分析関数の衝撃3 (後編) (Lag関数でシーケンス開始を検知)
分析関数の衝撃5 (総集編) (旅人算の感覚を使う)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数でシーケンス開始を検知する脳内のイメージは、下のようになります。
Lag関数でシーケンス開始を検知

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う脳内のイメージは、下のようになります。
旅人算の感覚を使う


分析関数の例8 全称肯定,全称否定,存在肯定,存在否定

これもUS-OTNでよく見かける問題です。

・全ての行が条件を満たすか?
・全ての行が条件を満たさないか?
・少なくとも1行が条件を満たすか?
・少なくとも1行が条件を満たさないか?

といった複数行にまたがったチェックをしたい時に使います。

create table TestTable8(ID,Val not null) as
select 'AA',10 from dual union all
select 'AA',20 from dual union all
select 'BB',10 from dual union all
select 'BB',30 from dual union all
select 'BB',50 from dual union all
select 'CC',80 from dual union all
select 'CC',90 from dual union all
select 'DD',20 from dual union all
select 'DD',70 from dual;

・check1 IDごとで、全ての行が Val<40 を満たすか?
・check2 IDごとで、全ての行が Val<40 を満たさないか?
・check3 IDごとで、少なくとも1つの行が Val<40 を満たすか?
・check4 IDごとで、少なくとも1つの行が Val<40 を満たさないか?

をチェックしてみましょう。

select ID,Val,
min(case when Val<40 then 1 else 0 end) over(partition by ID) as chk1,
min(case when Val<40 then 0 else 1 end) over(partition by ID) as chk2,
max(case when Val<40 then 1 else 0 end) over(partition by ID) as chk3,
max(case when Val<40 then 0 else 1 end) over(partition by ID) as chk4
  from TestTable8
order by ID,Val;

ID  Val  chk1  chk2  chk3  chk4
--  ---  ----  ----  ----  ----
AA   10     1     0     1     0
AA   20     1     0     1     0
BB   10     0     0     1     1
BB   30     0     0     1     1
BB   50     0     0     1     1
CC   80     0     1     0     1
CC   90     0     1     0     1
DD   20     0     0     1     1
DD   70     0     0     1     1

SQLのロジックの解説は、割愛します。興味がある方は、分析関数の衝撃4 (完結編)を御覧になって下さい。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
全称肯定,全称否定,存在肯定,存在否定の
SQLへの変換公式は下記となります。

全称 → min
存在 → max

肯定 → (case when 条件 then 1 else 0 end)
否定 → (case when 条件 then 0 else 1 end)

まとめると
全称肯定命題なら min(case when 条件 then 1 else 0 end) = 1
全称否定命題なら min(case when 条件 then 0 else 1 end) = 1
存在肯定命題なら max(case when 条件 then 1 else 0 end) = 1
存在否定命題なら max(case when 条件 then 0 else 1 end) = 1

真なら1、偽なら0にしておくことにより、ブール値としても使えます。


分析関数の参考リソース

書籍
達人に学ぶ SQL徹底指南書
Oracle SQLクイズ
SQLクックブック

Web
CodeZine 分析関数の衝撃

OTN-Japan
製品情報 - Oracle8i データウェアハウス


第2部   1. model句とは

ExcelやPL/SQLの結合配列のように、SQLの結果セットを扱うことができる。
having句の次に、model句が評価されるようです。

Oracle10gから使用可能(Oracle10g,Oracle11g,OracleXE)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
model句のメリット

Oracle9iまでは、分析関数を何度も使ったり、Union allを使ったり、親言語(JavaやVB6など)やPL/SQLで、
求めていた値を、SQLで容易に求めることができるようになります。

model句の使用例で代表的なのは、
・集計行の追加
・count(distinct Val) over(order by SortKey)の代用
・wmsys.wm_concatの代用
・group_concatもどき

のようなSQLを作成する時です。


HelloWorldなどの簡単なサンプル

まずは、簡単なサンプルを見てみましょう。

-- modelSample1
select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
 model
 dimension by (soeji)
 measures(ArrValue)
 rules(ArrValue[1] = 'Hello World');

ArrValue     soeji
-----------  -----
Hello World      1

それぞれの文法について説明しましょう。

-------------------------------------------------------------------------------------
・model
model句を使用する際のキーワードです。
model句を使用するには必須です。

-------------------------------------------------------------------------------------
・dimension by
dimensionは、次元という意味です。
双子座(ジェミニ)のサガの必殺技のアナザーディメンションは、別の次元に送り飛ばす技です ;-)

PL/SQLやJavaやRubyやPerlなどにおける、配列の添字だと理解しておいて問題ないでしょう。
Fortranではdimensionキーワードを使って配列を定義するようです。
model句を使用するには必須です。

-------------------------------------------------------------------------------------
・measures
配列が、どんな値の配列かを指定します。
model句を使用するには必須です。

-------------------------------------------------------------------------------------
・rules
配列の値を操作する式を記述します。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

それでは、SQLを解説しましょう。

-- modelSample1
select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
 model
 dimension by (soeji)
 measures(ArrValue)
 rules(ArrValue[1] = 'Hello World');

ArrValue     soeji
-----------  -----
Hello World      1

まずインラインビューの実行結果を見てみましょう。

select 'abcdefghijklmn' as ArrValue,1 as soeji from dual;

ArrValue        soeji
--------------  -----
abcdefghijklmn      1

次に、
 model
 dimension by (soeji)
 measures(ArrValue)
によって、
soejiを添字として、ArrValueを配列値とした、配列が作成されるイメージです。
そして、
rules(ArrValue[1] = 'Hello World')
によって、ArrValue[1]の値が、Hello Worldに上書きされるイメージです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
サンプル2も見てみましょう。

-- modelSample2
select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
 model
 dimension by (soeji)
 measures(ArrValue)
 rules(ArrValue[1] = 'Hello World',
       ArrValue[2] = 'Hello Model');

ArrValue     soeji
-----------  -----
Hello World      1
Hello Model      2

rulesは、デフォルトでupsert(あればupdate,なければinsert)です。
なので、
ArrValue[1] = 'Hello World' によって、ArrValue[1]がupdateされ、
ArrValue[2] = 'Hello Model' によって、ArrValue[2]がinsertされます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
サンプル3も見てみましょう。

-- modelSample3
select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
 model
 dimension by (soeji)
 measures(ArrValue)
 rules(ArrValue[3] = 'Hello Oracle');

ArrValue        soeji
--------------  -----
abcdefghijklmn      1
Hello Oracle        3

配列の添字は、連続してなくても問題ないのです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
サンプル4も見てみましょう。

-- modelSample4
select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
 model return updated rows
 dimension by (soeji)
 measures(ArrValue)
 rules(ArrValue[4] = 'Hello OOW2009');

ArrValue       soeji
-------------  -----
Hello OOW2009      4

model return updated rowsとすると、
rules句によって、更新(updateかinsert)された行のみ出力することができます。


model句の例1 集計行の追加

model句の代表的な使い道の1つです。
Oracle9iまでは、行の追加は、
union all,rollup,grouping sets,連番表とのクロスジョイン,表関数などを使って行われてましたが、
Oracle10gからは、model句で行の追加が行えます。

create table modelTest1(ID,Val) as
select 1,10 from dual union all
select 2,20 from dual union all
select 3,40 from dual union all
select 4,80 from dual;

集計行も表示してみましょう。

-- modelSample5
select ID,Val
  from modelTest1
 model
 dimension by(ID)
 measures(Val)
 rules(
 Val[null] = Val[1]+Val[2]+Val[3]+Val[4]);

  ID  Val
----  ---
   1   10
   2   20
   3   40
   4   80
null  150

-- modelSample5の代替方法
select ID,sum(Val) as Val
  from modelTest1
group by rollup(ID);

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
IDが3または4のみを集計対象として、集計行を表示してみましょう。

-- modelSample6
select ID,Val
  from modelTest1
 model
 dimension by(ID)
 measures(Val)
 rules(
 Val[null] = Val[3]+Val[4]);

  ID  Val
----  ---
   1   10
   2   20
   3   40
   4   80
null  120

-- modelSample6の代替方法
select ID,
case grouping(ID) when 0 then sum(Val)
     else sum(case when ID in(3,4) then Val end)
end as Val
  from modelTest1
group by rollup(ID);

例から分かるとおり、model句による行の追加は、他の方法と比べてシンプルになることが多いです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


model句の例2 pivotをあえてmodel句で

Pivotをあえて、model句を使ってやってみましょう。

create table modelPivot(ID,seq,Val) as
select 10,'01','AAAAA' from dual union all
select 10,'02','BBBBB' from dual union all
select 10,'03','CCCCC' from dual union all
select 20,'01','DDDDD' from dual union all
select 20,'02','EEEEE' from dual union all
select 30,'02','FFFFF' from dual union all
select 30,'03','GGGGG' from dual;

-- 一般的な方法
select ID,
max(decode(seq,'01',Val)) as Val01,
max(decode(seq,'02',Val)) as Val02,
max(decode(seq,'03',Val)) as Val03
  from modelPivot
group by ID
order by ID;

ID  Val01  Val02  Val03
--  -----  -----  -----
10  AAAAA  BBBBB  CCCCC
20  DDDDD  EEEEE  null
30  null   FFFFF  GGGGG

-- modelSample7
select ID,Val01,Val02,Val03
  from modelPivot
 model return updated rows
 partition by (ID)
 dimension by (seq)
 measures(Val as Val01,Val as Val02,Val as Val03)
 rules(Val02['01'] = Val02['02'],
       Val03['01'] = Val03['03'])
order by ID;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
partition by (ID)
というのは、rules句が行う処理をパーティションごとに分割する
という意味となります。

dimension by (seq)
でseqを添字とし、
Valをそれぞれ、Val01,Val02,Val03として複製し、
後は、rules句で、添字が01の行に、必要な値を集めてます。

model return updated rowsを指定してますので、更新された行のみ出力されます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。partition by は、脳内で赤線を引くと分かりやすいです。


model句の例3 all_objectsやall_catalogやdictの代用

1以上の整数の連番が欲しいことは、たまにあります。
model句の機能を使って、連番を取得してみます。

-- modelSample8
select soeji,dummy
  from dual
 model
 dimension by (1 as soeji)
 measures(0 as dummy)
 rules iterate (5)
 (dummy[iteration_number+1] = 0);

soeji  dummy
-----  -----
    1      0
    2      0
    3      0
    4      0
    5      0

rules iterate (5)
と記述すると、rulesが5回評価されます。
そして、ruleが1回評価されるごとに、インクリメントされる値として、iteration_numberがあります。

iteration_numberは、0から開始されます。
ちなみに、プログラム言語のfor文のループ変数でよく使われるiは、iterationの頭文字が由来だそうです。

for (int i=0;i<=10;i++) //Javaのforループ

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
rules iterate を使わずに、forコンストラクトで代用することもできます。

select soeji,dummy
  from dual
 model
 dimension by (1 as soeji)
 measures(0 as dummy)
 rules(
 dummy[for soeji from 1 to 5 increment 1]= 0);

soeji  dummy
-----  -----
    1      0
    2      0
    3      0
    4      0
    5      0


model句の例4 Partitioned Outer Joinもどき

Oracle10gから、Partitioned Outer Joinという新機能が追加されました。
パーティション化された外部結合とも呼ばれます。

create table LPOTable(ID,Key,Name) as
select 'AA',10,'aaaa' from dual union all
select 'AA',20,'bbbb' from dual union all
select 'BB',10,'cccc' from dual union all
select 'CC',10,'dddd' from dual union all
select 'CC',30,'eeee' from dual;

-- Partitioned Outer Join
select b.ID,a.hokanKey,b.Name
  from (select 10 as hokanKey from dual union all
        select 20 from dual union all
        select 30 from dual) a
  Left Outer Join LPOTable b
  partition by (b.ID)
  on (a.hokanKey = b.Key)
order by b.ID,a.hokanKey;

ID  hokanKey  Name
--  --------  ----
AA        10  aaaa
AA        20  bbbb
AA        30  null
BB        10  cccc
BB        20  null
BB        30  null
CC        10  dddd
CC        20  null
CC        30  eeee

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Partitioned Outer Joinを使うと、
パーティションを切ってできた、それぞれの集合と外部結合させることができます。

脳内のイメージは、下のようになります。partition by は、脳内で赤線を引くと分かりやすいです。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
それでは、model句を使って、同じ結果を取得してみましょう。

-- modelSample9
select ID,Key as hokanKey,Name
  from LPOTable
 model
 partition by(ID)
 dimension by(Key)
 measures(Name)
 rules(
 Name[10] = Name[cv()],
 Name[20] = Name[cv()],
 Name[30] = Name[cv()])
order by ID,Key;

cv関数は、dimension byで指定した添字を引数に取って、rules句の左辺で指定した添字を返します。
角括弧の中であれば、cv関数の引数は省略可能になります。
例えば、Name[10] = Name[cv()]
は、    Name[10] = Name[cv(Key)]  と同じ意味です。

partition by(ID)
dimension by(Key)
rules(
Name[10] = Name[cv()],
Name[20] = Name[cv()],
Name[30] = Name[cv()])
によって、IDごとで、
Keyが10の値があれば、Nameの値を上書きで代入、なければ、Nameの値をnullに設定
Keyが20の値があれば、Nameの値を上書きで代入、なければ、Nameの値をnullに設定
Keyが30の値があれば、Nameの値を上書きで代入、なければ、Nameの値をnullに設定
といった処理が行われます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

rules句の左辺での、添字の指定方法には、下記のような指定方法もあります。

-- modelSample10
select ID,Key as hokanKey,N1,N2,N3
  from LPOTable
 model
 partition by(ID)
 dimension by(Key)
 measures(Name as N1,Name as N2,Name as N3)
 rules(
 N1[for Key in(10,20,30)] = N1[cv()],
 N2[for Key from 10 to 30 increment 10] = N2[cv()],
 N3[for Key in(select * from table(sys.odciNumberList(10,20,30)))] = N3[cv()])
order by ID,Key;

ID  hokanKey  N1    N2    N3
--  --------  ----  ----  ----
AA        10  aaaa  aaaa  aaaa
AA        20  bbbb  bbbb  bbbb
AA        30  null  null  null
BB        10  cccc  cccc  cccc
BB        20  null  null  null
BB        30  null  null  null
CC        10  dddd  dddd  dddd
CC        20  null  null  null
CC        30  eeee  eeee  eeee

N1[for Key in(10,20,30)] は、値を列挙してます。
N2[for Key from 10 to 30 increment 10] は、forコンストラクトで初期値と終了値と増分を指定してます。
N3[for Key in(select * from table(sys.odciNumberList(10,20,30)))] は、select文の結果を使用してます。

下記のように、多次元配列でもselect文の結果を使用できます。

-- modelSample11
select *
  from dual
 model
 dimension by(0 as key1,0 as key2)
 measures(0 as dummy)
 rules(
 dummy[for (key1,key2) in(select 10,50 from dual)] = 0);

key1  key2  dummy
----  ----  -----
   0     0      0
  10    50      0


model句の例5 count(distinct Val) over(order by SortKey)の代用

US-OTNで結構見かける問題です。訪問者が、リピーターか新規かを見分ける時に使うSQLのようです。

create table VisiterT(SortKey,Visit) as
select 10,'AAAA' from dual union all
select 20,'BBBB' from dual union all
select 30,'AAAA' from dual union all
select 40,'BBBB' from dual union all
select 50,'CCCC' from dual union all
select 60,'CCCC' from dual union all
select 70,'DDDD' from dual union all
select 80,'AAAA' from dual;

SQL> select SortKey,Visit,
  2  count(distinct Visit) over(order by SortKey) as disVisit
  3    from VisiterT
  4  order by SortKey;
count(distinct Visit) over(order by SortKey) as disVisit
                         *
行2でエラーが発生しました。: ORA-30487: ここでORDER BYは使用できません。

分析関数でdistinctオプションを使用して、order byを指定するとORA-30487エラーになります。
どうにかして同じ結果を取得してみましょう。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
-- modelSample12の分析関数を使った代替方法
select SortKey,Visit,sum(willSum) over(order by SortKey) as disVisit
from (select SortKey,Visit,
      case Row_Number() over(partition by Visit order by SortKey)
      when 1 then 1 else 0 end as willSum
        from VisiterT)
order by SortKey;

-- modelSample12の相関サブクエリを使った代替方法
select SortKey,Visit,
(select count(distinct b.Visit)
   from VisiterT b
  where b.SortKey <= a.SortKey) as disVisit
  from VisiterT a
order by SortKey;

-- modelSample12
select SortKey,Visit,disVisit
  from VisiterT
 model
 dimension by(SortKey)
 measures(Visit,0 as disVisit)
 rules(
 disVisit[any] = count(distinct Visit)[SortKey <= cv()])
order by SortKey;

SortKey  Visit   disVisit
-------  -----   --------
     10  AAAA           1  ← AAAAで1
     20  BBBB           2  ← AAAA,BBBBで2
     30  AAAA           2  ← AAAA,BBBBで2
     40  BBBB           2  ← AAAA,BBBBで2
     50  CCCC           3  ← AAAA,BBBB,CCCCで3
     60  CCCC           3  ← AAAA,BBBB,CCCCで3
     70  DDDD           4  ← AAAA,BBBB,CCCC,DDDDで4
     80  AAAA           4  ← AAAA,BBBB,CCCC,DDDDで4

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
解説しますと、
disVisit[any] によって、配列の全ての添字を対象として、
count(distinct Visit)[SortKey <= cv()]) を求めてます。

count(distinct Visit)[SortKey <= cv()]) で集合関数の対象となるのは、
SortKey <= cv() が成立する行のみとなります。

結果として、相関サブクエリを使った代替方法と似た記述になりますね。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


model句の例6 wmsys.wm_concatの代用

分析関数のwmsys.wm_concatは、Oracle11gのマニュアルにすら載ってないので、model句で代用してみます。

create table EmuWmTable(ID,SortKey,Str) as
select 1,10,'AA' from dual union all
select 1,20,'BB' from dual union all
select 1,35,'CC' from dual union all
select 2,10,'DD' from dual union all
select 2,25,'EE' from dual union all
select 3,10,'FF' from dual;

col ConStr for a10

-- 分析関数のwmsys.wm_concat
select ID,SortKey,Str,
wmsys.wm_concat(Str) over(partition by ID order by SortKey) as ConStr
  from EmuWmTable
order by ID,SortKey;

-- modelSample13
select ID,SortKey,Str,ConStr
  from EmuWmTable
 model
partition by(ID)
dimension by(Row_Number() over(partition by ID order by SortKey) as rn)
measures(SortKey,Str,cast(null as varChar2(10)) as ConStr)
rules(
ConStr[any] order by rn = case when ConStr[cv()-1] is present
                               then ConStr[cv()-1] || ',' || Str[cv()]
                               else Str[cv()] end)
order by ID,SortKey;

ID  SortKey  Str  ConStr
--  -------  ---  --------
 1       10  AA   AA
 1       20  BB   AA,BB
 1       35  CC   AA,BB,CC
 2       10  DD   DD
 2       25  EE   DD,EE
 3       10  FF   FF

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
「Row_Number関数のpartition by IDは、省略できるのでは???」、と考えましたか?
model句のpartition by句は、rules句が行う処理をパーティションごとに分割する句ですので、
dimension by句のRow_Number関数でも、partition by IDを指定しないといけません。

Row_Number関数で作成した連番を使って、
最初からセルが存在したかどうか、
いいかえれば、セルがベースのクエリからのプレゼント(贈り物)かどうかを、
case式でis present述語を使って判断し、文字列を順番に連結させてます。

連結順序が重要なので、
ConStr[any] order by rn
といった形でorder byでルールの評価順序を指定してます。

is present述語の代用品として、presentv関数という関数もあります。
引数の取り方は、nvl2関数と似てますね。

-- modelSample14
select ID,SortKey,Str,ConStr
  from EmuWmTable
 model
partition by(ID)
dimension by(Row_Number() over(partition by ID order by SortKey) as rn)
measures(SortKey,Str,cast(null as varChar2(10)) as ConStr)
rules(
ConStr[any] order by rn = presentv(ConStr[cv()-1],
                                   ConStr[cv()-1] || ',' || Str[cv()],
                                   Str[cv()]))
order by ID,SortKey;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


model句の例7 group_concatもどき

wmsys.wm_concatは、連結順序をorder byで指定できませんが、
MySQLのgroup_concat関数は連結順序をorder byで指定できます。

MySQLのgroup_concat関数をmodel句で代用してみます。

create table EmuGrConT(ID,SortKey,Str) as
select 1,10,'AA' from dual union all
select 1,20,'BB' from dual union all
select 1,35,'CC' from dual union all
select 2,10,'DD' from dual union all
select 2,25,'DD' from dual union all
select 2,30,'EE' from dual union all
select 2,35,'DD' from dual union all
select 3,10,'FF' from dual;

-- MySQLで実行
select ID,group_Concat(Str order by SortKey) as conStr
  from EmuGrConT
group by ID;
+----+-------------+
| ID | conStr      |
+----+-------------+
|  1 | AA,BB,CC    |
|  2 | DD,DD,EE,DD |
|  3 | FF          |
+----+-------------+

col ConStr for a20

-- modelSample15
select ID,substr(ConStr,2) as ConStr
  from EmuGrConT
 model return updated rows
partition by(ID)
dimension by(Row_Number() over(partition by ID order by SortKey) as rn)
measures(Str,cast(null as varChar2(20)) as ConStr)
rules iterate(50) until presentv(Str[iteration_number+2],1,0) = 0
(ConStr[0] = ConStr[0] || ',' || Str[iteration_number+1])
order by ID;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
model return updated rowsが
結果セットの行を減らしたい時の定番です。

ITERATEとuntilを使ったときの処理順序について理解しておくといいでしょう。
処理1 ITERATION_NUMBERを宣言 (初期値=0)
処理2 rule適用
処理3 終了条件判定
処理4 ITERATION_NUMBERをインクリメント
処理5 処理2へ戻る

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、下のようになります。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
添字の0番ではなく、添字の1番に集めてもいいです。

-- modelSample16
select ID,substr(ConStr,2) as ConStr
  from EmuGrConT
 model return updated rows
partition by(ID)
dimension by(Row_Number() over(partition by ID order by SortKey) as rn)
measures(Str,cast(null as varChar2(20)) as ConStr)
rules iterate(50) until presentv(Str[iteration_number+2],1,0) = 0
(ConStr[1] = ConStr[1] || ',' || Str[iteration_number+1])
order by ID;


model句の参考リソース

書籍
Advanced Oracle SQL Programming(英語)

Web
OracleSQLパズル model句01 HelloWorld

OTN-Japan
モデリングのSQL
モデル式
Oracle Database 10g の SQL MODEL句(PDF)