sql for modeling part1

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/，如需轉載，請註明出處，否則將追究法律責任。

sql for modeling part1

相關文章