利用Oracle 10g的 MODEL SQL进行行间计算(2)

来源：互联网发布：二战德国和苏联知乎编辑：程序博客网时间：2024/05/22 03:23

规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求2008年的'10'=2007年的'30'+'31'

YEAR CODE       P_ID            V1
---- ---------- ------- ----------
2008 600001     err1             0
2008 600002     err1             1

如果年份很多，每个年份都是和上年比较，这种描述可以用CV()函数简化
SQL> insert into t603_2 select '2006' year,code,p_id,v1,v2 from t603_1;

SELECT year,code,
   p_id, v1
FROM t603_2
WHERE code IN ('600001','600002')
   MODEL RETURN UPDATED ROWS
     PARTITION BY (code)
     DIMENSION BY (p_id,year)
     MEASURES (v1)
     RULES (
       v1['err1',for year in( 2007 ,2008)] = v1['30',CV(year)-1] + v1['31',CV(year)-1] -v1['10',CV(year)])
ORDER BY code, p_id;
YEAR CODE       P_ID            V1
---- ---------- ------- ----------
2007 600001     err1             0
2008 600001     err1             0
2007 600002     err1             1
2008 600002     err1             1
如果year是数值类型，还可以用for year from 2007 to 2009 increment 1的语法，如果是其他类型，还可以用在in子句带子查询的办法，
比如for year in (select year from t603_2)。
但需要注意不能采用year in 的语法，year in的语法只能符号引用已经存在的单元格，而v1['err1',x]是新单元格。
单个年份的写法如下:
SELECT year,code,
   p_id, v1
FROM t603_2
WHERE code IN ('600001','600002')
   MODEL RETURN UPDATED ROWS
     PARTITION BY (code)
     DIMENSION BY (p_id,year)
     MEASURES (v1)
     RULES (
       v1['err1',2008] = v1['30',CV()] + v1['31',CV()] -v1['10',CV()])
ORDER BY code, p_id;
YEAR CODE       P_ID            V1
---- ---------- ------- ----------
2008 600001     err1             0
2008 600002     err1             1
而
SELECT year,code,
   p_id, v1
FROM t603_2
WHERE code IN ('600001','600002')
   MODEL RETURN UPDATED ROWS
     PARTITION BY (code)
     DIMENSION BY (p_id,year)
     MEASURES (v1)
     RULES (
       v1['err1',year in ('2008')] = v1['30',CV()] + v1['31',CV()] -v1['10',CV()])
则返回0行