《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的一个用户定义层次。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pd3PPBFcz3gRYt5Ad_PZRH9hwQUbQDt_mbWIdtm5RTRQcS1J9wYCToL9aCRTjsljr3hrnB3djvmk/2011-11-28%2016-45-16.png?psid=1
关于累积总和，我们首先用到一个函数PeriodsToDate（http://msdn.microsoft.com/zh-cn/library/ms144925.aspx）
例9-1
SELECT
{(.)} ON COLUMNS,
{
PeriodsToDate(
..,
...
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pW_oy_TLR76BfjDo1acoSfqY5swT67rRoeFl7horiC5DfovWRaNF7AxaSMtzqSyBA9wo6uiX5WEU/2011-11-28%2016-58-26.png?psid=1
例9-2
SELECT
{(.)} ON COLUMNS,
{...Members} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pt0BHo0yiwwGbqLtCFqE8NvV-U3OPY-C-Zm6q8vDJbza7Sd6kliBeaPf8cFaf6zlqekMwnCMGkeM/2011-11-28%2017-02-03.png?psid=1
例9-3
WITH
MEMBER . AS
Aggregate(
PeriodsToDate(
..,
..CurrentMember
),
(.)
)
SELECT
{
(.),
(.)
} ON COLUMNS,
{...Members} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pk2zhbt5Zb3cQfOcF6fplXUDW-SUv6Y-S9OFV5TtSbO5z77MhKD4D1ZG1b9Bt683pm18IieV7MR8/2011-11-28%2017-04-51.png?psid=1
例9-4
WITH
MEMBER . AS
Aggregate(
PeriodsToDate(
..,
..CurrentMember
),
(.)
)
MEMBER . AS
Aggregate(
PeriodsToDate(
..,
..CurrentMember
),
(.)
)
SELECT
{
(.),
(.),
(.)
} ON COLUMNS,
{...Members} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1p-0ykKQ2xWJLghgXbmGIl02GqmY0NWKKnY4VCOdGmukuSNzY33wzzkpSScfH2giJopgOunuRYd0Q/2011-11-29%2011-24-23.png?psid=1
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 . AS
Aggregate(
Ytd(..CurrentMember),
(.)
)
MEMBER . AS
Aggregate(
Qtd(..CurrentMember),
(.)
)
SELECT
{
(.),
(.),
(.)
} ON COLUMNS,
{...Members} ON ROWS
FROM
;
结果同上。
period-to-date返回一个特定边界（如一季、一年等）的值。有时，你想计算所有统计日期内的一个精确值，这就是所谓的初始日期（Inception-to-Date）值。你可以引用Null成员，例如Null: ..CurrentMember将强制AS使用前一个成员到当前时间成员所在级别上的第一个成员的边界（Range），下面的查询与上例类似
例9-6
WITH
MEMBER . AS
Aggregate(
PeriodsToDate(
..[(All)],
..CurrentMember
),
(.)
)
MEMBER . AS
Aggregate(
NULL:..CurrentMember,
(.)
)
SELECT
{
(.),
(.),
(.)
} ON COLUMNS,
{...Members} ON ROWS
FROM
;
2、计算滚动平均值（Calculating Rolling Averages）
MDX中计算滚动平均值，使用LastPeriods（http://msdn.microsoft.com/zh-cn/library/ms145588.aspx）
例9-7
SELECT
{(.)} ON COLUMNS,
{
LastPeriods(
3,
...
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pwhkqfwjGwT-xJ0LNiLVU1HoNBOolKOHAhXxhV1p3i4RpGDUL3x4YzlEzMKHsuHfJoHQGe4U4qSk/2011-11-29%2012-00-13.png?psid=1
前推三个月。
例9-8
SELECT
{(.)} ON COLUMNS,
{...Members} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1poSB14O9TJHEGr4-ohArFswkv3b_PZKx0BUWkW8fp8w89CniudjfDzpeVa5u321BejKhRqN2dWEo/2011-11-29%2012-02-46.png?psid=1
下面我们计算滚动平均值
例9-9
WITH
MEMBER . AS
Avg(
LastPeriods(
3,
..CurrentMember
),
(.)
)
SELECT
{
(.),
(.)
} ON COLUMNS,
{...Members} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pXmg3ezoXFkb1DWsQMmvXPqfaUTwBfHFKqXLjvYd86a7L__MU0WPQ8rksCPiSk0h5yIEtnt3Zh0I/2011-11-29%2013-08-48.png?psid=1
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
{(.)} ON COLUMNS,
{
Descendants(
...,
..,
SELF
)
} ON ROWS
FROM
;
上例中我们用到了前文提到过的Descendants（http://msdn.microsoft.com/zh-cn/library/ms146075.aspx）,一个计算后裔的函数。计算的是以为基础，在月等级上，返回指定的所有结果集
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pHq_a5OSKd80jfM9KmRVkT2kpU5tIPi8IF9pE2sWY458UsPdsc0aYYTODFf_JoLiZ_nKEdPIUZkM/2011-11-29%2013-22-28.png?psid=1
下面我们构造一个虚拟Period
例9-11
WITH
MEMBER . AS
ParallelPeriod(
..,
1,
..CurrentMember
).Name
SELECT
{
(.),
(.)
} ON COLUMNS,
{
Descendants(
...,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pFDruqF5unGQYDOt2aAoKhD4wqTJF8dSv8JbeSNL_8t3i-C7uSC0IXir0Tq8xtsS-DyVdnN1io64/2011-11-29%2013-27-29.png?psid=1
在此基础上，我们实现了同比计算。
例9-12
WITH
MEMBER . AS
(
ParallelPeriod(
..,
1,
..CurrentMember
),
.
)
,FORMAT="Currency"
SELECT
{
(.),
(.)
} ON COLUMNS,
{
Descendants(
...,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pwWAYI2slPttmBWvZkGyWGQ1ZGzBZn-Q_sJkHnJJRvOUAityVqDIUJgPBi1GSSvl_ScDOoHp0bfc/2011-11-29%2013-41-33.png?psid=1
下面我们做一个小小的改动，以显示同比增长率
例9-13
WITH
MEMBER . AS
(
ParallelPeriod(
..,
1,
..CurrentMember
),
.
)
,FORMAT="Currency"
MEMBER . AS
(
(.)-
(.)
) /
(.)
,FORMAT="Percent"
SELECT
{
(.),
(.),
(.)
} ON COLUMNS,
{
Descendants(
...,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pcUBGeJx1YiGOmcBIRa-r7p5fSFipg0gEB4nS7CUAiHxTPgVb0JQHDVOk1P-Lpy6qgh4ecTh_8oY/2011-11-29%2013-46-17.png?psid=1
一个忠告：到目前为止，我们再一次领会到基于时间的函数并不是时间感知，而是为了具体的功能而简单地进行导航。
我们通过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 . AS
Cousin(
..CurrentMember,
Ancestor(
..CurrentMember,
..
).Lag(1)
).Name
SELECT
{
(.),
(.)
} ON COLUMNS,
{
Descendants(
...,
..,
SELF
)
} ON ROWS
FROM
;
输出结果同例9-11
例9-15
WITH
MEMBER . AS
ParallelPeriod(
..,
1,
..CurrentMember
).Name
SELECT
{
(.),
(.)
} ON COLUMNS,
{
Descendants(
...[CY 2002],
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pYavtR0tcEUv3bKq3nojYZFUaYv_gRyNh7krKeVM1ZTnPAr2MU1EDNhmPQQoW9X4B6m_9iA6oP_U/2011-11-29%2014-03-38.png?psid=1
出现Null的原因是对应的2001年的统计数据是从July开始的，所以产生了整体的错位。这个问题的解决方案在哪里？目前还真没有。惟一能提醒大家的是：在计算类似功能时一定要先控制好数据的边界范围。
3、组合时间指标（Combining Time-Based Metrics）
我们基于一个实例来说明。
例9-16
SELECT
{
(.)
} ON COLUMNS,
{
Descendants(
..,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1paFA-0RKjdkJjb_g22ISgFXjldtkfJ5oBmlbyj5PD1vR_iEF7kP9ffrJfA_qTBBFKoX7-EntQE40/2011-11-29%2014-15-44.png?psid=1
例9-17
WITH
MEMBER . AS
Aggregate(
PeriodsToDate(
..,
..CurrentMember
),
(.)
)
,FORMAT="Currency"
SELECT
{
(.),
(.)
} ON COLUMNS,
{
Descendants(
..,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pMa6MTAvCRGSaumI5n0dqYFTkiPE9dsYImxlA5iFkLe-Zapjw-4Ec1oON-Fh7MLrVtyOs4ZSpTqo/2011-11-29%2014-17-44.png?psid=1
以上例为基础，可以很轻易地计算同比的“差额”,请细心观察下例的用法。
例9-18
WITH
MEMBER . AS
(
ParallelPeriod(
..,
1,
..CurrentMember
),
.
)
,FORMAT="Currency"
MEMBER . AS
Aggregate(
PeriodsToDate(
..,
..CurrentMember
),
(.)
)
,FORMAT="Currency"
SELECT
{
(.),
(.),
(.)
} ON COLUMNS,
{
Descendants(
..,
..,
SELF
)
} ON ROWS
FROM
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pVH_BBbPLNSZact3WPIHlQDM4EIKle0gegRlCmpv7WfsOKca0DqzGdnwNQTLqJWyHiIXqQ5jcQIE/2011-11-29%2014-21-10.png?psid=1
最后，顺便介绍一下两个日期函数OpeningPeriod（http://msdn.microsoft.com/zh-cn/library/ms145992.aspx）和ClosingPeriod（http://msdn.microsoft.com/zh-cn/library/ms145584.aspx）。
例9-19
WITH
MEMBER . AS
(
OpeningPeriod(
..,
..CurrentMember
),
.
)
,FORMAT="Standard"
SELECT
{
(.),
(.)
} ON COLUMNS,
{..Members} ON ROWS
FROM
WHERE (..)
;
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://byfiles.storage.live.com/y1pXt9LgISAZZOS-151ZQwfgLdfc5ct_gjU3y94QQ3_7VzXA-OzlYu0jEOAmL_dv8P2WnDyG1Us5kw/2011-11-29%2014-28-27.png?psid=1
小结：
本文介绍Time相关的函数及其应用。
参考资源：
1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）
页:
[1]