(轉載)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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- YYModel學習筆記筆記
- ctf學習筆記[轉載]筆記
- oracle學習筆記Oracle筆記
- oracle 11g model子句的用法Oracle
- oracle基本概念的學習筆記(轉)Oracle筆記
- oracle 學習筆記 (ORACLE NET )Oracle筆記
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- oracle學習筆記《一》Oracle筆記
- Oracle學習筆記2Oracle筆記
- Oracle學習筆記1Oracle筆記
- ORACLE dataguard學習筆記Oracle筆記
- Oracle FlashBack 學習筆記Oracle筆記
- ORACLE學習筆記(zt)Oracle筆記
- Oracle學習筆記-1Oracle筆記
- sed學習筆記(轉)筆記
- gurb學習筆記(轉)筆記
- ANT學習筆記 (轉)筆記
- GRUB學習筆記(轉)筆記
- TreeView學習筆記 (轉)View筆記
- [轉載] Oracle:start with...connect by子句的用法Oracle
- postgreSQL with子句學習SQL
- 【SQL Server學習筆記】Delete 語句、Output 子句、Merge語句SQLServer筆記delete
- Oracle DBA學習日記筆記Oracle筆記
- Oracle Scheduler學習筆記分享Oracle筆記
- oracle之awr學習筆記Oracle筆記
- Oracle基礎學習筆記Oracle筆記
- Oracle學習筆記之二Oracle筆記
- oracle 原理學習筆記（一）Oracle筆記
- SAP BASIS學習筆記(轉)筆記
- PE學習筆記（一） (轉)筆記
- AS 學習筆記 載入資料筆記
- oracle學習筆記--oracle常用的命令Oracle筆記
- oracle 學習筆記---效能優化學習(1)Oracle筆記優化
- JSP標記學習筆記 (轉)JS筆記
- 【轉】Oracle學習筆記：INITRANS和MAXTRANS引數的作用Oracle筆記
- 轉：Oracle RAC學習筆記:基本概念及入門Oracle筆記
- Oracle學習筆記（6）——函式Oracle筆記函式
- oracle學習筆記-常用的命令Oracle筆記