利用Oracle 10g的MODEL SQL进行行间计算(一)
规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求2008年的'10'=2007年的'30'+'31'
| 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',2007] + v1['31',2007] -v1['10',2008]) ORDER BY code, p_id; 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子句带子查询的办法，
