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

sexevil

　　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]代表底层事实表的订单数。
　　查询结果如下：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p5VZgDM3lT0TztmH2Jzqzl-GrFM_Dv_UGbQwZVgL5jmML9HFLgs6q9FZZUgrA5qSD1YxjWXI_f-s/2011-11-23%2010-25-20.png?psid=1
　　下面我们增加对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]
;
　　结果如下：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p8OxZ3vet-tf6GJQWqng5nFmsikgpQkgBD_Y8IGTH4EYq-nfBnt-SPKLcNDrTRWoGP2XHBjTdJjg/2011-11-23%2010-31-03.png?psid=1
　　前两列是累加的，因而没有问题，最后一列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]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1peBLOwQXGOPmsjwteRSKpdyDANWodBkayc0E9TGsZDpR8DaKFUYjfoRxAgDnltdeVpqIG3-BLuiA/2011-11-23%2010-45-03.png?psid=1
　　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]
;
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pOeQ0gy1Ka3xkBZPLrT8LCT8AeYi2Cc_EHFCgTWMW0VZWAQ52-S8GXP7zHKIGp2OUIFHikBFOZ_M/2011-11-23%2010-53-53.png?psid=1
　　下面我们求均值
　　例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]
;
　　结果：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pfhXQ-V_VmYpYqJc8qty_spAMOWzciJs5WWXNba_GFOnhd-lxwDS_5rgPkAHMJJgfFr1r6nOMd7k/2011-11-23%2011-07-06.png?psid=1
　　加上季度数据
　　例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]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pwO2mjEmPz_qQ40iDOM_kUEjBaE2cC2fo_JeuJl1gjL6oSva0Zggz0diw1bk73FkVWDMG-td9a4Y/2011-11-23%2011-13-48.png?psid=1
　　加上季度平均：
　　例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]
;
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pWIeN6wYxGm6ROtweVUjXkem-yVKYNhJ15o074JP-ND8ofETVFRBF5Xo4av08uSIO4-Eyzatzkzw/2011-11-23%2011-16-54.png?psid=1
　　注意：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]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pTw1pAG99RUMq2gzcHxuevTJAJOm-cNmcYzKNjG9iz6NWv5SiS26rAZwAQWfG9OhRQQhrXQKatCk/2011-11-23%2011-28-22.png?psid=1
　　下面我们加上每年的月均值，这是动态计算的：
　　例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]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pQio8SerqY2zLfvLEV2rlrdZ9F8OLAJl_JsAE6exeBI9dnwZVQoHnevbo34sgwK1sJ-tjYnLSWCs/2011-11-23%2011-31-50.png?psid=1
　　下面我们再加上每年的每个订单的销售均值，这也是动态计算的：
　　例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]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pzQBql9rPz_4yZtzje7_rDVYJ40uZtgYwH9TPW6XXZi4A_Qw0kvqfQTAt30-h4NZZw4AHLFm2sD8/2011-11-23%2011-37-43.png?psid=1
　　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度量调用一个命名计算
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1ptPdm1qSbfYA6_RCQit0e7VDEYn3uofGqaflO_fU1nsyAKo7xw8cngTAEoN2oblxCnS4ghFL0oX4/2011-11-23%2012-28-58.png?psid=1
　　执行结果：
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pDHWdhPmshxLwF-JuVwxkoDu_DVVmdnMDHL58ecmxG7mc4q8fT5GF0cJRbDFSQaH_Ae73hmHqNsQ/2011-11-23%2011-48-27.png?psid=1
　　
　　小结：本文是聚合函数的进阶，介绍了Aggregation与Sum函数的细微区别，用AVG求静态均值和用表达式求动态均值，还有一个计算标准偏差的函数Stdev。
　　下文将继续学习Min和Max函数及其他聚合相关功能。
　　参考资源：
　　1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）