《Microsoft SQL Server 2008 MDX Step by Step》学习笔记五：生成复杂的集合（Sets）（上）

妖怪幻

　　SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
　　导读:本文介绍集合（Sets）的进阶内容，本文将包括以下内容：
　　■1、组装一个有序集合
　　■2、应用标准来控制集合成员关系（Apply criteria to control set membership ）
　　■3、应用集合逻辑和高级的集合生成技术。
　　本文所用数据库和所有源码，请到微软官网下载
　　
　　1、组装有序集合
　　打开MDX查询编辑器，如下：
　　例6-1



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Product].[Product].Members} ON ROWS
FROM[Step-by-Step]
;
　　查询结果：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p69ItEN4Rs6aXNYch9rF_1z6w6sWWlVou1KMYFtgapbba9F0wyKIcDEltvya7G65Cp0u_ZfBGACw/2011-8-17%2015-03-42.png?psid=1
　　我们修改排序，如下：
　　例6-2



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Order(
{[Product].[Product].[Product].Members},
([Measures].[Reseller Sales Amount]),
DESC
) ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pQhVQ_C3wLiGPi2G9ow76QQMWqmzIeukY_l-FNjRCm25Yv-kHxBc8ZGzoEC2ffV6O8u9hbuCc6Lo/2011-8-17%2015-12-12.png?psid=1
　　注意排序字段，这里使用了Order函数（http://msdn.microsoft.com/zh-cn/library/ms145587.aspx）。
　　例6-3



SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
} ON COLUMNS,
Order(
{[Product].[Product].[Product].Members},
VBAMDX!ABS(
([Measures].[Reseller Sales Amount]) -
([Measures].[Internet Sales Amount])
),
DESC
) ON ROWS
FROM[Step-by-Step]
;
　　
　　注意，我们使用了上一节提到的VBA函数，结果以两列之差的绝对值为排序依据。
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p6_RGAj06WnCQUyIzA9Kp7IBuniwjTvj2zzWS52b-IncSlp8f4DELBYfohK7t8Xr0xHmm48HhQrg/2011-8-17%2015-22-00.png?psid=1
　　注意:使用Order还可以突破分层限制。
　　例6-4



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM[Step-by-Step]
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pv0iZs4g7lZ_udQAJMq8Kb4r4A02b2hwf_qPmv6QLVAO8_8t2vq1bqdjUN2Xe6ON824d_6vFvg5c/2011-8-17%2015-28-04.png?psid=1
　　例6-5



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Product].[Category].[Category].Members} *
{[Product].[Subcategory].[Subcategory].Members} ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pBxCwCBC67cAsw1UKAZHUFfKTeGlkXTzmp22VITVtKc-i3JrpdA_YhCe7DEqJ3wBjTTQWB-KrBdU/2011-8-17%2015-32-17.png?psid=1
　　再进一步，看一个双排序的例子
　　例6-6



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Order(
{[Product].[Category].[Category].Members} *
{[Product].[Subcategory].[Subcategory].Members},
([Measures].[Reseller Sales Amount]),
DESC
) ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pmAgFcQRpLl9LHyIaJ1AnTop375ga37GEvtbne-0nwDzWlPF1s5dtp3Hc-184cN2mWHIzuz2cg2Y/2011-8-17%2015-35-09.png?psid=1
　　
　　在上面的查询中，交叉联接使用默认排序，而且后面指定了([Measures].[Reseller Sales Amount])排序，但是大家注意到没有，这一列并没有按照指定的数值排序，为什么呢？因为默认的ASC和DESC是分层的（hierarchical），换句话说，这些元组的前一个成员的排序被保留了。为了打乱这个层次结构，可以使用BASC和BDESC（http://msdn.microsoft.com/zh-cn/library/ms145587(v=SQL.105)）。
　　
　　例6-7



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Order(
{[Product].[Category].[Category].Members} *
{[Product].[Subcategory].[Subcategory].Members},
([Measures].[Reseller Sales Amount]),
BDESC
) ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pmAgFcQRpLl_XfLeCi1dGDJGnFVvqE5bOtVdD4SFTp4RyGngPX_qPy-hlF-h571AeOEzfosSbwJ4/2011-8-17%2016-00-15.png?psid=1
　　OK!这下消停了。^_^
　　顺便，我们提一下Hierarchize函数（http://msdn.microsoft.com/zh-cn/library/ms145981.aspx），该函数可以按层次结构的顺序组织指定集中的成员，返回集的有效多维表达式 (MDX)。
　　我们看一个例子
　　例6-8



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Hierarchize(
{
([Product].[Product Categories].[Subcategory].[Road Bikes]),
([Product].[Product Categories].[All Products]),
([Product].[Product Categories].[Subcategory].[Mountain Bikes]),
([Product].[Product Categories].[Category].[Bikes]),
([Product].[Product Categories].[Subcategory].[Touring Bikes])
}
) ON ROWS
FROM[Step-by-Step]
;
　　
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pbW8yJuvm0SLpDMJ8R5bhjQlkua6AF2tms5KX2l34gf5zewtn0R76aCD6lVShLLJv8Gepr3dpXow/2011-8-17%2016-09-13.png?psid=1

　　如果定义POST标志，则 Hierarchize 函数按非自然顺序对一定级别的成员进行排序。 也就是说，子成员优先于他们的父级。
　　例6-9



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Hierarchize(
{
([Product].[Product Categories].[Subcategory].[Road Bikes]),
([Product].[Product Categories].[All Products]),
([Product].[Product Categories].[Subcategory].[Mountain Bikes]),
([Product].[Product Categories].[Category].[Bikes]),
([Product].[Product Categories].[Subcategory].[Touring Bikes])
},
POST
) ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1po7psUSdavkUXYfuU1JqdbT5dd_OmJLErCpw29nGVNPakCvE-tDcJ195TBiZfM61dYwKCkztZ38Q/2011-8-17%2016-13-23.png?psid=1
　　注意到排序规则了么？如果没有观察出来，请留言或联系我，3w@live.cn，呵呵。
　　范围操作符:包含一个默认的排序
　　例6-10



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[CY 2001]:[Date].[Calendar].[CY 2004]} ON ROWS
FROM[Step-by-Step]
;
/*
Reseller Sales Amount
CY 2001 $8,065,435.31
CY 2002 $24,144,429.65
CY 2003 $32,202,669.43
CY 2004 $16,038,062.60
*/
　　
　　2、检索一个集合的最后一个或第一个元组
　　我们用到两个函数TopCount（http://msdn.microsoft.com/zh-cn/library/ms144792.aspx）和BottomCount（http://msdn.microsoft.com/zh-cn/library/ms144864.aspx）
　　注意：这两个函数总是会打乱层次结构。
　　例6-11



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
TopCount(
{[Product].[Subcategory].[Subcategory].Members},
5,
([Measures].[Reseller Sales Amount])
) ON ROWS
FROM[Step-by-Step]
;
/*
Reseller Sales Amount
Road Bikes $29,358,206.96
Mountain Bikes $26,492,684.38
Touring Bikes $10,451,490.22
Mountain Frames $4,713,672.15
Road Frames $3,849,853.34
*/
　　
　　注意，未指定顺序时，默认按升序排列。
　　Head函数（http://msdn.microsoft.com/zh-cn/library/ms144859(v=SQL.105)），返回集中位置靠前的指定数目的元素，同时保留重复项。Head 函数从指定集的开始处返回指定的元组数目。并保留元素的顺序。Count 的默认值为 1。如果指定的元组数目小于 1，则 Head 函数返回空集。如果指定的元组数目超过了集中的元组数目，则此函数返回原始集。
　　Tail函数（http://msdn.microsoft.com/zh-cn/library/ms146056.aspx），Tail 函数从指定集的结尾处返回指定的元组数目。 会保留元素的顺序。 Count 的默认值为 1。如果指定的元组数目小于 1，则该函数返回空集。 如果指定的元组数目超过了集中的元组数目，则此函数返回原始集。
　　
　　例6-12



SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
Tail(
Order(
{[Product].[Subcategory].[Subcategory].Members},
([Measures].[Reseller Sales Amount]),
BDESC
),
5
) ON ROWS
FROM[Step-by-Step]
;
/*
Reseller Sales Amount
Tires and Tubes $925.21
Bike Stands (null)
Fenders (null)
Lights (null)
Panniers (null)
*/
　　
　　Item函数（http://msdn.microsoft.com/zh-cn/library/ms145501.aspx）,下面，我们看一个比较复杂的例子
　　例6-13



WITH
MEMBER [Measures].[Top Product Sales]AS
{
EXISTING
TopCount(
[Product].[Product].[Product].Members,
1,
([Measures].[Internet Sales Amount])
) *
{[Measures].[Internet Sales Amount]}
}.Item(0)
,FORMAT_STRING="Currency"
MEMBER [Measures].[Top Product Name]AS
{
EXISTING
TopCount(
[Product].[Product].[Product].Members,
1,
([Measures].[Internet Sales Amount])
)
}.Item(0).Item(0).Name
SELECT
{
([Measures].[Internet Sales Amount]),
([Measures].[Top Product Sales]),
([Measures].[Top Product Name])
} ON COLUMNS,
{
([Date].[Calendar Year].[CY 2001]),
([Date].[Calendar Year].[CY 2002]),
([Date].[Calendar Year].[CY 2003]),
([Date].[Calendar Year].[CY 2004])
} ON ROWS
FROM[Step-by-Step]
;
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pPlb4Ph0woebQO02ehf99uuScX2AmpGlWoPTpVmYBEPsHlfOQo8YambaxHm221HKZAX0GqYYoG-Y/2011-8-17%2016-44-19.png?psid=1
　　上例中综合运用了本节和上节的几个常用函数，大家可以自行分析。
　　小结：本文是集合（Sets）的进阶，介绍了几个常用的排序函数，下文继续介绍过滤集合和组合集合，以及一些高级的技巧。
　　
　　参考资源：
　　1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）