用MODEL从句进行电子表格计算

SQL中的MODEL从句是Oracle 10g数据库为企业智能领域引入的一个重要的新功能。这个从句对喜欢从Oracle中区出来，再放入电子表格进行分析的会计人员有很大帮助。SQL中的MODEL从句的目的就是让SQL语句拥有从普通的SELECT结果中创建多维数组的能力，然后再在这个SQL电子表格上进行行间或数组的计算。
MODEL从句通过讲一个查询中的各列映射为三组而定义一个多位数组，这三个组分别是分区(partitions)、维度(dimensions)和计量(measures)。分区定义的是结果集中作为独立数组的逻辑块。纬度定义的是分区中每个计量单元。计量则是实际的数据单元。计量一般会包括象销售量或者营业收入等数值。我们可以通过指定完全的维度组合来访问分区中的每个计量单元。下表是这些组件的一个示例。
用于演示MODEL从句的示例数据
分区 维度 维度 计量
C1 ABC 2002 450
C1 ABC 2001 455
C2 ABC 2002 500
C2 ABC 2001 550
C1 XYZ 2002 67
C1 XYZ 2001 78
C2 XYZ 2002 85
C2 XYZ 2001 80
25.MODEL从句的基本语法
下面是MODEL从句的最基本语法。MODEL从句很复杂，要想了解更多，可以参考ORACLE 10G SQL REFERENCE MANUAL。

MODEL [RETURN [UPDATED | ALL] ROWS]
[reference models]
[PARTITION BY ()]
DIMENSION BY ()
MEASURES () [IGNORE NAV] | [KEEP NAV]
[RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
[ITERATE (n) [UNTIL ] ]
( = ... )
26.MODEL从句举例
首先还是创建一个表，插入一些数据（略）。查询revenues表的结果如下图所示：
SQL> SELECT * FROM revenues;
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2002 450
C1 ABC 2001 455
C2 ABC 2002 500
C2 ABC 2001 550
C1 XYZ 2002 67
C2 XYZ 2002 85
C2 XYZ 2001 80
C1 XYZ 2001 78
C2 XYZ 2002 85
C2 XYZ 2001 80
2001年和2002年的营业收入数据是可用的。我们想根据这两年的营业数据，预测2003年的营业收入数据。假设2003年XYZ公司营业收入是其2001年和2002年的总和，ABC公司2003年的数据比其上年收入高20%。那么MODEL从句如下：
SQL>SELECT country, company, year, revenue
FROM revenues
MODEL
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (
revenue['ABC',2003] = revenue['ABC',2002]*1.2,
revenue['XYZ',2003] = revenue['XYZ',2001] +
revenue['XYZ',2002])
ORDER BY country, company, year;
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2001 455
C1 ABC 2002 450
C1 ABC 2003 540
C1 XYZ 2001 78
C1 XYZ 2002 67
C1 XYZ 2003 145
C2 ABC 2001 550
C2 ABC 2002 500
C2 ABC 2003 600
C2 XYZ 2001 80
C2 XYZ 2002 85
C2 XYZ 2003 165
12 rows selected.
如果我们只想返回新的行，那么可以使用RETURN UPDATED ROWS从句，例如：
SQL>SELECT country, company, year, revenue
FROM revenues
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (
revenue['ABC',2003] = revenue['ABC',2002]*1.2,
revenue['XYZ',2003] = revenue['XYZ',2001] +
revenue['XYZ',2002])
ORDER BY country, company, year;
COUNTRY COMPANY YEAR REVENUE
---------- ---------------------------------------- ---------- ----------
C1 ABC 2003 540
C1 XYZ 2003 145
C2 ABC 2003 600
C2 XYZ 2003 165
27.测试MODEL从句可能遇到的问题
在演示MODEL从句时，可能会遇到下面的错误：
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
在这种情况下，是因为OLAP被设置为ON但是却没有安装OLAP的原因。我们可以通过以SYS用户登录后，执行@?/olap/admin/olap.sql SYSAUX TEMP;来创建OLAP，然后再测试带MODEL从句的SQL就应该正确了。
28.单元格和值的寻址
理解单元格的引用方法对于充分发挥MODEL从句的作用非常重要。在默认情况下，MODEL从句中默认规则具有Upsert特征，也就是如果规则左侧指出的单元格存在，那么它会被更新，否则将生成包含该单元格的一个新行。基础表并不会更新也不会插入新值。要想对基础表进行更新或插入，必须使用update或insert。对单元格的引用必须符合DIMENSION BY从句中列处的所有维度的要求。人们既可以用位置引用，也可以使用符号引用。
29.单元格的位置引用
在位值引用中，方括号中提供的每个值将与DIMENSION BY从句中同样的位置的维度进行匹配。下面是一个使用位置单元格引用的查询，根据表达式中的维度进行引用：DIMENSION BY从句确定了每个维度指定的位置。在本例中，第一个位置是公司，第二个位置是年份。ABC公司2001年的营业收入在显示出来的结果中将被更新为1000。
SQL>SELECT country, company, year, revenue
FROM revenues
WHERE country = 'C1'
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (company, year)
MEASURES (revenue)
RULES (revenue['ABC',2001] = 1000);
COUNTRY COMPANY YEAR REVENUE
-------- -------- ---------- ----------
C1 ABC 2001 1000[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8119050/viewspace-1006603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8119050/viewspace-1006603/