MDX相关语法

淩亂°似流年 2022-04-02 03:46 286阅读 0赞

MDX（Multidimensional Expressions）是多维 数据库（OLAP 数据库）的查询语言.  
Mondrian 会解析MDX，转换成SQL 来查询关系数据库（可能是多条查询）。  
MDX 的内容很多，功能强大，这里只介绍最基础和最重要的部分。

1 基本语法

以下是里两条MDX 查询语句及其查询结果  
ð 语句1：  
SELECT  
\{ \[Measures\].\[Dollar Sales\], \[Measures\].\[Unit Sales\] \} on columns,  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\] \} on rows  
FROM \[Sales\]  
WHERE (\[Customer\].\[MA\])

语句 2  
SELECT  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\], \[Time\].\[Q3, 2005\] \} on columns,  
\{ \[Customer\].\[MA\], \[Customer\].\[CT\] \} on rows  
FROM Sales  
WHERE ( \[Measures\].\[Dollar Sales\] )  
第一条语句查询对\[Customer\].\[MA\]这个客户在 2005 年第一、第二季度的销售额、报表期销售额。  
第二条语句查询对\[Customer\].\[MA\], \[Customer\].\[CT\]这两个客户在 2005 年前三个季度的销售额。  
可以看到，mdx 有类似 sql 的结构，同样有 select、from、where 这三部分。但也有很多不同。

Select 字句指定一个集合，把它放到某个轴上。  
From 字句说明要从哪个数据立方体来查询。  
方括号（\[\]）用于维度名、层次名、维度成员名，避免名字和函数混淆（函数名  
是不加方括号的）。

Where 字句指定切片，即对不出现在轴上的维度的成员的限定。  
Mdx 没有 group by 字句。其实分组是隐含的。  
Mdx 没有 order by 字句。排序只会对某个轴进行，通过使用排序函数。  
和 sql 一样，mdx 也是不区分大小写的，并且可以随意分行。  
Mdx 中也可以包含注释，除了支持 sql 的--注释外还支持//和/\* ... \*/注释。  
2 轴

用 on \{axis\}语法来把维度分配到轴（Axis，复数 Axes）上，一个查询可以有多个轴。  
不同轴用逗号分隔，分配的顺序是没关系的。但如果把轴调换（如 A on columns, B on rows 改成 A on rows, B on columns），结果的行和列也会转置过来。  
轴用 axis(0)，axis(1)，axis(2)...表示，前五个轴可以使用别名 Columns，Rows，Pages，Chapters，Sections。因此 on Columns 等价于 on axis(0)。超过 5 个轴时只能用 axis(5)，axis(6)...来表示（极少会需要这么多的轴）。  
很多实现（包括 Mondrian）支持仅用数字表示轴，因此 on Columns 可以写成 on 0。axis(0)和别名表示可以混用，例如下面语句是可以的：  
SELECT  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\] \} on axis(0),  
\{ \[Customer\].\[MA\], \[Customer\].\[CT\] \} on rows  
FROM Sales  
轴必须从 0 开始，并且连续，不能跳过。下面的是不可以的：  
SELECT  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\] \} on rows,  
\{ \[Customer\].\[MA\], \[Customer\].\[CT\] \} on pages  
FROM Sales  
错误，跳过了轴 axis(0)（columns） 。

在大多查询中，轴一般是两个。一个轴也可以，甚至 0 个轴。  
如果轴多于两个，就没法在平面上表示。如果维度多于两个，需要把多个维度（交叉后）放到一个轴上。

3 切片维度

切片（Slice）维度就是出现在 MDX 语句 WHERE 子句中的维度，跟 SQL 一样，表示对数据集的限制。例如 MDX 语句：  
SELECT  
\{\[Product\].\[All Products\].\[Food\], \[Product\].\[All Products\].\[Drink\]\} ON COLUMNS  
FROM \[Sales\]  
WHERE \[Time\].\[Year\].\[2005\]  
限制了查询的数据范围，只限于 2005 年。语法和 SQL 不一样，\[Time\].\[Year\].\[2005\]（是一个元组）本身就表示了一个条件。这可以看成对数据立方体从某个方向进行切片（从 Time维度的方向）得到一个子立方体，因此叫切片。  
切片维度不会出现在轴上。上面的时间维度不会出现在轴上。  
一个维度不能同时出现在轴维度（SELECT 的维度）和切片维度上。

默认成员

如果一个维度既没有出现在轴维度上，也没有出现在切片维度上，就会用维度的（默认层次的）默认成员进行切片。一般维度的默认成员是"All xxx" ，因此默认是对这个维度所有成员的数据进行聚集操作。例如：  
SELECT \{\[Time\].\[Year\].Members \} ON COLUMNS  
FROM \[Sales\]  
Product 维度没有出现在轴维度和切片维度上。如果 Product 的默认成员是\[All Products\]，就会查询所有产品的销售额汇总，符合人的习惯。上面语句相当于：  
SELECT \{\[Time\].\[Year\].Members \} ON COLUMNS  
FROM \[Sales\]  
WHERE \[Product\].\[ All Products\]  
维度有一个函数 defaultMember 可以返回维度的默认成员，因此也相当于：  
SELECT \{\[Time\].\[Year\].Members \} ON COLUMNS  
FROM \[Sales\]  
WHERE \[Product\]. defaultMember  
除了 Product 维度，其他没有出现在轴维度和切片维度上的维度也是一样的。

度量维度（为了一致可以把度量看成一个维度：Measures 维度）是没有"All xxx"成员的，它的默认成员可以明确设置，如果没设置，就是第一个度量。如果默认度量是\[Store Sales\]，  
下面的查询  
SELECT \{\[Time\].\[Year\].Members \} ON COLUMNS  
FROM \[Sales\]  
和  
SELECT \{\[Time\].\[Year\].Members \} ON COLUMNS  
FROM \[Sales\]  
WHERE \[Measures\]. \[Store Sales\]  
是等价的。  
一个维度的默认成员、是否有 All 成员（一般都应该有），是可以在 Schema 文件中设置的。  
如果没有明确设置默认成员，默认成员就是 All 成员，如果没有 All 成员，默认成员就是第一个成员。

4、元组和集合

元组和集合是 MDX 中的两种数据类型，也是 MDX 语句的构件。

元组

元组（Tuple）就是一个或多个维度的成员的组合  
当一个元组有多个维度时，必须用括号括起来  
(\[Customer\].\[Chicago, IL\], \[Time\].\[Jan, 2005\])  
一个元组可以代表立方体的一个切片  
以这种语法构造的元组可以直接用于  
SELECT  
\{ ( \[Time\].\[2005\], \[Measures\].\[Dollar Sales\],  
( \[Time\].\[Feb, 2005\],\[Measures\].\[Unit Sales\] )  
\} ON COLUMNS ,  
\{ \[Product\].\[Tools\], \[Product\].\[Toys\]\} ON ROWS  
FROM \[Sales\]  
元组不能嵌套

where 字句也是一个元组，用以指定一个数据切片

集合

集合（Set）只是一个一批已排序的元组。一个集合可以有一个元组，多个元组，或者是空的。不像数学上的集合，MDX 集合一个元组可以出现多次，而且顺序是重要的。  
通常的指定集合的方式是把一个元组列表用花括号括起来。如\{ \[Customer\].\[MA\],  
\[Customer\].\[CT\] \}就表示一个集合，集合里是客户维度的两个成员。  
一个集合中的所有元组必须有同样的维度性质，即所表示的维度及其顺序。  
使用下面的集合将会报错，因为维度的顺序不一样：  
\{ ( \[Time\].\[2005\], \[Measures\].\[Dollar Sales\] ), ( \[Measures\].\[Unit Sales\], \[Time\].\[Feb, 2005\] ) \}  
很多函数都可以返回一个集合

5、维度成员

要把维度成员放在轴上，可以列举维度的成员，例如\{ \[Customer\].\[MA\], \[Customer\].\[CT\] \}。也可以通过范围语法或一个函数得到成员的集合。

成员范围

冒号（:）语法可以表示成员范围。冒号前后是同一个层次的起点和终点两个成员。  
SELECT  
\{ \[Time\].\[2003\] : \[Time\].\[2008\] \} on columns,  
\{ \[Product\].\[Drinks\] : \[Product\].\[Bread\] \} on rows  
FROM \[Sales\]  
时间维度表示 2003 年到 2008 年（6 个成员），产品维度表示从 Drinks 到 Bread。这通常跟排序方式有关。

集合里可以包含子集合。例如下面集合，包含 2001 年的前三个月跟后三个月。  
\{ \{ \[Time\].\[January-2001\] : \[Time\].\[March-2001\] \} ,\{ \[Time\].\[October-2001\] : \[Time\].\[December-2001\] \} \}

全部成员

大多时候需要得到一个维度、层次、层的全部成员，这个时候可以使用.Members 操作（函数）。比如\[Time\].\[Years\].Members 可以得到所有年份。  
SELECT  
\{ \[Time\].\[Years\].Members\} ON COLUMNS,  
\{ \[Product\].\[Line\].Members\} ON ROWS  
FROM \[SteelWheelsSales\]  
WHERE \[Measures\].\[sales\]  
这个语句查询所有年份、所有产品线的销售额，把年份放在列上，产品线放在行上。

下级成员

有时候需要得到某个成员的下一层次的全部成员，这是需要用.Children 函数。这在下钻操作时经常用到。  
例如要得到产品线 Classic Cars 下的所有产品，可以这样\[Product\].\[Line\].\[Classic  
Cars\].Children。\[Product\].\[Line\].\[Classic Cars\]是 Product 维度 Line 层的一个成员。  
.Children 只能得到直接下级成员，如果需要多级，需要使用 Descendants()函数。语法是：  
Descendants (member \[, \[ level \] \[, flag\]\] )  
Descendants 返回 member 成员下 level 层的成员，可选标志 flag 有多个选项，以设置包含最下一层上面的哪些层的成员。  
以下是一个例子，查询 Tools 和 Toys 两类产品在 2005 年各月的销售额，因为年和月两个层次中间有一个季度层次，所以不能用\[Time\].\[2005\].Children。  
SELECT  
\{ \[Product\].\[Tools\], \[Product\].\[Toys\] \} ON COLUMNS,  
Descendants (\[Time\].\[2005\],\[Time\].\[Month\],SELF\_AND\_BEFORE) ON ROWS  
FROM Sales  
WHERE \[Measures\].\[Dollar Sales\]  
flag 设为 SELF\_AND\_BEFORE，可以看到行上包含了 2005 年和各个季度（如果设为  
SELF 则不会包含） 。

成员属性

有时要获取维度成员的属性（维表上的某些列），这时可以使用 dimemsion properties子句。dimemsion关键字可以省略。  
以下查询同时获取客户所在地的邮编属性  
SELECT  
\{ \[Customer\].\[Akron, OH\].Children \}  
DIMENSION PROPERTIES \[Customer\].\[Zip Code\]  
on columns,  
\{ \[Product\].\[Category\].Members \} on rows  
FROM Sales  
WHERE (\[Measures\].\[Units Sold\], \[Time\].\[July 3, 2005\])

6、集合操作

NON EMPTY

在多维空间，数据很多时候是稀疏的。比如：比如，不是每一个产品都销售给了所有的客户，不是每一个客户在每个时期都购买了产品。如果按维度所有成员交叉得出报表，就会有很多空行、空列。  
要从查询结果去掉这些空行  
SELECT  
\{ \[Time\].\[Jan,2005\],\[Time\].\[Feb,2005\] \} ON COLUMNS ,  
NON EMPTY \{ \[Product\].\[Toys\], \[Product\].\[Toys\].Children \} ON ROWS  
FROM Sales  
WHERE (\[Measures\].\[Dollar Sales\], \[Customer\].\[TX\])  
这样空行就去掉了。non empty 可用于任何轴上。

CROSS JOIN

很多时候，我们需要对两个不同的集合进行交叉，也就是要得到两个集合成员的所有组合。CrossJoin()函数就是用来得到组合的最直接方式，它的语法是 CrossJoin (set1, set2)  
以下语句在每个季度下分出两个度量  
SELECT  
CrossJoin (  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\]\},  
\{ \[Measures\].\[Dollar Sales\], \[Measures\].\[Unit Sales\] \}  
) ON COLUMNS,  
\{ \[Product\].\[Tools\], \[Product\].\[Toys\] \} ON ROWS  
FROM Sales  
CrossJoin 的结果是一个集合。因此支持CrossJoin 嵌套。

FILTER

Filter 函数用来筛选一个集合，它以一个集合和一个 boolean 表达式为参数  
Filter (set，boolean-expression)。  
例如，以下表达式会返回关联的产品销售额至少为500 的产品分类的集合。  
Filter (  
\{ \[Product\].\[Product Category\].Members \},  
\[Measures\].\[Dollar Sales\] >= 500  
)  
要求销售额至少为 150 并且销售额要至少在成本的1.2 倍以上  
Filter (  
\{ \[Product\].\[Product Category\].Members \},  
(\[Measures\].\[Dollar Sales\] >= 1.2 \*\[Measures\].\[Dollar Costs\])  
AND \[Measures\].\[Dollar Sales\] >= 150  
)

ORDER

Order()函数用于对一个集合进行排序，语法：  
Order (set1, expression\[,ASC| DESC | BASC | BDESC\])  
SELECT  
\{ \[Measures\].\[Dollar Sales\] \} on columns，  
Order (  
\[Product\].\[Product Category\].Members,  
\[Measures\].\[Dollar Sales\],  
BDESC  
) on rows  
FROM \[Sales\]  
WHERE \[Time\].\[2004\]

7、计算成员

在 sql 中可以增加计算出来的列，MDX 中同样也可以，在 MDX 中叫计算成员（CalculatedMember）。因为MDX 操作的是多维数据，计算成员实际是给一个维度增加成员。  
语法：  
with  
member 成员标识 as '表达式' \[, 属性...\]  
select ...  
表达式用单引号引注。  
以下例子增加一个新的度量\[Avg Sales Price\]  
WITH  
MEMBER \[Measures\].\[Avg Sales Price\] AS  
'\[Measures\].\[Dollar Sales\] / \[Measures\].\[Unit Sales\]'  
SELECT  
\{ \[Measures\].\[Dollar Sales\], \[Measures\].\[Unit Sales\],  
\[Measures\].\[Avg Sales Price\]  
\} on columns,  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\] \} on rows  
FROM Sales  
WHERE (\[Customer\].\[MA\])

公式优先级（Solve Order）

当不止一个维度增加了计算成员时，由于每个维度的成员都有计算公式，在这些维度的交叉点上，就可以有多种计算顺序。这时候就不需要考虑公式优先级的问题。因此引入了 SOLVE\_ORDER 属性  
WITH  
MEMBER \[Measures\].\[Avg Sales Price\] AS  
'\[Measures\].\[Dollar Sales\] / \[Measures\].\[Unit Sales\]',  
SOLVE\_ORDER=0  
MEMBER \[Time\].\[Q1 to Q2 Growth\] AS  
'\[Time\].\[Q2, 2005\]- \[Time\].\[Q1, 2005\]',  
SOLVE\_ORDER=1  
SELECT  
\{ \[Measures\].\[Dollar Sales\], \[Measures\].\[Unit Sales\],  
\[Measures\].\[Avg Sales Price\]  
\} on columns,  
\{ \[Time\].\[Q1, 2005\], \[Time\].\[Q2, 2005\], \[Time\].\[Q1 to Q2 Growth\] \} on rows  
FROM \[Sales\]  
WHERE (\[Customer\].\[MA\])

8、命名集合

命名集合（Named Set）允许预先定义的一个集合，供后面的语句使用。语法和计算成员类似。  
with  
set 集合标识 as '集合表达式'  
select ...

WITH  
SET \[User Selection\] AS '\{ \[Product\].\[Action Figures\], \[Product\].\[Dolls\] \}'  
MEMBER \[Product\].\[UserTotal\] AS 'Sum ( \[User Selection\] )'  
SELECT  
\{ \[Time\].\[Jan, 2005\], \[Time\].\[Feb, 2005\] \} ON COLUMNS,  
\{ \[Product\].\[Toys\], \[User Selection\], \[Product\].\[UserTotal\] \} ON ROWS  
FROM Sales  
WHERE (\[Measures\].\[Unit Sales\])

9、函数

http://mondrian.pentaho.com/documentation/mdx.php

列出一些重要的，按返回类型来分类。

成员函数

.currentMember  
.parent  
.prevMember/.nextMember  
.firstChild/.lastChild  
.firstSibling/.lastSibling  
Ancestor(<Member>, <Level>)  
Ancestor(<Member>, <Numeric Expression>)

LAG 返回当前成员开始往前数的本层的第几个成员.  
<Member>.Lag(n)  
n是索引，0 是它本身，1是前一个(.prevMember)

LEAD 类似 Lag()，但方向相反

OpeningPeriod 返回某个层次上第一个后代成员  
语法：OpeningPeriod(\[<Level>\[, <Member>\]\])。  
ClosingPeriod 返回某个层次上最后一个后代成员

PARALLELPERIOD 返回一个成员同层次对应位置的成员  
ParallelPeriod(\[<Level>\[, <n>\[, <Member>\]\]\])在时间维度上取同期(如上年同期)等的时候需要用到它。

集合函数

前面介绍的 members、children、descendants、crossJoin、filter、order 都是集合函数  
union  
合并两个集合。语法：Union(set1,set2\[, ALL\]) All 标志指示保留重复元素  
Except  
从set1里去除set2的元素，即求两个集合的差。Except(set1,set2\[, ALL\])  
Head/Tail  
返回集合Head/Tail元素 。 Head/Tail(set\[, <count>))。  
.SIBLINGS  
返回成员的兄弟成员，包括它自己。<Member>.Siblings。  
.MEMBERS  
返回维度/层次的成员。<Dimemsion>.Members  
DESCENDANTS  
返回成员的后代成员。Descendants (member, \[level\[,flag\]\])  
flag 可以是：SELF、BEFORE、SELF\_BEFORE\_AFTER、LEAVES、AFTER、SELF\_AND\_BEFORE、SELF\_AND\_AFTER。

DrillDownLevel(set,\[level\])  
下钻（一级）成员。  
DrillDownLevelBottom(set,index\[,level\]\[,numeric\])  
下钻最下一级成员。  
DrillDownLevelBTop(set,index\[,level\]\[,numeric\])  
下钻最上一级成员。  
DrillDownMember  
下钻集合2中的成员.DrillDownMember(set1,set2\[,Recursive\])

TopCount 返回前n个数据的集合  
<Set> TopCount(<Set>, <Numeric Expression>, <Numeric Expression>)  
<Set> TopCount(<Set>, <Numeric Expression>)

（TopCount, BottomCount, TopPercent, Hierarchize ,etc.）

统计函数

count (set \[,INCLUDEEMPTY\])  
可选标记指定是包含无数据的元组  
Sum (set \[,数值表达式\]\])  
max/min/median/avg(set \[,数值表达式\]\])

逻辑函数

IS  
object is object2。  
例如：  
\[Jan 2000\].PrevMember IS NULL  
\[Jan 2000\].Level IS \[Time\].\[Month\]

ISEMPTY  
判断一个值是否为空。语法：IsEmpey(表达式)。

字符串函数

NAME  
返回维度、层次等的名称。语法：<Dimension/Hierarchy/Level/Member>.Name  
PROPERTIES  
返回成员的属性值。语法：<Member>.properties(<属性名>)

其他函数

这里是一些返回类型不定的函数。  
LiF  
根据条件返回值，类似Excel 的If 函数。语法：lif(<布尔表达式>, <值1>, <值2>)。  
ITEM  
根据索引返回集合中元素。语法：item(set, <index>)。  
返回类型一般为元组。  
  
感谢原文转自：https://blog.csdn.net/xbynet/article/details/52918706