SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
导读:本文介绍执行聚合(Aggregation)的进阶内容,包括:
■1、用Sum和Aggregation执行求和
■2、用Avg函数计算均值
■3、用表达式计算均值
■4、用Stedv计算标准偏差
本文所用数据库和所有源码,请到微软官网下载
1、执行求和
MDX的聚合函数:Aggregation(http://msdn.microsoft.com/zh-cn/library/ms145524.aspx)
求和还有一个函数:Sum(http://msdn.microsoft.com/zh-cn/library/ms145484.aspx)
我们依旧从一个最简单的例子看起
例7-1
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
{[Product].[Subcategory].[Subcategory].Members},
5,
([Measures].[Reseller Sales Amount])
) +
{([Product].[Subcategory].[All Products])} ON ROWS
FROM [Step-by-Step]
;
需要说明的是:上例中,[Reseller Sales Amount]代表这个表中Sales Amount字段的数量,[Reseller Transaction Count]代表底层事实表数据记录的数值,[Reseller Order Count]代表底层事实表的订单数。
查询结果如下:
下面我们增加对subcategory进行TopCount 5 求和
例7-2
WITH
MEMBER [Product].[Subcategory].[Top 5] AS
Sum(
TopCount(
[Product].[Subcategory].[Subcategory].Members,
5,
([Measures].[Reseller Sales Amount])
),
([Measures].CurrentMember)
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
[Product].[Subcategory].[Subcategory].Members,
5,
([Measures].[Reseller Sales Amount])
) +
{
([Product].[Subcategory].[Top 5]),
([Product].[Subcategory].[All Products])
} ON ROWS
FROM [Step-by-Step]
;
结果如下:
前两列是累加的,因而没有问题,最后一列Reseller Order Count不是从五个分类中累加的,而是对所有产品中的子分类的订单进行汇总。很显然,这不是我们想要的结果,此时,我们需要借助于Aggregation函数
例7-3
WITH
MEMBER [Product].[Subcategory].[Top 5] AS
Aggregate(
TopCount(
[Product].[Subcategory].[Subcategory].Members,
5,
([Measures].[Reseller Sales Amount])
),
([Measures].CurrentMember)
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
[Product].[Subcategory].[Subcategory].Members,
5,
([Measures].[Reseller Sales Amount])
) +
{
([Product].[Subcategory].[Top 5]),
([Product].[Subcategory].[All Products])
} ON ROWS
FROM [Step-by-Step]
;
Tips:在求和时,我们通常应该使用Aggregion,而非Sum。当然,这并非绝对。
2、用AVG函数计算均值
MDX中的求均值函数为Avg(http://msdn.microsoft.com/zh-cn/library/ms146067.aspx)
我们还是从一个最简单的例子入手:
例7-4
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
} ON ROWS
FROM [Step-by-Step]
;
下面我们求均值
例7-5
WITH
MEMBER [Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales] AS
Avg(
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
},
[Measures].CurrentMember
)
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{([Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales])} +
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
} ON ROWS
FROM [Step-by-Step]
;
结果:
加上季度数据
例7-6
WITH
MEMBER [Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales] AS
Avg(
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
},
[Measures].CurrentMember
)
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{([Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales])} +
Hierarchize(
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
} +
{
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q4 CY 2003]
}
) ON ROWS
FROM [Step-by-Step]
;
加上季度平均:
例7-7
WITH
MEMBER [Date].[Calendar].[CY 2003 Quarterly Avg Reseller Sales] AS
Avg(
{
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q4 CY 2003]
},
[Measures].CurrentMember
)
MEMBER [Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales] AS
Avg(
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
},
[Measures].CurrentMember
)
SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
([Date].[Calendar].[CY 2003 Monthly Avg Reseller Sales]),
([Date].[Calendar].[CY 2003 Quarterly Avg Reseller Sales])
} +
Hierarchize(
{
[Date].[Calendar].[Month].[January 2003]:
[Date].[Calendar].[Month].[December 2003]
} +
{
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q4 CY 2003]
}
) ON ROWS
FROM [Step-by-Step]
;
注意:AVG是一个静态函数,那么,如果是动态的表达式求均值,用什么方法?
3、用表达式计算均值
例7-8
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
{
[Date].[Calendar Year].[CY 2001]:
[Date].[Calendar Year].[CY 2004]
} ON ROWS
FROM [Step-by-Step]
;
下面我们加上每年的月均值,这是动态计算的:
例7-9
WITH
MEMBER [Measures].[Monthly Avg Reseller Sales Amount] AS
Avg(
EXISTING [Date].[Calendar].[Month].Members,
[Measures].[Reseller Sales Amount]
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count]),
([Measures].[Monthly Avg Reseller Sales Amount])
} ON COLUMNS,
{
[Date].[Calendar Year].[CY 2001]:
[Date].[Calendar Year].[CY 2004]
} ON ROWS
FROM [Step-by-Step]
;
下面我们再加上每年的每个订单的销售均值,这也是动态计算的:
例7-10
WITH
MEMBER [Measures].[Average Reseller Sales Amount] AS
([Measures].[Reseller Sales Amount]) / ([Measures].[Reseller Order Count])
,FORMAT_STRING="Currency"
MEMBER [Measures].[Monthly Avg Reseller Sales Amount] AS
Avg(
EXISTING [Date].[Calendar].[Month].Members,
[Measures].[Reseller Sales Amount]
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Count]),
([Measures].[Monthly Avg Reseller Sales Amount]),
([Average Reseller Sales Amount])
} ON COLUMNS,
{
[Date].[Calendar Year].[CY 2001]:
[Date].[Calendar Year].[CY 2004]
} ON ROWS
FROM [Step-by-Step]
;
4、计算标准偏差
MDX使用函数Stdev(http://msdn.microsoft.com/zh-cn/library/ms146068.aspx),来计算标准差,它使用无偏差总体公式,而
对应地,StdevP 函数(http://msdn.microsoft.com/zh-cn/library/ms146019.aspx)使用有偏差总体公式。
看一个复杂点的例子:
例7-11
WITH
MEMBER [Measures].[Average Reseller Sales Amount] AS
([Measures].[Reseller Sales Amount])/ ([Measures].[Reseller Transaction Count])
,FORMAT_STRING="Currency"
MEMBER [Measures].[Variance Reseller Sales Amount] AS
(
([Measures].[Squared Reseller Sales Amount]) /
(([Measures].[Reseller Transaction Count])-1)
) -
(([Measures].[Average Reseller Sales Amount])^2)
,FORMAT_STRING="Currency"
MEMBER [Measures].[StDev Reseller Sales Amount] AS
([Measures].[Variance Reseller Sales Amount])^(0.5)
,FORMAT_STRING="Currency"
SELECT
{
([Measures].[Average Reseller Sales Amount]),
([Measures].[Variance Reseller Sales Amount]),
([Measures].[StDev Reseller Sales Amount])
} ON COLUMNS,
{
[Date].[Calendar Year].[CY 2001]:
[Date].[Calendar Year].[CY 2004]
} ON ROWS
FROM [Step-by-Step]
;
注意:本例中Squared Reseller Sales Amount度量调用一个命名计算
执行结果:
小结:本文是聚合函数的进阶,介绍了Aggregation与Sum函数的细微区别,用AVG求静态均值和用表达式求动态均值,还有一个计算标准偏差的函数Stdev。
下文将继续学习Min和Max函数及其他聚合相关功能。
参考资源:
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....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
内含ppt,视频,文档,示例!!!!!!!