sql for modeling part1

yuanzai32發表於2014-02-08
SQL
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; 

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

相關文章