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

huashan8

　　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
　　如下图所示：

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pAf7Jp2h43hr5zZisol5yBlKOwurZ2G1U8NmL79MqMOJTslprmE3t1fTum0tgoP9lUorZtTpKHpHPIOncqiigcA/2011-11-22%2014-55-29.png?psid=1

　　看原始例子：
　　例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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pDdHiBLddl5jJsC6Hb3IXQqNWdaCT_7fSXxG3P7WZaGhhWnm_Jy_ctyq-W35aqv9a-VAGjN3aKe8/2011-11-22%2015-03-34.png?psid=1

　　看看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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p2O8OiWDf-Dbo2x9wo80dBaeUSJ37i135A861QrQY-h5D8ZQ3QiRJ0YdPiCBqbbFuQz52Aiq8R0Y/2011-11-22%2015-06-53.png?psid=1

　　使用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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p-Jr1apan7Ws9V-4851iMI0S4TN7XRLkaYulvcfAgCAM4S56U3NoV47IvG05XOddM9uWx_F2fWCQ/2011-11-22%2015-10-24.png?psid=1

　　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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pvg9Td3oS5wRvRlbSL4YgVf1Wyy2ovAB1Sp1DRqlqKP5o-o-PSzwbBslf74cGt1sAHlNpWC-m3tQ/2011-11-22%2015-16-01.png?psid=1

　　
　　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]
;
　　所得的结果与上例相同，

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pzAjn9p6k0j_nJrKMFkEokDcE-t2N9xVT-SSLHhe2JpilLyp60vVvnaCtArpUCKNh8FWsYXK34Gg/2011-11-22%2015-39-34.png?psid=1

　　那么，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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p9hbDK6zx9UTmOuJG07QIFvoqefn220roZdnN4BedBt6DQKBhgjboGQD2p3nZ0-bwrZ3vtaWYDnQ/2011-11-22%2015-42-18.png?psid=1

　　换句话说，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]
;
　　结果：

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pstgjhKe5XwmnZB4jeGEpDszbKR8tBKeBXpKlpt-0PmLOoV8AeQvzMreGh4SPQmn4XrvzzfdvXBU/2011-11-22%2015-54-05.png?psid=1

　　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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p-y4zn-fb-0fvOXSpoXmxCSurPSbC61N_dHK6Szp-Sej4BjlYYByxyW4zGk2KWR3huzHoBSDsP_A/2011-11-22%2016-02-28.png?psid=1

　　使用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]
;

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pPfznlwccWaEh77V4UMerXjElapnbFKkCqa8QYApL0RRBHnFJL541NElmKJ0vncL6l0lXxoUTjU0/2011-11-22%2016-00-34.png?psid=1

　　请注意：Extract在此处相当于使用了distinct。
　　小结：本文是集合（Sets）的进阶，介绍了几个常用的函数如Filter和NonEmpty，Generate和Extract，集的组合方式如Union、InterSection和Exception。
　　下文将继续学习执行Aggregation的相关内容。
　　参考资源：
　　1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）
　　

　　
　　

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn