一、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
可以回忆上一篇 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;
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;