sql for modeling part1

最新推荐文章于 2024-04-21 10:55:56 发布

cq53641729

最新推荐文章于 2024-04-21 10:55:56 发布

阅读量126

点赞数

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26477854/viewspace-1078195/