MDX函数使用介绍（三）：聚合函数 收藏
聚合函数
函数 描述
AddCalculatedMembers 向集合中添加计算成员。
WITHMEMBER [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) ONCOLUMNS, [Product].ChildrenONrowsfrom sales
AllMembers 返回包含指定维度或级别的所有成员的集合，包括计算成员。
SELECT
{[Product].Children} ONCOLUMNS, [Time].allmembersONrowsfrom sales
Ancestors 返回指定距离上某个成员的所有祖先。
1 withmember [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] } ONCOLUMNS, [Time].allmembersONrowsfrom sales
2 withmember [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] } ONCOLUMNS, [Time].allmembersONrowsfrom sales
Ascendants 返回成员祖先的集合，包括成员本身。
1 withmember [measures].[1] as 'settostr(Ascendants(time.currentmember ) ) '
SELECT
{ [Measures].[Unit Sales],[measures].[1] } ONCOLUMNS, [Time].allmembersONrowsfrom sales
2 withmember [measures].[1] as 'sum(Ascendants(time.currentmember ),[Measures].[Unit Sales]) '
SELECT
{ [Measures].[Unit Sales],[measures].[1] } ONCOLUMNS, [Time].allmembersONrowsfrom sales
Axis 返回与主轴关联的集合。
withmember [measures].[COLUMNS] as 'settostr(Axis(1)) '
member [measures].[rows] as 'settostr(Axis(1)) '
SELECT
{[measures].[rows],[measures].[COLUMNS] } ONCOLUMNS, [Time].allmembersONrowsfrom sales
BottomCount 从集合底端开始返回指定数目的项，可以选择首先对集合排序。
SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
BottomCount(Descendants([Store],[Store].[Store Name]),20,[Measures].[Store Sales] ) ONrowsfrom sales
BottomPercent 对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的百分比。
select {[Unit Sales]} onCOLUMNS,
NonEmptyBottomPercent([Product].[Brand Name].Members, 10, [Unit Sales]) onROWS
from Sales
BottomSum 对集合排序，并返回底端的 n 个元素，这些元素的累积合计至少为指定的值。
select {[Unit Sales]} onCOLUMNS,
NonEmptyBottomSum([Product].[Brand Name].Members, 600, [Unit Sales]) onROWS
from Sales
Children 返回成员的子代。
withmember [measures].[CurrentChildren] as 'settostr(time.currentmember.Children)'
select {[Unit Sales],[measures].[CurrentChildren]} onCOLUMNS,
{[time].allmembers} onROWS
from Sales
Crossjoin 返回两个集合的矢量积。
替代语法
«Set1» * «Set2»
select {time.allmembers*{[Unit Sales],[store sales]} } onCOLUMNS,
{[Store].[Store Name].members} onROWS
from Sales
或者
select Crossjoin({time.allmembers},{[Unit Sales],[store sales]}) onCOLUMNS,
{[Store].[Store Name].members} onROWS
from Sales
Descendants 返回某一成员在指定级别上的或者距某一成员指定距离的后代集合，可以选择包含或不包含其它级别上的后代。
Descendants([Time].[1997]) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997])} ONrowsfrom sales
Descendants
([Time].[1997],[time].[month]) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].month)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[ month],
SELF) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[month],SELF)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[month],
before) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[month],before)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[quarter],
AFTER) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[quarter],AFTER)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[quarter],
BEFORE_AND_AFTER) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[quarter],BEFORE_AND_AFTER)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[quarter],
SELF_BEFORE_AFTER) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[quarter],SELF_BEFORE_AFTER)} ONrowsfrom sales
Descendants
([Time].[1997],
[time].[quarter],
LEAVES) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],[time].[quarter],LEAVES)} ONrowsfrom sales
Descendants
([Time].[1997],1) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],1)} ONrowsfrom sales
Descendants
([Time].[1997],2,
SELF_BEFORE_AFTER) SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{Descendants([Time].[1997],2,SELF_BEFORE_AFTER)} ONrowsfrom sales
Distinct 从集合中删除重复的元组。
SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ distinct({
([Time].[1997],[Store].[All Stores].[USA]),
([Time].[1997],[Store].[All Stores].[Mexico]),
([Time].[1997],[Store].[All Stores].[USA])
})} ONrowsfrom sales
对比 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{{
([Time].[1997],[Store].[All Stores].[USA]),
([Time].[1997],[Store].[All Stores].[Mexico]),
([Time].[1997],[Store].[All Stores].[USA])
} } ONrowsfrom sales
DrilldownLevel 将集合中的成员从指定级别深化一个级别。
另一种方法是，在集合中的指定维度上深化。
SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],
[Time].[1997].[Q3],[Time].[1998]}) } ONrowsfrom sales
time.month
和
time.quarter
效果对比 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ DrilldownLevel({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],
[Time].[1998]},
time.quarter
) } ONrowsfrom sales
DrilldownLevelBottom 将集合底端的 n 个成员从指定级别深化一个级别。
SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],
[Time].[1997].[Q3],[Time].[1998]}
,2,,[Store Sales]
) } ONrowsfrom sales
有意思，分析一下结果 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ DrilldownLevelBottom({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}
,5,time.year,[Store Sales]
) } ONrowsfrom sales
DrilldownLevelTop 将集合顶端的 n 个成员从指定级别深化一个级别。
SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ DrilldownLevelTop({[Time].[1997],[Time].[1997].[Q1],[Time].[1997].[Q3],[Time].[1998]}
,2, ,[Store Sales]
) } ONrowsfrom sales
DrilldownMember 在第一个集合与第二个集合的交集中深化。
1 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ 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] }
) } ONrowsfrom sales
注意第2个set的写法 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ 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
) } ONrowsfrom sales
2 SELECT
{[Measures].[Store Sales] } ONCOLUMNS,
{ 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
) } ONrowsfrom 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] } ONCOLUMNS,
{ 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]}
) } ONrowsfrom sales
4
加RECURSIVE SELECT
{time.[1997] } ONCOLUMNS,
{ 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) } ONrowsfrom sales
DrilldownMemberBottom 类似于 DrilldownMember，除了只包括底端的 n 个子代。
1，RECURSIVE含义同上，且递归部分的成员同样受count控制 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ 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
) }
ONrowsfrom sales
对元组的深化同DrilldownMember
DrilldownMemberTop 类似于 DrilldownMember，除了只包括顶端的 n 个子代。
同DrilldownMemberBottom
DrillupLevel 从集合的某一指定级别之下的成员浅化。
1 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]},[store country])
} ONrowsfrom sales
2 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ DrillUpLevel({USA, Ca, [Los Angeles], Wa , Seattle, Canada, [BC]})
}
ONrowsfrom sales
DrillupMember 在第一个集合与第二个集合的交集中浅化。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ DrillupMember({Canada, Mexico, USA, Wa, Seattle},{Wa})
} ONrowsfrom sales
Except 查找两个集合之间不同的项，可以选择保留重复项。
1 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca})
}
ONrowsfrom sales
2，呵呵，普科显示的也有点问题 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ Except({Canada, [BC], Mexico, [BC], USA, Wa}, {Canada, Mexico, Ca},all)
}
ONrowsfrom sales
Extract 从析取的维度元素中返回元组集合。即 Crossjoin 的反运算。
Extract 函数执行的操作与 Crossjoin 函数相反 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ Extract({([1997], Wa ), ([1997], Ca ), ([1998], Ca )}, Time)
} ONrowsfrom sales
Filter 返回根据搜索条件对集合进行筛选所得到的集合。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ filter(time.allmembers,[Measures].[Store Sales]>50000)
} ONrowsfrom sales
Generate 将集合应用到另一集合的每个成员，然后用 union 运算合并所得的集合。
1 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ Generate({ USA, Canada }, Descendants(store.CurrentMember, [store state]))
} ONrowsfrom sales
2，ca，wa是USA的，加all则简单复制 SELECT {[Measures].[Store Sales] } ONCOLUMNS,
{ Generate({USA, Canada}, {ca, wa} ,all)
} ONrowsfrom sales
如果通过 CurrentMember，«Set1» 与 «set_expression» 无关，那么 Generate 生成 «set_expression» 所指的集合的简单复制，它包含的复制与 «Set1» 中的元组一样多。如果指定了可选的 ALL 标志，结果中将保留所有重复项。如果未指定 ALL，重复项将被删除。
3,字符串的 withmember [Measures].[合字符串] as 'Generate({Time.allmembers}, Time.CurrentMember.name," and ")'
SELECT { [Measures].[合字符串] } ONCOLUMNS,
{[Store].[All Stores]
} ONrowsfrom sales
3， 应用扩展 withmember [Measures].[合字符串] as 'Generate({Time.[1997].children}, cstr((Time.CurrentMember, [Measures].[Unit Sales],store.[all stores]))," and ")'
SELECT { [Measures].[合字符串] } ONCOLUMNS,
{[Store].[All Stores]
} ONrowsfrom sales
Head 返回集合中指定数目的前若干个元素。
SELECT { Head(Descendants([Time].[1997],2,SELF_BEFORE_AFTER), 3) } ONCOLUMNS,
{[measures].[store sales]
} ONrowsfrom sales
Hierarchize 在层次结构中对集合的成员排序。
SELECT Hierarchize
( {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] },post) ONCOLUMNS,
{[measures].[store sales]
} ONrowsfrom sales
和下面的语句比较一下就知道了
SELECT {[Time].[1997].[Q1],[Time].[1997].[Q2].[5],[Time].[1997].[Q1].[2],[Time].[1997].[Q2].[5],[Time].[1997].[Q2] } ONCOLUMNS,
{[measures].[store sales]
} ONrowsfrom 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) ONCOLUMNS,
{[measures].[store sales]
} ONrowsfrom sales
注意带ALL和不带ALL 的区别
LastPeriods 返回指定的成员之前（包含该成员）的成员集合。
withmember measures.test as 'Generate({LastPeriods(3,time.currentmember)}, Time.CurrentMember.name," and ") '
SELECT {measures.test } ONCOLUMNS,
{[Time].members} ONrowsfrom sales
Members 返回维度、层次结构或级别中所有成员的集合。
SELECT
{measures.[store sales] } ONCOLUMNS,
{[Time].members} ONrowsfrom sales
Mtd PeriodsToDate函数的快捷函数，将级别指定为 Month。
到目前没有发现其意义
NameToSet 基于包含成员名称的字符串表达式，返回一个包含单个成员的集合。
SELECT {measures.[store sales] } ONCOLUMNS,
{NameToSet("[Time].[1997]")} ONrowsfrom sales
NonEmptyCrossjoin 返回两个或多个集合的矢量积，除空成员之外。
SELECT {measures.[store sales] } ONCOLUMNS,
{NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]},2)
} ONrowsfrom sales
请详细看联机帮助，这个函数在使用的时候慎用，因为NonEmptyCrossjoin 函数以一个集合的形式返回两个或多个集合的矢量积，不包括空元组或无基础事实数据表提供的数据的元组，因此所有计算成员均被自动排除。
Order 排列集合的成员，可以选择保留或打破层次结构。
SELECT {measures.[store sales] } ONCOLUMNS,
Order([Store].[Store State].allmembers, measures.[store sales], BASC) ONrowsfrom sales
PeriodsToDate 返回指定级别上的一个时期（成员）集合，从第一个时期开始到指定的成员为止。
1 SELECT {measures.[store sales] } ONCOLUMNS,
PeriodsToDate([Time].[Quarter],[Time].[1997].[Q3].[8]) ONrowsfrom sales
2 SELECT {measures.[store sales] } ONCOLUMNS,
PeriodsToDate([Store].[Store Country],[Store].[All Stores].[USA].[OR]) ONrowsfrom sales
Qtd PeriodsToDate函数的快捷函数，将级别指定为 Quarter。
同上
Siblings 返回成员的兄弟，包括成员本身。
SELECT {measures.[store sales] } ONCOLUMNS,
{[Time].[1997].[Q2].Siblings} ONrowsfrom sales
StripCalculatedMembers 从集合中删除计算成员。
此函数从某个集合中删除计算成员，该集合包含使用 AddCalculatedMembers 添加的计算成员。 WITHMEMBER [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)
) ONCOLUMNS, [Product].ChildrenONrowsfrom sales
StrToSet 用字符串表达式构造一个集合。
SELECT {measures.[store sales] } ONCOLUMNS,
{StrToSet("Time.Members")} ONrowsfrom sales
Subset 从集合中返回元素的子集。
SELECT {measures.[store sales] } ONCOLUMNS,
{Subset(Time.allMembers,0,7)} ONrowsfrom sales
Tail 从集合尾部返回子集。
SELECT {measures.[store sales] } ONCOLUMNS,
{tail(Subset(Time.allMembers,0,7),4)} ONrowsfrom sales
ToggleDrillState 切换对成员的钻取状态。此函数是 DrillupMember 和 DrilldownMember 的组合。
* SELECT {[Measures].[Store Sales] } ONCOLUMNS,
ToggleDrillState({Product.Members},{Product.Bagels, Product.Muffins}, RECURSIVE)
ONrowsfrom sales
TopCount 从集合顶端开始返回指定数目的项，可以选择首先对集合排序。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales]) ONrowsfrom sales
TopPercent 对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的百分比。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
TopPercent(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90, [store sales]) ONrowsfrom sales
TopSum 对集合排序，并返回顶端的 n 个元素，这些元素的累积合计至少为指定的值。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
TopSum(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90000, [store sales]) ONrowsfrom sales
UNION 返回两个集合的并集，可以选择保留重复项。
SELECT {[Measures].[Store Sales] } ONCOLUMNS,
Union(USA.Children, CANADA.Children, ALL)
ONrowsfrom sales
VisualTotals 动态计算集合中指定的子成员的合计，并在结果集中对合计的标签使用某种模式来显示。
select
{[Measures].[Unit Sales]} oncolumns,
{ [Time].[1997],
[Time].[1997].[Q2],
[Time].[1997].[Q4]
} onrows
from Sales
和下面的对比着理解
select
{[Measures].[Unit Sales]} oncolumns,
{VisualTotals({ [Time].[1997],
[Time].[1997].[Q2],
[Time].[1997].[Q4]
}, "**Subtotal - *")
} onrows
from Sales
Wtd PeriodsToDate函数的快捷函数，将级别指定为 Week。
略
Ytd PeriodsToDate函数的快捷函数，将级别指定为 Year。
略
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/robot1980/archive/2007/12/06/1920086.aspx