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