MDX|Урок 4.3: Ранжирование данных| Cлайдер

Введение: Зачем нужно ранжирование в аналитике

После изучения фильтрации и сортировки мы переходим к ранжированию — мощному инструменту для определения позиции элементов в упорядоченном наборе. Ранжирование отвечает на вопросы: "Какое место занимает этот продукт по продажам?", "Входит ли клиент в топ-10?", "Какой процентиль занимает регион по прибыли?".

В отличие от простой сортировки, которая упорядочивает элементы, ранжирование присваивает каждому элементу числовую позицию. Это критически важно для создания рейтингов, выявления лидеров и аутсайдеров, а также для сравнительного анализа.

Теоретические основы ранжирования

Функция RANK и её синтаксис

Основная функция для ранжирования в MDX — это RANK. Её синтаксис:

RANK(элемент, набор [, выражение_сортировки])

Где:

элемент — член, для которого определяется ранг
набор — упорядоченный набор для ранжирования
выражение_сортировки — необязательный параметр для определения критерия

Важная особенность: если третий параметр не указан, RANK использует текущий порядок элементов в наборе. Если указан — сначала выполняется сортировка, затем определяется позиция.

Типы ранжирования

В аналитике существует несколько подходов к ранжированию:

Плотное ранжирование — одинаковые значения получают одинаковый ранг, следующий ранг идет по порядку
Стандартное ранжирование — одинаковые значения получают одинаковый ранг, но следующий ранг пропускает позиции
Уникальное ранжирование — каждый элемент получает уникальный ранг

MDX по умолчанию использует уникальное ранжирование на основе позиции в наборе.

Взаимосвязь ранжирования с сортировкой

Ранжирование тесно связано с сортировкой. Обычный подход:

Создаем отсортированный набор с помощью ORDER
Применяем RANK к этому набору
Используем ранг для анализа или фильтрации

Базовое ранжирование

Простейший пример ранжирования

Начнем с определения рангов продуктов по продажам:

WITH 
-- Создаем отсортированный набор продуктов
SET [SortedProducts] AS
    ORDER(
        [Product].[Product].[Product].Members,
        [Measures].[Internet Sales Amount],
        DESC  -- Сортируем по убыванию продаж
    )

-- Создаем меру для отображения ранга
MEMBER [Measures].[Sales Rank] AS
    RANK(
        [Product].[Product].CurrentMember,  -- Текущий продукт
        [SortedProducts]                    -- В отсортированном наборе
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Sales Rank]} ON COLUMNS,
    NON EMPTY [SortedProducts] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Разберем этот код подробно:

SET [SortedProducts] создает именованный набор отсортированных продуктов
MEMBER [Measures].[Sales Rank] создает расчетную меру для ранга
CurrentMember указывает на текущий элемент в контексте вычисления
RANK возвращает позицию элемента в отсортированном наборе

Ранжирование с фильтрацией

Часто нужно ранжировать только релевантные элементы:

WITH 
-- Фильтруем продукты с существенными продажами
SET [SignificantProducts] AS
    FILTER(
        [Product].[Product].[Product].Members,
        [Measures].[Internet Sales Amount] > 10000
    )

-- Сортируем отфильтрованный набор
SET [RankedProducts] AS
    ORDER(
        [SignificantProducts],
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Вычисляем ранг
MEMBER [Measures].[Filtered Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        [RankedProducts]
    )

-- Добавляем индикатор топ-10
MEMBER [Measures].[Is Top 10] AS
    IIF(
        [Measures].[Filtered Rank] <= 10 AND [Measures].[Filtered Rank] > 0,
        "Yes",
        "No"
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Filtered Rank],
     [Measures].[Is Top 10]} ON COLUMNS,
    HEAD([RankedProducts], 20) ON ROWS  -- Показываем топ-20
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Ранжирование в группах

Локальное ранжирование внутри категорий

Определим ранг продукта внутри его категории:

WITH 
-- Для каждого продукта определяем ранг в его категории
MEMBER [Measures].[Category Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        ORDER(
            -- Берем все продукты из той же категории
            FILTER(
                [Product].[Product].[Product].Members,
                [Product].[Product].CurrentMember.Parent.Parent = 
                [Product].[Category].CurrentMember
            ),
            [Measures].[Internet Sales Amount],
            DESC
        )
    )

-- Определяем категорию для контекста
MEMBER [Measures].[Product Category] AS
    [Product].[Product].CurrentMember.Parent.Parent.Name

-- Маркер лидера категории
MEMBER [Measures].[Category Leader] AS
    IIF([Measures].[Category Rank] = 1, "★ LEADER", "")

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Product Category],
     [Measures].[Category Rank],
     [Measures].[Category Leader]} ON COLUMNS,
    NON EMPTY 
        FILTER(
            [Product].[Product].[Product].Members,
            [Measures].[Category Rank] <= 5  -- Топ-5 в каждой категории
        ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Многоуровневое ранжирование

Создадим систему с глобальным и локальным рангами:

WITH 
-- Глобальный отсортированный набор
SET [AllSubcategories] AS
    ORDER(
        [Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Глобальный ранг
MEMBER [Measures].[Global Rank] AS
    RANK(
        [Product].[Subcategory].CurrentMember,
        [AllSubcategories]
    )

-- Локальный ранг внутри категории
MEMBER [Measures].[Local Rank] AS
    RANK(
        [Product].[Subcategory].CurrentMember,
        ORDER(
            FILTER(
                [Product].[Subcategory].[Subcategory].Members,
                [Product].[Subcategory].CurrentMember.Parent = 
                [Product].[Category].CurrentMember
            ),
            [Measures].[Internet Sales Amount],
            DESC
        )
    )

-- Комбинированный показатель
MEMBER [Measures].[Rank Display] AS
    "G:" + CStr([Measures].[Global Rank]) + 
    " / L:" + CStr([Measures].[Local Rank])

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Global Rank],
     [Measures].[Local Rank],
     [Measures].[Rank Display]} ON COLUMNS,
    CrossJoin(
        [Product].[Category].[Category].Members,
        [Product].[Subcategory].[Subcategory].Members
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Процентильное ранжирование

Вычисление процентилей

WITH 
-- Отсортированный набор клиентов
SET [SortedCustomers] AS
    ORDER(
        FILTER(
            [Customer].[Customer].[Customer].Members,
            NOT ISEMPTY([Measures].[Internet Sales Amount])
        ),
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Общее количество клиентов
MEMBER [Measures].[Total Customers] AS
    COUNT([SortedCustomers])

-- Ранг клиента
MEMBER [Measures].[Customer Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        [SortedCustomers]
    )

-- Процентиль (какой процент клиентов ниже)
MEMBER [Measures].[Percentile] AS
    IIF(
        [Measures].[Total Customers] = 0,
        NULL,
        ((1 - ([Measures].[Customer Rank] - 1) / [Measures].[Total Customers]) * 100)
    ),
    FORMAT_STRING = "#,##0.00"

-- Квартиль
MEMBER [Measures].[Quartile] AS
    CASE
        WHEN [Measures].[Percentile] >= 75 THEN "Q1 (Top 25%)"
        WHEN [Measures].[Percentile] >= 50 THEN "Q2 (25-50%)"
        WHEN [Measures].[Percentile] >= 25 THEN "Q3 (50-75%)"
        ELSE "Q4 (Bottom 25%)"
    END

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Customer Rank],
     [Measures].[Percentile],
     [Measures].[Quartile]} ON COLUMNS,
    HEAD([SortedCustomers], 100) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Децильный анализ

WITH 
-- Отсортированные регионы
SET [SortedRegions] AS
    ORDER(
        FILTER(
            [Customer].[State-Province].[State-Province].Members,
            NOT ISEMPTY([Measures].[Internet Sales Amount])
        ),
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Общее количество
MEMBER [Measures].[Total Count] AS
    COUNT([SortedRegions])

-- Ранг
MEMBER [Measures].[Region Rank] AS
    RANK(
        [Customer].[State-Province].CurrentMember,
        [SortedRegions]
    )

-- Дециль (группа из 10)
MEMBER [Measures].[Decile] AS
    IIF(
        [Measures].[Total Count] = 0,
        NULL,
        CEILING(([Measures].[Region Rank] * 10.0) / [Measures].[Total Count])
    )

-- Метка дециля
MEMBER [Measures].[Decile Label] AS
    "D" + CStr([Measures].[Decile])

Использование функций TopCount, BottomCount, TopPercent

TopCount для выбора лидеров

TopCount возвращает заданное количество лидирующих элементов:

WITH 
-- Топ-10 продуктов по продажам
SET [Top10Products] AS
    TOPCOUNT(
        [Product].[Product].[Product].Members,
        10,  -- Количество элементов
        [Measures].[Internet Sales Amount]  -- Критерий
    )

-- Ранг среди топ-10
MEMBER [Measures].[Top10 Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        [Top10Products]
    )

-- Доля от суммы топ-10
MEMBER [Measures].[Share of Top10] AS
    [Measures].[Internet Sales Amount] / 
    SUM([Top10Products], [Measures].[Internet Sales Amount]),
    FORMAT_STRING = "Percent"

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Top10 Rank],
     [Measures].[Share of Top10]} ON COLUMNS,
    [Top10Products] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

BottomCount для анализа аутсайдеров

WITH 
-- Худшие 10 субкатегорий по марже
MEMBER [Measures].[Profit Margin] AS
    IIF(
        [Measures].[Internet Sales Amount] = 0,
        NULL,
        ([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]) /
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Percent"

SET [Bottom10Subcategories] AS
    BOTTOMCOUNT(
        NON EMPTY [Product].[Subcategory].[Subcategory].Members,
        10,
        [Measures].[Profit Margin]
    )

-- Обратный ранг (от худшего)
MEMBER [Measures].[Bottom Rank] AS
    RANK(
        [Product].[Subcategory].CurrentMember,
        ORDER([Bottom10Subcategories], [Measures].[Profit Margin], ASC)
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Profit Margin],
     [Measures].[Bottom Rank]} ON COLUMNS,
    [Bottom10Subcategories] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

TopPercent для процентного отбора

WITH 
-- Продукты, составляющие 80% продаж (принцип Парето)
SET [Pareto80Products] AS
    TOPPERCENT(
        [Product].[Product].[Product].Members,
        80,  -- 80% от общей суммы
        [Measures].[Internet Sales Amount]
    )

-- Количество продуктов в 80%
MEMBER [Measures].[Pareto Count] AS
    COUNT([Pareto80Products])

-- Процент от общего количества продуктов
MEMBER [Measures].[Pareto Product %] AS
    [Measures].[Pareto Count] / 
    COUNT([Product].[Product].[Product].Members),
    FORMAT_STRING = "Percent"

-- Ранг в Парето-группе
MEMBER [Measures].[Pareto Rank] AS
    IIF(
        [Product].[Product].CurrentMember IN [Pareto80Products],
        RANK(
            [Product].[Product].CurrentMember,
            ORDER([Pareto80Products], [Measures].[Internet Sales Amount], DESC)
        ),
        NULL
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Pareto Rank],
     [Measures].[Pareto Count],
     [Measures].[Pareto Product %]} ON COLUMNS,
    HEAD(
        ORDER([Pareto80Products], [Measures].[Internet Sales Amount], DESC),
        20
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Динамическое ранжирование

Ранжирование с изменяющимся контекстом

WITH 
-- Ранг продукта среди всех продуктов его категории
MEMBER [Measures].[Dynamic Category Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        ORDER(
            EXISTING [Product].[Product].[Product].Members,  -- EXISTING учитывает текущий контекст
            [Measures].[Internet Sales Amount],
            DESC
        )
    )

-- Общее количество продуктов в контексте
MEMBER [Measures].[Context Product Count] AS
    COUNT(EXISTING [Product].[Product].[Product].Members)

-- Относительная позиция
MEMBER [Measures].[Relative Position] AS
    [Measures].[Dynamic Category Rank] + " из " + 
    CStr([Measures].[Context Product Count])

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Dynamic Category Rank],
     [Measures].[Relative Position]} ON COLUMNS,
    NON EMPTY [Product].[Product].[Product].Members ON ROWS
FROM [Adventure Works]
WHERE (
    [Date].[Calendar Year].&[2013],
    [Product].[Category].[Bikes]  -- Фильтр по категории влияет на ранжирование
)

Практические упражнения

Упражнение 1: Комплексная система ранжирования

-- Задача: создать многомерную систему оценки клиентов

WITH 
-- Ранжирование по разным метрикам
SET [ActiveCustomers] AS
    FILTER(
        [Customer].[Customer].[Customer].Members,
        [Measures].[Internet Sales Amount] > 0
    )

-- Ранг по объему продаж
MEMBER [Measures].[Sales Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        ORDER([ActiveCustomers], [Measures].[Internet Sales Amount], DESC)
    )

-- Ранг по количеству заказов
MEMBER [Measures].[Order Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        ORDER([ActiveCustomers], [Measures].[Internet Order Count], DESC)
    )

-- Средний чек
MEMBER [Measures].[Avg Order Value] AS
    IIF(
        [Measures].[Internet Order Count] = 0,
        NULL,
        [Measures].[Internet Sales Amount] / [Measures].[Internet Order Count]
    ),
    FORMAT_STRING = "Currency"

-- Ранг по среднему чеку
MEMBER [Measures].[AOV Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        ORDER([ActiveCustomers], [Measures].[Avg Order Value], DESC)
    )

-- Комплексная оценка (чем меньше, тем лучше)
MEMBER [Measures].[Combined Score] AS
    ([Measures].[Sales Rank] * 0.5 + 
     [Measures].[Order Rank] * 0.3 + 
     [Measures].[AOV Rank] * 0.2) / 1,
    FORMAT_STRING = "#,##0.00"

-- Финальный ранг по комплексной оценке
MEMBER [Measures].[Final Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        ORDER([ActiveCustomers], [Measures].[Combined Score], ASC)
    )

-- Категория клиента
MEMBER [Measures].[Customer Tier] AS
    CASE
        WHEN [Measures].[Final Rank] <= 10 THEN "Platinum"
        WHEN [Measures].[Final Rank] <= 50 THEN "Gold"
        WHEN [Measures].[Final Rank] <= 200 THEN "Silver"
        ELSE "Bronze"
    END

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Internet Order Count],
     [Measures].[Avg Order Value],
     [Measures].[Sales Rank],
     [Measures].[Order Rank],
     [Measures].[AOV Rank],
     [Measures].[Combined Score],
     [Measures].[Final Rank],
     [Measures].[Customer Tier]} ON COLUMNS,
    HEAD(
        ORDER([ActiveCustomers], [Measures].[Final Rank], ASC),
        25
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Упражнение 2: Анализ изменения рангов

-- Задача: отследить изменение позиций продуктов между периодами

WITH 
-- Ранг в текущем году
MEMBER [Measures].[Current Year Rank] AS
    RANK(
        [Product].[Subcategory].CurrentMember,
        ORDER(
            [Product].[Subcategory].[Subcategory].Members,
            ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2013]),
            DESC
        )
    )

-- Ранг в предыдущем году
MEMBER [Measures].[Previous Year Rank] AS
    RANK(
        [Product].[Subcategory].CurrentMember,
        ORDER(
            [Product].[Subcategory].[Subcategory].Members,
            ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2012]),
            DESC
        )
    )

-- Изменение позиции
MEMBER [Measures].[Rank Change] AS
    [Measures].[Previous Year Rank] - [Measures].[Current Year Rank]

-- Визуализация изменения
MEMBER [Measures].[Trend] AS
    CASE
        WHEN [Measures].[Rank Change] > 2 THEN "↑↑ Strong Growth"
        WHEN [Measures].[Rank Change] > 0 THEN "↑ Growth"
        WHEN [Measures].[Rank Change] = 0 THEN "→ Stable"
        WHEN [Measures].[Rank Change] > -2 THEN "↓ Decline"
        ELSE "↓↓ Strong Decline"
    END

-- Продажи текущего года
MEMBER [Measures].[Sales 2013] AS
    ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2013]),
    FORMAT_STRING = "Currency"

-- Продажи предыдущего года
MEMBER [Measures].[Sales 2012] AS
    ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2012]),
    FORMAT_STRING = "Currency"

SELECT 
    {[Measures].[Sales 2013],
     [Measures].[Sales 2012],
     [Measures].[Current Year Rank],
     [Measures].[Previous Year Rank],
     [Measures].[Rank Change],
     [Measures].[Trend]} ON COLUMNS,
    NON EMPTY 
        ORDER(
            [Product].[Subcategory].[Subcategory].Members,
            ABS([Measures].[Rank Change]),  -- Сортируем по величине изменения
            DESC
        ) ON ROWS
FROM [Adventure Works]

Оптимизация ранжирования

Кэширование для производительности

WITH 
-- Кэшируем отсортированный набор один раз
SET [CachedSortedSet] AS
    ORDER(
        FILTER(
            [Product].[Product].[Product].Members,
            NOT ISEMPTY([Measures].[Internet Sales Amount])
        ),
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Используем кэшированный набор для всех вычислений
MEMBER [Measures].[Cached Rank] AS
    RANK([Product].[Product].CurrentMember, [CachedSortedSet])

MEMBER [Measures].[Is Top 20] AS
    IIF([Measures].[Cached Rank] <= 20 AND [Measures].[Cached Rank] > 0, "Yes", "No")

MEMBER [Measures].[Rank Group] AS
    CASE
        WHEN [Measures].[Cached Rank] <= 10 THEN "Top 10"
        WHEN [Measures].[Cached Rank] <= 50 THEN "Top 50"
        WHEN [Measures].[Cached Rank] <= 100 THEN "Top 100"
        ELSE "Other"
    END

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Cached Rank],
     [Measures].[Is Top 20],
     [Measures].[Rank Group]} ON COLUMNS,
    HEAD([CachedSortedSet], 30) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Типичные ошибки и их решение

Ошибка 1: Ранжирование пустого набора

-- НЕПРАВИЛЬНО: может вызвать ошибку
MEMBER [Measures].[Bad Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        {}  -- Пустой набор
    )

-- ПРАВИЛЬНО: проверка на пустоту
MEMBER [Measures].[Safe Rank] AS
    IIF(
        COUNT([MySet]) = 0,
        NULL,
        RANK([Product].[Product].CurrentMember, [MySet])
    )

Ошибка 2: Ранжирование без учета NULL

-- НЕПРАВИЛЬНО: NULL значения могут исказить ранги
SET [AllProducts] AS
    ORDER(
        [Product].[Product].[Product].Members,
        [Measures].[Some Measure],  -- Может содержать NULL
        DESC
    )

-- ПРАВИЛЬНО: фильтрация NULL перед ранжированием
SET [ValidProducts] AS
    ORDER(
        FILTER(
            [Product].[Product].[Product].Members,
            NOT IsEmpty([Measures].[Some Measure])
        ),
        [Measures].[Some Measure],
        DESC
    )

Заключение

В этом уроке мы детально изучили ранжирование данных в MDX. Мы научились:

Использовать функцию RANK для определения позиций элементов
Создавать локальное и глобальное ранжирование
Вычислять процентили и квартили
Применять функции TopCount, BottomCount и TopPercent
Реализовывать динамическое ранжирование с учетом контекста
Оптимизировать производительность через кэширование

Ранжирование — это мощный инструмент для создания рейтингов, выявления лидеров и аутсайдеров, а также для сегментации данных. В сочетании с фильтрацией и сортировкой, изученными в предыдущих уроках, ранжирование позволяет создавать сложные аналитические отчеты.

Домашнее задание

Задание 1: Многокритериальное ранжирование

Создайте систему ранжирования продуктов, учитывающую продажи, маржу и оборачиваемость. Реализуйте взвешенную оценку.

Задание 2: Динамическое изменение рангов

Постройте отчет, показывающий изменение рангов клиентов по кварталам с визуализацией трендов.

Задание 3: Процентильный анализ

Реализуйте полный процентильный анализ регионов с автоматическим определением квинтилей и расчетом статистики по каждой группе.

Контрольные вопросы

В чем разница между RANK и простой нумерацией строк?
Как влияет сортировка набора на результат функции RANK?
Можно ли определить ранг элемента, не входящего в набор?
Как реализовать плотное ранжирование в MDX?
В чем преимущество TopCount перед комбинацией ORDER и HEAD?
Как правильно обрабатывать одинаковые значения при ранжировании?
Какие стратегии оптимизации применимы к ранжированию больших наборов?