model
第一部分
基本用法


创建实验脚本
create table sales(country varchar2(10),product varchar2(30),year varchar2(4),sales number);
insert into sales values('CHINA','PC','2013','300');
insert into sales values('CHINA','PC','2012','250');
insert into sales values('CHINA','PHONE','2013','1000');
insert into sales values('CHINA','PHONE','2012','800');
insert into sales values('CHINA','PHONE','2011','350');
insert into sales values('CHINA','PAD','2013','2000');
insert into sales values('JAPAN','PC','2013','400');
insert into sales values('JAPAN','PC','2012','200');
insert into sales values('JAPAN','PC','2011','150');
insert into sales values('JAPAN','PHONE','2013','650');
insert into sales values('JAPAN','PHONE','2012','400');
insert into sales values('JAPAN','PHONE','2011','180');
insert into sales values('JAPAN','PAD','2013','280');
insert into sales values('ITALY','PC','2013','690');
insert into sales values('ITALY','PC','2012','360');
insert into sales values('ITALY','PHONE','201','440');
insert into sales values('ITALY','PHONE','201','380');
insert into sales values('ITALY','PAD','201','600');
insert into sales values('ITALY','PAD','201','200');
COMMIT;


SQL FOR MODELING

牢记四大点
partition --分区
dimension --维度
measures  --计量
rules     --规则


test1 
统计各国各产品的产量和（展现方式：country product 2014 sales2013+sales2012）
select country, product, year, sales
  from sales t 
 model 
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales [ 'PAD', 2014 ] = sales [ 'PAD', 2013 ] + nvl(sales [ 'PAD', 2012 ], 0), 
    sales [ 'PC', 2014 ] = sales [ 'PC', 2013 ] + sales [ 'PC', 2012 ], 
    sales [ 'PHONE', 2014 ] = sales [ 'PHONE', 2013 ] + sales [ 'PHONE', 2012 ])
 order by country, product, year;   


COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PAD                            2013       2000
CHINA      PAD                            2014       2000
CHINA      PC                             2012        250
CHINA      PC                             2013        300
CHINA      PC                             2014        550
CHINA      PHONE                          2011        350
CHINA      PHONE                          2012        800
CHINA      PHONE                          2013       1000
CHINA      PHONE                          2014       1800
ITALY      PAD                            2012        200
ITALY      PAD                            2013        600
ITALY      PAD                            2014        800
ITALY      PC                             2012        360
ITALY      PC                             2013        690
ITALY      PC                             2014       1050
ITALY      PHONE                          2012        380
ITALY      PHONE                          2013        440
ITALY      PHONE                          2014        820
JAPAN      PAD                            2013        280
JAPAN      PAD                            2014        280
JAPAN      PC                             2011        150
JAPAN      PC                             2012        200
JAPAN      PC                             2013        400
JAPAN      PC                             2014        600
JAPAN      PHONE                          2011        180
JAPAN      PHONE                          2012        400
JAPAN      PHONE                          2013        650
JAPAN      PHONE                          2014       1050
 
28 rows selected
如果我们只想返回新的行，那么可以使用RETURN UPDATED ROWS从句（在后面的test中为了更直观默认只显示新行），例如：
select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales [ 'PAD', 2014 ] = sales [ 'PAD', 2013 ] + nvl(sales [ 'PAD', 2012 ], 0), 
    sales [ 'PC', 2014 ] = sales [ 'PC', 2013 ] + sales [ 'PC', 2012 ], 
    sales [ 'PHONE', 2014 ] = sales [ 'PHONE', 2013 ] + sales [ 'PHONE', 2012 ])
 order by country, product, year; 
 
COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PAD                            2014       2000
CHINA      PC                             2014        550
CHINA      PHONE                          2014       1800
ITALY      PAD                            2014        800
ITALY      PC                             2014       1050
ITALY      PHONE                          2014        820
JAPAN      PAD                            2014        280
JAPAN      PC                             2014        600
JAPAN      PHONE                          2014       1050
 
9 rows selected
 
test2 
统计各国各产品历年来的产量最大值（展现方式：country product 2014 max(sales)） 
select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales ['PAD', 2014 ] = max(sales) ['PAD',year between 2010 and 2013],
    sales ['PC', 2014 ] = max(sales) ['PC',year between 2010 and 2013],
    sales ['PHONE', 2014 ] = max(sales) ['PHONE',year between 2010 and 2013] ) 
 order by country, product, year; 
 
 COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PAD                            2014       2000
CHINA      PC                             2014        300
CHINA      PHONE                          2014       1000
ITALY      PAD                            2014        600
ITALY      PC                             2014        690
ITALY      PHONE                          2014        440
JAPAN      PAD                            2014        280
JAPAN      PC                             2014        400
JAPAN      PHONE                          2014        650
 
9 rows selected


test3
UPSERT, UPSERT ALL, and UPDATE options
在默认情况下，MODEL从句中默认规则具有Upsert特征，也就是如果规则左侧指出的单元格存在，
那么它会被更新，否则将生成包含该单元格的一个新行。
select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (update sales ['PAD', 2013 ] = 100,
   upsert sales ['PAD', 2014 ] = sales ['PAD', 2013 ]+nvl(sales ['PAD', 2012 ],0)) 
 order by country, product, year; 
 
 COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PAD                            2013        100
CHINA      PAD                            2014        100
ITALY      PAD                            2013        100
ITALY      PAD                            2014        300
JAPAN      PAD                            2013        100
JAPAN      PAD                            2014        100
 
6 rows selected


test4
通配符any
You can use ANY and IS ANY to specify all values in a dimension.


select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (update sales [any, 2013 ] = sales ['PAD', 2013 ]) --也可以写为 (update sales [product is any, 2013 ] = sales ['PAD', 2013 ]) 
 order by country, product, year; 
 COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PAD                            2013       2000
CHINA      PC                             2013       2000
CHINA      PHONE                          2013       2000
ITALY      PAD                            2013        600
ITALY      PC                             2013        600
ITALY      PHONE                          2013        600
JAPAN      PAD                            2013        280
JAPAN      PC                             2013        280
JAPAN      PHONE                          2013        280
 
9 rows selected




test5
cv函数的运用
如下面这种情况就可以用cv函数简化语句
 select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales[product='PC', 2013] = 1.2 * sales['PC', 2013],
    sales[product='PAD', 2013] = 1.2 * sales['PAD', 2013],
    sales[product='PHONE', 2013] = 1.2 * sales['PHONE', 2013]) 
 order by country, product, year; 
 可以改写为
  select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales[product in('PC','PAD','PHONE'), 2013] = 1.2 * sales[cv(product), 2013]) 
 order by country, product, year; 
 
 test6
Ordered computation
sales[product IS ANY, year BETWEEN 2011 AND 2013] ORDER BY year = 
  1.05 * sales[CV(product), CV(year)-1]
  
 test7
 Automatic rule ordering
 自动定义rule执行顺序
  select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules  AUTOMATIC ORDER             --规则
   (sales['PC', 2011] = sales['PC', 2012] + sales['PC', 2013],
    sales['PC', 2012] = 50000,
    sales['PC', 2013] = 40000) 
 order by country, product, year; 
 这个例子会先转化为
 (sales['PC', 2012] = 50000,
  sales['PC', 2013] = 40000,
  sales['PC', 2011] = sales['PC', 2012] + sales['PC', 2013]) 
  
  test8
  Iterative rule evaluation
  重复执行
  select country, product, year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product, t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   ITERATE (4)                        --重复执行 括号里面表示次数
   (sales['PC', 2011] = sales['PC', 2011]/2)
 order by country, product, year; 
 
 COUNTRY    PRODUCT                        YEAR      SALES
---------- ------------------------------ ---- ----------
CHINA      PC                             2011 
ITALY      PC                             2011 
JAPAN      PC                             2011      9.375


 在这个例子中JAPAN的PC销售量2011为150次，在重复4次/2(即/16)后  变为9.375
 
 test9
 select country,product,year, sales
  from sales t 
 model RETURN UPDATED ROWS            --只显示新增行
   partition by(t.country)            --按country分区
   dimension by(t.product,t.year)    --以产品、年份为维度
   measures(sales)                    --计算产量和
   rules                              --规则
   (sales['PC',2011] = avg(sales)['PC',year between 2011 and 2013])
 order by country,product, year;