《Microsoft SQL Server 2008 MDX Step by Step》学习笔记八：执行聚合（下）

SQL Server 2008中SQL应用系列及BI笔记系列--目录索引

导读:本文介绍执行聚合（Aggregation）的进阶内容，包括：

■1、使用Max和Min函数

■2、在集中对元组计数

■3、DistinctCount函数

■4、使用Generate浏览集合

本文所用数据库和所有源码，请到微软官网下载

 

1、求最大最小值

与SQL类似，MDX也提供了名称相同的两个函数：Min（http://msdn.microsoft.com/zh-cn/library/ms145600.aspx）

Max（http://msdn.microsoft.com/zh-cn/library/ms145601.aspx）

准备一个原始例子

例7-12

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;

 

 

下面我们增加Max函数

例7-13

WITH
MEMBER [Measures].[Max Sales By Subcategory] AS
Max(
{[Product].[Subcategory].[Subcategory].Members},
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Max Sales By Subcategory])
} ON COLUMNS,
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;

结果如下：

 

进一步，我们可以再动态计算均值与最大值的比

例7-14

WITH
MEMBER [Measures].[Percent of Max] AS
([Measures].[Reseller Sales Amount]) /
([Measures].[Max Sales By Subcategory])
,FORMAT_STRING="Percent"
MEMBER [Measures].[Max Sales By Subcategory] AS
Max(
{[Product].[Subcategory].[Subcategory].Members},
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Percent of Max])
} ON COLUMNS,
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;

 

 

2、在集合中对元组计数（Counting Tuples in Sets）

与SQL类似，MDX也使用Count（http://msdn.microsoft.com/zh-cn/library/ms146026.aspx）来计数，不过Count函数的参数可以是维度、集、层次结构级别和元组（Tuple）。

例7-15

SELECT
{
([Measures].[Internet Sales Amount]),
([Measures].[Reseller Sales Amount])
} ON COLUMNS,
{[Product].[Product].[Product].Members} ON ROWS
FROM [Step-by-Step]
;

注意结果中有很多null,使用Count加Exists

例7-5

WITH
MEMBER [Measures].[Products By Category] AS
Count(
EXISTING {[Product].[Product].[Product].Members}
)
SELECT
{([Measures].[Products By Category])} ON COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM [Step-by-Step]
;

结果：

Count也可以进行条件过滤

例7-17

WITH
MEMBER [Measures].[Products] AS
Count(
EXISTING {[Product].[Product].[Product].Members}
)
MEMBER [Measures].[Reseller Products] AS
Count(
Filter(
EXISTING {[Product].[Product].[Product].Members},
([Measures].[Reseller Sales Amount]) >=
([Measures].[Internet Sales Amount])
)
)
SELECT
{
([Measures].[Products]),
([Measures].[Reseller Products])
} ON COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM [Step-by-Step]
;

加上ExcludeEmpty关键字

例7-18

WITH
MEMBER [Measures].[Products] AS
Count(
EXISTING {[Product].[Product].[Product].Members}
)
MEMBER [Measures].[Reseller Products] AS
Count(
Filter(
EXISTING {[Product].[Product].[Product].Members},
([Measures].[Reseller Sales Amount]) >=
([Measures].[Internet Sales Amount])
),
EXCLUDEEMPTY
)
SELECT
{
([Measures].[Products]),
([Measures].[Reseller Products])
} ON COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM [Step-by-Step]
;

注意：例7-17和7-18结果并没有什么差异，然而，这是因为EXCLUDEEMPTY标志符使用当前度量来计算集中的元组。关于当前度量的概念，有后面的表达式高级介绍中会继续学习。

3、DistinctCount函数

MDX也提供了一个额外的计数函数，DistinctCount（http://msdn.microsoft.com/zh-cn/library/ms146033.aspx）

Count((Distinct{Set}),ExcludeEmpty)与DistinctCount({Set})相当

 

4、使用Generate浏览集合

Generate（http://msdn.microsoft.com/zh-cn/library/ms145526.aspx），其实是计算并集。

例7-19

WITH
MEMBER [Measures].[Products] AS
Count(
EXISTING {[Product].[Product].[Product].Members}
)
MEMBER [Measures].[Products List] AS
Generate(
EXISTING {[Product].[Product].[Product].Members},
[Product].[Product].CurrentMember.Name,
" | "
)
SELECT
{
([Measures].[Products]),
([Measures].[Products List])
} ON COLUMNS,
{[Product].[Subcategory].Members} ON ROWS
FROM [Step-by-Step]
;

执行结果：

小结：本文是聚合函数的进阶，介绍了Min和Max函数,Count函数与DistinctCount函数，Generate函数。

下文将继续学习层次结构（Hierarchies）。

参考资源：

1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）