oracle sql 高级编程学习笔记（二十一)

ゞ浴缸里的玫瑰 2022-05-12 03:28 169阅读 0赞

# 一、行求解顺序 #

select product, country, year, week, inventory, sale, receipts from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) 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 product, country, year, week;

对于列中求出每周的库存，我们把 automatic order 注释掉 ，  
automatic order 允许数据库自动识别规则之间的依赖关系  
会启用默认的排序sequential order 规则排序， 既rules中规则先后顺序  
sql语句会报如下错误  
ORA-32637: 顺序排序 MODEL 中的自循环规则  
![这里写图片描述][70]  
这是因为 代码inventory \[ cv(year), cv(week) - 1 \]子句进行了跨行引用，库存列的值必须按照周的升序来进行计算，  
例如 第二周的库存必须在得到第一周的库存值后才能进行计算。

当然除了使用 automatic order 我们也可以直接在规则进行排序

select product, country, year, week, inventory, sale, receipts from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules /*automatic order*/ (inventory [ year, week ] order by year,week asc --通过order自己显示声明了行求解顺序 = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ] ) order by product, country, year, week;

# 二、规则求解顺序 #

select * from (select product, country, year, week, inventory, sale, receipts from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules sequential order (inventory [ year, week ] order by year,week asc = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ] , receipts[year in(2000,2001),week in (51,52,53)] order by year ,week asc -- 这里排序不能少 不然因为上面规则中涉及到跨行处理 不然还是会报 ORA-32637错误 =receipts[cv(year),cv(week)]*10 ) order by product, country, year, week) where week>50 --用于筛选修改的receipts，便于查看结果;

![这里写图片描述][70 1]

从结果中明显看到，先按照求解库存后，再重新计算收入receipts

# 三、聚合函数 #

select product, country, year, week, inventory, sale, receipts, avg_inventory, max_sale from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory,0 avg_inventory,0 max_sale, 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) ] , avg_inventory[year,any]=round(avg(inventory)[cv(year),week],2), max_sale[year,any]=max(sale)[cv(year),week] ) order by product, country, year, week

avg\_inventory\[year, ANY\] = avg(inventory)\[cv(year) , week \]，用ANY是因为右侧的表达式对于所有的week都会返回相同的值，所以没必要重复计算，对于所有的week只需要计算一次就行了。Avg后面的\[cv(year) , week \]表示聚合的范围是这一年和所有周。  
![这里写图片描述][70 2]

# 四、迭代 #

## 1、实例演示 ##

select year, week, sale, sale_list from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures( cast('' as varchar2(50))sale_list, sale ) rules iterate(5)-- 规则会迭代5次，ITERATION_NUMBER从0到4。 ( sale_list[year,week] order by year,week asc= sale[cv(year),cv(week)-iteration_number+2]||--cv(week)-iteration_number+2 来访问前两周和后两周的数据 case when iteration_number=0 then '' else ',' end|| -- case语句在为每个列表中除了第一个成员外每个成员都加上逗号。 sale_list[cv(year),cv(week)] ) order by year, week

![这里写图片描述][70 3]

同样这也是列转行 可以通过pivot函数实现

## 2、空值 ##

语法  
presentv(cell\_reference,expr1,expr2)  
表示 如果 cell\_reference存在，则返回 expr1，不存在则返回expr2  
PRESENTNNV（NOT NULL VALUE）  
用法同PRESENTV，只是多了非空条件。  
PRESENTNNV(t , v1 , v2) 如果单元格t存在且非空，返回v1，否则，返回v2。

接着示列中的例子

select year, week, sale, sale_list from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures( cast('' as varchar2(50))sale_list, sale ) rules iterate(5) ( sale_list[year,week] order by year,week asc= presentv( sale[cv(year),cv(week)-iteration_number+2], sale[cv(year),cv(week)-iteration_number+2]|| case when iteration_number=0 then '' else ',' end|| sale_list[cv(year),cv(week)], sale_list[cv(year),cv(week)] ) ) order by year, week

结果解决了 示列中存在双逗号问题。  
![这里写图片描述][70 4]

[70]: /images/20220512/8c5c02c7c2564867871b7fdeab470b1d.png
[70 1]: /images/20220512/0930c8202bdf42cc8f62e144d530fae4.png
[70 2]: /images/20220512/2f2fe617793e41b69ea8283d988cfd16.png
[70 3]: /images/20220512/0e716df387ab4117b062433970718016.png
[70 4]: /images/20220512/c0b0b4685a754756a4a14476685b3bfc.png