Anchky's Tech Blog
专注.NET 专注BI
尽最大的努力，以求更好！
MDX函数使用介绍（三）：聚合函数

聚合函数

函数

描述

AddCalculatedMembers

向集合中添加计算成员。

 

WITH MEMBER [Time].[1997].[1到6月的] AS 'SUM([Time].[1]:[Time].[6])'

 MEMBER [Time].[1997].[1到9月的] AS 'SUM([Time].[1]:[Time].[9])' SELECT

AddCalculatedMembers([Time].[1997].Children) ON COLUMNS, [Product].Children ON rows from sales

AllMembers

返回包含指定维度或级别的所有成员的集合，包括计算成员。

 

SELECT

 {[Product].Children} ON COLUMNS, [Time].allmembers ON rows from sales

Ancestors

返回指定距离上某个成员的所有祖先。

1

with member [measures].[1] as 'settostr(Ancestors(time.currentmember,[Time].[Year]) ) '

 member [measures].[2] as 'settostr(Ancestors(time.currentmember,1) )'

 member [measures].[3] as 'settostr(Ancestors(time.currentmember,2) )'

 SELECT

 { [Measures].[Unit Sales],[measures].[1],[measures].[2],[measures].[3] } ON COLUMNS, [Time].allmembers ON rows from sales

2

with member [measures].[1] as 'sum(Ancestors(time.currentmember,[Time].[Year]),[Measures].[Unit Sales]) '

 member [measures].[2] as 'sum(Ancestors(time.currentmember,1),[Measures].[Unit Sales])'

 member [measures].[3] as 'sum(Ancestors(time.currentmember,2),[Measures].[Unit Sales])'

 SELECT

 { [Measures].[Unit Sales],[measures].[1],[measures].[2],[measures].[3] } ON COLUMNS, [Time].allmembers ON rows from sales

Ascendants

返回成员祖先的集合，包括成员本身。

1

with member [measures].[1] as 'settostr(Ascendants(time.currentmember ) ) ' 

 SELECT

 { [Measures].[Unit Sales],[measures].[1] } ON COLUMNS, [Time].allmembers ON rows from sales

2

with member [measures].[1] as 'sum(Ascendants(time.currentmember ),[Measures].[Unit Sales]) ' 

 SELECT

 { [Measures].[Unit Sales],[measures].[1] } ON COLUMNS, [Time].allmembers ON rows from sales

Axis

返回与主轴关联的集合。

 

with member [measures].[COLUMNS] as 'settostr(Axis(1)) ' 

member [measures].[rows] as 'settostr(Axis(1)) '  

 SELECT

 {[measures].[rows],[measures].[COLUMNS] } ON COLUMNS, [Time].allmembers ON rows from sales

BottomCount

从集合底端开始返回指定数目的项，可以选择首先对集合排序。

 

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

BottomCount(Descendants([Store],[Store].[Store Name]),20,[Measures].[Store Sales]  ) ON rows from sales

BottomPercent

对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的百分比。

 

select {[Unit Sales]} on COLUMNS,

 Non Empty BottomPercent([Product].[Brand Name].Members, 10, [Unit Sales]) on ROWS

from Sales

BottomSum

对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的值。

 

select {[Unit Sales]} on COLUMNS,

 Non Empty BottomSum([Product].[Brand Name].Members, 600, [Unit Sales]) on ROWS

from Sales

Children

返回成员的子代。

 

with member [measures].[CurrentChildren] as 'settostr(time.currentmember.Children)'

select {[Unit Sales],[measures].[CurrentChildren]} on COLUMNS,

{[time].allmembers} on ROWS

from Sales

Crossjoin

返回两个集合的矢量积。

 

替代语法

«Set1» * «Set2»

select {time.allmembers*{[Unit Sales],[store sales]} } on COLUMNS,

{[Store].[Store Name].members} on ROWS

from Sales

或者

select  Crossjoin({time.allmembers},{[Unit Sales],[store sales]})  on COLUMNS,

{[Store].[Store Name].members} on ROWS

from Sales

Descendants

返回某一成员在指定级别上的或者距某一成员指定距离的后代集合，可以选择包含或不包含其它级别上的后代。

Descendants([Time].[1997])

SELECT

{[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997])} ON rows from sales

Descendants

([Time].[1997],[time].[month])

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].month)} ON rows from sales

Descendants

([Time].[1997],

[time].[ month],

SELF)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[month],SELF)} ON rows from sales

Descendants

([Time].[1997],

[time].[month],

before)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[month],before)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

AFTER)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],AFTER)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

BEFORE_AND_AFTER)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],BEFORE_AND_AFTER)} ON rows from sales

Descendants

([Time].[1997],

[time].[quarter],

SELF_BEFORE_AFTER)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],SELF_BEFORE_AFTER)} ON rows from sales

 

Descendants

([Time].[1997],

[time].[quarter],

LEAVES)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],[time].[quarter],LEAVES)} ON rows from sales

Descendants

([Time].[1997],1)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],1)} ON rows from sales

Descendants

([Time].[1997],2,

SELF_BEFORE_AFTER)

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{Descendants([Time].[1997],2,SELF_BEFORE_AFTER)} ON rows from sales

Distinct

从集合中删除重复的元组。

 

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ distinct({

([Time].[1997],[Store].[All Stores].[USA]),

([Time].[1997],[Store].[All Stores].[Mexico]),

([Time].[1997],[Store].[All Stores].[USA])

})} ON rows from sales

对比

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{{

([Time].[1997],[Store].[All Stores].[USA]),

([Time].[1997],[Store].[All Stores].[Mexico]),

([Time].[1997],[Store].[All Stores].[USA])

} } ON rows from sales

DrilldownLevel

将集合中的成员从指定级别深化一个级别。

另一种方法是，在集合中的指定维度上深化。

 

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],

[Time].[1997].[Q3],[Time].[1998]}) }   ON rows from sales

time.month

和

time.quarter

效果对比

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],

[Time].[1998]},

time.quarter

) }   ON rows from sales

DrilldownLevelBottom

将集合底端的 n 个成员从指定级别深化一个级别。

 

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],

[Time].[1997].[Q3],[Time].[1998]}

,2,,[Store Sales]

 ) }   ON rows from sales

有意思，分析一下结果

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}

,5,time.year,[Store Sales]

 ) }   ON rows from sales

DrilldownLevelTop

将集合顶端的 n 个成员从指定级别深化一个级别。

 

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownLevelTop({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}

,2, ,[Store Sales]

 ) }   ON rows from sales

DrilldownMember

在第一个集合与第二个集合的交集中深化。

1

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] }

 ) }   ON rows from sales

注意第2个set的写法

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],

[Store].[All Stores].[Canada],[Store].[All Stores].[Canada].[BC],

[Store].[All Stores].[Mexico] } ,RECURSIVE

 ) }   ON rows from sales

 

2

SELECT

 {[Measures].[Store Sales]  } ON COLUMNS,

{ DrilldownMember({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]}

,{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] },RECURSIVE

 ) }   ON rows from sales

备注

帮助中有个错误的地方：

……示例

DrilldownMember({USA, Canada, Mexico}, {USA, Washington, Mexico},RECURSIVE)

返回集合：

{USA, <all states in USA before Washington>,

WA, <all cities in Washington>, <all cities（应该是states） in USA after Washington>,

Canada, Mexico, <all states in Mexico>}……..

3

SELECT

 {time.[1997]  } ON COLUMNS,

{ DrilldownMember(

{

([Store].[All Stores].[USA],[Measures].[Store Sales]),

([Store].[All Stores].[Canada],[Measures].[Store Sales]),

([Store].[All Stores].[Mexico],[Measures].[Store Sales])

}

,{[Store].[All Stores].[USA],[Store].[All Stores].[USA].[wa]}

 ) }   ON rows from sales

4

加RECURSIVE

SELECT

 {time.[1997]  } ON COLUMNS,

{ DrilldownMember(

{

([Store].[All Stores].[USA],[Measures].[Store Sales]),

([Store].[All Stores].[Canada],[Measures].[Store Sales]),

([Store].[All Stores].[Mexico],[Measures].[Store Sales])

}

,{[Store].[All Stores].[USA],[Store].[All Stores].[USA].[wa]}

,RECURSIVE) }   ON rows from sales

DrilldownMemberBottom

类似于 DrilldownMember，除了只包括底端的 n 个子代。

1，RECURSIVE 含义同上，且递归部分的成员同样受count控制

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { DrilldownMemberBottom({[Store].[All Stores].[USA],[Store].[All Stores].[Canada],[Store].[All Stores].[Mexico]} ,

{[Store].[All Stores].[USA], [Store].[All Stores].[USA].[or],[Store].[All Stores].[Mexico] },

 2,[Measures].[Store Sales],RECURSIVE

) }

  ON rows from sales  

 

对元组的深化同DrilldownMember

DrilldownMemberTop

类似于 DrilldownMember，除了只包括顶端的 n 个子代。

 

同DrilldownMemberBottom

DrillupLevel

从集合的某一指定级别之下的成员浅化。

1

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]},[store country])

}   ON rows from sales  

2

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]})

}

  ON rows from sales

DrillupMember

在第一个集合与第二个集合的交集中浅化。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { DrillupMember({Canada, Mexico, USA, Wa, Seattle},{Wa})

}   ON rows from sales  

Except

查找两个集合之间不同的项，可以选择保留重复项。

1

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca})

}

  ON rows from sales  

2，呵呵，普科显示的也有点问题

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca},all)

}

  ON rows from sales  

Extract

从析取的维度元素中返回元组集合。即 Crossjoin 的反运算。

Extract 函数执行的操作与 Crossjoin 函数相反

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { Extract({([1997], Wa ), ([1997], Ca ), ([1998], Ca )}, Time)

}   ON rows from sales  

Filter

返回根据搜索条件对集合进行筛选所得到的集合。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { filter(time.allmembers,[Measures].[Store Sales]>50000)

}   ON rows from sales  

Generate

将集合应用到另一集合的每个成员，然后用 union 运算合并所得的集合。

1

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { Generate({ USA, Canada }, Descendants(store.CurrentMember, [store state]))

}   ON rows from sales  

2，ca，wa是USA的，加all则简单复制

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 { Generate({USA, Canada}, {ca, wa} ,all)

}   ON rows from sales  

如果通过 CurrentMember，«Set1» 与 «set_expression» 无关，那么 Generate 生成 «set_expression» 所指的集合的简单复制，它包含的复制与 «Set1» 中的元组一样多。如果指定了可选的 ALL 标志，结果中将保留所有重复项。如果未指定 ALL，重复项将被删除。

3,字符串的

with member [Measures].[合字符串] as 'Generate({Time.allmembers}, Time.CurrentMember.name," and ")'

SELECT  { [Measures].[合字符串] } ON COLUMNS,

 {[Store].[All Stores]

}   ON rows from sales  

3， 应用扩展

with member [Measures].[合字符串] as 'Generate({Time.[1997].children}, cstr((Time.CurrentMember, [Measures].[Unit Sales],store.[all stores]))," and ")'

SELECT  { [Measures].[合字符串] } ON COLUMNS,

 {[Store].[All Stores]

}   ON rows from sales

Head

返回集合中指定数目的前若干个元素。

 

SELECT  { Head(Descendants([Time].[1997],2,SELF_BEFORE_AFTER), 3) } ON COLUMNS,

 {[measures].[store sales]

}   ON rows from sales

Hierarchize

在层次结构中对集合的成员排序。

 

SELECT  Hierarchize

( {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] },post) ON COLUMNS,

 {[measures].[store sales]

}   ON rows from sales

 

和下面的语句比较一下就知道了

SELECT   {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] }  ON COLUMNS,

 {[measures].[store sales]

}   ON rows from sales

Intersect

返回两个输入集合的交集，可以选择保留重复项。

 

SELECT 

 Intersect({[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5] },

{[Time].[1997].[Q2].[5],[Time].[1997].[Q2] },all)  ON COLUMNS,

 {[measures].[store sales]

}   ON rows from sales

注意带ALL和不带ALL 的区别

LastPeriods

返回指定的成员之前（包含该成员）的成员集合。

 

with member measures.test as 'Generate({LastPeriods(3,time.currentmember)}, Time.CurrentMember.name," and ") '

SELECT  {measures.test }  ON COLUMNS,

 {[Time].members}   ON rows from sales

Members

返回维度、层次结构或级别中所有成员的集合。

 

SELECT 

  {measures.[store sales] }  ON COLUMNS,

 {[Time].members}   ON rows from sales

Mtd

PeriodsToDate 函数的快捷函数，将级别指定为 Month。

 

到目前没有发现其意义

NameToSet

基于包含成员名称的字符串表达式，返回一个包含单个成员的集合。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

 {NameToSet("[Time].[1997]")}   ON rows from sales

NonEmptyCrossjoin

返回两个或多个集合的矢量积，除空成员之外。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

 {NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]},2)

 }   ON rows from sales

请详细看联机帮助，这个函数在使用的时候慎用，因为NonEmptyCrossjoin 函数以一个集合的形式返回两个或多个集合的矢量积，不包括空元组或无基础事实数据表提供的数据的元组，因此所有计算成员均被自动排除。

Order

排列集合的成员，可以选择保留或打破层次结构。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

 Order([Store].[Store State].allmembers, measures.[store sales], BASC) ON rows from sales

PeriodsToDate

返回指定级别上的一个时期（成员）集合，从第一个时期开始到指定的成员为止。

1

SELECT    {measures.[store sales] }  ON COLUMNS,

PeriodsToDate([Time].[Quarter],[Time].[1997].[Q3].[8]) ON rows from sales

2

SELECT    {measures.[store sales] }  ON COLUMNS,

PeriodsToDate([Store].[Store Country],[Store].[All Stores].[USA].[OR]) ON rows from sales

Qtd

PeriodsToDate 函数的快捷函数，将级别指定为 Quarter。

 

同上

Siblings

返回成员的兄弟，包括成员本身。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

{[Time].[1997].[Q2].Siblings} ON rows from sales

StripCalculatedMembers

从集合中删除计算成员。

此函数从某个集合中删除计算成员，该集合包含使用 AddCalculatedMembers 添加的计算成员。

WITH MEMBER [Time].[1997].[1到6月的] AS 'SUM([Time].[1]:[Time].[6])'

 MEMBER [Time].[1997].[1到9月的] AS 'SUM([Time].[1]:[Time].[9])' SELECT

StripCalculatedMembers(

AddCalculatedMembers([Time].[1997].Children)

) ON COLUMNS, [Product].Children ON rows from sales

 

StrToSet

用字符串表达式构造一个集合。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

{StrToSet("Time.Members")} ON rows from sales

Subset

从集合中返回元素的子集。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

{Subset(Time.allMembers,0,7)} ON rows from sales

Tail

从集合尾部返回子集。

 

SELECT    {measures.[store sales] }  ON COLUMNS,

{tail(Subset(Time.allMembers,0,7),4)} ON rows from sales

ToggleDrillState

切换对成员的钻取状态。此函数是 DrillupMember 和 DrilldownMember 的组合。

*

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 ToggleDrillState({Product.Members},{Product.Bagels, Product.Muffins}, RECURSIVE)

   ON rows from sales

TopCount

从集合顶端开始返回指定数目的项，可以选择首先对集合排序。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales])   ON rows from sales

TopPercent

对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的百分比。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 TopPercent(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90, [store sales])   ON rows from sales

TopSum

对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的值。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 TopSum(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90000, [store sales])   ON rows from sales

UNION

返回两个集合的并集，可以选择保留重复项。

 

SELECT  {[Measures].[Store Sales]  } ON COLUMNS,

 Union(USA.Children, CANADA.Children, ALL)

   ON rows from sales

VisualTotals

动态计算集合中指定的子成员的合计，并在结果集中对合计的标签使用某种模式来显示。

 

select

  {[Measures].[Unit Sales]} on columns,

  { [Time].[1997],

   [Time].[1997].[Q2],

    [Time].[1997].[Q4]

  } on rows

from Sales

和下面的对比着理解

select

{[Measures].[Unit Sales]} on columns,

{VisualTotals({ [Time].[1997],

   [Time].[1997].[Q2],

    [Time].[1997].[Q4]

}, "**Subtotal - *")

} on rows

from Sales

Wtd

PeriodsToDate 函数的快捷函数，将级别指定为 Week。

 

略

Ytd

PeriodsToDate 函数的快捷函数，将级别指定为 Year。

 

略

 

posted on 2006-11-28 08:31  anchky  阅读(3755)  评论(5)  编辑  收藏  举报

Copyright © 2024 anchky
Powered by .NET 8.0 on Kubernetes Powered By博客园