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

　　目的

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

网管网www_bitscn_com

　　所需时间 中国网管论坛bbs.bitsCN.com

　　大约 30 分钟 中国网管论坛bbs.bitsCN.com

　　主题

网管联盟bitsCN@com

　　本教程包括下列主题:

　　概述 网管联盟bitsCN_com

　　情景

网管bitscn_com

　　前提条件

　　设置示例数据

　　查看示例语法

　　使用位置和符号单元格引用 网管网www_bitscn_com

　　在规则右侧使用多单元格引用 中国网管论坛bbs.bitsCN.com

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

网管u家u.bitscn@com

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

　　了解规则的评估顺序

　　处理 NULL 度量和缺失单元格

网管论坛bbs_bitsCN_com

　　创建引用模型

网管联盟bitsCN_com

　　创建迭代模型 网管下载dl.bitscn.com

　　使用排序规则 网管u家u.bitscn@com

　　总结 中国网管论坛bbs.bitsCN.com

　　概述

网管联盟bitsCN_com

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

网管联盟bitsCN@com

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

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

网管网www.bitscn.com

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

网管下载dl.bitscn.com

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

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

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

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

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

网管u家u.bitscn@com

　　规则:

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

　　MODEL 子句的输出: 网管u家u.bitscn@com

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

网管论坛bbs_bitsCN_com

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

案例 网管论坛bbs_bitsCN_com

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

　　前提条件 网管论坛bbs_bitsCN_com

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

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

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

　　设置示例数据 中国网管联盟bitsCN.com

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

　　1.启动一个 SQL*Plus 会话。选择开始 > 程序 > Oracle-OraDB10g_home > Application Development > SQL Plus。 中国网管联盟bitsCN.com

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

　　

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

　　 网管论坛bbs_bitsCN_com

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

@c:\wkdir\cleanup.sql

网管网www_bitscn_com

　　cleanup.sql 脚本包含以下内容:

DROP VIEW sales_view;DROP TABLE dollar_conv;DROP TABLE growth_rate;DROP TABLE ledger;

　　 网管网www.bitscn.com