|
技术
SQL
发布新的MODEL子句
作者：Jonathan Gennick
新的MODEL子句为数据库带来了电子数据表计算。
你可能正在编写一个返回已计算列值的查询。在计算过程中输入数值时，你需要来自多个行的值。你该怎样做？这实际上是一个相当普通的问题。解决方法通常会涉及令人费解的自联接与子查询的结合。
Oracle数据库10g提供了一种新的方法。利用SELECT语句的新的MODEL子句，你可以将关系型数据看作一个多维数组，对该数组应用类似于电子数据表的计算。结果是获得一个更易于开发、更易于理解和更易于修改的查询。
情景
假设你是一个图书出版商，要根据在2002年与2003年出版的图书的销售情况预测2004年的销售额。你在美国和加拿大都有出版业务，并且有如下所示的月销售数据表：
CREATE TABLE book_sales (
isbn VARCHAR2(13),
country VARCHAR2(2),
year NUMBER,
month NUMBER,
gross_revenue NUMBER,
return_revenue NUMBER,
net_revenue NUMBER,
gross_units NUMBER,
return_units NUMBER,
net_units NUMBER);
你希望利用以下公式预测2004年的销售额：
s4 = ((s3-s2)/s2)*s3+s3
利用此公式可以计算2002年一个给定月份与2003年同一月份销售额的上升或下降百分比，然后，将这个百分比应用于2003年的销售额，以获得对2004年该月份的预测。你所基于的假设是从2002年到2003年的趋势将延续到2004年。
为了向你的预测任务中增加一些实际的复杂性，假设并不是所有的图书都从2002年1月开始印刷；这样，对于一些书目，你就不能拥有其完整的24个月的销售历史。为了执行预测公式，如果一本书在一个给定月份内没有销售历史，那么你可以使用该书在本年度有销售历史的所有月份的平均销售额。此外，你也没有2003年的完整销售信息，没有2003年第四季度的销售数据。为应对这种情况，你希望从每本书在2003年第二到第三季度的销售趋势预测第四季度的销售趋势，并将那些预测的销售额平均分配到10月、11月和12月。最后，来自美国以外的销售历史是以本地货币记录的，而你希望用以美元为单位来预测所有国家的销售额。
进入MODEL子句
我刚刚描述的计算方法是当你在一个电子数据表中建立销售模型对可能用到的典型计算方法。
假设你正在构建一个用于预测2004年销售额的模型，而且幸运的是，你正在运行Oracle数据库10g。在刚刚阅读了关于新的MODEL子句的内容后，现在你决定使用这个子句来解决在预测中遇到的问题。
要使用MODEL子句，需要在概念上将你的数据组成一个多维数组。每一个结果集行都成为该数组的一个单元。
当创建一个模型时，应该首先考虑如何以及是否将你的模型划分为多个独立的数组。划分是可选的，但它为数据库提供了一个能够并行开展工作的点，而且它使公式更容易编写。如果你有两个国家的销售数据，每个国家的预测与另一国家的预测无关，那么应该按国家进行划分：
MODEL
PARTITION BY (country c)
接下来，你需要对数据进行"分维"（dimension）。我这样说的意思是指，你需要决定将哪些值组合起来以唯一地确定数组中的一行。对于图书销售数据，你应当按ISBN号、年和月进行"分维"。对于每个国家来说，这些值唯一地确定了该国的数组中的一行：
MODEL
PARTITION BY (country c)
DIMENSION BY (isbn i, year y, month m)
模型中的每个单元都保留一个或多个值，但是预测公式只需要净收入值，所以列出一列作为你的测量标准（measure）：
MODEL
PARTITION BY (country c)
DIMENSION BY (isbn i, year y, month m)
MEASURES (s.net_revenue r)
最后，你可以开始编写定义你希望利用你的衡量标准进行计算的规则。作为一个示例，让我们集中预测一本书在2004年1月的净收入。在公式中引用该单元如下：
r['1-56592-948-9',2004,1]
单元引用以MEASURES子句中指定的别名开始。接下来是一个用方括号括起来的维度值列表。
这个例子说明了位置引用，按与DIMENSION BY子句相对应的顺序为每个维度列举了一个值。
位置引用简单明了，它允许你插入新的单元值，然后这些值转换为该查询结果集中的新行。默认情况下，MODEL子句支持Oracle所称的upsert语义：当你为一个以前不存在的单元指定维度，并为该单元分配一个值时，数据库就会将维度、衡量标准与分区数据结合起来，以在结果集中创建一个新行。
为了向一个单元格分配一个值，可使用如下记数法：
r['1-56592-948-9',2004,1] = value
r['1-56592-948-9',2004,1] = expression
清单
1 给出了一个完整的MODEL子句，包括一条用来预测一本书在2004年销售额的规则。这条规则执行了前面所述的预测公式。在此规则中，当使用2002年与2003年的数据计算对2004年的估计值时，你将会看到等号两侧的单元引用。当谈到规则时，经常使用术语"左侧"和"右侧"来指代该规则在赋值运算符左侧和右侧的部分。
对于清单
1中的查询，将对每一个分区计算一次该规则的值--换言之，一个国家一次。因此，你将获得一行关于所述图书在加拿大2004年1月的销售情况，而另一行则是在美国同一月份的销售情况。
当你执行一个如清单
1所示的查询时，最后求值的子句为MODEL、SELECT，最后是ORDER BY。你可以在MODEL子句中指定列的别名，我已经这样做了，因为这些别名使我可以更简单明了地指出计算结果。对SELECT子句来说，这些别名就是列名，此外你可以在SELECT子句中取其他的别名。
通过衡量标准循环
你的需求之一是填充缺失的月销售数据，以便你在每个单元中都有一个数据的密集数组（dense
array）。你可以在清单
2中规则的左侧使用以下三个FOR循环来完成这一工作：
- FOR i IN (SELECT isbn FROM book)-迭代每一个ISBN号
- FOR y FROM 2002 TO 2003 INCREMENT 1-迭代两个年份
- FOR m FROM 1 TO 12 INCREMENT 1-迭代12个月份
使FOR循环有用的关键是CV()函数，它使你能够访问一个维度的当前值。右边的CV()函数捕获用来FOR循环进行迭代的值。因此，单元引用
[CV(), CV(), CV()]
变为
[' 0-596-00441-9', 2002, 1]
[' 0-596-00441-9', 2002, 2]
[' 0-596-00441-9', 2002, 3]
...
Upsert语义确保了在必要时创建新的结果集行。执行清单
2中的查询，你将看到每一本书在全部24个月期间的月销售行。即使这些行并不是全部存在于数据库中，你也会看到它们。你将会看到每个国家的销售行，因为规则是自动、独立地被应用于模型中每个分区的。
清单
2的CASE语句中新的IS PRESENT谓词测试某个月的净收入数据是否存在。如果在由MODEL子句求值的初始行集中存在一个单元，那么IS
PRESENT的值为"真"。如果r的值存在，那么该值就会被复制到nr。否则，应将nr设置为所述图书在一年内的平均销售额。
我可以将r本身设置为CASE语句的结果，但是为r创建新的值可能会改变像AVG(r)这样的集合函数的结果。通过使用nr，我保留了原始的r或者说净收入值，以用于进一步的计算。
清单
2中的AVG函数引入了另一种类型的单元寻址。根据一系列衡量标准执行聚合函数所使用的基本格式为：
AVG(expression)[dimension,
dimension...]
对于前两个维度，清单
2 使用了CV()函数，以引用当前图书与年份的值。然后，在计算平均值时，它使用了谓词m BETWEEN 1 AND 12，以考虑当前年份与图书的全部值。
AVG函数在括号内指明了一个表达式。 在这个示例中，该表达式只是一个列引用，对特定范围内的每一个单元都要计算该表达式的值。然后，AVG函数返回这些值的平均值。
规则，满足分区外部连接
在使用MODEL子句时，你需要了解你所编写的规则的数量。MODEL查询最多只能有1万条规则。然而，规则计数与一个查询返回的分区值数量无关，因此如果一个模型定义了三条规则，那么无论查询返回2个还是2万个分区值，它都有三条规则。
注意，FOR循环类似于一个宏，在编译一个查询时，FOR循环被扩展为许多单一单元规则。例如，清单
2中的FOR循环对于每一本书可产生24条规则，即两年内的每一个月份都对应一条规则。
如果你创建了一个可能达到规则极限的模型，那么你可以采用不同的方法来减少规则数量。一种方法是从规则中删除一个或多个FOR循环，并将FOR循环的维度列添加到PARTITION
BY子句中。
另一种方法是在生成MODEL数组之前，利用Oracle数据库10g中新的分区外联接（outerjoin）语法生成所需的行，从而在MODEL子句的外部完成该致密化（densification）任务。
在我的例子中，我使用了分区外联接方法。清单
3 使用了以下外联接来消除月销售数据之间的差异：
PARTITION BY (s.country, s.isbn)
RIGHT OUTER JOIN month mo
ON (s.month = mo.m AND s.year=mo.y)
在清单
3中，月份表包含36行，2002年、2003年和2004年的每个月对应一行。第三行的PARTITION BY子句指明了对每一个国家与ISBN号的组合独立执行外联接，使外联接更易于编写与理解。正确的外联接保证了每个月份都有一个结果。由于不再需要由MODEL子句为2002年至2003年创建新的行，因此规则左侧的单元引用可以由使用FOR循环更改为使用ANY谓词：
nr[ANY, ANY, ANY] = ...
这三个ANY谓词使得对于图书、年和月的每个组合都执行规则。由清单
3 得到的结果与由清单
2得到的结果相同，但是没有了由FOR循环而导致的过多规则。
预测2004年的销售额
你几乎已经完成了这一工作。但首先，需要根据2003年第二、三季节的销售趋势观测第四季度的趋势。清单
4给出了完成这一预测的规则。新的规则可能看起来有点儿复杂，但它只是前面给出的公式s4 = ((s3-s2)/s2)*s3+s3的一个扩展，它使用BETWEEN谓词来隔离给定季度的单元。例如，下面的表达式返回当前图书在2003年第二季度的总销售额：
SUM(nr)[CV(), 2003, m BETWEEN 4 and 6])
清单
4 还给出了预测2004年销售额的规则，它是前面给出的同一个公式的扩展。
货币转换
完成2004年销售额预测的最后一个要求是进行货币转换。为此，你可以将一个包含货币转换系数的换算表添加到你的模型中。在清单
5中，注意MODEL关键字后面的REFERENCE子句。
换算表只是另一个多维数组。我按国家代码命名数组中的货币，并对其进行"分维"。现在，你可以通过引用money.to_us[c]获得对任意给定国家的转换系数。为使国家代码对模型中每个单元都可用，清单
5 将其添加到衡量标准表中。一个分区或维度列作为一个衡量标准也是可以的。清单
5 使用以下嵌套的单元引用获得每个单元的正确转换系数：
money.to_us[cc[CV(),CV(),CV()]]
最内层的（也就是用于别名cc的）单元格引用返回当前的国家代码，然后这个代码被用作货币数组的一个索引，用于检索转换系数，利用该系数可以将净收入转换为美元。
采用新的MODEL子句
MODEL子句提供了一种与众不同的关系数据处理方法。现在，你可以在一个数据库内部执行类似于电子表格的计算。利用规则，你可以清楚地表达你的意图，如果使用其他方式，这是不可能的。你还可以更轻松地对商务需求的变化做出响应。通过将MODEL与其他特性（如分区外联接）相结合，你可以开发针对许多查询问题的创造性解决方案。
Jonathan Gennick (Jonathan@Gennick.com)
是一位经验丰富的Oracle DBA和Oracle认证专家。他一个关于Oracle文章的电子邮件列表，你可以通过访问
http://gennick.com来了解它的一些情况。Gennick最近与他人合著了《Oracle正则表达式袖珍参考手册》(O'Reilly
& Associates出版社出版, 2003年)。 |
