oracle model 子句

 

oracle model 入门（行间计算）

分类： oracle 9i/10g (Expert Oracle)2010-06-11 14:51 3385人阅读 评论(1) 收藏 举报

oracleappleinsert2010测试merge

一、给出我的测试表和测试数据。

CREATE TABLE MODEL_TEST(  DOMAINS      VARCHAR2(20),  NAME         VARCHAR2(20),  YEAR         NUMBER,  INPUTS       NUMBER,  INCREAMENTS  NUMBER)insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('chchina', 'apple', 2000, 100, 150);insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('chchina', 'banana', 2001, 200, 250);insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('chchina', 'orange', 2002, 300, 450);insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('hongkong', 'apple', 2000, 100, 150);insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('hongkng', 'banana', 2001, 200, 250);insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)values ('hongkong', 'orange', 2002, 300, 450);

二、几点说明
1、model子句只是单纯的返回一个结果集，不会对源表产生任何影响。
2、如果想使用model自己产生的结果集可以使用insert ，update，或者merge into子句完善功能。
三、根据表结构和需求定义分区（partition by），维度（dimension by ） ，度量（measures） ，其中分区表名了规则适用的范围和作用域，维度用来唯一的定定位一个单元格，度量用来真正存储规则产生的结果（自己的理解）。
四、 根据上述表结构定义上面提到的三个要素：分区：domains ，维度：name 和 year ，度量：inputs ，increaments 。

五、model子句的使用方法：

1.预测2003年大陆地区的进货和收益。

/* Formatted on 2010-6-11 PM 02:20:41 (QP5 v5.114.809.3010) */SELECT   domains,         NAME,         YEAR,         inputs,         increaments  FROM   modeL_test WHERE   domains = 'chchina'MODEL RETURN UPDATED ROWS   PARTITION BY (domains)   DIMENSION BY (NAME, YEAR)   MEASURES (inputs, increaments)   RULES      (inputs ['apple', '2003'] = 2 * inputs['apple', '2000'],      increaments ['apple', '2003'] = 2 * increaments['apple', '2000'],      inputs ['banana', '2003'] = 2 * inputs['banana', '2001'],      increaments ['banana', '2003'] = 2 * increaments['banana', '2001'],      inputs ['orange', '2003'] = 2 * inputs['orange', '2002'],      increaments ['orange', '2003'] = 2 * increaments['orange', '2002']);

    由此可以看出model子句支持多维度和多度量的实现，另在model后面加RETURN UPDATED ROWS ，目的在于只返回受影响的记录，表中的原始数据
不再显示。

2.指定2003年大陆地区的进货和收益。

/* Formatted on 2010-6-11 PM 02:23:07 (QP5 v5.114.809.3010) */SELECT   domains,         NAME,         YEAR,         inputs,         increaments  FROM   modeL_test WHERE   domains = 'chchina'MODEL RETURN UPDATED ROWS   PARTITION BY (domains)   DIMENSION BY (NAME, YEAR)   MEASURES (inputs, increaments)   RULES      (inputs ['apple', '2003'] = 500,      increaments ['apple', '2003'] = 600,      inputs ['banana', '2003'] = 700,      increaments ['banana', '2003'] = 800,      inputs ['orange', '2003'] = 900,      increaments ['orange', '2003'] = 1000);

3.如果希望创建新单元格（例如，未来几年的值），则必须使用位置引用或 FOR 循环.也就是说，位置引用允许更新数组以及向数组中插入新值,以上两个例子就是使用位置引用的。下面使用符号引用。

/* Formatted on 2010-6-11 PM 02:29:05 (QP5 v5.114.809.3010) */SELECT   domains,         NAME,         YEAR,         inputs,         increaments  FROM   modeL_test WHERE   domains = 'chchina'MODEL RETURN UPDATED ROWS   PARTITION BY (domains)   DIMENSION BY (NAME, YEAR)   MEASURES (inputs, increaments)   RULES      (inputs [NAME IN ('apple', 'banana', 'orange'), YEAR < 2003] = 0,      increaments [NAME IN ('apple', 'banana', 'orange'), YEAR < 2003] = 0);

4.符号引用功能前大，但是不能创建新的单元格，上例还说明了，单一规则可以访问多个单元格，上例演示了在规则左侧使用多单元格引用，下面演示在
右侧使用多单元格引用。

/* Formatted on 2010-6-11 PM 02:31:09 (QP5 v5.114.809.3010) */SELECT   domains,         NAME,         YEAR,         inputs,         increaments  FROM   modeL_test WHERE   domains = 'chchina'MODEL RETURN UPDATED ROWS   PARTITION BY (domains)   DIMENSION BY (NAME, YEAR)   MEASURES (inputs, increaments)   RULES      (inputs ['apple', 2005] =            MIN (inputs)[NAME IN ('apple', 'banana', 'orange'), YEAR < 2003]  + 600,      increaments ['apple', 2005] =            MAX (increaments)[NAME IN ('apple', 'banana', 'orange'), YEAR < 2003]* 2);

5.使用cv函数和any运算符。CV() 函数是一个非常强大的工具，它可以高效地进行规则创建。CV() 用于规则的右侧，以复制左侧指定的当前维度值。对于左侧规范引用多个单元格来说，它非常有用。

SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple',YEAR BETWEEN 2000 AND 2004]=inputs['orange',cv(YEAR)]+600,           increaments['apple',YEAR BETWEEN 2000 AND 2004]=increaments['orange',cv(YEAR)]*2);等价于：SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple',ANY ]=inputs['orange',cv(YEAR)]+600,           increaments['apple',ANY ]=increaments['orange',cv(YEAR)]*2          );等价于：SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple',year IS ANY ]=inputs['orange',cv(YEAR)]+600,           increaments['apple',YEAR IS ANY ]=increaments['orange',cv(YEAR)]*2          );

6.使用for语句，强制转化为位置引用，可以插入新的单元格。

SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple', FOR YEAR IN (2005,2006,2007)]=inputs['orange',2002]+600,           increaments['apple',FOR YEAR IN (2005,2006,2007) ]=increaments['orange',2002]*2          );对比一下： SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple', YEAR IN (2005,2006,2007)]=inputs['orange',2002]+600,           increaments['apple', YEAR IN (2005,2006,2007) ]=increaments['orange',2002]*2          );

上面的这个查询返回的是0行，因为符号引用不允许插入记录。

7.使用for步进子句。
7.1 递增：

SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple', FOR YEAR FROM  2005 TO 2010 INCREMENT 1 ]=inputs['orange',2002]+600,           increaments['apple', FOR YEAR FROM  2005 TO 2010 INCREMENT  1]=increaments['orange',2002]*2          );

7.2 递减：

SELECT  domains,NAME,YEAR,inputs  ,increaments     FROM  modeL_test      WHERE domains='chchina'      MODEL RETURN UPDATED ROWS          PARTITION BY (domains)          DIMENSION BY (NAME,YEAR)          MEASURES ( inputs,increaments)          RULES (           inputs['apple', FOR YEAR FROM  2010 TO 2005 DECREMENT 1 ]=inputs['orange',2002]+600,           increaments['apple', FOR YEAR FROM  2010 TO 2005 DECREMENT  1]=increaments['orange',2002]*2          );

六 、谈一点体会（我的理解）
 1.所谓位置引用就是直接给出维度的值，例如：inputs ['apple', '2003']，它允许更新和插入。
 2.所谓符号引号就不用直接给出维度的值，功能很强大，但是不能插入新的记录，例如：inputs['apple',YEAR BETWEEN 2000 AND 2004]。
 3.两种引用规则只是对规则左侧有效，右侧不适用。
 4.for 比较特殊，它看起来像是符号引用，但是实际上转化为了位置引用，有更新和插入的功能。
 5.这里写的只是我的一点体会，也是经常用到的。尽管这样，还是被oracle的这个功能深深折服，关于oracle的行间计算还有更加高级的功能，感兴趣的
“同志”，可以参考相关方面的书籍，或这继续交流，如果对oracle分析函数比较熟悉的话，可能会理解的更深些，有些地方很相似，但不完全相同。