BIEE分析及报表函数使用详解.docx

BIEE分析及报表函数使用详解.docx

《BIEE分析及报表函数使用详解.docx》由会员分享，可在线阅读，更多相关《BIEE分析及报表函数使用详解.docx（34页珍藏版）》请在冰豆网上搜索。

BIEE分析及报表函数使用详解

常见BIEE分析及报表函数使用详解

系统中目前存在预制的标准函数，使用现有函数将有助于您更好的完成复杂格式和逻辑的报表。

1.聚合函数

1.AGGREGATEAT

此函数根据指定的级别聚合列。

使用AGGREGATEAT可确保始终在关键字AT之后指定的级别执行度量聚合，而无论WHERE子句如何

语法

AGGREGATE（exprATlevel[,level1,levelN]）

其中：

expr是至少引用一个度量列的任何表达式。

level是要在其中聚合的级别。

您可以选择性地指定多个级别。

AGGREGATE（"基础项"."预算金额"AT"时间"."时间层次"."年"）要使用级别，就需要使用

时间层次

2.AVG

此函数计算结果集中表达式的平均值。

它必须采用数值表达式作为参数。

语法

AVG（numExpr[byExpr]）

其中：

numExpr是求值结果为数值的任何表达式。

Expr是维度列，可以设置AVG的范围

如AVG（"基础项"."预算金额"BY"时间"."年份"）计算年内平均金额

AVGDISTINCT

此函数计算表达式中所有不同值的平均值。

3.BOTTOMN

按从1到n的顺序依次排列表达式参数中最小的n个值，1对应于最小的数值。

BOTTOMN函数对结果集中返回的值执行运算。

请求可以只包含一个BOTTOMN表达式。

语法

BOTTOMN（Expr,integer）

其中：

Expr

求出数值的任意表达式。

Integer

任意正整数。

表示结果集中显示的最低级别编号，1是最低的级别。

一般用于取得排名的数据，隐藏和排除字段都是不会影响结果的，跟表提示一起使用时，会先BottomN然后再提示

4.COUNT

此函数计算表达式中包含非空值的行数。

该表达式通常为列名，此种情况下，将返回该列中包含非空值的行数。

语法：

COUNT（expr[BYexpr1]）

其中：

expr为任意表达式。

expr1可以指定范围。

如COUNT（"基础项"."预算金额"BY"时间"."年份"）

COUNTDISTINCT

此函数对COUNT函数进行相异处理。

5.Max&Min

计算满足数值表达式参数的行的最大值（最大数值）。

语法

Max（numExpr）

其中：

numExpr

求出数值的任意表达式。

可以使用Max（numExprbyDim）设置Max的范围

如MAX（"事实-销售订单头"."订单金额"BY"时间维"."年"）

VSMAX（"事实-销售订单头"."订单金额"）

6.MEDIAN

此函数计算符合数值表达式参数要求的行的中间值。

如果有偶数个行，则中间值为两个中间行的平均值。

此函数始终返回双精度值。

语法

MEDIAN（numExpr[BYexpr1]）

其中：

numExpr是求值结果为数值的任何表达式。

expr1可以指定范围

7.NTILE

此函数根据用户指定的范围确定值的排名。

它返回整数，表示任意排名范围。

换句话说，生成的排序数据集分为多个维片，每个维片中值的数目大致相当。

numTiles=100的NTile返回的结果一般称为“百分比段”（数值从1到100，100表示最高排名）。

该值不同于OracleBIPERCENTILE函数的结果，该函数在SQL92中符合所谓的“百分比排名”，并返回从0到1的值。

语法

NTILE（numExpr,numTiles）

其中：

numExpr是求值结果为数值的任何表达式。

numTiles是非空值正整数，表示维片数量。

如果numExpr参数不为空值，则该函数返回介于请求的范围中的表示排名的整数。

该函数可以用于求得比如说，销售额排名前25%的区域，就设置numTiles为4，然后筛选该参数值等于4的

8.PERCENTILE

此函数计算符合数值表达式参数要求的每个值的百分比排名。

百分比段排名范围介于0（第1个百分比段）和1（第100个百分比段）之间，包含两端的值。

根据结果集内的值计算百分比段。

语法

PERCENTILE（numExpr）

其中：

numExpr是求值结果为数值的任何表达式。

PERCENTILE（"基础项"."预算金额"）*100

9.RANK

计算满足数值表达式参数的每个值的级别。

级别1分配给最大数值，下一个连续的整数（2,3,4,...）依次分配给每个后继的级别。

如果某些值相等，则分配相同的级别,同时，下一级别数字为前面出现的个数（例如，1,1,1,4,5,5,7...）。

根据结果集中的值计算级别。

语法

RANK（Expr1,Expr2[byExp1,Exp2]）

其中：

Expr

求出数值的任意表达式。

by是定下RANK的范围如

设置RANK（"事实-销售订单行"."销量"by"时间维"."年"）

最后能够达到类似于TopN的效果，而且，对于表提示器也是生效的，当然得建立过滤器

同时，这样会有一个局限在于，Rank的序列生成方式是（1,1,1,4,5,5,7...）

这样的，所以设置过滤小于10，可能返回的值不止9个，就像上面左图一样

10.STDDEV

此函数返回一组值的样本标准差。

返回类型始终为双精度值。

STDEV_SAMP是STDDEV的同义词。

语法

STDDEV（[ALL|DISTINCT]numExprBYexpr）

其中：

numExpr是求值结果为数值的任何表达式。

如果指定ALL，则会为数据集内的所有数据计算标准差。

如果指定DISTINCT，则会在计算中忽略所有重复项。

如果未指定任何内容（默认值），则会考虑所有数据。

expr可以设置范围

如STDDEV（"基础项"."预算金额"BY"时间"."年份"）

11.STDDEV_POP

此函数使用总体方差和标准差的计算公式返回一组值的标准差。

语法

STDDEV_POP（[ALL|DISTINCT]numExprBYexpr）

其中：

numExpr是求值结果为数值的任何表达式。

如果指定ALL，则会为数据集内的所有数据计算标准差。

如果指定DISTINCT，则会在计算中忽略所有重复项。

如果未指定任何内容（默认值），则会考虑所有数据。

expr可以设置范围

如STDDEV_POP（"基础项"."预算金额"BY"时间"."年份"）

12.SUM

此函数计算通过累加符合数值表达式参数要求的所有值得出的总和。

语法

SUM（numExprBYexpr）

其中：

numExpr是求值结果为数值的任何表达式。

expr可以设置范围

SUMDISTINCT

此函数计算通过累加符合数值表达式参数要求的所有不同值得出的总和。

13.TOPN

按从1到n的顺序依次排列表达式参数中最大的n个值，1对应于最大的数值。

TOPN函数对结果集中返回的值执行运算。

请求可以只包含一个TOPN表达式。

语法

TopN（Expr,integer）

其中：

Expr

求出数值的任意表达式。

Integer

任意正整数。

表示结果集中显示的最低级别编号，1是最高的级别。

一般，我们是新建一个TOPN的指标，然后将这个指标排除或者隐藏起来

TOPN对表提示器无效，因为是先TOPN再进行提示的

2.运行聚合函数

运行聚合函数与函数聚合相似，它们都采用一组记录作为输入，但运行聚合函数不会为整组记录输出一个聚合，而会根据迄今为止出现的记录输出聚合。

14.MAVG

此函数计算结果集内最后n行数据（包括当前行）的移动平均值。

第一行的平均值等于第一行的数值表达式。

第二行的平均值通过计算前两行数据的平均值得出。

第三行的平均值通过计算前三行数据的平均值得出，依此类推，直到您到达第n行为止，第n行的平均值根据最后n行数据计算得出。

语法

MAVG（numExpr,integer）

其中：

numExpr是求值结果为数值的任何表达式。

integer是任意正整数。

它表示最后n行数据的平均值。

比如，这里是3，那么就是求上两行数据的平均值，第一行为第一行数据的平均值，第二行是前两行的平均值，第三行是前三行的平均值，后面每一行都是左边前三行的平均值

15.MSUM

此函数计算最后n行数据（包括当前行）的移动总和。

第一行的总和等于第一行的数值表达式。

第二行的总和通过计算前两行数据的总和得出。

第三行的总和通过计算前三行数据的总和得出，依此类推。

在您到达第n行以后，根据最后n行数据计算总和。

语法

MSUM（numExpr,integer）

其中：

numExpr是求值结果为数值的任何表达式。

integer是任意正整数。

它表示最后n行数据的平均值。

16.RSUM

此函数根据迄今为止出现的记录来计算运行总和。

第一行的总和等于第一行的数值表达式。

第二行的总和通过计算前两行数据的总和得出。

第三行的总和通过计算前三行数据的总和得出，依此类推。

语法

RSUM（numExpr）

其中：

numExpr是求值结果为数值的任何表达式。

在Answers中，您还可以使用以下替代语法：

RSUM（expression1[BYexpression2[,expression3[,...]]]）

其中：

expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。

BY子句导致RSUM计算从任何BY列值不同于上一行的行重新开始。

17.RCOUNT

此函数采用一组记录作为输入，并计算迄今为止出现的记录数量。

语法

RCOUNT（expr）

其中：

expr是任何数据类型的表达式。

在Answers中，您还可以使用以下替代语法：

RCOUNT（expression1[BYexpression2[,expression3[,...]]]）

其中：

expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。

BY子句导致RCOUNT计算从任何BY列值不同于上一行的行重新开始。

18.RMAX&RMIN

此函数采用一组记录作为输入，并根据迄今为止出现的记录显示最大值。

指定数据类型必须是可排序的数据类型。

语法

RMAX（expr）

其中：

expr是任何数据类型的表达式。

该数据类型必须是具有关联的排序顺序的数据类型。

在Answers中，您还可以使用以下替代语法：

RMAX（expression1[BYexpression2[,expression3[,...]]]）

其中：

expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。

BY子句导致RMAX计算从任何BY列值不同于上一行的行重新开始。

3.字符串函数

19.ASCII

1.Concat

CONCAT（strExpr1,strExpr2||strExpr3）

其中：

strExprs是求值结果为逗号分隔字符串和字符串连接运算符||（双竖线）的表达式。

CONCAT函数首先将strExpr2与strExpr3连接在一起以生成中间字符串，然后将strExpr1和中间字符串连接在一起以生成最终字符串。

示例

此示例请求返回显示的结果。

SELECTDISTINCTCONCAT（'abc','def'||'ghi'）FROMemployee

结果：

abcdefghi

2.Left

从字符串左方返回指定字符数。

语法

Left（strExpr,integer）

其中：

strExpr

求出字符串的任意表达式。

Integer

任意正整数，表示要从字符串左方返回的字符数。

3.Lower

将字符串转换成小写字母。

语法

Lower（strExpr）

其中：

strExpr

求出字符串的任意表达式。

4.Replace

使用一个或多个其它字符替换指定字符表达式中的一个或多个字符。

语法

Replace（strExpr1,strExpr2,strExpr3）

其中：

strExpr1

求出字符串的任意表达式。

这是要替换其中字符的字符串。

strExpr2

求出字符串的任意表达式。

第二个字符串指定第一个字符串中被替换的字符。

strExpr3

求出字符串的任意表达式。

第三个字符串指定要替换至第一个字符串的字符。

5.Right

从字符串左方返回指定字符数。

语法

Right（strExpr,integer）

其中：

strExpr

求出字符串的任意表达式。

Integer

任意正整数，表示要从字符串右方返回的字符数。

6.Substring

从固定字符数开始，将新字符串创建至原始字符串。

语法

Substring（strExprFROMstartPos）

其中：

strExpr

求出字符串的任意表达式。

startPos

任意正整数，表示从结果所在的字符串左边开始的字符数。

7.TrimBoth

除去字符串中的前导和尾部字符。

语法

Trim（BOTHcharFROMstrExpr）

其中：

char

任意单个字符。

如果您忽略此说明（以及要求的单个引号），空白字符则会用作缺省值。

strExpr

求出字符串的任意表达式。

8.TrimLeading

除去字符串中的前导字符。

语法

Trim（LEADINGcharFROMstrExpr）

其中：

char

任意单个字符。

如果您忽略此说明（以及要求的单个引号），空白字符则会用作缺省值。

strExpr

求出字符串的任意表达式。

9.TrimTrailing

除去字符串中的尾部字符。

语法

Trim（TRAILINGcharFROMstrExpr）

其中：

char

任意单个字符。

如果您忽略此说明（以及要求的单个引号），空白字符则会用作缺省值。

strExpr

求出字符串的任意表达式。

10.Upper

将字符串转换成大写字母。

语法

Upper（strExpr）

其中：

strExpr

求出字符串的任意表达式。

4.数学函数

1.Abs

计算数值表达式的绝对值。

语法

Abs（numExpr）

其中：

numExpr

求出数值的任意表达式。

2.Ceiling

将一个非整数数值表达式的值舍入至相邻的最大整数。

如果数值表达式求出整数，则CEILING函数返回该整数。

语法

Ceiling（numExpr）

其中：

numExpr

求出数值的任意表达式。

3.Floor

将一个非整数数值表达式的值舍入至相邻的最小整数。

如果数值表达式求出整数，则FLOOR函数返回该整数。

语法

Floor（numExpr）

其中：

numExpr

求出数值的任意表达式。

4.Mod

将第一个数值表达式除以第二个数值表达式，然后返回商的非余数部分。

语法

Mod（numExpr1,numExpr2）

其中：

numExpr

求出数值的任意表达式。

5.Rand

返回0至1之间的伪随机数。

语法

Rand（）

6.Round

将数值表达式的值四舍五入到n位精度。

语法

Round（numExpr,integer）

其中：

numExpr

求出数值的任意表达式。

integer

表示精度位数的任何正整数。

7.Truncate

舍去小数，以返回从小数点指定的位数。

语法

Truncate（numExpr,integer）

其中：

numExpr

求出数值的任意表达式。

integer

表示小数点右边要返回的字符数的任何正整数。

5.日历日期/时间函数

20.CURRENT_DATE

此函数返回当前日期。

该日期由运行OracleBIServer的系统确定。

语法

CURRENT_DATE

21.CURRENT_TIME

此函数返回当前时间。

该时间由运行OracleBIServer的系统确定。

语法

CURRENT_TIME（integer）

其中：

integer是任意整数，它表示显示零点几秒的精度的位数。

该参数是可选的；未指定该参数时，函数返回默认精度。

默认是整秒，设置了精度，也看不到零点几秒的情况，标记为复查

CURRENT_TIMESTAMP

此函数返回当前日期/时间戳。

该时间戳由运行OracleBIServer的系统确定。

语法

CURRENT_TIMESTAMP（integer）

其中：

integer是任意整数，它表示显示零点几秒的精度的位数。

该参数是可选的；未指定该参数时，函数返回默认精度。

1.Day_of_Quarter

返回一个介于1和92之间的数字，该数字对应于指定日期在季度中的某一天。

语法

Day_Of_Quarter（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

2.DayName

返回指定日期的那一天的名称。

语法

DayName（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

3.DayOfWeek

返回一个介于1和7之间的数字，该数字对应于指定日期是星期几（从星期日到星期六）。

例如，数字1对应于星期日，而数字7对应于星期六。

语法

DayOfWeek（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

4.DayOfYear

返回一个介于1和366之间的数字，该数字对应于指定日期在一年中的某一天。

语法

DayOfYear（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

5.Week_Of_Quarter

返回一个介于1和13之间的数字，该数字对应于指定日期在季度中的某一周。

语法

Week_Of_Quarter（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

6.Week_Of_Year

返回一个介于1和53之间的数字，该数字对应于指定日期在一年中处于的某一周。

语法

Week_Of_Year（dateExpr）

其中：

dateExpr

求出日期值的任意表达式。

6.表达式

1.Case（Switch）

Case语句的这种形式也称为CASE（Lookup）形式。

先检验expression1的值，然后检验WHEN表达式。

如果expression1与任何WHEN表达式匹配，则会分配对应THEN表达式中的值。

如果与任何一个WHEN表达式都不匹配，则会分配在ELSE表达式中指定的缺省值。

如果未指定ELSE表达式，系统将自动添加ELSENULL。

如果expression1与多个WHEN子句中的表达式匹配，则只分配第一次匹配之后的表达式。

语法

CASEexpression1

WHENexpression2THENexpression2

{WHENexpression...THENexpression...}

ELSEexpression

END

其中：

CASE

开始CASE语句。

后面必须跟随表达式和一个或多个WHEN和THEN语句、（可选）ELSE语句以及END关键字。

WHEN

指定要满足的条件。

THEN

指定在满足对应的WHEN表达式的情况下要分配的值。

ELSE

指定在不满足任何一个WHEN条件的情况下要分配的值。

如果忽略，则会假定ELSENULL。

END

结束CASE语句。

示例

CASEScore-par

WHEN-5THEN'BirdieonPar6'

WHEN-4THEN'MustbeTiger'

WHEN-3THEN'Threeunderpar'

WHEN-2THEN'Twounderpar'

WHEN-1THEN'Birdie'

WHEN0THEN'Par'

WHEN1THEN'Bogey'

WHEN2THEN'DoubleBogey'

ELSE'TripleBogeyorWorse'

END

2.Case（If）

Case语句的此形式评估每个WHEN条件，如果满足，则会分配对应THEN表达式中的值。

如果不满足任何一个WHEN表达式，则会分配在ELSE表达式中指定的缺省值。

如果未指定ELSE表达式，系统将自动添加ELSENULL。

语法

CASE

WHENrequest_condition1THENexpression1

{WHENrequest_condition2THENexpression2}

{WHENrequest_condition...THENexpression...}

ELSEexpression

END

其中：

CASE

开始CASE语句。

后面必须跟随一个或多个WHEN和THEN语句、（可选）ELSE语句以及END关键字。

WHEN

指定要满足的条件。

THEN

表示在满足对应WHEN表达式的情况下要分配的值。

ELSE

指定不满足任何一个WHEN条件的情况下要分配的值。

如果忽略，则会假定ELSENULL。

END

结束CASE语句。

示例

CASE

WHENscore-par<0THEN'UnderPar'

WHENscore-par=0THEN'Par'

WHENscore-par=1THEN'Bogie'

WHENscore-par=2THEN'DoubleBogey'

ELSE'TripleBogeyorWorse'

END

7.时间序列计算

累计、同比、环比是BI项目中常用的分析方法。

如果采用关系型数据库，直接通过SQL语句进行这样的计算是比较麻烦的。

OracleBIEE提供的解决方法是时间轴函数。

时间轴函数是位于逻辑模型层内的函数，使用时间轴函数能够定义出“去年同期值”或“本年累计值”之类的与时间相关的计算字段。

OracleBIEE现在支持两个时间轴函数：

Ago：

从当前时间起回溯用户指定的n个时间段，返回当时的度量值。

ToDate：

从用户指定时间段的起点开始，到当前时间为止，计算度量值的累计值。

使用时间轴函数的前提条件是在逻辑模型内定义了时间维。

上面两个函数说明中的“时间段”实际上指的是时间维的层级（Level）。

例如使用:

Ago函数定义“去年同期值”，指定的时间段为“年”这一层级，指定的回溯周期为1，表示得到去年的值。

ToDate函数定义“本年累计值”，指定的时间段也是“年”这一层级，表示从年的起点（年初）开始到当前时间为止，求度量值的累计值。

1.Ago

一个时间序列聚合函数,用于计算从当前时间追溯到以前的指定时段之间的聚合值。

例如,Ago可以生成当前季度每个月的销售额以及相应的季前销售额。

语法

AG