MDX|Урок 5.4: Скользящие средние и функция LastPeriods| Cлайдер

Введение

Представьте, что вы анализируете продажи магазина одежды. В декабре продажи взлетают из-за новогодних покупок, в январе резко падают, в феврале снова растут из-за Дня святого Валентина. Если принимать решения о закупках, глядя только на месячные показатели, можно совершить критические ошибки: перезаказать товар после декабрьского пика или недозаказать после январского спада. Именно поэтому профессиональные аналитики используют скользящие средние — они показывают истинный тренд, очищенный от случайных колебаний и сезонности.

Скользящие средние окружают нас повсюду. Метеорологи используют их для определения климатических изменений, отфильтровывая ежедневные колебания температуры. Трейдеры на фондовом рынке полагаются на индикаторы MA50 и MA200 для определения долгосрочных трендов. В экономике скользящие средние помогают видеть направление развития, игнорируя месячную волатильность. Даже фитнес-трекеры показывают среднее количество шагов за неделю, а не за день, чтобы дать более объективную картину активности.

В MDX функция LastPeriods предоставляет элегантный способ создания скользящих окон для анализа. В отличие от накопительных итогов, которые мы изучили в предыдущем уроке, скользящие средние движутся вместе с текущим периодом, всегда охватывая фиксированное количество последних периодов. Это делает их незаменимым инструментом для выявления трендов, прогнозирования и принятия взвешенных управленческих решений. В этом уроке мы научимся создавать различные типы скользящих средних и применять их для решения реальных бизнес-задач.

Теоретическая часть

A. Функция LastPeriods: синтаксис и механизм работы

Функция LastPeriods возвращает набор периодов, заканчивающийся указанным членом. Её полный синтаксис:

LastPeriods(Index_Expression, Member_Expression)

Параметры:

Index_Expression — количество периодов для включения в набор. Положительное число берет периоды назад, отрицательное — вперед
Member_Expression (необязательный) — конечный член набора. Если не указан, используется текущий член из контекста

Механизм работы LastPeriods (пошагово):

Определение конечной точки: Берется Member_Expression или текущий член
Отсчет периодов: От конечной точки отсчитывается Index_Expression периодов назад (или вперед для отрицательных значений)
Формирование набора: Создается упорядоченный набор от начальной до конечной точки включительно

Визуализация работы LastPeriods:

Timeline: [Jan][Feb][Mar][Apr][May][Jun][Jul][Aug][Sep]
                                ↑ CurrentMember (Jun)
                    ←-- LastPeriods(3) --→
                        [Apr][May][Jun]
                        
При LastPeriods(-3):
                                ↑ CurrentMember (Jun)
                                    ←-- LastPeriods(-3) --→
                                    [Jun][Jul][Aug]

Отличие от Lag и Lead:

Lag(n) возвращает один член, сдвинутый на n позиций назад
Lead(n) возвращает один член, сдвинутый на n позиций вперед
LastPeriods(n) возвращает набор из n членов, заканчивающийся текущим

⚠️ Частая ошибка: Путаница между LastPeriods и Lag

[Jun 2013].Lag(2) вернет [Apr 2013] — один член

LastPeriods(3, [Jun 2013]) вернет {[Apr 2013], [May 2013], [Jun 2013]} — набор из трех членов

Для скользящей средней нужен именно набор, поэтому используем LastPeriods

B. Типы скользящих средних

Тип

Формула

Преимущества

Недостатки

Применение

Simple MA (SMA)

Σ(xi)/n

Простота расчета, стабильность

Равный вес всем периодам, запаздывание

Долгосрочные тренды

Weighted MA (WMA)

Σ(xi×wi)/Σwi

Больший вес последним данным

Сложность расчета весов

Краткосрочное прогнозирование

Exponential MA (EMA)

α×xt + (1-α)×EMAt-1

Быстрая реакция на изменения

Сложность реализации в MDX

Трейдинг, быстрые рынки

Centered MA

Окно вокруг точки

Лучше для анализа исторических данных

Невозможна для последних периодов

Сезонная декомпозиция

C. Выбор окна (периода) усреднения

Размер окна критически влияет на результат анализа:

3-периодное окно: Быстро реагирует на изменения, подходит для оперативного управления
7-периодное окно: Недельное сглаживание для ежедневных данных
12-периодное окно: Устраняет месячную сезонность в годовых данных
30-периодное окно: Месячное сглаживание для ежедневных метрик

???? Pro Tip: Выбор оптимального окна

Для устранения сезонности: окно = длина сезонного цикла
Для выявления тренда: окно = 1/3 от длины анализируемого периода
Для прогнозирования: тестируйте разные окна на исторических данных

Правило большого пальца: чем больше шума в данных, тем больше должно быть окно

D. Комбинирование с другими функциями

LastPeriods становится мощным инструментом в сочетании с агрегатными функциями:

AVG(LastPeriods(n)) — классическая скользящая средняя
STDEV(LastPeriods(n)) — скользящее стандартное отклонение для оценки волатильности
MIN/MAX(LastPeriods(n)) — скользящие экстремумы для определения коридоров
COUNT(LastPeriods(n), EXCLUDEEMPTY) — количество непустых периодов в окне

E. Обработка граничных эффектов

Начало временного ряда: При расчете 12-месячной скользящей средней для января у нас нет 11 предыдущих месяцев. Решения:

Использовать адаптивное окно (сколько есть данных)
Возвращать NULL до накопления достаточных данных
Дополнять недостающие данные прогнозными значениями

Пропущенные периоды: Если в середине ряда есть периоды без данных:

EXCLUDEEMPTY исключит их из расчета
Интерполяция заполнит пропуски средними значениями
Можно использовать последнее известное значение (LOCF — Last Observation Carried Forward)

F. Производительность и оптимизация

Факторы, влияющие на производительность:

Размер окна (больше окно — больше вычислений)
Количество точек данных
Сложность агрегатной функции
Наличие индексов на временном измерении

Стратегии оптимизации:

Кэшируйте наборы LastPeriods в именованных множествах
Предрассчитывайте скользящие средние при ETL для часто используемых окон
Используйте материализованные агрегации для популярных комбинаций
Ограничивайте диапазон анализа разумными периодами

G. Применение в анализе трендов

Определение направления тренда:

Восходящий: текущее значение > скользящей средней
Нисходящий: текущее значение < скользящей средней
Боковой: значение колеблется вокруг средней

Точки пересечения (кроссоверы):

"Золотой крест": краткосрочная MA пересекает долгосрочную снизу вверх (сигнал к росту)
"Мертвый крест": краткосрочная MA пересекает долгосрочную сверху вниз (сигнал к падению)

???? Практический кейс: Сезонность в ритейле

Сеть магазинов одежды использует 4-недельную скользящую среднюю для планирования закупок и 52-недельную для стратегического планирования. Это позволяет:

Игнорировать эффект "черной пятницы" в недельных данных
Видеть реальный годовой тренд без сезонных пиков
Прогнозировать потребность в складских площадях
Оптимизировать график работы персонала

Практическая часть

Пример 1: Базовая скользящая средняя за 3 месяца

WITH 
-- Простая скользящая средняя за 3 месяца
MEMBER [Measures].[3-Month SMA] AS
    IIF(
        -- Проверяем, что у нас есть хотя бы 2 периода для осмысленного среднего
        COUNT(
            LastPeriods(3, [Date].[Calendar].CurrentMember),
            EXCLUDEEMPTY
        ) < 2,
        NULL,  -- Недостаточно данных для расчета
        AVG(
            LastPeriods(3, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount]
        )
    ),
    FORMAT_STRING = "Currency"

-- Отклонение от скользящей средней
MEMBER [Measures].[Deviation from SMA] AS
    IIF(
        ISEMPTY([Measures].[3-Month SMA]),
        NULL,
        [Measures].[Internet Sales Amount] - [Measures].[3-Month SMA]
    ),
    FORMAT_STRING = "Currency"

-- Процент отклонения
MEMBER [Measures].[Deviation %] AS
    IIF(
        ISEMPTY([Measures].[3-Month SMA]) OR [Measures].[3-Month SMA] = 0,
        NULL,
        [Measures].[Deviation from SMA] / [Measures].[3-Month SMA]
    ),
    FORMAT_STRING = "Percent"

-- Набор месяцев для анализа (первое полугодие 2013)
SET [H1 2013 Months] AS {
    [Date].[Calendar].[Calendar Year].&[2013].FirstChild.FirstChild,      -- Jan 2013
    [Date].[Calendar].[Calendar Year].&[2013].FirstChild.Children.Item(1), -- Feb 2013
    [Date].[Calendar].[Calendar Year].&[2013].FirstChild.LastChild,       -- Mar 2013
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(1).FirstChild, -- Apr 2013
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(1).Children.Item(1), -- May 2013
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(1).LastChild  -- Jun 2013
}

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[3-Month SMA],
     [Measures].[Deviation from SMA],
     [Measures].[Deviation %]} ON COLUMNS,
    [H1 2013 Months] ON ROWS
FROM [Adventure Works]

Пример 2: Адаптивная скользящая средняя с обработкой границ

WITH 
-- Адаптивная скользящая средняя (использует доступные периоды)
MEMBER [Measures].[Adaptive MA] AS
    -- Определяем размер окна динамически
    AVG(
        LastPeriods(
            MIN(3, COUNT(  -- Берем минимум из 3 или количества доступных периодов
                PeriodsToDate(
                    [Date].[Calendar].[Calendar Year],
                    [Date].[Calendar].CurrentMember
                )
            )),
            [Date].[Calendar].CurrentMember
        ),
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Currency"

-- Количество периодов, использованных в расчете
MEMBER [Measures].[Periods Used] AS
    COUNT(
        LastPeriods(
            MIN(3, COUNT(
                PeriodsToDate(
                    [Date].[Calendar].[Calendar Year],
                    [Date].[Calendar].CurrentMember
                )
            )),
            [Date].[Calendar].CurrentMember
        ),
        EXCLUDEEMPTY
    ),
    FORMAT_STRING = "#,##0"

-- Индикатор полноты данных
MEMBER [Measures].[Data Completeness] AS
    CASE
        WHEN [Measures].[Periods Used] = 3 THEN "Full Window"
        WHEN [Measures].[Periods Used] = 2 THEN "Partial (2 periods)"
        WHEN [Measures].[Periods Used] = 1 THEN "Single Period"
        ELSE "No Data"
    END

-- Валидированная скользящая средняя (NULL если недостаточно данных)
MEMBER [Measures].[Validated MA] AS
    IIF(
        [Measures].[Periods Used] >= 2,
        [Measures].[Adaptive MA],
        NULL
    ),
    FORMAT_STRING = "Currency"

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Adaptive MA],
     [Measures].[Periods Used],
     [Measures].[Data Completeness],
     [Measures].[Validated MA]} ON COLUMNS,
    -- Используем первые 6 месяцев для демонстрации адаптивности
    HEAD(
        DESCENDANTS(
            [Date].[Calendar].[Calendar Year].&[2013],
            [Date].[Calendar].[Month],
            SELF
        ),
        6
    ) ON ROWS
FROM [Adventure Works]

Пример 3: Взвешенная скользящая средняя

WITH 
-- Взвешенная скользящая средняя (больший вес последним периодам)
-- Веса: текущий месяц = 3, предыдущий = 2, позапрошлый = 1
MEMBER [Measures].[3-Month WMA] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,  -- Нужны все 3 периода для корректного взвешивания
        (
            -- Текущий месяц * 3
            ([Date].[Calendar].CurrentMember, [Measures].[Internet Sales Amount]) * 3 +
            -- Предыдущий месяц * 2
            ([Date].[Calendar].CurrentMember.Lag(1), [Measures].[Internet Sales Amount]) * 2 +
            -- Позапрошлый месяц * 1
            ([Date].[Calendar].CurrentMember.Lag(2), [Measures].[Internet Sales Amount]) * 1
        ) / 6  -- Сумма весов: 3 + 2 + 1 = 6
    ),
    FORMAT_STRING = "Currency"

-- Простая скользящая средняя для сравнения
MEMBER [Measures].[3-Month SMA] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,
        AVG(
            LastPeriods(3, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount]
        )
    ),
    FORMAT_STRING = "Currency"

-- Разница между WMA и SMA
MEMBER [Measures].[WMA vs SMA] AS
    [Measures].[3-Month WMA] - [Measures].[3-Month SMA],
    FORMAT_STRING = "Currency"

-- Индикатор тренда (WMA > SMA = восходящий тренд)
MEMBER [Measures].[Trend Direction] AS
    CASE
        WHEN ISEMPTY([Measures].[WMA vs SMA]) THEN "N/A"
        WHEN [Measures].[WMA vs SMA] > 1000 THEN "Strong Uptrend"
        WHEN [Measures].[WMA vs SMA] > 0 THEN "Uptrend"
        WHEN [Measures].[WMA vs SMA] > -1000 THEN "Downtrend"
        ELSE "Strong Downtrend"
    END

-- Месяцы Q2 2013 для анализа
SET [Q2 2013 Months] AS
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(1).Children

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[3-Month SMA],
     [Measures].[3-Month WMA],
     [Measures].[WMA vs SMA],
     [Measures].[Trend Direction]} ON COLUMNS,
    [Q2 2013 Months] ON ROWS
FROM [Adventure Works]

Пример 4: Анализ волатильности и отклонений

WITH 
-- Скользящее стандартное отклонение за 3 месяца
MEMBER [Measures].[3-Month StdDev] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,
        STDEV(
            LastPeriods(3, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount]
        )
    ),
    FORMAT_STRING = "Currency"

-- Скользящая средняя
MEMBER [Measures].[3-Month Avg] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,
        AVG(
            LastPeriods(3, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount]
        )
    ),
    FORMAT_STRING = "Currency"

-- Коэффициент вариации (относительная волатильность)
MEMBER [Measures].[Coefficient of Variation] AS
    IIF(
        ISEMPTY([Measures].[3-Month Avg]) OR [Measures].[3-Month Avg] = 0,
        NULL,
        [Measures].[3-Month StdDev] / [Measures].[3-Month Avg]
    ),
    FORMAT_STRING = "Percent"

-- Верхняя граница (среднее + 2 стандартных отклонения)
MEMBER [Measures].[Upper Band] AS
    [Measures].[3-Month Avg] + (2 * [Measures].[3-Month StdDev]),
    FORMAT_STRING = "Currency"

-- Нижняя граница (среднее - 2 стандартных отклонения)
MEMBER [Measures].[Lower Band] AS
    [Measures].[3-Month Avg] - (2 * [Measures].[3-Month StdDev]),
    FORMAT_STRING = "Currency"

-- Определение выбросов
MEMBER [Measures].[Outlier Detection] AS
    CASE
        WHEN ISEMPTY([Measures].[Upper Band]) THEN "Insufficient Data"
        WHEN [Measures].[Internet Sales Amount] > [Measures].[Upper Band] THEN "Above Upper Band"
        WHEN [Measures].[Internet Sales Amount] < [Measures].[Lower Band] THEN "Below Lower Band"
        ELSE "Within Bands"
    END

-- Анализируем второе полугодие 2013
SET [H2 2013 Months] AS {
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(2).FirstChild,     -- Jul 2013
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(2).Children.Item(1), -- Aug 2013
    [Date].[Calendar].[Calendar Year].&[2013].Children.Item(2).LastChild,      -- Sep 2013
    [Date].[Calendar].[Calendar Year].&[2013].LastChild.FirstChild,            -- Oct 2013
    [Date].[Calendar].[Calendar Year].&[2013].LastChild.Children.Item(1),      -- Nov 2013
    [Date].[Calendar].[Calendar Year].&[2013].LastChild.LastChild              -- Dec 2013
}

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[3-Month Avg],
     [Measures].[3-Month StdDev],
     [Measures].[Coefficient of Variation],
     [Measures].[Lower Band],
     [Measures].[Upper Band],
     [Measures].[Outlier Detection]} ON COLUMNS,
    [H2 2013 Months] ON ROWS
FROM [Adventure Works]

Пример 5: Комплексный анализ трендов с множественными окнами

WITH 
-- Краткосрочная скользящая средняя (3 месяца)
MEMBER [Measures].[Short MA (3M)] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,
        AVG(LastPeriods(3, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount])
    ),
    FORMAT_STRING = "Currency"

-- Долгосрочная скользящая средняя (6 месяцев)
MEMBER [Measures].[Long MA (6M)] AS
    IIF(
        COUNT(LastPeriods(6, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 6,
        NULL,
        AVG(LastPeriods(6, [Date].[Calendar].CurrentMember),
            [Measures].[Internet Sales Amount])
    ),
    FORMAT_STRING = "Currency"

-- Разница между краткосрочной и долгосрочной MA
MEMBER [Measures].[MA Spread] AS
    [Measures].[Short MA (3M)] - [Measures].[Long MA (6M)],
    FORMAT_STRING = "Currency"

-- Сигнал пересечения
MEMBER [Measures].[Crossover Signal] AS
    CASE
        WHEN ISEMPTY([Measures].[MA Spread]) THEN "No Signal"
        WHEN [Measures].[MA Spread] > 0 AND 
             (
                [Date].[Calendar].CurrentMember.Lag(1),
                [Measures].[Short MA (3M)] - [Measures].[Long MA (6M)]
             ) <= 0 THEN "Golden Cross ↑"
        WHEN [Measures].[MA Spread] < 0 AND
             (
                [Date].[Calendar].CurrentMember.Lag(1),
                [Measures].[Short MA (3M)] - [Measures].[Long MA (6M)]
             ) >= 0 THEN "Death Cross ↓"
        WHEN [Measures].[MA Spread] > 0 THEN "Bullish"
        WHEN [Measures].[MA Spread] < 0 THEN "Bearish"
        ELSE "Neutral"
    END

-- Простой линейный прогноз на следующий период
-- Основан на среднем приросте за последние 3 месяца
MEMBER [Measures].[Next Period Forecast] AS
    IIF(
        COUNT(LastPeriods(3, [Date].[Calendar].CurrentMember), EXCLUDEEMPTY) < 3,
        NULL,
        [Measures].[Internet Sales Amount] + 
        AVG(
            {
                ([Date].[Calendar].CurrentMember, [Measures].[Internet Sales Amount]) -
                ([Date].[Calendar].CurrentMember.Lag(1), [Measures].[Internet Sales Amount]),
                
                ([Date].[Calendar].CurrentMember.Lag(1), [Measures].[Internet Sales Amount]) -
                ([Date].[Calendar].CurrentMember.Lag(2), [Measures].[Internet Sales Amount])
            }
        )
    ),
    FORMAT_STRING = "Currency"

-- Уверенность прогноза на основе волатильности
MEMBER [Measures].[Forecast Confidence] AS
    CASE
        WHEN ISEMPTY([Measures].[Next Period Forecast]) THEN "N/A"
        WHEN [Measures].[Coefficient of Variation] < 0.1 THEN "High (CV < 10%)"
        WHEN [Measures].[Coefficient of Variation] < 0.2 THEN "Medium (CV 10-20%)"
        ELSE "Low (CV > 20%)"
    END

-- Коэффициент вариации для оценки стабильности
MEMBER [Measures].[Coefficient of Variation] AS
    IIF(
        ISEMPTY([Measures].[Short MA (3M)]) OR [Measures].[Short MA (3M)] = 0,
        NULL,
        STDEV(LastPeriods(3, [Date].[Calendar].CurrentMember),
              [Measures].[Internet Sales Amount]) / 
        [Measures].[Short MA (3M)]
    ),
    VISIBLE = 0  -- Скрываем, так как используется только для расчета

-- Все месяцы 2013 года
SET [Year 2013 Months] AS
    DESCENDANTS(
        [Date].[Calendar].[Calendar Year].&[2013],
        [Date].[Calendar].[Month],
        SELF
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Short MA (3M)],
     [Measures].[Long MA (6M)],
     [Measures].[MA Spread],
     [Measures].[Crossover Signal],
     [Measures].[Next Period Forecast],
     [Measures].[Forecast Confidence]} ON COLUMNS,
    [Year 2013 Months] ON ROWS
FROM [Adventure Works]

Заключение

В этом уроке мы освоили мощный инструментарий для анализа временных рядов с помощью функции LastPeriods. Эта функция позволяет создавать скользящие окна, которые движутся вместе с анализируемым периодом, обеспечивая динамическое сглаживание данных и выявление трендов. Мы изучили различные типы скользящих средних — от простой SMA до взвешенной WMA, научились обрабатывать граничные эффекты и анализировать волатильность данных.

Ключевым навыком является правильный выбор размера окна: слишком маленькое окно не устранит шум, слишком большое — скроет важные изменения. Помните, что для сезонных данных оптимальное окно часто равно длине сезонного цикла, а для выявления долгосрочных трендов используйте множественные окна с анализом их пересечений.

Комбинирование LastPeriods с накопительными итогами из предыдущего урока открывает еще больше возможностей. Например, можно рассчитать скользящую среднюю YTD показателей или сравнить текущее накопительное значение со скользящей средней аналогичных периодов прошлых лет. В следующем уроке мы углубимся в анализ сезонности и научимся выполнять декомпозицию временных рядов, отделяя тренд от сезонной и случайной составляющих.