SQL Server 2008 MDX学习笔记之结果集Sets使用技巧是本文我们主要要介绍的内容,接下来就让我们从以下的几个例子中区了解结果集Sets的使用技巧吧。
Sets的基本技巧
在Analysis Service中,Set代表元组(Tuples)的集合。在一个Set内部,独立的元组被用逗号隔开,如下:
- {
- ([Product].[Category].[Accessories]),
- ([Product].[Category].[Bikes]),
- ([Product].[Category].[Clothing]),
- ([Product].[Category].[Components])
- }
下面我们组装一个基本的Sets
打开MDX查询编辑器,如下:
例4-1
- SELECT
- {
- ([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
- } ON COLUMNS,
- {
- ([Product].[Category].[Accessories]),
- ([Product].[Category].[Bikes]),
- ([Product].[Category].[Clothing]),
- ([Product].[Category].[Components])
- } ON ROWS
- FROM [Step-by-Step];
我们增加一个元组([Product].[Subcategory].[Mountain Bikes]),如下:
例4-2
- SELECT
- {
- ([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
- } ON COLUMNS,
- {
- ([Product].[Category].[Accessories]),
- ([Product].[Category].[Bikes]),
- ([Product].[Category].[Clothing]),
- ([Product].[Category].[Components]),
- ([Product].[Subcategory].[Mountain Bikes])
- } ON ROWS
- FROM [Step-by-Step];
- /*
- Executing the query ...
- Members belong to different hierarchies in the function.
- 函数中指定的两个集具有不同的维数。
- Execution complete
- */
我们使用相同维数的用户层次结构[Product Categories],修改如下:
例4-3
- SELECT
- {
- ([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
- } ON COLUMNS,
- {
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Bikes]),
- ([Product].[Product Categories].[Clothing]),
- ([Product].[Product Categories].[Components]),
- ([Product].[Product Categories].[Mountain Bikes])
- } ON ROWS
- FROM [Step-by-Step];
- /* CY 2002 CY 2003 CY 2004
- United States United States United States
- Accessories $61,263.90 $151,136.35 $76,027.18
- Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
- Clothing $317,939.41 $495,443.62 $197,590.92
- Components $2,526,542.06 $3,284,551.84 $1,137,105.72
- Mountain Bikes $6,970,418.73 $5,832,626.02 $2,539,198.92
- */
下面这个查询有类似错误:
例4-4
- SELECT
- {
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2002]),
- ([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
- ([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
- } ON COLUMNS,
- {
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Bikes]),
- ([Product].[Product Categories].[Clothing]),
- ([Product].[Product Categories].[Components]),
- ([Product].[Product Categories].[Mountain Bikes])
- } ON ROWS
- FROM [Step-by-Step];
- /*
- Executing the query ...
- Query (2, 4) Two sets specified in the function have different dimensionality.
- 函数中指定的两个集具有不同的维数。
- Execution complete
- */
正确应为:
例4-5
- SELECT
- {
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2002]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2004])
- } ON COLUMNS,
- {
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Bikes]),
- ([Product].[Product Categories].[Clothing]),
- ([Product].[Product Categories].[Components]),
- ([Product].[Product Categories].[Mountain Bikes])
- } ON ROWS
- FROM [Step-by-Step];
我们可以这样改变顺序,并增加一个行:
例4-6
- SELECT
- {
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2004]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2002])
- } ON COLUMNS,
- {
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Bikes]),
- ([Product].[Product Categories].[Clothing]),
- ([Product].[Product Categories].[Components]),
- ([Product].[Product Categories].[Mountain Bikes])
- } ON ROWS
- FROM [Step-by-Step];
查询结果如下:
- /* United States United States United States
- CY 2004 CY 2003 CY 2002
- Accessories $76,027.18 $151,136.35 $61,263.90
- Accessories $76,027.18 $151,136.35 $61,263.90
- Bikes $7,951,335.55 $16,139,984.68 $14,716,804.14
- Clothing $197,590.92 $495,443.62 $317,939.41
- Components $1,137,105.72 $3,284,551.84 $2,526,542.06
- Mountain Bikes $2,539,198.92 $5,832,626.02 $6,970,418.73
- */
多出的重复行怎么办?我们可以使用distinct函数(http://msdn.microsoft.com/zh-cn/library/ms146033.aspx)
例4-6
- SELECT
- {
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2004]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
- ([Geography].[Country].[United States], [Date].[Calendar].[CY 2002])
- } ON COLUMNS,
- DISTINCT(
- {
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Accessories]),
- ([Product].[Product Categories].[Bikes]),
- ([Product].[Product Categories].[Clothing]),
- ([Product].[Product Categories].[Components]),
- ([Product].[Product Categories].[Mountain Bikes])
- }
- ) ON ROWS
- FROM [Step-by-Step];
- /* United States United States United States
- CY 2004 CY 2003 CY 2002
- Accessories $76,027.18 $151,136.35 $61,263.90
- Bikes $7,951,335.55 $16,139,984.68 $14,716,804.14
- Clothing $197,590.92 $495,443.62 $317,939.41
- Components $1,137,105.72 $3,284,551.84 $2,526,542.06
- Mountain Bikes $2,539,198.92 $5,832,626.02 $6,970,418.73
- */
关于SQL Server 2008 MDX学习笔记之结果集Sets使用技巧的知识就介绍到这里了,希望本次的介绍能够对您有所收获!
【编辑推荐】
- SQL Server数据库DataRelation的应用示例详解
- SQL Server 2008 MDX应用之检索集合中的元组
- SQL Server 2005/2008中的CTE应用之递归查询
- SQL Server 2008数据库学习笔记之MDX查询示例
- 浅析SQL Server数据库SSIS导入IIS日志的简单步骤