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

曾经终败给现在 2022-05-12 04:36 287阅读 0赞

# 一、model 空值 #

model 子句存在空值的原因有两个  
1、单员格存在，但值为空  
2、单元格不存在

## 1、实例演示 nav ##

select product, country, year, week, sale from sales_fact where country = 'Australia' and product = 'Xtend Memory' model keep nav return updated rows --keep nav是默认值 nav(non avaliable values) 没有可用值 partition by(product, country) dimension by(year, week) measures(sale) rules sequential order(sale [ 2001, 1 ] order by year, week = sale [ 2001, 1 ], sale [ 2002, 1 ] order by year, week = sale [ 2001, 1 ] + sale [ 2002, 1 ]) order by product, country, year, week

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

## 2、实例演示 ignore nav ##

select product, country, year, week, sale from sales_fact where country = 'Australia' and product = 'Xtend Memory' model ignore nav return updated rows -- 如果位空 则返回0 partition by(product, country) dimension by(year, week) measures(sale) rules sequential order(sale [ 2001, 1 ] order by year, week = sale [ 2001, 1 ], sale [ 2002, 1 ] order by year, week = sale [ 2001, 1 ] + sale [ 2002, 1 ]) order by product, country, year, week

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

可以回忆上一篇 [https://blog.csdn.net/whandgdh/article/details/82697327][https_blog.csdn.net_whandgdh_article_details_82697327] 中presetv 以及presentnnv 两个函数对空值的处理

# 二、model子句性能调优 #

model 子句最关键的就是规则求解。  
规则求解可以使用如下5种算法：  
1、ACYCLIC  
2、ACYCLIC FAST  
3、CYCLIC  
4、ORDERED  
5、ORDERED FAST  
ACYCLIC FAST ORDERED FAST是相对较优的算法，允许单元格高效求解。  
如果算法指定了automatic order 会选择使用ACYCLIC和CYCLIC 算法  
如果指定了 sequential order 则会选择使用ORDERED 算法  
如果规则访问某个独立的单元格不进行聚合有可能选择 ACYCLIC FAST 或ORDERED FAST

## 2、执行计划 ##

**2、1 ACYCLIC**  
实例演示 ：model 子句开始求库存的实例 我们在规则种再加上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 -- 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;

![这里写图片描述][70 2]  
ACYCLIC 表明了规则之间没有可能的CYCLIC（循环）依赖关系  
**2.2、ACYCLIC FAST**  
如果规则只访问一个单元格，可以使用ACYCLIC FAST 算法

select product, country, year, week, sale ,sale_modify from sales_fact where country = 'Australia' and product = 'Xtend Memory' model return updated rows partition by(product, country) dimension by(year, week) measures( 0 sale_modify, sale ) rules automatic order(sale_modify[2001,1]=sale[2001,1]*10) order by product, country, year, week;

![这里写图片描述][70 3]  
**2.3 、CYCLIC**

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;

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

2.4、ORDERED

select product, country, year, week, inventory, sale, receipts,sale_modify 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 sale_modify,sale, receipts) rules sequential order(inventory [ year, week ] order by year ,week -- order 来控制规则之间的依赖关系，避免循环依赖性 = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ], sale_modify[2001,1]=sale[2001,1]*10 ) order by product, country, year, week;

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

[70]: /images/20220512/c7fb1125542c45a3853a84ff591e4469.png
[70 1]: /images/20220512/e31c1e995c264c888097bba1cb6089a7.png
[https_blog.csdn.net_whandgdh_article_details_82697327]: https://blog.csdn.net/whandgdh/article/details/82697327
[70 2]: /images/20220512/13242f9c5b5742f888f2eac00d9359b0.png
[70 3]: /images/20220512/420c7b4ef62c4ab591960d99a017b94c.png
[70 4]: /images/20220512/3bb3edfd2e0d4389830da5c3168d2b78.png
[70 5]: /images/20220512/4b2830662b184bc49094f64f8eb301fe.png