ORACLE高级查询之MODEL PART5

接PART4：http://blog.chinaunix.net/uid-7655508-id-5835079.html

1.3.1.4 IS ANY

  IS ANY用于符号单元格引用，和ANY的作用一致，引用输入单元格的所有值，包括NULL，只能用在规则左边，它也会阻止新单元格的插入，因为插入新单元格是不可能的。

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[y IS ANY, 3] = (s[CV(),3] + s[CV(),3]) / 2)

ORDER BY y, r, m;

y IS ANY表示引用所有年份。

1.3.1.5  IS PRESENT

  IS PRESENT是单元格判断表达式，返回TRUE或FALSE,如果在MODEL语句执行之前此单元格存在（不管值是否为NULL），则返回TRUE,否则返回FALSE,常于CASE,DECODE等连用，只能用在规则右边。

  下面的因为s[2004,3]不存在，所以s[2001,3]的结果为0。

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2001, 3] =
CASE WHEN s[2004,3] IS PRESENT

THEN s[2004,3]

ELSE 0 END )

ORDER BY y, r, m;

--虽然s[2001,12]的值为null,但是还是为真，返回100

SELECT r, y, m, s

FROM sales_history

WHERE month = 12

MODEL

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2000, m=12] =
CASE WHEN s[2001，12] IS PRESENT

THEN 100

ELSE 0 END )

ORDER BY y, r, m;

1.3.1.6  PRESENTV

  PRESENTV是一个函数，PRESENTV(cell,expr1,expr2)。如果在model语句执行前此单元格存在，不管是否是NULL，则返回expr1,否则返回expr2。可以用来代替IS PRESENT判断，只能用在规则右边。

  下面的和CASE s[2004,3] IS PRESENT THEN s[2004,3] ELSE 0 END效果一样。

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2001, 3] =PRESENTV( s[2004,3], s[2004,3],0))

ORDER BY y, r, m;

1.3.1.7  PRESENTNNV

PRESENTNNV函数参数和PRESENTV一样，作用是如果在MODEL语句执行之前此单元格存在且不为NULL，则返回expr1,否则返回expr2，比PRESENTV多个NN（NOT NULL）约束，也只能用在规则右边。有了这两个函数，IS PRESENT基本可以不需要了

1.存在且非NULL，将2000年3月的赋给2001年3月的

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2001, 3] =PRESENTV( s[2001,3], s[2000,3],0))

ORDER BY y, r, m;

2.存在但是为NULL，赋值为10

SELECT r, y, m, s

FROM sales_history

WHERE month = 12

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2001, 12] =PRESENTNNV( s[2000,12], s[2000,12],10))

ORDER BY y, r, m;

      

2.PRESENTV只需要存在，不管是否为NULL，返回结果s[2000,12]为NULL

SELECT r, y, m, s

FROM sales_history

WHERE month = 12

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2001, 12] =PRESENTV( s[2000,12], s[2000,12],10))

ORDER BY y, r, m;

1.3. 2 规则右边指定范围

  到目前为止，规则左边可以使用符号单元格引用或FOR LOOP实现多个范围的单元格引用，没有说规则右边，其实规则右边可以使用很多功能，比如组函数，分析函数，当然也可以使用符号单元格引用，FOR LOOP是不可以的。注意：规则右边使用函数，是函数(度量列)[….]这种形式。

计算s[2004,3]月的单元格为对应区域对应1995年到2003年对应年份的3月的所有单元格值的平均值。当然AVG(col)不包含NULL的计算，符号单元格用在左边，如果单元格不存在，则不计算，但是用在右边，不存在会计算为NULL。

SELECT r, y, m, s

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[2004, 3] = AVG(s)[y BETWEEN 1995 AND 2003,3])

ORDER BY y, r, m;

  其他复杂的运用，比如分析函数等，类型注意等后面详细讲解。

1.3. 3 规则的计算顺序

   MODEL语句的规则可能有多个，而这些规则可能是相互依赖的，一个规则中的单元格结果会成为另一个规则的输入，这种情况下，规则的计算顺序是很重要的、在RULES中有两个选项：

RULES [ [SEQUENTIAL | AUTOMATIC] ORDER ]

默认的是SEQUENTIAL ORDER。

1.3.3.1 SEQUENTIAL ORDER

  SEQUENTIAL ORDER,顾名思义就是按照规则出现在RULES里的顺序从左到右一个规则地计算，后一个规则的输入(规则右边)引用了前一个规则的输出（规则左边），如果后一个规则的输出单元格和前一个规则的输出单元格一样，那么则会覆盖前一个规则的输出。

  第1个规则的s[2002,3]输出会作为第2个规则的右边输入，第2个规则的s[2003,3]就是第1个规则的单元格结果*1.1,最终会计算两个规则单元格，s[2002,3]与s[2003,3]。

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

  RETURN UPDATED ROWS

  PARTITION BY (region_id r)

  DIMENSION BY (year y, month m)

  MEASURES (sales s)

  RULES SEQUENTIAL ORDER

  (

    s[2002,3] = (s[2000,3] + s[2001,3])/2,

    s[2003,3] = s[2002,3] * 1.1

  )

ORDER BY y, r, m;

      

如果换成

RULES SEQUENTIAL ORDER

  (

    s[2003,3] = s[2002,3] * 1.1,

    s[2002,3] = (s[2000,3] + s[2001,3])/2

  )

则因为s[2002,3]不存在，则第1个规则的结果为NULL，第2个规则结果有。

换成

RULES SEQUENTIAL ORDER

  (

    s[2011,3] = (s[2000,3] + s[2001,3])/2,

    s[y=2011,3] = 10

)

  因为第1个s[2011,3]有值，虽然第2个是符号单元格引用，但是因为第1个已经计算出来，此单元格存在，而且与第1个一样，所以覆盖第1个规则的计算结果，为10.

如果第2个规则改为s[y=2012,3]=10，那么因为不存在，不计算，只显示第1个规则的结果。