用MODEL從句進行電子表格計算

SQL中的MODEL從句是Oracle 10g資料庫為企業智慧領域引入的一個重要的新功能。這個從句對喜歡從Oracle中區出來，再放入電子表格進行分析的會計人員有很大幫助。SQL中的MODEL從句的目的就是讓SQL語句擁有從普通的SELECT結果中建立多維陣列的能力，然後再在這個SQL電子表格上進行行間或陣列的計算。
MODEL從句透過講一個查詢中的各列對映為三組而定義一個多位陣列，這三個組分別是分割槽(partitions)、維度(dimensions)和計量(measures)。分割槽定義的是結果集中作為獨立陣列的邏輯塊。緯度定義的是分割槽中每個計量單元。計量則是實際的資料單元。計量一般會包括象銷售量或者營業收入等數值。我們可以透過指定完全的維度組合來訪問分割槽中的每個計量單元。下表是這些元件的一個示例。
用於演示MODEL從句的示例資料
分割槽 維度 維度 計量
C1 ABC 2002 450
C1 ABC 2001 455
C2 ABC 2002 500
C2 ABC 2001 550
C1 XYZ 2002 67
C1 XYZ 2001 78
C2 XYZ 2002 85
C2 XYZ 2001 80
25.MODEL從句的基本語法
下面是MODEL從句的最基本語法。MODEL從句很複雜，要想了解更多，可以參考ORACLE 10G SQL REFERENCE MANUAL。

MODEL [RETURN [UPDATED | ALL] ROWS]
[reference models]
[PARTITION BY ()]
DIMENSION BY ()
MEASURES () [IGNORE NAV] | [KEEP NAV]
[RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
[ITERATE (n) [UNTIL ] ]
( = ... )
26.MODEL從句舉例
首先還是建立一個表，插入一些資料（略）。查詢revenues表的結果如下圖所示：
SQL> SELECT * FROM revenues;
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2002 450
C1 ABC 2001 455
C2 ABC 2002 500
C2 ABC 2001 550
C1 XYZ 2002 67
C2 XYZ 2002 85
C2 XYZ 2001 80
C1 XYZ 2001 78
C2 XYZ 2002 85
C2 XYZ 2001 80
2001年和2002年的營業收入資料是可用的。我們想根據這兩年的營業資料，預測2003年的營業收入資料。假設2003年XYZ公司營業收入是其2001年和2002年的總和，ABC公司2003年的資料比其上年收入高20%。那麼MODEL從句如下：
SQL>SELECT country, company, year, revenue
FROM revenues
MODEL
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (
revenue['ABC',2003] = revenue['ABC',2002]*1.2,
revenue['XYZ',2003] = revenue['XYZ',2001] +
revenue['XYZ',2002])
ORDER BY country, company, year;
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2001 455
C1 ABC 2002 450
C1 ABC 2003 540
C1 XYZ 2001 78
C1 XYZ 2002 67
C1 XYZ 2003 145
C2 ABC 2001 550
C2 ABC 2002 500
C2 ABC 2003 600
C2 XYZ 2001 80
C2 XYZ 2002 85
C2 XYZ 2003 165
12 rows selected.
如果我們只想返回新的行，那麼可以使用RETURN UPDATED ROWS從句，例如：
SQL>SELECT country, company, year, revenue
FROM revenues
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (
revenue['ABC',2003] = revenue['ABC',2002]*1.2,
revenue['XYZ',2003] = revenue['XYZ',2001] +
revenue['XYZ',2002])
ORDER BY country, company, year;
COUNTRY COMPANY YEAR REVENUE
---------- ---------------------------------------- ---------- ----------
C1 ABC 2003 540
C1 XYZ 2003 145
C2 ABC 2003 600
C2 XYZ 2003 165
27.測試MODEL從句可能遇到的問題
在演示MODEL從句時，可能會遇到下面的錯誤：
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
在這種情況下，是因為OLAP被設定為ON但是卻沒有安裝OLAP的原因。我們可以透過以SYS使用者登入後，執行@?/olap/admin/olap.sql SYSAUX TEMP;來建立OLAP，然後再測試帶MODEL從句的SQL就應該正確了。
28.單元格和值的定址
理解單元格的引用方法對於充分發揮MODEL從句的作用非常重要。在預設情況下，MODEL從句中預設規則具有Upsert特徵，也就是如果規則左側指出的單元格存在，那麼它會被更新，否則將生成包含該單元格的一個新行。基礎表並不會更新也不會插入新值。要想對基礎表進行更新或插入，必須使用update或insert。對單元格的引用必須符合DIMENSION BY從句中列處的所有維度的要求。人們既可以用位置引用，也可以使用符號引用。
29.單元格的位置引用
在位值引用中，方括號中提供的每個值將與DIMENSION BY從句中同樣的位置的維度進行匹配。下面是一個使用位置單元格引用的查詢，根據表示式中的維度進行引用：DIMENSION BY從句確定了每個維度指定的位置。在本例中，第一個位置是公司，第二個位置是年份。ABC公司2001年的營業收入在顯示出來的結果中將被更新為1000。
SQL>SELECT country, company, year, revenue
FROM revenues
WHERE country = 'C1'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (revenue['ABC',2001] = 1000);
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2001 1000[@more@]