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

2024-08-29 13:34:36

字體：大中小

來源：轉載

供稿：網友

4.您希望通過單一查詢來更新多個國家/地區的多種產品在數年中的銷售，并且還希望插入新的單元格。通過將數個規則置于一個查詢中，處理會更加高效，因為這減少了需要訪問數據的次數。它還答應使用更為簡潔的 SQL，以使開發人員的工作效率更高。從 SQL*Plus 會話中，執行以下腳本:
@c:/wkdir/pos_sym.sql pos_sym.sql 腳本包含以下內容: 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/該示例數據沒有超出 2001 年的值，因此所有涉及到 2002 年或之后的規則都要求插入新的單元格。對于此處定義的任何新產品名也是如此。在第三條規則中，2_Products 被定義為 2005 年的銷售是 2001 年 Bounce 銷售與 2000 年 Y Box 銷售總和的產品。對于 2002 年的 Bounce，第一個規則將插入新的單元格，因為這是位置表示法。對于 Y Box，第二個規則使用符號表示法，但是此處已經有了 2001 年的 Y Box 值，因此它將更新這些值。對于 2005 年的 2_Products，第三個規則是位置表示法，因此它可以插入新的單元格，您將在輸出中看到這些新單元格。 Oracle中使用SQL MODEL定義行間計算（3）（圖一）

在規則右側使用多單元格引用早先的示例只能在規則的左側使用多單元格引用。假如要在規則右側引用多個單元格，您可以在規則右側使用多單元格引用，在這種情況下，需要對其應用聚合函數，以將它們轉換為單一值。可以使用所有現有的聚合函數，包括 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, 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/ Oracle中使用SQL MODEL定義行間計算（3）（圖二）

請注重，聚合函數只出現在規則的右側。聚合函數的參數可以是常量、約束變量、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, 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/ Oracle中使用SQL MODEL定義行間計算（3）（圖三）

請注重，在上述結果中，盡管接受了 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, 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/ Oracle中使用SQL MODEL定義行間計算（3）（圖四）