《Microsoft SQL Server 2008 MDX Step by Step》学习笔记十：Time进阶

SQL Server 2008中SQL应用系列及BI笔记系列--目录索引

导读:在AS中，最重要的一个维度莫过于Time，围绕Time，MDX提价供了一系列函数来满足复杂的业务分析需求。本文主要内容包括：

■1、计算累积总和（Calculating an Accumulating Total）

■2、计算滚动平均值（Calculating Rolling Averages）

■3、执行阶段至阶段（Period-over-Period）的分析

■4、组合时间指标（Combining Time-Based Metrics）

本文所用数据库和所有源码，请到微软官网下载

 

1、计算累积总和（Calculating an Accumulating Total）

关于Time维度，最核心的一个层次是calendars，它允许你从更高级的颗粒度，如年，或者更小的颗粒度季、月、天等进行钻取。

基于标准Calendar的一个用户定义层次。

 

关于累积总和，我们首先用到一个函数PeriodsToDate（http://msdn.microsoft.com/zh-cn/library/ms144925.aspx）

例9-1

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Month].[April 2002]
)
} ON ROWS
FROM [Step-by-Step]
;

例9-2

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

例9-3

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

例9-4

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Quarter],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Quarter to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

MDX还提供了一系列的函数来进行PeriodsToDate的简化运算，如

Ytd（http://msdn.microsoft.com/zh-cn/library/ms146039.aspx）

Qtd（http://msdn.microsoft.com/zh-cn/library/ms145978.aspx）

Mtd（http://msdn.microsoft.com/zh-cn/library/ms144753.aspx）

Wtd（http://msdn.microsoft.com/zh-cn/library/ms144930.aspx）

所以，例9－4也可以这样实现:

例9-5

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
Ytd([Date].[Calendar].CurrentMember),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
Aggregate(
Qtd([Date].[Calendar].CurrentMember),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Quarter to Date Reseller Sales])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

结果同上。

 

period-to-date返回一个特定边界（如一季、一年等）的值。有时，你想计算所有统计日期内的一个精确值，这就是所谓的初始日期（Inception-to-Date）值。你可以引用Null成员，例如Null: [Date].[Calendar].CurrentMember将强制AS使用前一个成员到当前时间成员所在级别上的第一个成员的边界（Range），下面的查询与上例类似

例9-6

WITH
MEMBER [Measures].[Inception to Date Reseller Sales - PTD] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[(All)],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
MEMBER [Measures].[Inception to Date Reseller Sales - Range] AS
Aggregate(
NULL:[Date].[Calendar].CurrentMember,
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Inception to Date Reseller Sales - PTD]),
([Measures].[Inception to Date Reseller Sales - Range])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

 

2、计算滚动平均值（Calculating Rolling Averages）

MDX中计算滚动平均值，使用LastPeriods（http://msdn.microsoft.com/zh-cn/library/ms145588.aspx）

例9-7

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
LastPeriods(
3,
[Date].[Calendar].[Month].[January 2002]
)
} ON ROWS
FROM [Step-by-Step]
;

前推三个月。

例9-8

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

下面我们计算滚动平均值

例9-9

WITH
MEMBER [Measures].[Three Month Avg Reseller Sales Amount] AS
Avg(
LastPeriods(
3,
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Three Month Avg Reseller Sales Amount])
} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;

 

 

3、执行阶段至阶段（Period-over-Period）的分析

在统计中我们常听到两个概念：同比、环比。所谓同比就是今年第n月与去年第n月比。环比就是今年第n月与第n-1月或第n+1月比。在AS中，我们可以用ParallelPeriod（http://msdn.microsoft.com/zh-cn/library/ms145500.aspx）进行运算。

例9-10

SELECT
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

上例中我们用到了前文提到过的Descendants（http://msdn.microsoft.com/zh-cn/library/ms146075.aspx）,一个计算后裔的函数。计算的是以[CY 2003]为基础，在月等级上，返回指定的所有结果集

下面我们构造一个虚拟Period

例9-11

WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

在此基础上，我们实现了同比计算。

例9-12

WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales Amount])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

下面我们做一个小小的改动，以显示同比增长率

例9-13

WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
,FORMAT="Currency"
MEMBER [Measures].[Prior Period Growth] AS
(
([Measures].[Reseller Sales Amount])-
([Measures].[Prior Period Reseller Sales Amount])
) /
([Measures].[Prior Period Reseller Sales Amount])
,FORMAT="Percent"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Prior Period Reseller Sales Amount]),
([Measures].[Prior Period Growth])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

一个忠告：到目前为止，我们再一次领会到基于时间的函数并不是时间感知，而是为了具体的功能而简单地进行导航。

我们通过Cousin（http://msdn.microsoft.com/zh-cn/library/ms145481.aspx）

Ancestor（http://msdn.microsoft.com/zh-cn/library/ms145616.aspx）

Lag（http://msdn.microsoft.com/zh-cn/library/ms144866.aspx）

重新实现上例9-11。

例9-14

WITH
MEMBER [Measures].[x] AS
Cousin(
[Date].[Calendar].CurrentMember,
Ancestor(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Calendar Year]
).Lag(1)
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

输出结果同例9-11

例9-15

WITH
MEMBER [Measures].[x] AS
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
).Name
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[x])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[Calendar Year].[CY 2002],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

出现Null的原因是对应的2001年的统计数据是从July开始的，所以产生了整体的错位。这个问题的解决方案在哪里？目前还真没有。惟一能提醒大家的是：在计算类似功能时一定要先控制好数据的边界范围。

 

3、组合时间指标（Combining Time-Based Metrics）

我们基于一个实例来说明。

例9-16

SELECT
{
([Measures].[Reseller Sales Amount])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

例9-17

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

以上例为基础，可以很轻易地计算同比的“差额”,请细心观察下例的用法。

例9-18

WITH
MEMBER [Measures].[Prior Period Year to Date Reseller Sales] AS
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Year to Date Reseller Sales]
)
,FORMAT="Currency"
MEMBER [Measures].[Year to Date Reseller Sales] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Reseller Sales Amount])
)
,FORMAT="Currency"
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Year to Date Reseller Sales]),
([Measures].[Prior Period Year to Date Reseller Sales])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;

 

最后，顺便介绍一下两个日期函数OpeningPeriod（http://msdn.microsoft.com/zh-cn/library/ms145992.aspx）和ClosingPeriod（http://msdn.microsoft.com/zh-cn/library/ms145584.aspx）。

例9-19

WITH
MEMBER [Measures].[First Child Rate] AS
(
OpeningPeriod(
[Date].[Calendar].[Date],
[Date].[Calendar].CurrentMember
),
[Measures].[End of Day Rate]
)
,FORMAT="Standard"
SELECT
{
([Measures].[First Child Rate]),
([Measures].[End of Day Rate])
} ON COLUMNS,
{[Date].[Calendar].Members} ON ROWS
FROM [Step-by-Step]
WHERE ([Destination Currency].[Destination Currency].[Euro])
;

 

小结：

本文介绍Time相关的函数及其应用。

参考资源：

1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）