MDX基本语法

最新推荐文章于 2024-05-21 09:37:54 发布

sgmcumt

最新推荐文章于 2024-05-21 09:37:54 发布

阅读量6k

点赞数 9

分类专栏： OLAP

本文链接：https://blog.csdn.net/sgmcumt/article/details/87437459

版权

OLAP 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MDX

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 个轴。

如果轴多于两个，就没法在平面上表示。如果维度多于两个，需要把多个维度（交叉后）放到一个轴上。

2.1 切片维度

切片（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 的维度）和切片维度上。

2.2 默认成员

如果一个维度既没有出现在轴维度上，也没有出现在切片维度上，就会用维度的（默认层次的）默认成员进行切片。一般维度的默认成员是"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 维度，其他没有出现在轴维度和切片维度上的维度也是一样的。

2.3 度量维度

度量维度（为了一致可以把度量看成一个维度：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 成员，默认成员就是第一个成员。

3 元组和集合

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

3.1 元组（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 字句也是一个元组，用以指定一个数据切片

3.2 集合

集合（Set）是零个、一个或多个元组的有序集合。集合最常用于在Mdx查询中定义轴维度和切片器维度，并且同样可能只具有单个元组或可能在某些情况下为空。在Mdx语法中，元组用花括号{}来构造集合。

不像数学上的集合，MDX 集合一个元组可以出现多次，而且顺序是重要的。

如{ [Customer].[MA],[Customer].[CT] }就表示一个集合，集合里是客户维度的两个成员。

一个集合中的所有元组必须有同样的维度性质，即所表示的维度及其顺序。

使用下面的集合将会报错，因为维度的顺序不一样：

{ ( [Time].[2005], [Measures].[Dollar Sales] ), ( [Measures].[Unit Sales], [Time].[Feb, 2005] ) }

很多函数都可以返回一个集合

4 维度成员

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

4.1 成员范围

冒号（:）语法可以表示成员范围。冒号前后是同一个层次的起点和终点两个成员。

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] } }

4.2 全部成员

大多时候需要得到一个维度、层次、层的全部成员，这个时候可以使用.Members 操作（函数）。比如[Time].[Years].Members 可以得到所有年份。

SELECT
    { [Time].[Years].Members} ON COLUMNS,
    { [Product].[Line].Members} ON ROWS
FROM [SteelWheelsSales]
WHERE [Measures].[sales]

这个语句查询所有年份、所有产品线的销售额，把年份放在列上，产品线放在行上。

4.3 下级成员

有时候需要得到某个成员的下一层次的全部成员，这是需要用**.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 则不会包含）。

4.4 成员属性

有时要获取维度成员的属性（维表上的某些列），这时可以使用 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])

5 集合操作

5.1 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 可用于任何轴上。

5.2 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 嵌套。

5.3 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
)

5.4 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]

6 计算成员

在 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])

6.1 公式优先级（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])

7 命名集合

命名集合（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])

8 函数

http://mondrian.pentaho.com/documentation/mdx.php
列出一些重要的，按返回类型来分类。

8.1 成员函数

.currentMember
.parent
.prevMember/.nextMember
.firstChild/.lastChild
.firstSibling/.lastSibling
Ancestor(, )
Ancestor(, )
LAG 返回当前成员开始往前数的本层的第几个成员.
.Lag(n) n是索引，0 是它本身，1是前一个(.prevMember)
LEAD 类似 Lag()，但方向相反
OpeningPeriod 返回某个层次上第一个后代成员。

语法：

OpeningPeriod([[, ]])。

ClosingPeriod 返回某个层次上最后一个后代成员
PARALLELPERIOD 返回一个成员同层次对应位置的成员
ParallelPeriod([[, [, ]]])在时间维度上取同期(如上年同期)等的时候需要用到它。

8.2 集合函数

前面介绍的 members、children、descendants、crossJoin、filter、order 都是集合函数

8.3 union

合并两个集合。语法：Union(set1,set2[, ALL]) All 标志指示保留重复元素

8.4 Except

从set1里去除set2的元素，即求两个集合的差。Except(set1,set2[, ALL])

8.5 Head/Tail

返回集合Head/Tail元素。 Head/Tail(set[, ))。

8.6 .SIBLINGS

返回成员的兄弟成员，包括它自己。.Siblings。

8.7 .MEMBERS

返回维度/层次的成员。.Members

8.8 DESCENDANTS

返回成员的后代成员。Descendants (member, [level[,flag]])

flag 可以是：SELF、BEFORE、SELF_BEFORE_AFTER、LEAVES、AFTER、SELF_AND_BEFORE、SELF_AND_AFTER。

8.9 DrillDownLevel(set,[level])

下钻（一级）成员。

8.10 DrillDownLevelBottom(set,index[,level][,numeric])

下钻最下一级成员。

8.11 DrillDownLevelBTop(set,index[,level][,numeric])

下钻最上一级成员。

8.12 DrillDownMember

下钻集合2中的成员.DrillDownMember(set1,set2[,Recursive])

8.13 TopCount 返回前n个数据的集合

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

8.14 统计函数

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
返回维度、层次等的名称。语法：.Name

PROPERTIES

返回成员的属性值。语法：.properties(<属性名>)

其他函数

这里是一些返回类型不定的函数。

LiF

根据条件返回值，类似Excel 的If 函数。语法：lif(<布尔表达式>, <值1>, <值2>)。

ITEM

根据索引返回集合中元素。语法：item(set, )。
返回类型一般为元组。

sgmcumt

关注

9
点赞
踩
30

收藏

觉得还不错? 一键收藏
2
评论
MDX基本语法

文章目录MDX1 基本语法2 轴2.1 切片维度2.2 默认成员2.3 度量维度3 元组和集合3.1 元组（Tuple）3.2 集合4 维度成员4.1 成员范围4.2 全部成员4.3 下级成员4.4 成员属性5 集合操作5.1 NON EMPTY5.2 CROSS JOIN5.3 FILTER5.4 ORDER6 计算成员6.1 公式优先级（Solve Order）7 命名集合8 函数8.1 成员...
复制链接

扫一扫