高处不胜寒
-
个人空间
- 组别:新手上路
- 性别:
- 来自:
- 积分:7
- 帖子:28
- 注册:
2007-06-02
|
15个很具代表性的MDX查询语句 [转贴二]
05, 哪些产品品牌构成公司（指超市）的前80%的销售量？ TopPercent()函数与TopCount()函数类似，只是TopPercent()返回的是最少项,如本例返回组成unit sales 80%的最少项（换句话说，这些项是unit sales数值大的项）。Listing 5 在行显示产品品牌，列及对应区域显示Total unit 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
ROWSfrom Sales查询效果展示：  注： TopCount
| 从集合顶端开始返回指定数目的项，可以选择首先对集合排序。
| 例子
| SELECT
{[Measures].[Store Sales]
} ON
COLUMNS,
Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales])
ON
rows
from sales
| TopPercent
| 对集合排序，并返回顶端的 n
个元素，这些元素的累积合计至少为指定的百分比。
| 例子
| SELECT
{[Measures].[Store Sales]
} ON
COLUMNS,
TopPercent(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90, [store sales])
ON
rows
from sales
|
06. 那些产品构成销量总量最少的20%？查询Listing 6 用BottomPercent()返回仅构成Total unit sales 20%的最多的产品项，也就是说，这些产品的unit sales 最小。查询用关键字Non Empty排除了没有销售量的产品。需要注意的是，Non Empty跟Filter()与IsEmpty()的组合使用（见Listing 3）略有不同，因为Non Empty最用在某个轴（如rows）的所有项上。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
ROWSfrom Sales
查询结果表展示：
查询结果图展示：
07. 销量最好的五个商店是哪五个？这五个商店中消费最高的五位顾客？
查询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
查询结果表展示：
查询结果图展示：
注：
BottomPercent
| 对集合排序，并返回底端的 n
个元素，这些元素的累积合计至少为指定的百分比。
| 例子
| select {[Unit Sales]} on
COLUMNS,
Non
Empty
BottomPercent([Product].[Brand Name].Members, 10, [Unit Sales]) on
ROWS
from Sales
| Generate
| 将集合应用到另一集合的每个成员，然后用 union 运算合并所得的集合。
| 例子1
| SELECT
{[Measures].[Store Sales]
} ON
COLUMNS,
{ Generate({ USA, Canada }, Descendants(store.CurrentMember, [store state]))
}
ON
rows
from sales
| 例子2：
ca，wa是USA的，加all则简单复制
| SELECT
{[Measures].[Store Sales]
} ON
COLUMNS,
{ Generate({USA, Canada}, {ca, wa} ,all)
}
ON
rows
from sales
如果通过 CurrentMember，«Set1» 与 «set_expression» 无关，那么 Generate
生成 «set_expression» 所指的集合的简单复制，它包含的复制与 «Set1» 中的元组一样多。如果指定了可选的 ALL 标志，结果中将保留所有重复项。如果未指定 ALL，重复项将被删除。
| 例子3：
合并字符串
| with
member [Measures].[合字符串] as 'Generate({Time.allmembers}, Time.CurrentMember.name," and ")' SELECT
{ [Measures].[合字符串] } ON
COLUMNS,
{[Store].[All Stores]
}
ON
rows
from sales
| 例子4：
应用扩展
| with
member [Measures].[合字符串] as 'Generate({Time.[1997].children}, cstr((Time.CurrentMember, [Measures].[Unit Sales],store.[all stores]))," and ")' SELECT
{ [Measures].[合字符串] } ON
COLUMNS,
{[Store].[All Stores]
}
ON
rows
from sales
|
8. For each product brand, what are the two top-selling products and what percentage of total sales do they make up? To answer this question, Listing 8's relatively complicated query uses a combination of a calculated member and the Generate() function. The calculated member determines the percentage of the brand's total unit sales that a brand's top two products make up. The Generate() function searches the list of all brands and returns a set of each brand's top two products and the percent of total value that those products account for. 查出每一种品牌销售最好的两种产品的销量额，以及分别占销售总额的百分比。Listing8相对比较复杂，综合运用了计算成员和Generate()函数。计算成员确定了每个品牌销售最好的前两个产品占所在品牌unit sales总量百分比。Generate函数查询每个品牌并返回每个品牌下销售最好的两个产品，以及每个产品的销售额和百分比。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
ROWSfrom Sales
9. Show all the product brands for the past four quarters, highlighting brands that sold in the bottom 10 percent for that quarter. Cell properties are a convenient way to perform exception highlighting (i.e., changing the font or color to draw the user's attention to important information) in a query. In Listing 9, I added the cell property FONT_FLAGS to the calculated member HLUnit Sales to boldface the unit sales numbers in the bottom 10 percent of all product brands for that quarter. Because a cell property's value can be an MDX expression, you can perform conditional logic to determine whether the font will be roman or boldface. In this case, the condition logic determines whether the current brand is in the bottom 10 percent by doing a set intersect with the full list of brands in the bottom 10 percent. If the intersect yields a count of 0, the brand isn't among the bottom members and will appear in a roman font. If the count is 1, the brand is among the bottom 10 percent, and the value will appear in boldface.
显示四个季度所有品牌的销售情况，高亮显示各个季度销售组成最少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
标签: sqlserver 
|
