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)
分享到:
相关推荐
Microsoft SQL Server 2008 MDX Step by Step Teach yourself the Multidimensional Expressions (MDX) query language—one step at a time. With this practical, learn-by-doing tutorial, you’ll build the ...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
Microsoft+SQL+Server+2008+MDX+Step+by+Step 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
Microsoft.Press.Microsoft.SQL.Server.2008.MDX.Step.by.Step.Feb.2009.rar
Smart Business Intelligence Solutions with Microsoft SQL Server 2008 Discover how to: Manage the development life cycle and build a BI team Dig into SQL Server Analysis Services, Integration ...
"Practical DMX Queries for Microsoft SQL Server Analysis Services 2008" contains more than 250 downloadable DMX queries you can use to extract and visualize data. The application, syntax, and results...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
项目需要用MDX,所以翻译了Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX.pdf中第II部分第三章的内容。有翻译不当之处请联系QQ:185633513或发送EMAIL:yubinsure@gmail.com进行修正。
《SQL Server2012Analysis Services高级教程(第2版)》由Microsoft产品团队的主要成员(哈日那思、菲尔格林等)编写,介绍了如何应用这些新功能来创建复杂的BI解决方案。为了实现这一目的,你将在本书中了解到如何...
MDX step by step CD which including every chapter's sample
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
内含ppt,视频,文档,示例!!!!!!!
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...