oracle 新功能model子句的简单使用

使用版本：>= 10G

一、建立测试表

-----------建立测试表
 CREATE TABLE SALE_REPORT (
       SALE_DATE  DATE NOT NULL ,
       SALE_ITEM  VARCHAR(2) NOT NULL ,
       SALE_MONEY DECIMAL(10,2) NOT NULL
    )

二、测试表输入数据

----------测试表输入数据
 DECLARE
      v_begin_day  DATE;
      v_end_day  DATE;
    BEGIN
      v_begin_day := TO_DATE('2009-01-01', 'YYYY-MM-DD');
      v_end_day := TO_DATE('2010-01-01', 'YYYY-MM-DD');
  
      WHILE v_begin_day < v_end_day LOOP
        INSERT INTO SALE_REPORT VALUES(v_begin_day,  'A',  TO_NUMBER( TO_CHAR(v_begin_day, 'YYYY') ));
       INSERT INTO SALE_REPORT VALUES(v_begin_day,  'B',  TO_NUMBER( TO_CHAR(v_begin_day, 'MM') ));
       INSERT INTO SALE_REPORT VALUES(v_begin_day,  'C',  TO_NUMBER( TO_CHAR(v_begin_day, 'DD') ));
       v_begin_day := v_begin_day + 1;
     END LOOP;
   END;


With cte AS
(
SELECT
  TO_CHAR(SALE_DATE, 'MM') AS month,
  SUM(sale_money) AS sum_money
FROM
  sale_report
GROUP BY
  TO_CHAR(SALE_DATE, 'MM')
)
SELECT * 
FROM cte 
ORDER BY month;

 

三、功能展示

（1、自定义model子句展示，使用 for 及currentv() 后的展示效果，其中currentv() 是定位当前所在 行的行数

With test AS
(
SELECT
  EXTRACT(MONTH FROM SALE_DATE) AS month,
  SUM(sale_money) AS sum_money,
  0 AS prev_sum_money
FROM
  sale_report
GROUP BY
  EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
  *
FROM test
MODEL 
  DIMENSION BY(month) -- 按照 月 为 维度
  MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
  (
  sum_money[3]=1000,
 prev_sum_money[FOR month FROM 3 TO 7 INCREMENT 2]
   = sum_money[CURRENTV() - 2]+1000,
   sum_money[13]=1500,
   prev_sum_money[13]=2200
  )
ORDER BY
  month;

     MONTH  SUM_MONEY PREV_SUM_MONEY
---------- ---------- --------------
         1      62806              0
         2      56714              0
         3       1000          63806
         4      60855              0
         5      62930           2000
         6      60915              0
         7      62992          63930
         8      63023              0
         9      61005              0
        10      63085              0
        11      61065              0
        12      63147              0
        13       1500           2200
13 rows selected
SQL> 

（2、使用办减积险除的model子句与新增加记录的

with cte as (
SELECT
 TO_CHAR(SALE_DATE, 'MM') AS month,
  SUM(sale_money) AS sum_money
FROM
  sale_report
GROUP BY
 TO_CHAR(SALE_DATE, 'MM')
)
SELECT *
FROM cte
MODEL
  DIMENSION BY (month) -- 按照 月 为 维度
  MEASURES(sum_money) -- 数组的数据为 sum_money
  RULES(
 sum_money['J1'] = sum_money['01']+sum_money['02']+sum_money['03'],
 sum_money['J2'] = sum_money['04']+sum_money['05']+sum_money['06'],
 sum_money['J3'] = sum_money['07']+sum_money['08']+sum_money['09'],
 sum_money['J4'] = sum_money['10']+sum_money['11']+sum_money['12'],
 sum_money['YY'] = sum_money['J1']+sum_money['J2']+sum_money['J3']+sum_money['J4']
 -- , sum_money['YYYYYY']=round( sum_money['XX'] /4,2)  YYYYYY  是不可以的，超出范围
  )
ORDER BY
  month;

MONTH  SUM_MONEY
----- ----------
01         62806
02         56714
03         62868
04         60855
05         62930
06         60915
07         62992
08         63023
09         61005
10         63085
11         61065
12         63147
J1        182388
J2        184700
J3        187020
J4        187297
YY        741405
17 rows selected

（2.1、使用常用 函数的操作

-- 上一个SQL, 用 + 用的太多了。
-- 这里用 SUM()[BETWEEN和AND] 返回特定范围内的数据单元
-- 这里用 SUM()[ IN ] 返回特定范围内的数据单元
With cte AS
(
SELECT
  TO_CHAR(SALE_DATE, 'MM') AS month,
  SUM(sale_money) AS sum_money
FROM
  sale_report
GROUP BY
  TO_CHAR(SALE_DATE, 'MM')
)
SELECT *
FROM cte
MODEL
  DIMENSION BY(month) -- 按照 月 为 维度
  MEASURES(sum_money) -- 数组的数据为 sum_money
  (
 sum_money['J1'] = SUM(sum_money)[month BETWEEN '01' AND'03'],
 sum_money['J2'] = SUM(sum_money)[month BETWEEN '04' AND'06'],
 sum_money['J3'] = SUM(sum_money)[month BETWEEN '07' AND'09'],
 sum_money['J4'] = SUM(sum_money)[month BETWEEN '10' AND'12'],
 sum_money['YY'] = SUM(sum_money)[month IN ('J1', 'J2', 'J3', 'J4')]
  )
ORDER BY
  month;

MONTH  SUM_MONEY
----- ----------
01         62806
02         56714
03         62868
04         60855
05         62930
06         60915
07         62992
08         63023
09         61005
10         63085
11         61065
12         63147
J1        182388
J2        184700
J3        187020
J4        187297
YY        741405
17 rows selected

总结：model子句刚看到，感觉很难，使用几秒中后就完全没有压力。
如果有不懂，请观看：

http://blog.csdn.net/wanghai__/article/details/4783021
http://www.2cto.com/database/201204/127059.html
最后：为什么要使用model ？

答：不知道，我使用union 也可以做出和model一样的效果，但据网上谣言，使用model比union速度快，效率高，还可以配合开发的语言使用。