oracle:MODEL子句

使用MODEL 子句

MODEL 子句像数组那样访问记录中的某列

eg：查询员工（e_id）#21完成的产品类型（id）为#1和#2的销量，并根据2003年销售预测2004年123月的值

SELECT id,year,month,sales_amount
FROM table_name
WHERE id BETWEEN 1 and 2
AND e_id=21
MODEL
PARTITION BY(id)
DIMENSION BY(month , year) 
MEASURES (amount sales_amount) (
  sales_amount[1,2004] = sales_amount[1,2003],
  sales_amount[2,2004] = sales_amount[2,2003],
  sales_amount[3,2004] = ROUND(sales_amount[3,2003]*1.24,2)
)
ORDER BY id, year,month;

其中：
PARTITION BY(id)指定结果是根据id 分区的

DIMENSION BY(month, year)是定义数组的维数。

MEASURES(amount sales_amount)用来指定包含amount的数组中的任何一个单元，数组名为sales_amount。 注意 table_name 中时没有sales_name的，SELECT 中的sales_name 是通过这里来的。

 

• 使用BETWEEN AND 返回特定范围的数据

sales_amount[1,2004] =
ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3,2003],2)

这样就可以求得 2003 年 1.2.3月的平均值赋给 2004年1月 四舍五入到小数点后两位

 

• 使用ANY 和 IS ANY 访问说有数据单元

ANY匹配的位置标记，IS ANY 匹配的是符号标记，意思是ANY 所在的位置上，对应该列的所有值，IS ANY 要跟列名合在一齐用

sales_amount[1,2004] = 
ROUND(SUM (sales_amount)[ANY, year IS ANY], 2)

ANY 对应的是month，IS ANY 对应的是year ，是要把列名 year 写出来
这样的赋值就是全部年份的全部月份的和的平均值

• 用CURRENTV()获取某个维度的当前值

sales_amount[1,2004] = 
sales_amount[CURRENTV(),2003]

这样CURRENTV() 获得了上面的sales_amount[1,2004] 的 ‘1’

 

• for循环访问数据单元

 

sales_amount [FOR month FROM 1 TO 3 INCREMENT 1, 2004] = 
sales_amount[CURRENTV(),2003]

 

• 处理空值和缺失值

IS PRESENT:

cellZAI MODEL子句执行前存在，（存在的意思是那个单元分配了，但是数据可以为NULL或确实的值）IS PRESENT返回TRUE

sales_amount[1,2004] = 
CASE
WHEN sales_amount[CURRENTV(),2003] IS PRESENT THEN
  sales_amount[CURRENTV(),2003]
ELSE
  0
END

 

PRESENTV():

如果cell在MODEL子句前已经存在，PRESENTV(cell, expr1, expr2) 返回表达式1，如果不存在返回expr2

sales_amount[1,2004] = 
PRESENTV(sales_amount[CURRENTV(),2003], sales_amount[CURRENTV(),2003] , 0)

 

PRESENTNNV()：

如果cell在MODEL执行前已经存在，而且单元值非空，则PRESENTNNV(cell, expr1, expr2) 返回expr1，如果记录不存在或者单元值为空，返回expr2

 

IGNORE NAV 、KEEP NAV：

IGONRE NAV的返回值：

空置或缺失数字值时返回0

空置或缺失字符串值时返回空字符串  （NULL 和 ‘’ 的区别）

空置或缺失日期值时01-JAN-2000

KEEP NAV 对空值或缺失数字值返回空值

默认条件为 KEEP NAV

MODEL IGNORE NAV

MODEL KEEP NAV

更新已有单元、阻止创建新单元（RULES UPDATE）:

默认情况下，表达式左边单元如果已经存在，则更新其值，不存在则创建一条新记录

使用RULES UPDATE 可以阻止创建新记录

MEASURE(month, year)
RULES UPDATE(
  sales_amount[FOR month FROM 1 TO 3 INCREMENT 1 ,2004] = sales_amount[CURRENTV(), 2003]
)