oracle sql model,SQL语句中的model用法

事情起因，今天看到一条非常”稀奇古怪“的SQL，select语句中，在表后面跟了一大窜以model开头的东东，后来查一下资料，原来model在数据仓库中是比较有地位的.列一下几个简单的测试：[@more@]

1,测试表中数据：

SQL> select * from test1;

COUNTRY PRODUCT YEAR SALES

---------- ---------- ---------- ----------

USA Stock1 2008 100

USA Stock1 2009 200

USA Stock2 2008 300

USA Stock2 2009 500

CHINA Stock1 2008 1000

CHINA Stock1 2009 2000

CHINA Stock2 2008 3000

CHINA Stock2 2009 5000

现在预测2010年每个产品的销量，假设2010销量是2008和2009的平均值：

SQL> select * from test1

2 model

3 return updated rows --只返回更新的记录

4 partition by(country) dimension by(product,year)--两维数组，数组下标为product,year. --partition by(country) 其实也是数组的一维，product,year是在country下，细分的维数

5 measures(sales s)--由上面定义的两维数组，指向的哪一列数据

6 rules upsert all --更新或插入数据的规则

7 ( s[product,2010]=avg(s)[cv(product),year between 2008 and 2009]);--cv(current value)是就当前值

COUNTRY PRODUCT YEAR S

---------- ---------- ---------- ----------

USA Stock1 2010 150

USA Stock2 2010 400

CHINA Stock1 2010 1500

CHINA Stock2 2010 4000

从返回的结果来看，是我们所希望的。model的大致原理：先以表的数据构造出相应的数组,或者说是hash表，然后根据一些规则，构造出另外的记录，或者对原来的记录做相应的操作。其实这里涉及两个

问题：

1,既然是数组，怎么定义数组的下标，这些下标可能是一维，也可能是多维，然后就是这些维数指向 的具体的数据.

2,怎么操作这些数组，也就要定义操作规则.

这两个问题都在model子句里解决：partition by和dimension by就是定义数组的维，measures就是这些维指向的要操作的数据. rules后面就是定义这些数组的操作规则.

再看一个例子：

SQL> select country,product,year,sales,profit from test1

2 model

3 partition by(country) dimension by(product,year)

4 measures(sales, 0 as profit)

5 rules upsert all

6 ( sales[product,2010]=sales[CV(product),2008]+sales[CV(product),2009],

7 profit[product,year]=sales[cv(product),cv(year)]-sales[cv(product),cv(year)-1])

8 /

COUNTRY PRODUCT YEAR SALES PROFIT

---------- ---------- ---------- ---------- ----------

USA Stock1 2008 100

USA Stock1 2009 200 100

USA Stock2 2008 300

USA Stock2 2009 500 200

USA Stock1 2010 300 100

USA Stock2 2010 800 300

CHINA Stock1 2008 1000

CHINA Stock1 2009 2000 1000

CHINA Stock2 2008 3000

CHINA Stock2 2009 5000 2000

CHINA Stock1 2010 3000 1000

CHINA Stock2 2010 8000 3000

这里虚构出了一列数据profit. 这列的数据是在rules里定义的。用当年数值减去上一年的数值，这个跟高级复合函数(lead/lag() partition by)有相同的功能.

文档里有详细的介绍，model跟高级复合函数一样都比较强大，关健是要能熟练掌握。