Oracle SQL多维建模

作者：开发者在线 来源:开发者在线

电子数据表常常被当作关系表的例子。它的行和列与数据库表格中的行和列类似。每个行和列交叉的地方是值。

二者的不同之处在于：在电子数据表里，每个单元格可以包含一个函数，用来计算该单元格的值。每个单元格里的函数可以完全不同；而关系表里只能保存值。你可以在查询里使用计算，但是这些计算会应用到查询的每一行里。

从Oracle 10g开始，你可以把查询的输出结果当作电子表格来处理，对输出的每个单元格进行单独的计算。你甚至可以在输出里生成原有表格里没有的行。SELECT语句的MODEL子句会定义哪些列用来聚合（度量单位）、哪些行用作唯一的数组索引（维），以及哪些函数用来计算值（规则）。

里的查询用订单登录（Order Entry，OE）示例架构计算出了一个小计。在检查输出结果的时候，我发现1998年之前销售额相对较小，所以想把它计入1998年的总额，而不再报告中显示出来。利用了MODEL子句来实现这一目的。

DIMENSION BY用来定义哪些列用作虚拟电子表格里的索引。有两种：order_mode（在线或者直接）以及order_year。MEASURES用来定义哪个列为单元格提供值。在本文里，它是order_total，我把它简写为“tot”。

RETURN UPDATED ROWS用来删除我们的规则不会更新的所有行——这是删除1998年之前内容的一种方式。

RULES列表用来定义如何计算单元格。我们的模型有两个规则：

Tot[ANY, 1998] = SUM(tot)[CV(order_mode),order_year <=1998]
Tot[ANY, order_year>1998] = tot[CV(order_mode), CV(order_year)]

第一个规则是：对于1998年，把包括1998年在内的所有年份的小计加在一起，得到总计。ANY这个关键字在一个规则里就完成了两种订单模式的计算。第二个规则是：对于1998年之后的年份，就使用现有的总计（当前值，简写作CV，被用于所有的订单模式和所有年份）。

通过这些规则，你可以在任何时候访问到查询里的所有行，方法是找对与数组单元格唯一对应的指数组合就行了。它的一些应用如：

• 把特定行的结果强制设定为指定值。
• 利用函数从现有行创建新的行（趋势预测）。
• 把行的结果设定为一组行的最大、最小或者平均值。

虽然这里列出的Oracle几个例子只用到了二维，但是SQL模型可能会复杂得多。MODEL子句有很多用于计算数据的选项，我们这里就不一一列举了。要获得更多关于MODEL的信息,请参看《Oracle数据库数据仓指南》的第22章——《》。

Bob Watkins（OCP、MCDBA、MCSE、MCT）具有从事技术培训师、咨询师和数据库管理员的25年计算机职业经验。他是位于达拉斯／沃尔斯堡地区提供数据库咨询和培训服务的B. Watkins公司的高级咨询师兼合伙人。

Listing A

CREATE OR REPLACE VIEW order_data_view AS
   SELECT 
       TO_CHAR(o.order_date,'yyyy') order_year,
       o.order_mode,
       SUM(o.order_total) order_total
   FROM
     orders o 
   GROUP BY order_mode, 
            TO_CHAR(o.order_date,'yyyy')
   ORDER BY
       order_year, o.order_mode
/


SQL> @model_a

View created.

SQL> SELECT * FROM order_data_view;

ORDER_YEAR      ORDER_MO ORDER_TOTAL                                            
--------------- -------- -----------                                            
1990            direct       61655.7                                            
1996            direct        5546.6                                            
1997            direct           310                                            
1998            direct      309929.8                                            
1998            online      100056.6                                            
1999            direct     1274078.8                                            
1999            online     1271019.5                                            
2000            direct      252108.3                                            
2000            online      393349.4                                            

9 rows selected.

SQL>
Listing B

SELECT order_mode, order_year, tot
FROM order_data_view
MODEL
  RETURN UPDATED ROWS
  DIMENSION BY (order_mode, order_year)
  MEASURES (order_total tot)
  RULES UPDATE (
    tot[ANY, 1998] = 
       SUM(tot)[CV(order_mode),order_year <=  1998],
    tot[ANY, order_year >1998] = 
        tot[CV(order_mode),CV(order_year)]
  )
ORDER BY order_year, order_mode
/


SQL> @model_b

ORDER_MO ORDER_YEAR               TOTAL                                         
-------- --------------- --------------                                         
direct   1998                377,442.10                                         
online   1998                100,056.60                                         
direct   1999              1,274,078.80                                         
online   1999              1,271,019.50                                         
direct   2000                252,108.30                                         
online   2000                393,349.40                                         

6 rows selected.

SQL>