Table of Contents
备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊Oracle的Model子句
Oracle的Model子句非常强大，本文章简单介绍下Model
Model子句语法:
MODEL
[]
[]
[MAIN ]
[PARTITION BY ()]
DIMENSION BY ()
MEASURES ()
[]
[RULES]
(, ,.., )
::=
::= RETURN {ALL|UPDATED} ROWS
::=
[IGNORE NAV | [KEEP NAV]
[UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
::=
[UPDATE | UPSERT | UPSERT ALL]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
[ITERATE () [UNTIL ]]
::= REFERENCE ON ON ()
DIMENSION BY () MEASURES ()
复制一.以一个例子来认识Model子句
看到上面Model复杂的语法，头都大了，不清楚如何使用
我们先简单的捋一捋Model子句是用来解决什么问题的
上面是一个excel表格，记录了每周口罩的进货量、销售量和库存
本周库存 = 本周进货量-本周销售额+上周库存
在excel中，我们可以用上图中的公式，来计算每周的库存
Oracle中，Model子句就是为了解决这类跨行引用
测试数据:
create table sales_fact
(
prod varchar2(20),--产品
year number, --年
week number, --月
sale number, --销售额
receipts number --进货量
);
insert into sales_fact values ('口罩',2020,1,100,200);
insert into sales_fact values ('口罩',2020,2,100,200);
insert into sales_fact values ('口罩',2020,3,150,300);
insert into sales_fact values ('口罩',2020,4,1000,5000);
insert into sales_fact values ('口罩',2020,5,2000,10000);
insert into sales_fact values ('口罩',2020,6,3000,0);
insert into sales_fact values ('口罩',2020,7,5000,0);
insert into sales_fact values ('口罩',2020,8,10000,10000);
insert into sales_fact values ('口罩',2020,9,100000,100000);
insert into sales_fact values ('口罩',2020,10,100000,100000);
insert into sales_fact values ('口罩',2020,11,100000,100000);
insert into sales_fact values ('口罩',2020,12,100000,100000);
insert into sales_fact values ('口罩',2020,13,100000,100000);
insert into sales_fact values ('口罩',2020,14,100000,100000);
commit;
复制我们通过Model子句可以很轻易的实现这个需求:
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts) rules automatic
order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] )
order by prod,year,week;
复制SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts) rules automatic
13 order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] )
14 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200 100
口罩 2020 2 100 200 200
口罩 2020 3 150 300 350
口罩 2020 4 1000 5000 4350
口罩 2020 5 2000 10000 12350
口罩 2020 6 3000 0 9350
口罩 2020 7 5000 0 4350
口罩 2020 8 10000 10000 4350
口罩 2020 9 100000 100000 4350
口罩 2020 10 100000 100000 4350
口罩 2020 11 100000 100000 4350
口罩 2020 12 100000 100000 4350
口罩 2020 13 100000 100000 4350
口罩 2020 14 100000 100000 4350
14 rows selected
复制| 子句 | 说明 |
|---|---|
| partition by(prod) | 将prod列指定为分区列 |
| dimension by(year, week) | 指定year,week为维度列 |
| measures(0 inventory, sale, receipts) | 将inventory,sales,receipts列指定为度量值列 |
| order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]) |
规则类似于一个公式 |
prod分区，与Oracle其它分析函数的分区相同，分区列值相同的所有行被认为是在同一个分区中
year、week为维度，维度列唯一辩识每一行，产品为口罩，每年每周只有一行数据
measures(0 inventory, sale, receipts)表示 inventory, sale, receipts三列为计算的列值
计算公式这个，可以类比excel截图中的公式来看， 0 inventory 代表如果找不到上周的库存，默认值为0
二.位置标记
假设2020年第8周，口罩销售量和进货量猛增，有部分数据没有录入系统
其实真实的进货量为300000，销售量也为200000
--公式列直接修改数据，这也太方便了吧
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts) rules automatic
order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],sale[2020,8] = 200000, receipts[2020,8] = 300000)
order by prod,year,week;
复制--公式列直接修改数据，这也太方便了吧
--sale[2020,8] = 200000, receipts[2020,8] = 300000
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts) rules automatic
13 order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],sale[2020,8] = 200000, receipts[2020,8] = 300000)
14 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200 100
口罩 2020 2 100 200 200
口罩 2020 3 150 300 350
口罩 2020 4 1000 5000 4350
口罩 2020 5 2000 10000 12350
口罩 2020 6 3000 0 9350
口罩 2020 7 5000 0 4350
口罩 2020 8 200000 300000 104350
口罩 2020 9 100000 100000 104350
口罩 2020 10 100000 100000 104350
口罩 2020 11 100000 100000 104350
口罩 2020 12 100000 100000 104350
口罩 2020 13 100000 100000 104350
口罩 2020 14 100000 100000 104350
14 rows selected
--可以看到model子句只是改了输出，原表的数据并没有更新
SQL> select * from sales_fact;
PROD YEAR WEEK SALE RECEIPTS
-------------------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200
口罩 2020 2 100 200
口罩 2020 3 150 300
口罩 2020 4 1000 5000
口罩 2020 5 2000 10000
口罩 2020 6 3000 0
口罩 2020 7 5000 0
口罩 2020 8 10000 10000
口罩 2020 9 100000 100000
口罩 2020 10 100000 100000
口罩 2020 11 100000 100000
口罩 2020 12 100000 100000
口罩 2020 13 100000 100000
口罩 2020 14 100000 100000
14 rows selected
复制假设15周的数据忘记记录了，在原有的基础上加上15周的数据
2020年第15周销售额 100000，进货量100000
--15周数据不存在
--我直接在公式列指定即可，马上就有数据了
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts) rules automatic
order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],sale[2020,8] = 200000, receipts[2020,8] = 300000,sale[2020,15] = 100000, receipts[2020,15] = 100000)
order by prod,year,week;
复制SQL> --15周数据不存在
SQL> --我直接在公式列指定即可，马上就有数据了
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts) rules automatic
13 order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],sale[2020,8] = 200000, receipts[2020,8] = 300000,sale[2020,15] = 100000, receipts[2020,15] = 100000)
14 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200 100
口罩 2020 2 100 200 200
口罩 2020 3 150 300 350
口罩 2020 4 1000 5000 4350
口罩 2020 5 2000 10000 12350
口罩 2020 6 3000 0 9350
口罩 2020 7 5000 0 4350
口罩 2020 8 200000 300000 104350
口罩 2020 9 100000 100000 104350
口罩 2020 10 100000 100000 104350
口罩 2020 11 100000 100000 104350
口罩 2020 12 100000 100000 104350
口罩 2020 13 100000 100000 104350
口罩 2020 14 100000 100000 104350
口罩 2020 15 100000 100000 104350
15 rows selected
--可以看到model子句只是改了输出，原表的数据并没有更新
SQL> select * from sales_fact;
PROD YEAR WEEK SALE RECEIPTS
-------------------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200
口罩 2020 2 100 200
口罩 2020 3 150 300
口罩 2020 4 1000 5000
口罩 2020 5 2000 10000
口罩 2020 6 3000 0
口罩 2020 7 5000 0
口罩 2020 8 10000 10000
口罩 2020 9 100000 100000
口罩 2020 10 100000 100000
口罩 2020 11 100000 100000
口罩 2020 12 100000 100000
口罩 2020 13 100000 100000
口罩 2020 14 100000 100000
14 rows selected
复制三.符号标记
此时需求14-16周的销售额和进货量都是录入数据的1.2倍
--符号标记不同于位置标记，如果不存在不会新增
--此列自己指定了每个公式的order by 没有用 automatic order，但是推荐使用 automatic order,Oracle还是很智能的
--注释了return updated rows之后，显示所有
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model --return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts)
rules (--inventory[year,week] order by year,week = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] ,
sale[year in (2020),week in (14,15,16)] order by year,week = sale[cv(year),cv(week)]*1.2,
receipts[year in (2020),week in (14,15,16)] order by year,week = receipts[cv(year),cv(week)]*1.2
)
order by prod,year,week;
--符号标记不同于位置标记，如果不存在不会新增
--此列自己指定了每个公式的order by 没有用 automatic order，但是推荐使用 automatic order,Oracle还是很智能的
--加上return updated rows之后，只显示更改的
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts)
rules (--inventory[year,week] order by year,week = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] ,
sale[year in (2020),week in (14,15,16)] order by year,week = sale[cv(year),cv(week)]*1.2,
receipts[year in (2020),week in (14,15,16)] order by year,week = receipts[cv(year),cv(week)]*1.2
)
order by prod,year,week;
复制SQL> --符号标记不同于位置标记，如果不存在不会新增
SQL> --此列自己指定了每个公式的order by 没有用 automatic order，但是推荐使用 automatic order,Oracle还是很智能的
SQL> --注释了return updated rows之后，显示所有
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model --return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts)
13 rules (--inventory[year,week] order by year,week = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] ,
14 sale[year in (2020),week in (14,15,16)] order by year,week = sale[cv(year),cv(week)]*1.2,
15 receipts[year in (2020),week in (14,15,16)] order by year,week = receipts[cv(year),cv(week)]*1.2
16 )
17 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200 0
口罩 2020 2 100 200 0
口罩 2020 3 150 300 0
口罩 2020 4 1000 5000 0
口罩 2020 5 2000 10000 0
口罩 2020 6 3000 0 0
口罩 2020 7 5000 0 0
口罩 2020 8 10000 10000 0
口罩 2020 9 100000 100000 0
口罩 2020 10 100000 100000 0
口罩 2020 11 100000 100000 0
口罩 2020 12 100000 100000 0
口罩 2020 13 100000 100000 0
口罩 2020 14 120000 120000 0
14 rows selected
SQL> --符号标记不同于位置标记，如果不存在不会新增
SQL> --此列自己指定了每个公式的order by 没有用 automatic order，但是推荐使用 automatic order,Oracle还是很智能的
SQL> --加上return updated rows之后，只显示更改的
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts)
13 rules (--inventory[year,week] order by year,week = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] ,
14 sale[year in (2020),week in (14,15,16)] order by year,week = sale[cv(year),cv(week)]*1.2,
15 receipts[year in (2020),week in (14,15,16)] order by year,week = receipts[cv(year),cv(week)]*1.2
16 )
17 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 14 120000 120000 0
复制四.for循环
假设现在有需求，2020年第8周开始，每周进货量和销售额都是150000
for dimension for <value1> to <value2> [increment | decrement] <value3>复制
--此时不存在的15周也出来了
--for循环可以减少很多代码量
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts)
rules automatic order(
sale[2020,for week from 8 to 15 increment 1] = 150000,
receipts[2020,for week from 8 to 15 increment 1] = 150000,
inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)]
)
order by prod,year,week;
复制SQL> --此时不存在的15周也出来了
SQL> --for循环可以减少很多代码量
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory --库存量
7 from sales_fact
8 where 1 = 1
9 model return updated rows
10 partition by(prod)
11 dimension by(year,week)
12 measures(0 inventory,sale,receipts)
13 rules automatic order(
14 sale[2020,for week from 8 to 15 increment 1] = 150000,
15 receipts[2020,for week from 8 to 15 increment 1] = 150000,
16 inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)]
17 )
18 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY
-------------------- ---------- ---------- ---------- ---------- ----------
口罩 2020 1 100 200 100
口罩 2020 2 100 200 200
口罩 2020 3 150 300 350
口罩 2020 4 1000 5000 4350
口罩 2020 5 2000 10000 12350
口罩 2020 6 3000 0 9350
口罩 2020 7 5000 0 4350
口罩 2020 8 150000 150000 4350
口罩 2020 9 150000 150000 4350
口罩 2020 10 150000 150000 4350
口罩 2020 11 150000 150000 4350
口罩 2020 12 150000 150000 4350
口罩 2020 13 150000 150000 4350
口罩 2020 14 150000 150000 4350
口罩 2020 15 150000 150000 4350
15 rows selected
复制五.聚合
Model子句还可以配合avg、sum、max等函数一起使用
select prod ,
year ,
week ,
sale , --销售量
receipts, --进货量
inventory, --库存量
avg_inventory , --平均库存
max_sale --单周最大销售额
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts,0 avg_inventory,0 max_sale) rules automatic
order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],
avg_inventory[year,ANY] = round(avg(inventory)[cv(year),week],2),
max_sale[year,ANY] = max(sale)[cv(year),week]
)
order by prod,year,week;
复制SQL> --在求库存的基础上,增加平均库存及最大销售额
SQL> select prod ,
2 year ,
3 week ,
4 sale , --销售量
5 receipts, --进货量
6 inventory, --库存量
7 avg_inventory , --平均库存
8 max_sale --单周最大销售额
9 from sales_fact
10 where 1 = 1
11 model return updated rows
12 partition by(prod)
13 dimension by(year,week)
14 measures(0 inventory,sale,receipts,0 avg_inventory,0 max_sale) rules automatic
15 order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],
16 avg_inventory[year,ANY] = round(avg(inventory)[cv(year),week],2),
17 max_sale[year,ANY] = max(sale)[cv(year),week]
18 )
19 order by prod,year,week;
PROD YEAR WEEK SALE RECEIPTS INVENTORY AVG_INVENTORY MAX_SALE
-------------------- ---------- ---------- ---------- ---------- ---------- ------------- ----------
口罩 2020 1 100 200 100 4392.86 100000
口罩 2020 2 100 200 200 4392.86 100000
口罩 2020 3 150 300 350 4392.86 100000
口罩 2020 4 1000 5000 4350 4392.86 100000
口罩 2020 5 2000 10000 12350 4392.86 100000
口罩 2020 6 3000 0 9350 4392.86 100000
口罩 2020 7 5000 0 4350 4392.86 100000
口罩 2020 8 10000 10000 4350 4392.86 100000
口罩 2020 9 100000 100000 4350 4392.86 100000
口罩 2020 10 100000 100000 4350 4392.86 100000
口罩 2020 11 100000 100000 4350 4392.86 100000
口罩 2020 12 100000 100000 4350 4392.86 100000
口罩 2020 13 100000 100000 4350 4392.86 100000
口罩 2020 14 100000 100000 4350 4392.86 100000
14 rows selected
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
目录
