OracleSQLパズル
明智重蔵のブログ
明智重蔵のTwitter
記事(OTN)
記事(CodeZine)
分析関数とmodel句
概要
Oracle OpenWorld 2009 Tokyo の"Unconference"の、4月23日(木)の15:00-15:30の枠
で私が、オーガナイザ(講師)をさせていただきまして、そのまとめページです。
OTN-JapanやUS-OTNのフォーラムでのSQLの問題を解くのに、使うことの多い、
分析関数とmodel句について、私の思考法や、脳内のイメージを解説しました。
2007年にLaurent Schneiderさんがスピーカーをされた"SQL 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関数でシーケンス開始を検知する脳内のイメージは、下のようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う脳内のイメージは、下のようになります。
分析関数の例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にしておくことにより、ブール値としても使えます。
分析関数の参考リソース
第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句の参考リソース