爱站程序员基地文章目录
- 一.以一个例子来认识Model子句
- 二.位置标记
- 三.符号标记
- 四.for循环
- 五.聚合
备注:测试数据库版本为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_factwhere 1 = 1model return updated rowspartition by(prod)dimension by(year,week)measures(0 inventory,sale,receipts) rules automaticorder(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_fact8 where 1 = 19 model return updated rows10 partition by(prod)11 dimension by(year,week)12 measures(0 inventory,sale,receipts) rules automatic13 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 435014 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_factwhere 1 = 1model return updated rowspartition by(prod)dimension by(year,week)measures(0 inventory,sale,receipts) rules automaticorder(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] = 300000SQL> select prod ,2 year ,3 week ,4 sale , --销售量5 receipts, --进货量6 inventory --库存量7 from sales_fact8 where 1 = 19 model return updated rows10 partition by(prod)11 dimension by(year,week)12 measures(0 inventory,sale,receipts) rules automatic13 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 10435014 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 10000014 rows selected
假设15周的数据忘记记录了，在原有的基础上加上15周的数据
2020年第15周销售额 100000，进货量100000
--15周数据不存在--我直接在公式列指定即可，马上就有数据了select prod ,year ,week ,sale , --销售量receipts, --进货量inventory --库存量from sales_factwhere 1 = 1model return updated rowspartition by(prod)dimension by(year,week)measures(0 inventory,sale,receipts) rules automaticorder(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_fact8 where 1 = 19 model return updated rows10 partition by(prod)11 dimension by(year,week)12 measures(0 inventory,sale,receipts) rules automatic13 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 10435015 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 10000014 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_factwhere 1 = 1model --return updated rowspartition 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_factwhere 1 = 1model return updated rowspartition 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_fact8 where 1 = 19 model --return updated rows10 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.216 )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 014 rows selectedSQL> --符号标记不同于位置标记，如果不存在不会新增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_fact8 where 1 = 19 model return updated rows10 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.216 )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_factwhere 1 = 1model return updated rowspartition 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_fact8 where 1 = 19 model return updated rows10 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 435015 rows selected
五.聚合
Model子句还可以配合avg、sum、max等函数一起使用
select prod ,year ,week ,sale , --销售量receipts, --进货量inventory, --库存量avg_inventory , --平均库存max_sale --单周最大销售额from sales_factwhere 1 = 1model return updated rowspartition by(prod)dimension by(year,week)measures(0 inventory,sale,receipts,0 avg_inventory,0 max_sale) rules automaticorder(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_fact10 where 1 = 111 model return updated rows12 partition by(prod)13 dimension by(year,week)14 measures(0 inventory,sale,receipts,0 avg_inventory,0 max_sale) rules automatic15 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 10000014 rows selected
