SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
导读:本文介绍集合(Sets)的进阶内容,本文将包括以下内容:
■1、Filter函数
■2、NonEmpty函数
■3、Set的Union、InterSection和Exception
■4、Generate和Extract函数
本文所用数据库和所有源码,请到微软官网下载
1、使用Filter函数(http://msdn.microsoft.com/zh-cn/library/ms146037.aspx)
例6-14
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step]
;
例6-15
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) > ([Measures].[Reseller Sales Amount]) AND
(
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
;
例6-16
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members},
([Measures].[Internet Sales Amount]) >
([Measures].[Reseller Sales Amount]) AND
NOT (
[Product].[Category].CurrentMember Is [Product].[Category].[Clothing] OR
[Product].[Category].CurrentMember Is [Product].[Category].[Accessories]
)
) ON ROWS
FROM [Step-by-Step]
;
2、使用NonEmpty函数(http://msdn.microsoft.com/en-us/library/ms145988.aspx)
例6-17
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
NonEmpty(
{[Product].[Product].[Product].Members},
{([Measures].[Reseller Sales Amount])}
) ON ROWS
FROM [Step-by-Step]
;
例6-18
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
NON EMPTY {[Product].[Product].[Product].Members} ON ROWS
FROM [Step-by-Step]
;
这两个例子中,使用NonEmpty函数和Non Empty关键字其实效果是一样的。
3、组合结果集
结果集的组合有三种形式:Union,intersection和Exception
如下图所示:
看原始例子:
例6-19:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
) ON ROWS
FROM [Step-by-Step]
;
看看Union的效果:
例6-20:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Union(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
)
) ON ROWS
FROM [Step-by-Step]
;
使用Exception的效果:
例6-21:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Except(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
)
) ON ROWS
FROM [Step-by-Step]
;
InterSection的效果:
例6-22:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Intersect(
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
),
TopCount(
{[Product].[Product].[Product].Members},
10,
([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2003])
)
) ON ROWS
FROM [Step-by-Step]
;
4、Generate和Extract函数
MDX语言提供了两个强有力的“深奥”函数
Generate(http://msdn.microsoft.com/zh-cn/library/ms145526%28v=SQL.105%29.aspx)
Extract(http://msdn.microsoft.com/zh-cn/library/ms145980.aspx)
看原始语句:
例6-23:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
TopCount(
{[Product].[Product].[Product].Members},
5,
([Measures].[Reseller Sales Amount])
) ON ROWS
FROM [Step-by-Step]
;
加上Generate
例6-24:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
{[Product].[Category].[Category].Members},
TopCount(
{[Product].[Product].[Product].Members},
5,
([Measures].[Reseller Sales Amount])
)
) ON ROWS
FROM [Step-by-Step]
;
所得的结果与上例相同,
那么,generate起什么作用呢?我们再加上All标志符
例6-25:
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
{[Product].[Category].[Category].Members},
TopCount(
{[Product].[Product].[Product].Members},
5,
([Measures].[Reseller Sales Amount])
),
ALL
) ON ROWS
FROM [Step-by-Step]
;
换句话说,Generate其实是实现了遍历,按每个分类进行TopCount,这个在统计中非常棒。如果老板要统计今年每个月工作量最大的十个员工,那么你应该想到Generate,更进一步,你可以使用Category的hierarchy属性,得到如下结果:
例6-26
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Generate(
{[Product].[Category].[Category].Members},
{([Product].[Category].CurrentMember)} *
TopCount(
EXISTING {[Product].[Product].[Product].Members},
5,
([Measures].[Reseller Sales Amount])
),
ALL
) ON ROWS
FROM [Step-by-Step]
;
结果:
Extract的用途,先看下面的例子,使用Filter
例6-27
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Filter(
{[Product].[Product].[Product].Members} *
{[Date].[Calendar].[Month].Members},
([Measures].[Reseller Sales Amount])>160000
) ON ROWS
FROM [Step-by-Step]
;
使用Extract的例子
例6-28
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Extract(
Filter(
{[Product].[Product].[Product].Members} *
{[Date].[Calendar].[Month].Members},
([Measures].[Reseller Sales Amount])>160000
),
[Product].[Product]
) ON ROWS
FROM [Step-by-Step]
;
请注意:Extract在此处相当于使用了distinct。
小结:本文是集合(Sets)的进阶,介绍了几个常用的函数如Filter和NonEmpty,Generate和Extract,集的组合方式如Union、InterSection和Exception。
下文将继续学习执行Aggregation的相关内容。
参考资源:
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, ...
Microsoft.Press.Microsoft.SQL.Server.2008.MDX.Step.by.Step.Feb.2009.rar
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....
"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, ...
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 ...
项目需要用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....
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....
MDX.Solutions.with.Microsoft.SQL.Server.Analysis.Services.2005
Written by members of the Analysis Services product team at Microsoft, this timely, authoritative book shows you how to use Analysis Services with SQL Server components to provide comprehensive, end-...