15个经典的MDX查询

3. 在美国，刚刚过去的三个季度里都有销售量的食品和饮料有哪些？

（这个查询也可以理解为“过去三个季度里食品和饮料销售量都不为0的产品有哪些？”）。查询Listing 3 示范了如何动态的确定对应的时间集合—这是一项很有价值的技巧。时间集合动态随着cube数据的更新而改变（也就是说该查询无论你在什么时候执行，无论cube的数据作了多少次更新，结果都是最近三个月的数据）。首先，自定义集合LastQuarter 定义了时间维度中有销售记录的最近一个季度。自定义集合Last3Quarters在LastQuarter的基础上利用Range()函数（实际没有range（）函数，冒号：就是作者说的函数）指定了以LastQuarter为最后一个季度的连续的三个季度。我不直接在Last3Quarter（原文是LastQuarter，我认为是作者笔误）的定义中使用Tail（）函数是因为这样做返回的可能不是连续的三个季度；因为空记录的季度肯能出现在任何季度，Filter（）函数只能排除掉空季度。Lag()函数结合range()函数,确保了返回的三个季度是连续的。

在这个查询中，item(0).item(0) 函数取得指定集合的第一个成员，因为集合在技术上就是一组元组[如：来自不同维度的一系列成员也可以组成一个元组]，所以用第一个Item（）函数选择元组，第二个Item（）选择该元组里的成员。（我们可以这样理解，集合由元组组成，元组由成员组成）。

Listing_03.Determining Brands Sold During the Past Three Quarters.txt

说明：在过去三个季度里都存在销售量的商品销售记录

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not

IsEmpty([Time].CurrentMember)),1)'

set [Last3Quarters] as ' [LastQuarter].item(0).item(0).Lag(2) : [LastQuarter].item(0).item(0)'

select [Last3Quarters] on COLUMNS,

Non Empty Union(Descendants( [Food], [Product].[Brand Name] ), Descendants( [Drink], [Product].[Brand Name] )) on ROWS

from Sales

查询结果展示：

注：

查询效果展示：

查询效果展示：

查询结果图展示：

7. 销量最好的五个商店是哪五个？这五个商店中消费最高的五位顾客？

查询Listing 7 示范了很实用也比较复杂的Generate()函数。如果你有过开发经验，你会发现Generate()类似VB或则C#中的For each 语句。下面对Generate()做具体的说明：

如：Generate( {Miami, Atlanta}, Customers.CurrentMember.Parent) Generate()对第二个参数

Customers.CurrentMember.Parent进行计算，计算第一个参数{Miami, Atlanta}中的所有项。在本例，第二个参数的mdx 表达式返回当前项的父成员，所以最终结果是{Florida, Georgia}---第一个参数中每一个项的父成员的集合。

（注：我们可以这样理解，第一参数是要计算的范围，第二个参数是要计算的对象）

本查询同时使用Generate() 函数嵌套了递归。确定了五个销售最佳的商店，每个商店的消费最高的五个顾客后，Generate()合并了顾客集合从而创建了一份由25项store-customer组成的列表。

Listing_07.Determining the Top Five Stores and the Top Five Customers.txt

说明：查出销售量最好的前5名店和每个店的前5个顾客及其销售记录

select {[Unit Sales]} on COLUMNS,

Generate( TopCount([Store].[Store Name].Members, 5, [Unit Sales]),

{ [Store].CurrentMember } * TopCount( [Customers].[Name].Members, 5, ([Unit Sales], [Store].CurrentMember) ) ) on ROWS

from Sales

查询结果表展示：

查询结果图展示：

from Sales

9.显示四个季度所有品牌的销售情况，高亮显示各个季度销售组成最少10%的品牌。单元格属性是在查询中突出展示异常数据的便捷方式（如，改变字体风格或者颜色以吸引读者对重要信息的注意）。Listing9，通过向计算成员HLUnit Sales添加单元格属性Font_FLAGS以黑体显示各个季度销售居于最低10%的品牌。由于一个MDX只能设置一个单元格属性，所以只能通过条件逻辑判断是显示罗马字体还是黑体。在本例中，使用的条件逻辑是判断当前品牌与组成最低10%的所有品牌的交集是否为空。如果产生的交集为0，说明该品牌不再组成10%的品牌中，罗马字体显示；如果交集是1，说明该品牌在这10%中，黑体显示。

Listing_09.Highlighting Products in the Bottom 10 Percent.txt

说明：查出4个季度中，每个时期销售量在后10%的产品销售量，并显示为粗体

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not

IsEmpty([Time].CurrentMember)))'

set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'

member [Measures].[HLUnit Sales] as '[Unit Sales]',

FONT_FLAGS = 'iif( Count(Intersect( BottomPercent( [Product].[Brand Name].Members, 10, ([Unit Sales]) ), {[Product].CurrentMember})) = 0, 0, 1)'

select [Last4Quarters] on COLUMNS,

[Product].[Brand Name].Members on ROWS

from Sales

where ([HLUnit Sales])

cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS

附:

LISTING 1: Determining Products Sold in Each State

说明：

with set [SoldInUSA] as 'Filter([Product].[Brand Name].Members, Not IsEmpty( ([USA], [Unit Sales]) ))' member [Measures].[SoldInState] as 'iif( IsEmpty(([Product].CurrentMember, [Unit Sales],

[Customers].CurrentMember)), "No","Yes" )'

select [USA].children on COLUMNS,

[SoldInUSA] on ROWS

from Sales

where ([SoldInState])

LISTING 2: Determining Top 10 Product Categories

说明：

select {[Unit Sales]} on COLUMNS,

TopCount( [Product].[Product Category].Members, 10, ([Unit Sales]) ) on ROWS

from Sales

Listing_03.Determining Brands Sold During the Past Three Quarters.txt

说明：在过去三个季度里都存在销售量的商品销售记录

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)),1)' set [Last3Quarters] as ' [LastQuarter].item(0).item(0).Lag(2) : [LastQuarter].item(0).item(0)'

select [Last3Quarters] on COLUMNS,

Non Empty Union(Descendants( [Food], [Product].[Brand Name] ), Descendants( [Drink],[Product].[Brand Name] )) on ROWS

from Sales

Listing_04.Determining Recent Trends for Best-Selling Brands.txt

说明：查出最近6个月销售趋势最好的前10个商品及销售量

with set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'

set [LastMonth] as 'Tail(Filter([Time].[Month].Members, Not IsEmpty([Time].CurrentMember)),1)'

set [Last6Months] as ' [LastMonth].item(0).item(0).Lag(6) : [LastMonth].item(0).item(0)'

select [Last6Months] on COLUMNS,

[TenBest] on ROWS

from Sales

Listing_05.Determining Brands that Make Up 80 Percent of Sales.txt

说明：找出组成销售额80%的商品销售及其记录；

select {[Unit Sales]} on COLUMNS,

TopPercent([Product].[Brand Name].Members, 80, [Unit Sales]) on ROWS

from Sales

Listing_06.Determining Brands That Make Up the Bottom 20 Percent of Sales.txt

说明：按销售量排序，找出组成20%销售量的商品销售记录

select {[Unit Sales]} on COLUMNS,

Non Empty BottomPercent([Product].[Brand Name].Members, 20, [Unit Sales]) on ROWS

from Sales

Listing_07.Determining the Top Five Stores and the Top Five Customers.txt

说明：查出销售量最好的前5名店和每个店的前5个顾客及其销售记录

select {[Unit Sales]} on COLUMNS,

Generate( TopCount([Store].[Store Name].Members, 5, [Unit Sales]),

{ [Store].CurrentMember } * TopCount( [Customers].[Name].Members, 5, ([Unit

Sales], [Store].CurrentMember) ) ) on ROWS

from Sales

Listing_08.Determining Two Top-Selling Products.txt

说明：查出每种产品大类前2名产品小类型号的销售记录，以及小类型号占大类的百分比

with member [Measures].[PercTotalSales] as

' Sum( TopCount([Product].CurrentMember.Children,2,[Unit Sales]),[Unit Sales])

/([Product].CurrentMember, [Unit Sales])',

FORMAT_STRING = '##.0%'

select [Store].[(All)].Members on COLUMNS,

Generate( [Product].[Brand Name].Members,

Union(

TopCount( [Product].CurrentMember.Children, 2, [Unit Sales] ) * {[Unit Sales]},

{ ([Product].CurrentMember, [PercTotalSales]) }

)

) on ROWS

from Sales

Listing_09.Highlighting Products in the Bottom 10 Percent.txt

说明：查出4个季度中，每个时期销售量在后10%的产品销售量，并显示为粗体

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'

set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'

member [Measures].[HLUnit Sales] as '[Unit Sales]',

FONT_FLAGS = 'iif(Count(Intersect( BottomPercent( [Product].[Brand Name].Members, 10,([Unit

Sales]) ), {[Product].CurrentMember})) = 0, 0, 1)'

select [Last4Quarters] on COLUMNS,

[Product].[Brand Name].Members on ROWS

from Sales

where ([HLUnit Sales])

cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS

Listing_https://www.360docs.net/doc/9c3604890.html,paring Sales with Those of Parallel Months.txt

说明：比较具有相同相对位置的时间点的销售量，例如今年7月和去年7月

with set [PromoMonths] as 'Filter([Time].[Month].Members, Not IsEmpty( ([Unit Sales], [Double Your Savings]) ) )' set [PromoRange] as 'Head( [PromoMonths] ).item(0).item(0) : Tail( [PromoMonths]).item(0).item(0)' member [Measures].[Uplift] as '([Unit Sales], [Double Your Savings])'

member [Measures].[This Quarter] as '[Unit Sales]'

member [Measures].[Last Quarter] as '( ParallelPeriod( [Time].[Quarter] ), [Unit Sales] )'

member [Measures].[Growth] as ' [This Quarter] - [Last Quarter]'

select [PromoRange] on Columns,

{ [This Quarter], [Last Quarter], [Growth], [Uplift] } on Rows

from [Sales]

Listing_11.Determining Sales That Exceed Store Cost by 160 Percent.txt

说明：查出利润率在16%以上的产品及销售记录

with member [Measures].[SalesRatio] as '([Store Sales] - [Store Cost]) / [Store Cost]',

FORMAT_STRING = '##%'

select { [Store Sales], [Store Cost], [SalesRatio] } on COLUMNS,

Filter( [Product].[Brand Name].Members, [SalesRatio] > 1.60 ) on ROWS

from Sales

Listing_12.Determining Brands that Have Grown by More Than 50 Percent.txt

说明：找出最近一季度比前一季度销售量增长幅度大于50%的产品销售记录

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))' member [Measures].[CurrQSales] as '([LastQuarter].item(0).item(0), [Unit Sales])'

member [Measures].[PrevQSales] as '([LastQuarter].item(0).item(0).PrevMember, [Unit Sales])'

member [Measures].[Growth] as ' ([CurrQSales] - [PrevQSales]) / [PrevQSales]',

FORMAT_STRING='##%'

select { [PrevQSales], [CurrQSales], [Growth] } on COLUMNS,

Filter( [Product].[Brand Name].Members, [Growth] > 0.5 ) on ROWS

from Sales

Listing_13.Determing the Top 10 and Bottom 10 Product Brands.txt

说明：找出销售额在前、后10名的产品销售记录，并列出总排名，就是找出销售情况最好和最坏的产品

with set [OrderedBrands] as 'Order( [Product].[Brand Name].Members, [Unit Sales], BDESC )'

member [Measures].[Brand Rank] as 'Rank( [Product].CurrentMember, [OrderedBrands] )'

select {[Brand Rank], [Unit Sales]} on COLUMNS,

Union( Head( [OrderedBrands], 10 ), Tail( [OrderedBrands], 10 ) ) on ROWS

from Sales

Listing_https://www.360docs.net/doc/9c3604890.html,paring Product Trends.txt

说明：比较一下产品销售趋势

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))' set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)' member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'

member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'

select [Last4Quarters] on COLUMNS,

{ [Unit Sales], [GroupAvg], [AllAvg] } on ROWS

from Sales

where ([Ebony Plums])

Listing_15.Determining the Top 10 Middle-Tier Brands.txt

说明：查出一定条件下的前10名产品的销售记录，例如销售量在500到3000之间的

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))' set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)' member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'

member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'

member [measures].[abc] as '[Product].CurrentMember.uniquename'

select [Last4Quarters] on COLUMNS,

{ [Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] } on ROWS

from Sales

where ([Ebony Plums])

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'

set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'

member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'

member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'

member [measures].[abc] as '[Product].CurrentMember.uniquename'

member [measures].[abcd] as 'lookupcube("Trained Cube","MemberToStr([Customers].[All Customers].[Canada])")' select [Last4Quarters] on COLUMNS,

{[Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] ,[measures].[abcd] } on ROWS

from Sales

where ([Ebony Plums])