oracle model 入门(行间计算)

一、给出我的测试表和测试数据。
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分析函数比较熟悉的话,可能会理解的更深些,有些地方很相似,但不完全相同。







  • 1
    点赞
  • 24
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值