目的
在本教程中，您將學習如何使用 Oracle 數據庫 10g SQL MODEL 子句執行行間計算。
所需時間
大約 30 分鐘
主題
本教程包括下列主題:
概述
情景
前提條件
設置示例數據
查看示例語法
使用位置和符號單元格引用
在規則右側使用多單元格引用
使用 CV() 函數和 ANY 通配符
Coding FOR Loops:指定新單元格的簡潔方法
了解規則的評估順序
處理 NULL 度量和缺失單元格
創建引用模型
創建迭代模型
使用排序規則
總結
概述
利用 SQL MODEL 子句，您可以根據查詢結果定義多維數組，然後將規則應用於該數組以計算新值。這些規則可以是復雜的相互依賴的計算。與外部解決方案相比，通過將高級計算集成到數據庫中，可以大幅度提升性能、可伸縮性以及可管理性。用戶可以將數據保留在 Oracle 環境內，而無需將數據復制到單獨的應用程序或 PC 電子表格中。
MODEL 子句通過將查詢列映射到以下三組來定義多維數組:分區列、維度列和度量列。這些元素執行以下任務:
分區以類似於分析函數的分區方式(在數據倉庫指南中標題為“數據倉庫中用於分析的 SQL”的一章中有述)來定義結果集的邏輯塊。將 MODEL 規則應用於每個分區的單元格。
維度用於標識分區內的每個度量單元格。這些列用於標識日期、區域以及產品名之類的特征。
度量類似於星型模式中事實表的度量。它們通常包含數值，例如銷售單位或成本。通過指定每個單元格的完整維度組合，可以在單元格所處的分區內對其進行訪問。
要針對這些多維數組創建規則，您需要定義以維度值形式表達的計算規則。規則靈活且簡潔，並且可以使用通配符和 FOR 循環，以最大限度地表達您的意圖。利用 MODEL 子句構建的計算通過將分析集成到數據庫中改善了傳統的電子表格計算，通過符號引用提高了可讀性，並提供了可伸縮性和更好的可管理性。
下圖使用假設的銷售表格從概念的角度概述了該模型的特征。該表格具有四列:國家/地區、產品、年份和銷售量。該圖分為三個部分。上段闡釋了將表格劃分為分區、維和度量三列的概念。中段給出了兩個假想規則，以預測 Prod1 和 Prod2 的銷售，因為產品銷售的計算值來自前兩年。最後，第三部分顯示了將規則應用於這個包含假設數據的表格後得出的查詢輸出。黑色輸出是從數據庫檢索的數據，而藍色輸出表示根據規則計算出的行。請注意，這些規則是在每個分區內應用的。
映射到分區、維和度量的列 COUNTRY PRODUCT YEAR SALES 分區 維度 維度 度量 規則: sales('prod1', 2002) = sales('prod1', 2000) + sales('prod1', 2001) sales('prod2', 2002) = sales('prod2', 2000) + sales('prod2', 2001)
MODEL 子句的輸出:
COUNTRY PRODUCT YEAR SALES 分區 維度 維度 度量 A prod1 2000 10 A prod1 2001 15 A prod2 2000 12 A prod2 2001 16 B prod1 2000 21 B prod1 2001 23 B prod2 2000 28 B prod2 2001 29 A prod1 2002 25 A prod2 2002 28 B prod1 2002 44 B prod2 2002 57
請注意，MODEL 子句沒有更新表格中的現有數據，也沒有向表格中插入新數據 要更改表格中的值，必須將模型結果提供給 INSERT、UPDATE 或 MERGE 語句。
案例
通過使用 MODEL 子句，您可以將電子表格計算引入數據庫。您將使用 Sales History 模式數據並通過包含新 MODEL 子句的 SELECT 語句來執行類似電子表格的計算。您需要標識規則，以找出諸如銷售預測之類的信息。
前提條件
開始本教程之前，您應該:
1.完成了教程在 Windows 上安裝 Oracle 數據庫 10g。
2.下載 model_clause.zip 並將其解壓縮到您的工作目錄(例如，c:\wkdir)
設置示例數據
使用 SH 模式創建視圖。該視圖將按國家/地區提供產品銷售的年度總計(總款和總量)，並跨所有渠道進行聚集。
1.啟動一個 SQL*Plus 會話。選擇開始 > 程序 > Oracle-OraDB10g_home > Application Development > SQL Plus。
(注意:本教程假設您擁有 c:\wkdir 文件夾。如果沒有，則需要創建此文件夾，並將 model_clause.zip 的內容解壓縮到此文件夾中。當執行這些腳本時，指定路徑)
2.以 SH 用戶的身份登錄。輸入 SH 作為 User Name，並輸入 SH 作為 PassWord。然後單擊 OK。
3.首先，確保環境簡潔。從 SQL*Plus 會話運行 cleanup.sql 腳本。 @c:\wkdir\cleanup.sql
cleanup.sql 腳本包含以下內容: DROP VIEW sales_vIEw;DROP TABLE dollar_conv;DROP TABLE growth_rate;DROP TABLE ledger;
4.現在，您可以創建 SALES_VIEW 視圖。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\sample_data.sql sample_data.sql 腳本包含以下內容: CREATE VIEW sales_view AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt FROM sales, times, customers, countries, products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countrIEs.country_id GROUP BY country_name, prod_name, calendar_year /
5.驗證視圖創建正確，並具有 3219 行。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\sel_sv.sql sel_sv.sql 腳本包含以下內容: SELECT COUNT(*) FROM sales_vIEw;
6.要使性能最佳化，系統應當已經具有基於以上視圖使用的數據構建的物化視圖。該物化視圖是在 SH 模式數據的安裝期間創建的。Oracle 的摘要管理系統將使用上述視圖自動重寫所有查詢，以便利用該物化視圖。
查看示例語法
作為模型的初始示例，請考慮以下語句:
SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country IN ('Italy','Japan') MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000], sales['Y Box', 2002] = sales['Y Box', 2001], sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002]) ORDER BY country, prod, year;
結果是:
COUNTRY PROD YEAR SALES -------------------- --------------- ---------- ---------- Italy 2_Products 2002 90387.54 Italy Bounce 2002 9179.99 Italy Y Box 2002 81207.55 Japan 2_Products 2002 101071.96 Japan Bounce 2002 11437.13 Japan Y Box 2002 89634.83
因為該語句按照國家/地區劃分，所以這些規則一次應用於一個國家/地區的數據。請注意，數據結束於 2001 年，因此為 2002 年或之後年份定義值的任何規則都將插入新單元格。第一個規則將 2002 年 Bounce 的銷售定義為 2000 年和 2001 年的銷售總和。第二個規則將 2002 年 Y Box 的銷售定義為 2001 年的銷售值。第三個規則定義了一個名為 2_Products 的類別，它是 2002 年的 Bounce 與 Y Box 值相加所得的總和。請注意，2_Products 的值派生自前兩個規則的結果，因此這兩個規則必須在 2_Products 規則之前執行。
語法准則
請注意，MODEL 關鍵字後面的 RETURN UPDATED ROWS 子句將結果限制為在該查詢中創建或更新的那些行。使用該子句是使結果集只包含新計算的值的簡便方法。在整個示例中，都將用到 RETURN UPDATED ROWS 子句。
示例中顯示在規則開頭處的 RULES 關鍵字是可選的，但是建議您使用以方便閱讀。
許多示例在 COUNTRY 列並不需要 ORDER BY。但是，為了方便修改示例並添加多個國家/地區，應將其包含在規范中。
技術細節
以下示例演示了 MODEL 子句的主要功能，從基本單元格引用到引用模型以及迭代模型。
使用位置和符號單元格引用
本部分探究了在 MODEL 語句中使用符號和位置單元格引用的技巧。
1.要查看產品 Bounce 於 2000 年在意大利的 SALES 值，並將其設為 10，可使用“位置單元格引用”。單元格引用的值將根據其在表達式中的位置與相應的維度匹配。模型的 DIMENSION BY 子句決定指定給每個維 — 在本例中，第一個位置是產品 (PROD)，第二個位置是 YEAR.從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\pos_cell1.sql pos_cell1.sql 腳本包含以下內容: COLUMN country FORMAT a20 COLUMN prod FORMAT a20 SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2000] = 10 ) ORDER BY country, prod, year /
2.要創建產品 Bounce 於 2005 年在意大利的 SALES 預測值，並將其設為 20，可使用 SELECT 語句中的規則將年份值設為 2005，從而在數組中創建新單元格。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\pos_cell2.sql pos_cell2.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2005] = 20 ) ORDER BY country, prod, year /
注意:如果您希望創建新單元格(例如，未來幾年的值)，則必須使用位置引用或 FOR 循環(本教程稍後討論)。也就是說，位置引用允許更新數組以及向數組中插入新值。這稱為 UPSERT 過程，它由 Oracle SQL MERGE 語句處理。
3.要更新產品 Bounce 自 1999 年以來針對意大利記錄的所有年份的 SALES，並將它們設為 10，可以使用“符號單元格引用”。單元格引用的值通過布爾條件與相應的維度匹配。您可以使用所有常見的運算符，例如 <、>、IN 和 BETWEEN。在本例中，查詢將查找等於 Bounce 的產品值和所有大於 1999 的年份值。這展示了單一規則如何訪問多個單元格。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\sym_cell1.sql sym_cell1.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales[prod='Bounce', year>1999] = 10 ) ORDER BY country, prod, year /
注意:符號引用功能強大，但它們只能用於更新現有的單元格:它們不能創建新單元格，例如，未來幾年的銷售規劃。
4.您希望通過單一查詢來更新多個國家/地區的多種產品在數年中的銷售，並且還希望插入新的單元格。通過將數個規則置於一個查詢中，處理會更加高效，因為這減少了需要訪問數據的次數。它還允許使用更為簡潔的 SQL，以使開發人員的工作效率更高。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\pos_sym.sql pos_sym.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country IN ('Italy','Japan') MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2002] = sales['Bounce', year = 2001] , --positional notation: can insert new cell sales['Y Box', year>2000] = sales['Y Box', 1999], --symbolic notation: can update existing cell sales['2_Products', 2005] = sales['Bounce', 2001] + sales['Y Box', 2000] ) --positional notation: permits insert of new cells --for new product ORDER BY country, prod, year /
該示例數據沒有超出 2001 年的值，因此所有涉及到 2002 年或之後的規則都要求插入新的單元格。對於此處定義的任何新產品名也是如此。在第三條規則中，2_Products 被定義為 2005 年的銷售是 2001 年 Bounce 銷售與 2000 年 Y Box 銷售總和的產品。
對於 2002 年的 Bounce，第一個規則將插入新的單元格，因為這是位置表示法。對於 Y Box，第二個規則使用符號表示法，但是此處已經有了 2001 年的 Y Box 值，因此它將更新這些值。對於 2005 年的 2_Products，第三個規則是位置表示法，因此它可以插入新的單元格，您將在輸出中看到這些新單元格。
在規則右側使用多單元格引用
早先的示例只能在規則的左側使用多單元格引用。如果要在規則右側引用多個單元格，您可以在規則右側使用多單元格引用，在這種情況下，需要對其應用聚合函數，以將它們轉換為單一值。可以使用所有現有的聚合函數，包括 OLAP 聚合函數(逆分配函數、虛擬等級和分配函數等)、統計聚合函數以及用戶定義的聚合函數。
1.您希望預測 2005 年 Bounce 在意大利的銷售比其在 1999 至 2001 年間的最大銷售多 100。為此，您需要在規則右側使用 BETWEEN 子句來指定多個單元格，並通過 MAX() 函數將其聚合為單一值。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\multi_c.sql multi_c.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2005] = 100 + max(sales)['Bounce', year BETWEEN 1998 AND 2002] ) ORDER BY country, prod, year /
請注意，聚合函數只出現在規則的右側。聚合函數的參數可以是常量、約束變量、MODEL 子句的度量或者涉及這三種參數的表達式。
使用 CV() 函數和 ANY 通配符
CV() 函數是一個非常強大的工具，它可以高效地進行規則創建。CV() 用於規則的右側，以復制左側指定的當前維度值。對於左側規范引用多個單元格來說，它非常有用。用關系數據庫的概念來理解，該函數類似於連接操作。
CV() 允許使用非常靈活的表達式。例如，通過從 CV(year) 值進行減法運算，可以引用數據集中的其他行。如果您的單元格引用中有表達式“CV(year) -2”，您兩年前就可以訪問數據。CV() 函數通常作為單元格引用的一部分使用，但是也可以在單元格引用外部作為獨立的表達式元素。
1.您希望更新 Bounce 在意大利多年的銷售值，使用的規則是 Bounce 每年的銷售是 Y Box 當年銷售的 20% 與 Mouse Pad 當年銷售的總和。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\cvf1.sql cvf1.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', year BETWEEN 1995 AND 2002] = sales['Mouse Pad', cv(year)] + 0.2 * sales['Y Box', cv(year)]) ORDER BY country, prod, year /
請注意，在上述結果中，盡管接受了 1995–2002 年間的所有年份，您也只會看到 1999–2001 年的值。這是因為該表格只有這幾年的數據。CV() 函數將提供左側當前引用的單元格的 DIMENSION BY 關鍵字當前值。當上述規則的左側引用單元格 Bounce 和 1999 時，右側表達式將如下所示: sales['Mouse Pad', 1999] + 0.2 * sales['Y Box', 1999]
同樣，當左側引用單元格 Bounce 和 2000 時，右側表達式將為: sales['Mouse Pad', 2000] + 0.2 * sales['Y Box', 2000]
CV() 函數將維度關鍵字作為其參數。還可以使用不帶任何參數的 CV()(如 CV())，在這種情況下，暗示了位置引用。以上規則還可以寫為: s['Bounce', year BETWEEN 1995 AND 2002] =
s['Mouse Pad', cv()] + 0.2 * s['Y Box', cv()]
CV() 函數只能在右側單元格引用中使用。
2.您希望計算出產品 Y Box、Bounce 和 Mouse Pad 在意大利的銷售年增長率。從 SQL*Plus 會話中，執行以下腳本:
@c:\wkdir\cvf2.sql cvf2.sql 腳本包含以下內容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales, growth FROM sales_vIEw WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales, 0 growth) RULES ( growth[prod in ('Bounce','Y Box','Mouse Pad'), year between 1998 and 2001] = 100* (sales[cv(prod), cv(year)] - sales[cv(prod), cv(year) -1] ) / sales[cv(prod), cv(year) -1] ) ORDER BY country, prod, year /
請注意，結果中的空白單元格都是 NULL。如果沒有前兩年的產品值，那麼規則將生成 NULL。由於沒有一種產品有 1998 年的值，因此在任何情況下，1999 年的增長計算均為 NULL。
