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

一、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

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

中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;

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;

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;

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;