oracle子句model的梳理

1：规则rules的使用

select ArrValue,soeji
from(select 'abcdefghijklmn' as ArrValue,1 as soeji from dual) model
dimension by(soeji) measures(ArrValue)
rules(ArrValue[1] = 'Hello World');

--rules的缺省行为是存在就更新，不存在则追加

/*使用model return updated rows的话，被rules更新或者插入的行才显
示，没有更新过的行不再作为SQL的结果。*/
select ArrValue,soeji
from(select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
model return updated rows
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World');

select ArrValue,soeji
from(select 'abcdefghijklmn' as ArrValue,1 as soeji from dual)
model return updated rows
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[null] = ArrValue[1]+ArrValue[1]+ArrValue[1]);

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;

更新已有单元、阻止创建新单元（RULES UPDATE）:
默认情况下，表达式左边单元如果已经存在，则更新其值，不存在则创建一条新记录
使用RULES UPDATE 可以阻止创建新记录
MEASURE(month, year)
RULES UPDATE(
  sales_amount[FOR month FROM 1 TO 3 INCREMENT 1 ,2004] = sales_amount[CURRENTV(), 2003]
)

 

2：规则MEASURES的使用

使用BETWEEN AND 返回特定范围的数据
sales_amount[1,2004] = ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3,2003],2)

使用ANY 和 IS ANY 访问说有数据单元
ANY匹配的是位置标记，IS ANY 匹配的是符号标记，意思是ANY 所在的位置上，对应该列的所有值，IS ANY 要跟列名合在一齐用
sales_amount[1,2004] = ROUND(SUM (sales_amount)[ANY, year IS ANY], 2)

用CURRENTV()获取某个维度的当前值
sales_amount[1,2004] = sales_amount[CURRENTV(),2003]

for循环访问数据单元
sales_amount [FOR month FROM 1 TO 3 INCREMENT 1, 2004] = sales_amount[CURRENTV(),2003]

处理空值和缺失值

IS PRESENT:
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)