Oracle SQL Model Clause

參考官方文檔，學(xué)習(xí)了一下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
現(xiàn)將過程記錄在這里：
1. 準(zhǔn)備過程, 連接到Oracle自帶schema SH(sales history)，創(chuàng)建數(shù)據(jù)源：
   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與分析函數(shù)的partition類似，將結(jié)果集分成了若干邏輯塊。Model的rules被應(yīng)用于每個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內(nèi)區(qū)分每個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近似于星形模型中的事實表。它們典型包含數(shù)值，如銷售單位或成本。每一個單元格都通過指定全部的維度在它的partition內(nèi)訪問。
    原文： 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創(chuàng)建了幾個新行，比如sales['Y  Box', 2002]使用的是 sales['Y  Box ', 2001 ]的銷售金額， sales['2_Products ', 2002 ] 是另外兩個產(chǎn)品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]表示 產(chǎn)品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的和, 這里用了函數(shù)CV(current value)去引用其他單元格數(shù)據(jù)

另一個求累加的：
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)代表的是將規(guī)則迭代100次。 ITERATION_NUMBER是循環(huán)計數(shù)，注意是從0開始

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