Oracle 10g SQL MODEL 子句：跨行計算的強大利器

教學目標與大綱

本教程將引導您掌握 Oracle Database 10g 中 SQL MODEL 子句的核心功能，學習如何在 SQL 查詢中定義多維陣列並套用規則進行跨行計算。預計學習時間約 30 分鐘，涵蓋從基礎語法到進階應用的完整主題：

• 概觀與應用情境
• 前置條件與範例資料建置
• 基本語法與符號/位置單元格引用
• 多單元格引用、CV() 函數與 ANY 通配符
• FOR 迴圈、規則評估順序、NULL 處理
• 參考模型、迭代模型與排序規則

核心概念：多維陣列與規則運算

SQL MODEL 子句將查詢結果轉換為多維陣列，包含三大元素：

• 分區欄位：劃分邏輯資料塊，規則獨立套用於各分區（類似分析函數的 PARTITION BY）。
• 維度欄位：識別陣列中每個儲存格的座標（如產品、年份）。
• 度量欄位：儲存數值資料（如銷售額），透過完整維度組合定位。

規則以維度值表達計算邏輯，支援萬用字元與 FOR 迴圈，將複雜的試算表運算內建於資料庫，大幅提升效能、擴展性與管理性，避免資料外移至 Excel 等工具。

快速上手：建立範例視圖

使用 SH 範例結構描述，建立 sales_view 視圖，彙總各國產品年度銷售額與筆數：

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;

驗證視圖共 3,219 筆記錄，並確認物化視圖已啟用查詢重寫以優化效能。

基礎範例：預測 2002 年銷售

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;

關鍵點：

• RETURN UPDATED ROWS 僅回傳新增或更新的列（此處為 2002 年預測值）。
• 規則依序執行：前兩條規則產生的 2002 年值，被第三條規則引用彙總。
• 位置引用（如 sales['Bounce', 2002]）可插入新儲存格；符號引用（如 sales[prod='Bounce', year>2000]）僅能更新既有儲存格。

進階技巧：靈活的單元格引用與聚合

位置 vs. 符號引用

• 位置引用：依維度順序對應，支援 UPSERT（更新或插入）。
• 符號引用：以布林條件比對維度，功能強大但無法建立新儲存格。

規則右側的多單元格引用

需搭配聚合函數（MAX、SUM 等）將多值化為單值：

sales['Bounce', 2005] = 100 + MAX(sales)['Bounce', year BETWEEN 1998 AND 2002]

CV() 函數與 ANY 通配符：動態引用當前維度

CV() 可在規則右側取得左側當前的維度值，實現跨列同期計算：

sales['Bounce', year BETWEEN 1995 AND 2002] =
    sales['Mouse Pad', CV(year)] + 0.2 * sales['Y Box', CV(year)]

亦可運算：CV(year)-1 存取前一年資料。ANY 通配符則代表該維度所有值，簡化規則撰寫。

FOR 迴圈：批量產生新儲存格

以簡潔語法建立多個預測年份：

sales[FOR prod IN ('Bounce','Y Box'), FOR year IN (2002 TO 2005)] = 0

支援 INCREMENT、FROM ... TO 等多種生成模式。

規則評估順序與 NULL 處理

• SEQUENTIAL ORDER（預設）：依規則撰寫順序執行，後續規則可見前一規則結果。
• AUTOMATIC ORDER：Oracle 自動分析相依性並決定執行順序。
• 使用 IGNORE NAV 忽略 NULL 度量，或 KEEP NAV 保留 NULL；UNIQUE SINGLE REFERENCE / UNIQUE DIMENSION 控制單元格唯一性。

參考模型與迭代模型

• 參考模型：唯讀子查詢結果陣列，供主模型規則引用（如匯率表、成長率表）。
• 迭代模型：規則重複執行至滿足終止條件（ITERATE(n) + UNTIL），適用遞迴計算（如複利、階乘）。

排序規則與總結

ORDER BY 子句可指定規則內儲存格處理順序，確保相依計算正確。SQL MODEL 將試算表級的跨行運算納入資料庫核心，以宣告式 SQL 取代程序式邏輯，兼顧效能與維護性，是資料倉庫與複雜報表的利器。