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

sexevil

　　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
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Month].[April 2002]
)
} ON ROWS
FROM [Step-by-Step]
;
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
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
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 [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]
;
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 [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]
;
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 [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]
;
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
{([Measures].[Reseller Sales Amount])} ON COLUMNS,
{[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]
;
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 [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]
;
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
{([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]为基础，在月等级上，返回指定的所有结果集
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 [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]
;
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 [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]
;
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 [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]
;
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 [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]
;
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
{
([Measures].[Reseller Sales Amount])
} ON COLUMNS,
{
Descendants(
[Date].[Calendar].[CY 2003],
[Date].[Calendar].[Month],
SELF
)
} ON ROWS
FROM [Step-by-Step]
;
　　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 [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]
;
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 [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]
;
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 [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])
;
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）