Oracleのmodel句（1）
――model句のサンプルと集計行の追加方法

model句の入門事項

3. 集計行の追加

　集計行の追加は、model句の代表的な使い道の1つです。Oracle 9iまでは行の追加は、union all、rollup、grouping sets、連番表とのクロスジョイン、表関数などを使って行われてましたが、Oracle 10gからはmodel句で行の追加が行えます。サンプルを見てみましょう。

addTotal

ID	Val
1	30
2	100
3	50
4	300

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

model句で集計行の追加1

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

出力結果

ID	Val
1	30
2	100
3	50
4	300
null	480

　model句を使わない方法としては、rollupを使う方法や、union allを使う方法があります。

rollupで集計行の追加1

select ID,sum(Val) as Val
  from addTotal
group by rollup(ID);

union allで集計行の追加1

select ID,Val
  from addTotal
union all
select null,Sum(Val)
  from addTotal;

　前問をアレンジして、今度はIDが3または4の行のみを集計対象として、集計行を表示してみましょう。

model句で集計行の追加2

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

出力結果

ID	Val
1	30
2	100
3	50
4	300
null	350

　SQLのイメージは、下のようになります。dimension by(ID)に対応する配列の添字を紫色でイメージしてます。

SQLのイメージ

　model句を使わない方法としては、rollupを使う方法やunion allを使う方法がありますが、上記のSQLと比較すると、model句による行の追加は、他の方法と比べてシンプルになることが多いと分かります。

rollupで集計行の追加2

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 addTotal
group by rollup(ID);

union allで集計行の追加2

select ID,Val
  from addTotal
union all
select null,Sum(Val)
  from addTotal
 where ID in(3,4);

最後に

　今回は、Hello Worldなどのmodel句のサンプルと、集計行の追加を行う方法を扱いました。次回は、all_objectsやall_catalogやdictの代用や、空き番号一覧の作成を扱います。

参考資料

1. 『Oracle Database 10g の SQL MODEL句』
   Oracleが公開しているmodel句に関する解説書です。（PDF）
2. 『モデリングのSQL』
   Oracleの公式マニュアルのmodel句に関する説明です。
3. 『モデル式』
   Oracleの公式マニュアルのmodel句に関する説明です。