MDX查询学习笔记2-常用函数

（一）排序函数

1，一维排序

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
order(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All])
},
([Measures].[Sales Amount]),
desc
)}on rows
from [Adventure Works DW2008R2]

2，多维排序

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
order(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All],
	[Dim Customer].[English Country Region Name].members-[Dim Customer].[English Country Region Name].[All])
},
([Measures].[Sales Amount]),
bdesc
)}on rows
from [Adventure Works DW2008R2]

（二）topcount函数

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
topcount(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All],
	[Dim Customer].[English Country Region Name].members-[Dim Customer].[English Country Region Name].[All])
},3,
([Measures].[Sales Amount])
)}on rows
from [Adventure Works DW2008R2]

（三）过滤函数

1，单值过滤

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
filter(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All],
	[Dim Customer].[English Country Region Name].members-[Dim Customer].[English Country Region Name].[All])
},
([Measures].[Sales Amount]>2000000)
)}on rows
from [Adventure Works DW2008R2]

2，多值组合

select
{
	([Measures].[Sales Amount]),
	([Measures].[Order Quantity])
}on columns,
non empty{
filter(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All],
	[Dim Customer].[English Country Region Name].members-[Dim Customer].[English Country Region Name].[All])
},
([Measures].[Sales Amount]>2000000)
)}on rows
from [Adventure Works DW2008R2]

3，多值过滤

select
{
	([Measures].[Sales Amount]),
	([Measures].[Order Quantity])
}on columns,
non empty{
filter(
{
	([Dim Product].[Product Line].members-[Dim Product].[Product Line].[All],
	[Dim Customer].[English Country Region Name].members-[Dim Customer].[English Country Region Name].[All])
},
([Measures].[Sales Amount]>2000000 and [Measures].[Order Quantity]>4000)
)}on rows
from [Adventure Works DW2008R2]

（三）集合函数

1，union函数

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
union(
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Australia])
),
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Canada])
))}
on rows
from [Adventure Works DW2008R2]

2，intersect函数

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
intersect(
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Australia])
),
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Canada])
))}
on rows
from [Adventure Works DW2008R2]

3，except函数

select
{
	([Measures].[Sales Amount])
}on columns,
non empty{
except(
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Australia])
),
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount],[Dim Customer].[English Country Region Name].&[Canada])
))}
on rows
from [Adventure Works DW2008R2]

（四）计算函数

1，sum函数

with
member [model name].[top 5 all sales] as
sum(
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount])),
[Measures].currentmember
)
select
{
	([Measures].[Sales Amount])
}on columns,
topcount(
{
	([Dim Product].[Model Name].[Model Name])
},5,
([Measures].[Sales Amount]))+
{
	([model name].[top 5 all sales])
}on rows
from [Adventure Works DW2008R2]

2，max函数

with
member [Measures].[max sales] as
max(
{
	([Dim Product].[Model Name].[Model Name].members)
},
([Measures].[Sales Amount])
)
select
{
	([Measures].[Sales Amount]),
	([Measures].[max sales])	
}on columns,
{
	([Dim Product].[Model Name].[Model Name].members)
}on rows
from [Adventure Works DW2008R2]

2，百分数

with
member [Measures].[percent of sales] as
([Measures].[Sales Amount]/[Measures].[max sales]),format_string="Percent"
member [Measures].[max sales] as
max(
{
	([Dim Product].[Model Name].[Model Name].members)
},
([Measures].[Sales Amount])
)
select
{
	([Measures].[Sales Amount]),
	([Measures].[percent of sales])	
}on columns,
{
	([Dim Product].[Model Name].[Model Name].members)
}on rows
from [Adventure Works DW2008R2]

3，百分数+排序

with
member [Measures].[percent of sales] as
([Measures].[Sales Amount]/[Measures].[max sales]),format_string="Percent"
member [Measures].[max sales] as
max(
{
	([Dim Product].[Model Name].[Model Name].members)
},
([Measures].[Sales Amount])
)
select

{
	([Measures].[Sales Amount]),
	([Measures].[percent of sales])	
}on columns,
non empty{
order(
{
	([Dim Product].[Model Name].[Model Name].members)
},
([Measures].[percent of sales]),
desc)}
on rows
from [Adventure Works DW2008R2]