概述
Model子句为SQL计算带来了新的能力和灵活性，使用Model子句可以根据查询结果创建多维数组，然后将公式(rules)应用与数组以计算新值。Model子句可以取代电子表格进行SQL建模，数据仓库中使用较多
语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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子句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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)等价于星型模型中事实表的度量。它们通常包含数字值，例如销售额。
模型元素
模型流程处理
SQL Model功能
1. Cell addressing using dimension values
单个行中的度量列被视为多维数组中的单元格，并且可以使用维度值进行引用和更新。例如将product和year作为维度列，sales则作为度量列。则可以通过sales[‘Bounce’, 2002]的形式来提供2002年Bounce的sales值
2. Symbolic array computation
可以指定一系列rules来对数据进行操作。rules可以调用单个单元格或涉及多个单元格的函数。例如单个单元格的调用：
1
|
sales[product='Bounce',year=2002] = sales['Bounce',2000]+ sales['Bounce',1999]
|
设计多个单元格的函数调用
1
|
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单独指定则会覆盖掉全局选项。
1
2
|
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来表示维度中的所有值.
1
|
sales[product IS ANY,2001]=sales['Bounce',2000]
|
5. Accessing dimension values using the CV function
通过在rules右侧使用CV函数来访问左侧引用的单元格维度值，它使你能够将执行类似计算的多个规则组合到一个规则中。
1
2
3
|
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]
|
上述规则可以简化为下列规则
1
|
sales[country IN ('Spain', 'Italy', 'Japan'), year=2002] = 1.2 * sales[CV(country), 2001]
|
6. Ordered computation
对于更新一组单元格的rules，结果可能取决于维度值的排序。可以通过ORDER BY 在rules中指定一个值来强制维度值的特定排序
1
|
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关键字的单元格之间的依赖关系自动排序。例如在下列语句中，最后两个规则将在第一个规则之前处理，因为第一个规则取决于后两个规则：
1
2
3
|
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
你可以指定循环迭代的规则，满足条件退出循环
1
2
|
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)，可以关联两张表如下列规则：
1
|
dollar_sales['Spain',2001] = sales['Spain',2000] * ratio['Spain']
|
10. Scalable computation
可以独立于其它分区对每个分区的数据进行分区和规则，这使得基于分区的Model计算可以并行化
1
2
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
- 其他所有数据库类型时返回空值
1
2
3
4
5
6
7
|
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对空值或缺失数字值返回空值，此为默认值
参考链接
- SQL For Modeling