一、行求解顺序
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 中的自循环规则
这是因为 代码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,便于查看结果;
从结果中明显看到,先按照求解库存后,再重新计算收入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 ]表示聚合的范围是这一年和所有周。
四、迭代
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
同样这也是列转行 可以通过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
结果解决了 示列中存在双逗号问题。