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

     来源：oracle    作者：gfwz

4.您希望通过单一查询来更新多个国家/地区的多种产品在数年中的销售，并且还希望插入新的单元格。通过将数个规则置于一个查询中，处理会更加高效，因为这减少了需要访问数据的次数。它还允许使用更为简洁的 SQL，以使开发人员的工作效率更高。从 SQL*Plus 会话中，执行以下脚本:

 

@c:\wkdir\pos_sym.sql 

pos_sym.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, 
SUBSTR(prod,1,15) prod, year, sales
FROM 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 product
ORDER 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:\wkdir\multi_c.sql 

multi_c.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, 
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE 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:\wkdir\cvf1.sql 

cvf1.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, 
SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE 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:\wkdir\cvf2.sql 
cvf2.sql 脚本包含以下内容: SELECT SUBSTR(country,1,20) country, 
  SUBSTR(prod,1,15) prod, year, sales, growth
FROM sales_view
WHERE 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。