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

一、Model 子句剖析:

通过 model return updated rows 或者model来声明这个语句
使用model子句。一个model子句有三组列，分区列，唯独列，
以及度量值列。
分区列类似于电子表格excel中的一张工作表，
维度列类似于行标签(A,B,C……)和列标签(1,2,3……)
度量值类似于 含有公式的单元格

实例演示:

实现求某地区某周的库存

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;

查询结果:

通过上面实例演示:
实列中一个非常有用的函数cv。cv表示现值
可以用来表示从规则左侧计算得来规则右侧的列值。
例如：cv(year) 指的是规则左侧year列的指。其实就是引用某个单元格的值。
假如数据来到 第一行 year=1998，week=1
inventory[cv(year),cv(week)-1]表示 取 year=1998，week=0 的库存 很明显是空
sale [ cv(year), cv(week) ] 表示 year=1998，week=1的销售 sale 值
同理 receipts [ cv(year), cv(week) ] 表示 year=1998，week=1的 receipts 值

inventory [ 1998, 1 ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]

inventory [ 1998, 1 ] = nvl(inventory [ cv(1998), cv(1) - 1 ], 0) - sale [ cv(1998), cv(1) ] + receipts [ cv(1998), cv(1) ])
= 0-58.15+67.03=8.88

二、符号标记

CV函数提供了引用一个单元格的能力，还能通过使用位置或符号标记来引用某个单独的单元格或单元格组。此外还可以通过for循环来以类似数组的方式创建或修改多个单元格。位置标记提供了在结果集中插入一个新单元格或更新一个单元格的能力：如果存在则更新，不存在则插入，这种概念称为upsert特性是update和insert功能的融合版本

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) ]
   ,sale[2002,1]=0,
    receipts[2002,1]=0)
 order by product, country, year, week;

数据中没有满足年份是2002，week是1的数据，所以sale列以及receipts列数据都会插入进去，见结果集中最后一行数据

三、符号标记

仅提供了update功能，而位置标记提供了upsert功能。注意 model子句中不能使用别名点，会如下错误：

将year =2000以及2001 week in（1，52，53）的 sale改为原来的1.1倍

select country, product,year, week, sale
  from sales_fact  
 where country = 'Australia'
   and product = 'Xtend Memory' 
   model  return updated rows
--  return updated rows 只返回修改的记录行
    partition by(country, product) 
    dimension by(year, week)
    measures(sale)
    rules(
              sale [ year in (2000, 2001),
              week in (1, 52, 53) ] order by year,
             week = sale [ cv(year),
             cv(week) ] * 1.10
             )
 order by year, week

可见，对于不满足week条件=53的记录，既没有修改也没有插入，符号标记不具有insert功能。

四、for循环

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) ]
   ,sale[2002,for week from 1 to 53 increment 1]=0,
   receipts[2002,for week from 1 to 53 increment 1]=0
   )--increment 设置步长
 order by product, country, year, week;

结果如下，通过for循环初始化2002年的数据，可见for循环也支持upsert功能