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

目的 在本教程中，您将学习如何使用 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:wkdircleanup.sql cleanup.sql 脚本包含以下内容: DROP VIEW sales_view;DROP TABLE dollar_conv;DROP TABLE growth_rate;DROP TABLE ledger; 4.现在，您可以创建 SALES_VIEW 视图。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirsample_data.sql sample_data.sql 脚本包含以下内容: CREATE VIEW sales_view ASSELECT country_name country, prod_name prod, calendar_year year,SUM(amount_sold) sale, COUNT(amount_sold) cntFROM sales, times, customers, countries, productsWHERE sales.time_id = times.time_id ANDsales.prod_id = products.prod_id AND sales.cust_id = customers.cust_idAND customers.country_id = countries.country_idGROUP BY country_name, prod_name, calendar_year/5.验证视图创建正确，并具有 3219 行。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirsel_sv.sql sel_sv.sql 脚本包含以下内容: SELECT COUNT(*) FROM sales_view;6.要使性能最佳化，系统应当已经具有基于以上视图使用的数据构建的物化视图。该物化视图是在 SH 模式数据的安装期间创建的。Oracle 的摘要治理系统将使用上述视图自动重写所有查询，以便利用该物化视图。 查看示例语法 作为模型的初始示例，请考虑以下语句: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country IN ('Italy','Japan') MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000], sales['Y Box', 2002] = sales['Y Box', 2001], sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])ORDER BY country, prod, year;结果是: COUNTRY PROD YEAR SALES-------------------- --------------- ---------- ----------Italy 2_Products 2002 90387.54Italy Bounce 2002 9179.99Italy Y Box 2002 81207.55Japan 2_Products 2002 101071.96Japan Bounce 2002 11437.13Japan Y Box 2002 89634.83因为该语句按照国家/地区划分，所以这些规则一次应用于一个国家/地区的数据。请注重，数据结束于 2001 年，因此为 2002 年或之后年份定义值的任何规则都将插入新单元格。第一个规则将 2002 年 Bounce 的销售定义为 2000 年和 2001 年的销售总和。第二个规则将 2002 年 Y Box 的销售定义为 2001 年的销售值。第三个规则定义了一个名为 2_Products 的类别，它是 2002 年的 Bounce 与 Y Box 值相加所得的总和。请注重，2_Products 的值派生自前两个规则的结果，因此这两个规则必须在 2_Products 规则之前执行。 语法准则 请注重，MODEL 要害字后面的 RETURN UPDATED ROWS 子句将结果限制为在该查询中创建或更新的那些行。使用该子句是使结果集只包含新计算的值的简便方法。在整个示例中，都将用到 RETURN UPDATED ROWS 子句。 示例中显示在规则开头处的 RULES 要害字是可选的，但是建议您使用以方便阅读。 许多示例在 COUNTRY 列并不需要 ORDER BY。但是，为了方便修改示例并添加多个国家/地区，应将其包含在规范中。 技术细节 以下示例演示了 MODEL 子句的主要功能，从基本单元格引用到引用模型以及迭代模型。 使用位置和符号单元格引用 本部分探究了在 MODEL 语句中使用符号和位置单元格引用的技巧。 1.要查看产品 Bounce 于 2000 年在意大利的 SALES 值，并将其设为 10，可使用“位置单元格引用”。单元格引用的值将根据其在表达式中的位置与相应的维度匹配。模型的 DIMENSION BY 子句决定指定给每个维 — 在本例中，第一个位置是产品 (PROD)，第二个位置是 YEAR.从 SQL*Plus 会话中，执行以下脚本: @c:wkdirpos_cell1.sql pos_cell1.sql 脚本包含以下内容: COLUMN country FORMAT a20COLUMN prod FORMAT a20SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2000] = 10 )ORDER BY country, prod, year/2.要创建产品 Bounce 于 2005 年在意大利的 SALES 猜测值，并将其设为 20，可使用 SELECT 语句中的规则将年份值设为 2005，从而在数组中创建新单元格。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirpos_cell2.sql pos_cell2.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2005] = 20 )ORDER BY country, prod, year/注重:假如您希望创建新单元格(例如，未来几年的值)，则必须使用位置引用或 FOR 循环(本教程稍后讨论)。也就是说，位置引用答应更新数组以及向数组中插入新值。这称为 UPSERT 过程，它由 Oracle SQL MERGE 语句处理。 3.要更新产品 Bounce 自 1999 年以来针对意大利记录的所有年份的 SALES，并将它们设为 10，可以使用“符号单元格引用”。单元格引用的值通过布尔条件与相应的维度匹配。您可以使用所有常见的运算符，例如 <、>、IN 和 BETWEEN。在本例中，查询将查找等于 Bounce 的产品值和所有大于 1999 的年份值。这展示了单一规则如何访问多个单元格。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirsym_cell1.sql sym_cell1.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales[prod='Bounce', year>1999] = 10 )ORDER BY country, prod, year/注重:符号引用功能强大，但它们只能用于更新现有的单元格:它们不能创建新单元格，例如，未来几年的销售规划。 4.您希望通过单一查询来更新多个国家/地区的多种产品在数年中的销售，并且还希望插入新的单元格。通过将数个规则置于一个查询中，处理会更加高效，因为这减少了需要访问数据的次数。它还答应使用更为简洁的 SQL，以使开发人员的工作效率更高。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirpos_sym.sql pos_sym.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_view WHERE country IN ('Italy','Japan') MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2002] = sales['Bounce', year = 2001] , --positional notation: can insert new cell sales['Y Box', year>2000] = sales['Y Box', 1999], --symbolic notation: can update existing cell sales['2_Products', 2005] = sales['Bounce', 2001] + sales['Y Box', 2000] ) --positional notation: permits insert of new cells --for new productORDER BY country, prod, year/该示例数据没有超出 2001 年的值，因此所有涉及到 2002 年或之后的规则都要求插入新的单元格。对于此处定义的任何新产品名也是如此。在第三条规则中，2_Products 被定义为 2005 年的销售是 2001 年 Bounce 销售与 2000 年 Y Box 销售总和的产品。 对于 2002 年的 Bounce，第一个规则将插入新的单元格，因为这是位置表示法。对于 Y Box，第二个规则使用符号表示法，但是此处已经有了 2001 年的 Y Box 值，因此它将更新这些值。对于 2005 年的 2_Products，第三个规则是位置表示法，因此它可以插入新的单元格，您将在输出中看到这些新单元格。 在规则右侧使用多单元格引用 早先的示例只能在规则的左侧使用多单元格引用。假如要在规则右侧引用多个单元格，您可以在规则右侧使用多单元格引用，在这种情况下，需要对其应用聚合函数，以将它们转换为单一值。可以使用所有现有的聚合函数，包括 OLAP 聚合函数(逆分配函数、虚拟等级和分配函数等)、统计聚合函数以及用户定义的聚合函数。 1.您希望猜测 2005 年 Bounce 在意大利的销售比其在 1999 至 2001 年间的最大销售多 100。为此，您需要在规则右侧使用 BETWEEN 子句来指定多个单元格，并通过 MAX() 函数将其聚合为单一值。从 SQL*Plus 会话中，执行以下脚本: @c:wkdirmulti_c.sql multi_c.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', 2005] = 100 + max(sales)['Bounce', year BETWEEN 1998 AND 2002] )ORDER BY country, prod, year/请注重，聚合函数只出现在规则的右侧。聚合函数的参数可以是常量、约束变量、MODEL 子句的度量或者涉及这三种参数的表达式。 使用 CV() 函数和 ANY 通配符 CV() 函数是一个非常强大的工具，它可以高效地进行规则创建。CV() 用于规则的右侧，以复制左侧指定的当前维度值。对于左侧规范引用多个单元格来说，它非常有用。用关系数据库的概念来理解，该函数类似于连接操作。 CV() 答应使用非常灵活的表达式。例如，通过从 CV(year) 值进行减法运算，可以引用数据集中的其他行。假如您的单元格引用中有表达式“CV(year) -2”，您两年前就可以访问数据。CV() 函数通常作为单元格引用的一部分使用，但是也可以在单元格引用外部作为独立的表达式元素。 1.您希望更新 Bounce 在意大利多年的销售值，使用的规则是 Bounce 每年的销售是 Y Box 当年销售的 20% 与 Mouse Pad 当年销售的总和。从 SQL*Plus 会话中，执行以下脚本: @c:wkdircvf1.sql cvf1.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES ( sales['Bounce', year BETWEEN 1995 AND 2002] = sales['Mouse Pad', cv(year)] + 0.2 * sales['Y Box', cv(year)])ORDER BY country, prod, year/请注重，在上述结果中，尽管接受了 1995–2002 年间的所有年份，您也只会看到 1999–2001 年的值。这是因为该表格只有这几年的数据。CV() 函数将提供左侧当前引用的单元格的 DIMENSION BY 要害字当前值。当上述规则的左侧引用单元格 Bounce 和 1999 时，右侧表达式将如下所示: sales['Mouse Pad', 1999] + 0.2 * sales['Y Box', 1999] 同样，当左侧引用单元格 Bounce 和 2000 时，右侧表达式将为: sales['Mouse Pad', 2000] + 0.2 * sales['Y Box', 2000] CV() 函数将维度要害字作为其参数。还可以使用不带任何参数的 CV()(如 CV())，在这种情况下，暗示了位置引用。以上规则还可以写为: s['Bounce', year BETWEEN 1995 AND 2002] = s['Mouse Pad', cv()] + 0.2 * s['Y Box', cv()] CV() 函数只能在右侧单元格引用中使用。 2.您希望计算出产品 Y Box、Bounce 和 Mouse Pad 在意大利的销售年增长率。从 SQL*Plus 会话中，执行以下脚本: @c:wkdircvf2.sql cvf2.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales, growthFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWS PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale sales, 0 growth) RULES ( growth[prod in ('Bounce','Y Box','Mouse Pad'), year between 1998 and 2001] = 100* (sales[cv(prod), cv(year)] - sales[cv(prod), cv(year) -1] ) / sales[cv(prod), cv(year) -1] )ORDER BY country, prod, year/请注重，结果中的空白单元格都是 NULL。假如没有前两年的产品值，那么规则将生成 NULL。由于没有一种产品有 1998 年的值，因此在任何情况下，1999 年的增长计算均为 NULL。