概述

Model子句为SQL计算带来了新的能力和灵活性，使用Model子句可以根据查询结果创建多维数组，然后将公式(rules)应用与数组以计算新值。Model子句可以取代电子表格进行SQL建模，数据仓库中使用较多

语法

MODEL
[<global reference options>]
[<reference models>]
[MAIN ]
    [PARTITION BY (<cols>)]
    DIMENSION BY (<cols>)
    MEASURES (<cols>)
    [<reference options>]
    [RULES]  <rule options>
    (<rule>, <rule>,.., <rule>)
    <global reference options> ::= <reference options> <ret-opt>
      <ret-opt> ::= RETURN {ALL|UPDATED} ROWS
    <reference options> ::=
    [IGNORE NAV | [KEEP NAV]
    [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
    <rule options> ::=
    [UPDATE | UPSERT | UPSERT ALL]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (<number>)  [UNTIL <condition>]]
    <reference models> ::= REFERENCE ON <ref-name> ON (<query>)
    DIMENSION BY (<cols>) MEASURES (<cols>) <reference options>

简单的Model子句

SELECT SUBSTR(country, 1, 20) country, 
            SUBSTR(product, 1, 15) product, year, sales
FROM sales_view
WHERE country IN ('Italy', 'Japan')
MODEL
    PARTITION BY (country) 
    DIMENSION BY (product, year)
    MEASURES (sales sales)
    RULES 
    (sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],
      sales['Y Box', 2002] = sales['Y Box', 2001],
      sales['All_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, product, year;
  
COUNTRY              PRODUCT               YEAR      SALES
-------------------- --------------- ---------- ----------
Italy                Bounce                2001       1000
Italy                Bounce                2002    5333.69
Italy                Y Box                 2002   81207.55
Japan                Bounce                2001       1000
Japan                Bounce                2002    6133.53
Japan                Y Box                 2002   89634.83

该Model子句允许通过将查询的列映射到三个组来创建多维数组：分区，维度，度量列。

  • 分区(PARTITION)列以分析函数的分区方式定义结果集的逻辑块。Model子句中的规则适用于每个独立于其它分区的分区。

  • 维度(Dimension)定义多维数组并用于识别分区内的单元。默认情况下，完整的维度组合应该只识别分区的一个单元。在默认模式下，它们可以被认为关系表中的键。

  • 度量列(Measures)等价于星型模型中事实表的度量。它们通常包含数字值，例如销售额。

模型元素

Model Elements

模型流程处理

Model Flow Processing

SQL Model功能

1. Cell addressing using dimension values

单个行中的度量列被视为多维数组中的单元格，并且可以使用维度值进行引用和更新。例如将product和year作为维度列，sales则作为度量列。则可以通过sales[‘Bounce’, 2002]的形式来提供2002年Bounce的sales值

2. Symbolic array computation

可以指定一系列rules来对数据进行操作。rules可以调用单个单元格或涉及多个单元格的函数。例如单个单元格的调用：

sales[product='Bounce',year=2002] = sales['Bounce',2000]+ sales['Bounce',1999]

设计多个单元格的函数调用

sales[product='Bounce',year=2002] = MAX(sales)['Bounce',year BETWEEN 1997 AND 2000]

3. UPSERT, UPSERT ALL, and UPDATE options

UPSERT为默认选项，可以创建输入数据中不存在的单元格值，如果存在则更新。如果数据中不存在引用的单元格，并且rules中使用了合适的表达式，则插入该单元格。UPSERT ALL提供了UPSERT更广泛的rules支持。UPDATE不会插入任何新的单元格。

如果全局指定这些选项，则适用于所有rules。如果在rules单独指定则会覆盖掉全局选项。

UPDATE sales['Bounce', 1999] = 3567.99,  
UPSERT sales['Bounce', 2001] = sales['Spain', 2000]+ sales['Spain', 1999]

第一个rule会更新1999年Spain的sales值，第二个rule如果单元格存在就更新2001年Spain的sales值，如果不存在就创建一个新的单元格。

4. Wildcard specification of dimensions

可以通过通配符ANY或IS ANY来表示维度中的所有值.

sales[product IS ANY,2001]=sales['Bounce',2000]

5. Accessing dimension values using the CV function

通过在rules右侧使用CV函数来访问左侧引用的单元格维度值，它使你能够将执行类似计算的多个规则组合到一个规则中。

sales[country='Spain', year=2002] = 1.2 * sales['Spain', 2001],
sales[country='Italy', year=2002] = 1.2 * sales['Italy', 2001],
sales[country='Japan', year=2002] = 1.2 * sales['Japan', 2001]

上述规则可以简化为下列规则

sales[country IN ('Spain', 'Italy', 'Japan'), year=2002] = 1.2 * sales[CV(country), 2001]

6. Ordered computation

对于更新一组单元格的rules，结果可能取决于维度值的排序。可以通过ORDER BY 在rules中指定一个值来强制维度值的特定排序

sales[country IS ANY, year BETWEEN 2000 AND 2003] ORDER BY year = 1.05 * sales[CV(country), CV(year)-1]

7. Automatic rule ordering

Model子句中的规则可以根据使用AUTOMATIC ORDER关键字的单元格之间的依赖关系自动排序。例如在下列语句中，最后两个规则将在第一个规则之前处理，因为第一个规则取决于后两个规则：

RULES AUTOMATIC ORDER  
    {sales[c='Spain', y=2001] = sales[c='Spain', y=2000] + sales[c='Spain', y=1999] sales[c='Spain', y=2000] = 50000,  
    sales[c='Spain', y=1999] = 40000}

8. Iterative rule evaluation

你可以指定循环迭代的规则，满足条件退出循环

MODEL DIMENSION BY (x) MEASURES (s)  
    RULES ITERATE (4) (s[x=1] = s[x=1]/2)

该语句指定规则s[x=1] = s[x=1]/2循环4次，其中ITERATE选项指定了循环次数。也可以通过UNTIL子句设置退出条件

9. Reference models

一个Model可以包含多个参考Model，它们是只读数组。规则可以引用来自不同参考模型的单元。规则可以只在一个多维数组中更新或插入单元格，称为Primary Model。参考模型的使用使您能够将模型与不同的维度关联起来。例如有销售表ft(country, year, sales)和货币表cr(country, ratio)，可以关联两张表如下列规则：

dollar_sales['Spain',2001] = sales['Spain',2000] * ratio['Spain']

10. Scalable computation

可以独立于其它分区对每个分区的数据进行分区和规则，这使得基于分区的Model计算可以并行化

MODEL PARTITION BY (country) DIMENSION BY (year) MEASURES (sales)
    (sales[year=2001] = AVG(sales)[year BETWEEN 1990 AND 2000]

上述Model按country分区，并在每个分区类可以计算2001的sales等于1990到2000年间的平均值。

处理空值和缺失值

1. IS PRESENT

通过IS PRESENT判断单元格是否存在，存在即为true，否则为false

select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
Order by prd_type_id,year,month;

2. PRESENTV()

PRESENTV(cell, expr1, expr2)如果存在就返回expr1，不存在就返回expr2

select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;

3. PRESENTNNV()

PRESENTNNV(cell,expr1,expr2)如果存在且不为空就返回expr1,如果不存在或为空就返回expr2

select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTNNV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;

4. IGNORE NAV和KEEP NAV

IGNORE NAV返回值如下：

  • 空值或缺失数字值时返回0
  • 空值或缺失字符串值时返回空字符串
  • 空值或缺失日期值时返回01-JAN-2000
  • 其他所有数据库类型时返回空值
    SELECT product, year, sales
    FROM sales_view
    WHERE country = 'Poland'
    MODEL
        DIMENSION BY (product, year) MEASURES (sales sales) IGNORE NAV
        RULES UPSERT
        (sales['Bounce', 2003] = sales['Bounce', 2002] + sales['Bounce', 2001]);
    KEEP NAV对空值或缺失数字值返回空值，此为默认值

参考链接

  1. SQL For Modeling