Oracle SQL Model Clause

參考官方文件，學習了一下Oracle SQL Model語法。
摘自Pro Oracle SQL:
With the MODEL clause, you build matrixes (or a model) of data with a variable number of dimensions. The model
uses a subset of the available columns from the tables in your FROM clause and has to contain at least one dimension,
at least one measure, and, optionally, one or more partitions. You can think of a model as a spreadsheet file containing
separate worksheets for each calculated value (measures). A worksheet has an x- and a y-axis (two dimensions), and
you can imagine having your worksheets split up in several identical areas, each for a different attribute (partition).

官方文件地址：http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm
現將過程記錄在這裡：
1. 準備過程, 連線到Oracle自帶schema SH(sales history)，建立資料來源：
   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

2. Model語法分為3個部分，partition, dimension, measures。 
    Partition與分析函式的partition類似，將結果集分成了若干邏輯塊。Model的rules被應用於每個partition的cells。
  原文：Partitions define logical blocks of the result set in a way similar to the partitions of the analytical functions (described in the chapter titled "SQL for Analysis in Data Warehouses" in the Data Warehousing Guide). MODEL rules are applied to the cells of each partition.
   Dimension用於在每一個partition內區分每個measure的單元格。有點類似於excel中的行與列。如(A1, B1)就定義了一個單元格
   原文：Dimensions identify each measure cell within a partition. These columns identify characteristics such as date, region, and product name.
   Measures: Measures近似於星形模型中的事實表。它們典型包含數值，如銷售單位或成本。每一個單元格都通過指定全部的維度在它的partition內訪問。
    原文：Measures are analogous to the measures of a fact table in a star schema. They typically contain numeric values such as sales units or cost. Each cell is accessed within its partition by specifying its full combination of dimensions.
   
3. 例項：
  例1： 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;
 
 這個SQL建立了幾個新行，比如sales['Y  Box', 2002]使用的是sales['Y  Box', 2001]的銷售金額，sales['2_Products', 2002]是另外兩個產品2002年的銷售金額之和

例2: 
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['Bounce', 2003] = sum(sales)['Bounce',year <= 2002],
       sales['Y Box', 2003] = sum(sales)['Y Box',year <= 2002],
       sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, prod, year;

 
這個例子中，引入了新的rules,  sales['Bounce', 2003] = sum(sales)['Bounce',year <= 2002]表示產品Bounce在2003年的收入金額是2002年以及2002年前的銷售金額之和

例項3：算累加
with t as (
 select rownum rn from dual connect by rownum <= 100 
)
select rn, total from t 
model return updated rows
dimension by (rn)
measures(0 total)
rules(
 total[rn] = cv(rn)+ nvl(total[cv(rn) - 1], 0)
)
求出1到100的和, 這裡用了函式CV(current value)去引用其他單元格資料

另一個求累加的：
with t as (
 select 1 rn from dual
) 
select rn,total from t 
model return updated rows 
dimension by (rn)
measures(0 total)
rules iterate(100)(
 total[1]= nvl(total[1], 0) + (ITERATION_NUMBER +1)
)

rules iterate(100)代表的是將規則迭代100次。ITERATION_NUMBER是迴圈計數，注意是從0開始

參考：
http://www.itpub.net/thread-1904347-4-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-1981874/，如需轉載，請註明出處，否則將追究法律責任。