Oracle中使用SQL MODEL定义行间计算 （1）

   来源：oracle    作者：gfwz

目的

在本教程中，您将学习如何使用 Oracle 数据库 10g SQL MODEL 子句执行行间计算。

所需时间

大约 30 分钟

主题

本教程包括下列主题:

概述

情景

前提条件

设置示例数据

查看示例语法

使用位置和符号单元格引用

在规则右侧使用多单元格引用

使用 CV() 函数和 ANY 通配符

Coding FOR Loops:指定新单元格的简洁方法

了解规则的评估顺序

处理 NULL 度量和缺失单元格

创建引用模型

创建迭代模型

使用排序规则

总结

概述

利用 SQL MODEL 子句，您可以根据查询结果定义多维数组，然后将规则应用于该数组以计算新值。这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集成到数据库中，可以大幅度提升性能、可伸缩性以及可管理性。用户可以将数据保留在 Oracle 环境内，而无需将数据复制到单独的应用程序或 PC 电子表格中。

MODEL 子句通过将查询列映射到以下三组来定义多维数组:分区列、维度列和度量列。这些元素执行以下任务:

分区以类似于分析函数的分区方式(在数据仓库指南中标题为“数据仓库中用于分析的 SQL”的一章中有述)来定义结果集的逻辑块。将 MODEL 规则应用于每个分区的单元格。

维度用于标识分区内的每个度量单元格。这些列用于标识日期、区域以及产品名之类的特征。

度量类似于星型模式中事实表的度量。它们通常包含数值，例如销售单位或成本。通过指定每个单元格的完整维度组合，可以在单元格所处的分区内对其进行访问。

要针对这些多维数组创建规则，您需要定义以维度值形式表达的计算规则。规则灵活且简洁，并且可以使用通配符和 FOR 循环，以最大限度地表达您的意图。利用 MODEL 子句构建的计算通过将分析集成到数据库中改善了传统的电子表格计算，通过符号引用提高了可读性，并提供了可伸缩性和更好的可管理性。

下图使用假设的销售表格从概念的角度概述了该模型的特征。该表格具有四列:国家/地区、产品、年份和销售量。该图分为三个部分。上段阐释了将表格划分为分区、维和度量三列的概念。中段给出了两个假想规则，以预测 Prod1 和 Prod2 的销售，因为产品销售的计算值来自前两年。最后，第三部分显示了将规则应用于这个包含假设数据的表格后得出的查询输出。黑色输出是从数据库检索的数据，而蓝色输出表示根据规则计算出的行。请注意，这些规则是在每个分区内应用的。

映射到分区、维和度量的列

COUNTRY  PRODUCT  YEAR  SALES 
 分区  维度  维度  度量 

规则: sales('prod1', 2002) = sales('prod1', 2000) + sales('prod1', 2001)
sales('prod2', 2002) = sales('prod2', 2000) + sales('prod2', 2001)

MODEL 子句的输出:

COUNTRY  PRODUCT  YEAR  SALES 
 分区  维度  维度  度量 
 A  prod1  2000  10 
 A  prod1  2001  15 
 A  prod2  2000  12 
 A  prod2  2001  16 
 B  prod1  2000  21 
 B  prod1  2001  23 
 B  prod2  2000  28 
 B  prod2  2001  29 
 A  prod1  2002  25 
 A  prod2  2002  28 
 B  prod1  2002  44 
 B  prod2  2002  57

请注意，MODEL 子句没有更新表格中的现有数据，也没有向表格中插入新数据 要更改表格中的值，必须将模型结果提供给 INSERT、UPDATE 或 MERGE 语句。

案例

通过使用 MODEL 子句，您可以将电子表格计算引入数据库。您将使用 Sales History 模式数据并通过包含新 MODEL 子句的 SELECT 语句来执行类似电子表格的计算。您需要标识规则，以找出诸如销售预测之类的信息。

前提条件

开始本教程之前，您应该:

1.完成了教程在 Windows 上安装 Oracle 数据库 10g。

2.下载 model_clause.zip 并将其解压缩到您的工作目录(例如，c:\wkdir)

设置示例数据

使用 SH 模式创建视图。该视图将按国家/地区提供产品销售的年度总计(总款和总量)，并跨所有渠道进行聚集。

1.启动一个 SQL*Plus 会话。选择开始 > 程序 > Oracle-OraDB10g_home > Application Development > SQL Plus。

(注意:本教程假设您拥有 c:\wkdir 文件夹。如果没有，则需要创建此文件夹，并将 model_clause.zip 的内容解压缩到此文件夹中。当执行这些脚本时，指定路径)

2.以 SH 用户的身份登录。输入 SH 作为 User Name，并输入 SH 作为 Password。然后单击 OK。

3.首先，确保环境简洁。从 SQL*Plus 会话运行 cleanup.sql 脚本。 @c:\wkdir\cleanup.sql

cleanup.sql 脚本包含以下内容: DROP VIEW sales_view;DROP TABLE dollar_conv;DROP TABLE growth_rate;DROP TABLE ledger;