Oracle中使用SQL MODEL定義行間計算（2）

2024-08-29 13:34:37

字體：大中小

來源：轉載

供稿：網友

4.現在，您可以創建 SALES_VIEW 視圖。從 SQL*Plus 會話中，執行以下腳本:
@c:/wkdir/sample_data.sql sample_data.sql 腳本包含以下內容: 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/ Oracle中使用SQL MODEL定義行間計算（2）（圖一）

5.驗證視圖創建正確，并具有 3219 行。從 SQL*Plus 會話中，執行以下腳本: @c:/wkdir/sel_sv.sql sel_sv.sql 腳本包含以下內容: SELECT COUNT(*) FROM sales_view; Oracle中使用SQL MODEL定義行間計算（2）（圖二）

6.要使性能最佳化，系統應當已經具有基于以上視圖使用的數據構建的物化視圖。該物化視圖是在 SH 模式數據的安裝期間創建的。Oracle 的摘要治理系統將使用上述視圖自動重寫所有查詢，以便利用該物化視圖。查看示例語法作為模型的初始示例，請考慮以下語句: 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;結果是: 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因為該語句按照國家/地區劃分，所以這些規則一次應用于一個國家/地區的數據。請注重，數據結束于 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 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/ Oracle中使用SQL MODEL定義行間計算（2）（圖三）

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, 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/注重:假如您希望創建新單元格(例如，未來幾年的值)，則必須使用位置引用或 FOR 循環(本教程稍后討論)。也就是說，位置引用答應更新數組以及向數組中插入新值。這稱為 UPSERT 過程，它由 Oracle SQL MERGE 語句處理。 Oracle中使用SQL MODEL定義行間計算（2）（圖四）

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, 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/注重:符號引用功能強大，但它們只能用于更新現有的單元格:它們不能創建新單元格，例如，未來幾年的銷售規劃。 Oracle中使用SQL MODEL定義行間計算（2）（圖五）