OBIEE中MSUM函数的用法

 

计算的粒度为月，如果想计算得到上个月的销售量，或者三个月前的销售量，下个季度的销售量，去年的销售量，从而得到

  Last Month % Increase

  Last Quarter % Increase

  Next Quarter % Increase

  Last Year % Increase

可以使用MSUM 函数间接计算得到。

查阅文档得到MSUM函数的说明如下：

MSUM

This function calculates a moving sum for the last n rows of data, inclusive of the current row.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data. The sum for the third row is calculated by taking the sum of the first three rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior .

Syntax:

MSUM (n_expression, n)

Where:

n_expression	Any expression that evaluates to a numerical value.
n	Any positive integer. Represents the sum of the last n rows of data.

Example:

The following example shows a query that uses the MSUM function and the query results.

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

MONTH	REVENUE	3_MO_SUM
JAN	100.00	100.00
FEB	200.00	300.00
MAR	100.00	400.00
APRIL	100.00	400.00
MAY	300.00	500.00
JUNE	400.00	800.00
JULY	500.00	1200.00
AUG	500.00	1400.00
SEPT	500.00	1500.00
OCT	300.00	1300.00
NOV	200.00	1000.00
DEC	100.00	600.00

根据文档，该函数可以得到最后N行的求和结果。特别地，当N等于1的时候，计算的就是自身。

获得N个月前的值的通用公式如下(与oracle的lag分析函数同等效果) ：

 

lag( <sales volume>, <n> ) =
      msum( <sales volume>, <n> + 1 ) –
      msum( <sales volume>, <n> ) 

 

 当月与N个月前值的增长比例计算公司如下：

100 * ( <current sales> – <months ago sales> ) /
      <months ago sales> 

 

 等价于：

100 * ( <sales volume> –
      msum( <sales volume>, <n> + 1 ) +
      msum( <sales volume>, <n> )
      ) /
      ( msum( <sales volume>, <n> + 1 ) –
      msum( <sales volume>, <n> ) ) 

 

现在我们看下，如果想要得到以上三个值，分别如何计算：

1， Last Month % Increase

计算公式：

100*( <current sales> – <one month ago sales> ) /
      <one month ago sales> )

用MSUM函数实现等价于：

100 * ( 2 * <sales volume> –
      msum( <sales volume>, 2 )
      ) /
      ( msum( <sales volume>, 2 ) –
      <sales volume> ) 

 

分母表示：得到当前月和起一个月的总和后，减去当前月的值，得到的就是上个月的值。

分子表示：当前月的值-上个月的值

2， Last Quarter % Increase

Last Month % Increase类似，只不过需要改下N的值。

具体的公式如下：

100 * (  <sales volume> –
      msum( <sales volume>, 4 ) +
      msum( <sales volume>, 3 ) 
      ) /
      ( msum( <sales volume>, 4 ) –
      msum( <sales volume>, 3 )  ) 

 

 

3， Next Quarter % Increase

但是要计算当前月的下个季度增长率，则会有所不同。MSUM函数只能向过去推算(不支持负数的N值)。由于OBIEE不支持的这种向未来推算的函数，我们需要另一个函数来得到下个季度的对应值。Oracle提供了很多分析函数，比如

1.  自动汇总函数rollup,cube,
2.  rank 函数, rank,dense_rank,row_number
3.  lag,lead函数
4.  msum,mavg,的移动增加,移动平均数
5.  ratio_to_report报表处理函数
6.  first,last取基数的分析函数

lag函数跟MSUM函数的作用类似，而lead函数刚好就能满足我们的当下需求。不过这个函数就有点依赖于数据库了。如果底层的数据源不是ORACLE则无法使用。

现在可以利用OBIEE的evaluate函数来调用oracle的lead 分析函数。要的到N个月后的值的公式如下：

 

evaluate( 'lead( %1, <n> ) over ( order by %2 )' as double,
      <sales volume>, <ordering column> ) 

 

  在这里要计算下个季度的值，需要把N改为3，具体为：

      

注意：上面的%1和%2分别对应后面紧跟的两个参数。

不过这里也会遇到跟Last Year一样的空值问题。不过上图中的公式里增加了一个排序列就会默认补充空值，这点没有理解。原话如下：

写道

We’ve added “Calendar Month Desc” to the request as a hidden column to provide a single sort column for the evaluate function (we don’t have the same problem here with trailing rows – rows to which the formula doesn’t apply will have null values).

 

4， Last Year % Increase

理论上只需要按照之前利用MSUM函数计算，向前推12个月即可。

不过这里会遇到点问题,对于这最早一年的前11个月而言，使用MSUM会得到空的行从而计算的结果为NULL。但是对于第12个月而言，公式中会部分找到行进行累加，而另外一部分却得到空值，这样会导致计算错误。所以需要加上限制，仅对行数大与12的进行计算。

       

以上用到了rcount函数。该函数的使用介绍如下：

RCOUNT

This function takes a set of records as input and counts the number of records encountered so far.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior .

Syntax:

RCOUNT (Expr)

Where:

Expr	An expression of any data type.

Example:

The following example shows a query that uses the RCOUNT function and the query results.

select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200.

MONTH	PROFIT	RCOUNT (profit
MAY	300.00	2
JUNE	400.00	3
JULY	500.00	4
AUG	500.00	5
SEPT	500.00	6
OCT	300.00	7