(轉載)ORACLE MODEL子句學習筆記
ORACLE 10G中新增的MODEL子句可以用來進行行間計算。MODEL子句允許像訪問陣列中元素那樣訪問記錄中的某個列。這就提供了諸如電子表格計算之類的計算能力。
1、MODEL子句示例
下面這個查詢獲取2003年內由員工#21完成的產品型別為#1和#2的銷量，並根據2003年的銷售資料預測出2004年1月、2月、3月的銷量。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=sales_amount[1,2003],
Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003],
Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)
)
Order by prd_type_id,year,month;
現在小分析一下上面這個查詢：
- partition by (prd_type_id)指定結果是根據prd_type_id分割槽的。
- dimension by (month,year)定義陣列的維度是month和year。這就意味著必須提供月份和年份才能訪問陣列中的單元。
- measures (amount sales_amount)表明陣列中的每個單元包含一個數量，同時表明陣列名為sales_amount。
- MEASURES之後的三行命令分別預測2004年1月、2月、3月的銷量。
- Order by prd_type_id,year,month僅僅是設定整個查詢返回結果的順序。
上面這個查詢的輸出結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 10034.84
1 2004 2 35282.48
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1034.84
2 2004 2 3582.48
2 2004 3 2547.29
30 rows selected.
2、用位置標記和符號標記訪問資料單元
前面的例子已經介紹瞭如何使用位置標記來訪問陣列中的某個單元。還可以使用符號標記顯式指定維度的含義。例如，sales_amount[month=1,year=2004]。下面這個查詢用符號標記重寫了前面的查詢。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[month=1,year=2004]=sales_amount[month=1, year=2003],
Sales_amount[month=2, year=2004]=sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003],
Sales_amount[month=3, year=2004]=ROUND(sales_amount[month=3, year=2003]*1.25,2)
)
Order by prd_type_id,year,month;
使用位置標記或符號標記之間有一個區別需要了解，即它們處理維度中空值的方式不同。例如，sales_amount[null,2003]返回月份為空值、年份為2003的銷量，而sales_amount[month=null,year=2004]則不會訪問任何有效的資料單元，因為null=null的返回值總是false。
3、用BETWEEN和AND返回特定範圍內的資料單元
BETWEEN和AND關鍵字可用於訪問一段範圍內的資料單元。例如，下面這個表示式將2004年1月的銷量設定為2003年1月至3月銷量的平均值取整：
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
下面這個查詢展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
)
Order by prd_type_id,year,month;
結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 15105.77
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1539.11
26 rows selected.
4、用ANY和IS ANY訪問所有的資料單元
可以用ANY和IS ANY謂詞訪問陣列中所有的資料單元。ANY和位置標記合用，IS ANY和符號標記合用。例如，下面這個表示式將2004年1月的銷量設定為所有年份月份的銷量之和取整：
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
下面這個查詢展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
)
Order by prd_type_id,year,month;
結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 197916.96
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 20426.96
26 rows selected.
5、用CURRENTV()獲取某個維度的當前值
CURRENTV()函式用於獲得某個維度的當前值。例如，下面的表示式將2004年第一個月的銷量設定為2003年同月銷量的1.25倍。注意此處用CURRENTV()獲得當前月份，其值為1
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面這個查詢展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
執行結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
26 rows selected.
6、用FOR迴圈訪問資料單元
可以通過FOR迴圈訪問資料單元。例如，下面這個表示式將2004年前三個月的銷量設定為2003年相應月份銷量的1.25倍。注意其中使用了FOR迴圈，還通過INCREMENT關鍵字定義每一次迴圈迭代中month的增量：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面這個和查詢語句展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
執行結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
1 2004 2 18930.81
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
2 2004 2 1930.81
2 2004 3 2547.29
30 rows selected.
7、處理空值和缺失值
1）使用IS PRESENT
當資料單元指定的記錄在MODEL子句執行之前存在，則IS PRESENT返回TRUE。例如：
Sales_amount[CURRENTV(),2003] IS PRESENT
如果Sales_amount[CURRENTV(),2003]存在，則返回TRUE。
下面的表示式將2004年前三個月的銷量設定為2003年同期銷量的1.25倍：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
下面這個查詢展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
Order by prd_type_id,year,month;
2）使用PRESENTV()
如果cell引用的記錄在MODEL子句執行以前就存在，那麼PRESENTV(cell,expr1,expr2)返回表示式expr1。如果這條記錄不存在，則返回表示式expr2。例如：
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在，上面的表示式返回取整後的銷量；否則，返回0.下面這個查詢展示了上述表示式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;
3）使用PRESENTNNV()
如果cell引用的單元在MODEL子句執行之前已經存在，並且該單元的值不為空，則PRESENTNNV(cell,expr1,expr2)返回表示式expr1。如果記錄不存在，或單元值為空值，則返回表示式expr2。例如：
PRESENTNNV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在且為非空值，那麼上面的表示式將返回取整後的銷量；否則返回0。
4）使用IGNORE NAV和KEEP NAV
IGNORE NAV的返回值如下：
- 空值或缺失數字值時返回0。
- 空值或缺失字串值時返回空字串。
- 空值或缺失日期值時返回01-JAN-2000。
- 其他所有資料庫型別時返回空值。
KEEP NAV對空值或缺失數字值返回空值。注意預設條件下使用KEEP NAV。
下面這個查詢展示了IGNORE NAV的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
8、更新已有的單元
預設情況下，如果表示式左端的引用單元存在，則更新該單元。如果該單元不存在，就在陣列中建立一條新的記錄。可以用RULES UPDATE改變這種預設的行為，指出在單元不存在的情況下不建立新紀錄。
下面這個查詢展示了RULES UPDATE的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
RULES UPDATE
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
執行結果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 11 1329.73
2 2003 12 1034.84
24 rows selected.
可以看到，雖然2004年的單元不存在，可是同時指定了RULES UPDATE，所以不會在陣列中為2004年建立新紀錄，因此這條查詢語句不會返回2004年的記錄。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16923247/viewspace-716672/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [轉載] Oracle:start with...connect by子句的用法Oracle
- oracle學習筆記《一》Oracle筆記
- 工作學習筆記（二） 正規表示式（轉載）筆記
- Oracle學習筆記（6）——函式Oracle筆記函式
- postgreSQL with子句學習SQL
- Oracle體系結構學習筆記Oracle筆記
- Oracle行列轉換及pivot子句的用法Oracle
- repuest轉發學習筆記一筆記
- 載譚 Binomial Sum 學習筆記筆記
- oracle學習筆記（十一） 高階查詢Oracle筆記
- Oracle學習、進階資料合集（含教程、筆記、題庫下載與學習方法分享）Oracle筆記
- Python——格式轉換的學習筆記Python筆記
- 10g NewFeatures學習筆記(轉)筆記
- Python學習筆記 - 下載圖片Python筆記
- 彙編學習筆記之轉移指令筆記
- 依賴倒轉原則--學習筆記筆記
- numpy的學習筆記\pandas學習筆記筆記
- jvm學習筆記6：類載入器JVM筆記
- JVM學習筆記——類載入機制JVM筆記
- SpringCloud學習筆記：負載均衡Ribbon（3）SpringGCCloud筆記負載
- Oracle高階培訓 第5課 學習筆記Oracle筆記
- Oracle高階培訓 第6課 學習筆記Oracle筆記
- Oracle高階培訓 第7課 學習筆記Oracle筆記
- oracle學習筆記（十七） PL/SQL高階應用Oracle筆記SQL
- 學習筆記筆記
- [轉載]《吳恩達深度學習核心筆記》釋出，黃海廣博士整理！吳恩達深度學習筆記
- Web【轉載學習】Web
- C++學習筆記（二） 運算子過載C++筆記
- fu-liou radiative transfer model 學習日記
- 【轉載】Kano Model — Ways to use it and NOT use it
- 【學習筆記】數學筆記
- 《JAVA學習指南》學習筆記Java筆記
- 機器學習學習筆記機器學習筆記
- Spring---IoC（控制反轉）原理學習筆記【全】Spring筆記
- 學習筆記-粉筆980筆記
- 學習筆記（3.29）筆記
- 學習筆記（4.1）筆記
- 學習筆記（3.25）筆記
- 學習筆記（3.26）筆記