Заказать интеграцию
Урок 4.2: Сортировка наборов

Урок 4.2: Сортировка наборов

Модуль 4: Фильтрация, сортировка и ранжирование

Введение: Важность упорядочивания данных в бизнес-анализе

Представьте, что вы открываете отчет о продажах, где продукты расположены хаотично — ни по алфавиту, ни по объему продаж, ни по какому-либо другому логическому принципу. Найти нужную информацию становится практически невозможно. Именно поэтому сортировка является фундаментальной операцией в аналитике данных.

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

Основы сортировки в MDX

Функция ORDER — главный инструмент упорядочивания

В MDX для сортировки используется функция ORDER. Её структура интуитивно понятна:

Haxe
ORDER(что_сортируем, по_чему_сортируем, как_сортируем)

Более формально:

  • Первый параметр — набор элементов, который нужно упорядочить
  • Второй параметр — выражение, определяющее критерий сортировки
  • Третий параметр — направление и тип сортировки

Типы сортировки: сохранение против разрушения иерархии

MDX предоставляет четыре варианта сортировки:

  • ASC (Ascending) — возрастающий порядок с учетом иерархии
  • DESC (Descending) — убывающий порядок с учетом иерархии
  • BASC (Break Ascending) — возрастающий порядок, игнорируя иерархию
  • BDESC (Break Descending) — убывающий порядок, игнорируя иерархию

Буква "B" означает "Break" — разрушение иерархической структуры. Это критическое отличие, которое определяет, будут ли дочерние элементы оставаться под своими родителями или сортироваться глобально.

Механизм работы сортировки

Когда MDX выполняет сортировку:

  • Оценивает выражение сортировки для каждого элемента набора
  • Создает упорядоченный список на основе полученных значений
  • Применяет указанное направление сортировки
  • Учитывает иерархические связи (если не указан флаг B)

Сортировка по числовым мерам

Простейший случай: упорядочивание по продажам

Начнем с базового примера — отсортируем субкатегории продуктов по объему интернет-продаж:

SELECT 
    [Measures].[Internet Sales Amount] ON COLUMNS,
    ORDER(
        [Product].[Subcategory].[Subcategory].Members,  -- Что сортируем
        [Measures].[Internet Sales Amount],              -- По чему сортируем  
        DESC                                              -- Как сортируем
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Этот запрос:

  • Берет все элементы уровня Subcategory
  • Вычисляет для каждого значение Internet Sales Amount
  • Располагает их от большего к меньшему

Комбинирование фильтрации и сортировки

Часто требуется сначала отобрать релевантные данные, затем их упорядочить:

WITH 
-- Определяем минимальный порог продаж
MEMBER [Measures].[Sales Threshold] AS 100000

SELECT 
    {[Measures].[Internet Sales Amount], 
     [Measures].[Internet Order Count]} ON COLUMNS,
    ORDER(
        -- Сначала фильтруем элементы выше порога
        FILTER(
            [Product].[Subcategory].[Subcategory].Members,
            [Measures].[Internet Sales Amount] > [Measures].[Sales Threshold]
        ),
        [Measures].[Internet Order Count],  -- Сортируем по количеству заказов
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Работа с NULL значениями в сортировке

NULL значения требуют особого внимания:

WITH 
-- Рассчитываем рентабельность
MEMBER [Measures].[ROI] AS
    IIF(
        [Measures].[Internet Total Product Cost] = 0,
        NULL,
        ([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]) / 
        [Measures].[Internet Total Product Cost]
    ),
    FORMAT_STRING = "Percent"

-- Безопасная версия для сортировки
MEMBER [Measures].[ROI for Sort] AS
    IIF(
        IsEmpty([Measures].[ROI]),
        -1,  -- Помещаем NULL в конец при DESC сортировке
        [Measures].[ROI]
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[ROI]} ON COLUMNS,
    ORDER(
        NON EMPTY [Product].[Product].[Product].Members,
        [Measures].[ROI for Sort],  -- Используем безопасную версию
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Иерархическая сортировка

Сохранение структуры при сортировке

Рассмотрим, как работает сортировка с сохранением иерархии:

-- Создаем иерархический набор: категории и их подкатегории
WITH SET [CategoryAndSubs] AS
    Generate(
        [Product].[Category].[Category].Members,
        {[Product].[Category].CurrentMember} +  -- Категория
        Children([Product].[Category].CurrentMember)  -- Её подкатегории
    )

SELECT 
    [Measures].[Internet Sales Amount] ON COLUMNS,
    ORDER(
        [CategoryAndSubs],
        [Measures].[Internet Sales Amount],
        DESC  -- Подкатегории остаются под своими категориями
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Глобальная сортировка с разрушением иерархии

Теперь применим BDESC для полной пересортировки:

WITH SET [AllLevels] AS
    [Product].[Category].[Category].Members +
    [Product].[Subcategory].[Subcategory].Members

SELECT 
    {[Measures].[Internet Sales Amount],
     [Product].[Product Categories].CurrentMember.Level.Name} ON COLUMNS,
    ORDER(
        [AllLevels],
        [Measures].[Internet Sales Amount],
        BDESC  -- Все элементы сортируются независимо от иерархии
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Сортировка внутри каждой группы

Для локальной сортировки внутри групп:

WITH 
-- Для каждой страны создаем отсортированный список регионов
SET [SortedByCountry] AS
    Generate(
        [Customer].[Country].[Country].Members,
        {[Customer].[Country].CurrentMember} +
        ORDER(
            Filter(
                [Customer].[State-Province].[State-Province].Members,
                [Customer].[State-Province].CurrentMember.Parent = 
                [Customer].[Country].CurrentMember
            ),
            [Measures].[Internet Sales Amount],
            DESC
        )
    )

SELECT 
    [Measures].[Internet Sales Amount] ON COLUMNS,
    [SortedByCountry] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Сортировка по вычисляемым критериям

Использование расчетных мер для упорядочивания

Создадим комплексный критерий сортировки:

WITH 
-- Базовые метрики
MEMBER [Measures].[Units Sold] AS
    [Measures].[Order Quantity]

MEMBER [Measures].[Revenue] AS
    [Measures].[Internet Sales Amount]

-- Средняя стоимость единицы
MEMBER [Measures].[Unit Price] AS
    IIF(
        [Measures].[Units Sold] = 0,
        0,
        [Measures].[Revenue] / [Measures].[Units Sold]
    ),
    FORMAT_STRING = "Currency"

-- Комплексный индекс привлекательности
MEMBER [Measures].[Attractiveness Score] AS
    -- Учитываем объем, цену и частоту покупок
    [Measures].[Revenue] * 0.5 +
    [Measures].[Unit Price] * 100 * 0.3 +
    [Measures].[Internet Order Count] * 50 * 0.2,
    FORMAT_STRING = "#,##0"

SELECT 
    {[Measures].[Revenue],
     [Measures].[Unit Price],
     [Measures].[Internet Order Count],
     [Measures].[Attractiveness Score]} ON COLUMNS,
    ORDER(
        NON EMPTY [Product].[Subcategory].[Subcategory].Members,
        [Measures].[Attractiveness Score],
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Относительная сортировка

Упорядочивание по отклонению от среднего:

WITH 
-- Среднее значение по всем элементам
MEMBER [Measures].[Global Average] AS
    AVG(
        [Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount]
    )

-- Отклонение от среднего в процентах
MEMBER [Measures].[Variance %] AS
    IIF(
        [Measures].[Global Average] = 0,
        0,
        ([Measures].[Internet Sales Amount] - [Measures].[Global Average]) / 
        [Measures].[Global Average] * 100
    ),
    FORMAT_STRING = "#,##0.00"

-- Категоризация
MEMBER [Measures].[Category] AS
    CASE
        WHEN [Measures].[Variance %] > 100 THEN "Outstanding"
        WHEN [Measures].[Variance %] > 0 THEN "Above Average"
        WHEN [Measures].[Variance %] > -50 THEN "Below Average"
        ELSE "Poor"
    END

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Global Average],
     [Measures].[Variance %],
     [Measures].[Category]} ON COLUMNS,
    ORDER(
        NON EMPTY [Product].[Subcategory].[Subcategory].Members,
        [Measures].[Variance %],
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Сортировка по атрибутам элементов

Алфавитное упорядочивание

Сортировка по названию элемента:

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Internet Order Count]} ON COLUMNS,
    ORDER(
        NON EMPTY [Customer].[City].[City].Members,
        [Customer].[City].CurrentMember.Name,  -- Сортируем по имени города
        ASC
    ) ON ROWS
FROM [Adventure Works]
WHERE (
    [Date].[Calendar Year].&[2013],
    [Customer].[Country].[United States]
)

Сортировка по свойствам членов

Использование дополнительных атрибутов:

WITH 
-- Получаем уникальный ключ элемента
MEMBER [Measures].[Member Key] AS
    [Product].[Product].CurrentMember.UniqueName

-- Получаем уровень в иерархии
MEMBER [Measures].[Hierarchy Level] AS
    [Product].[Product Categories].CurrentMember.Level.Ordinal

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Member Key],
     [Measures].[Hierarchy Level]} ON COLUMNS,
    ORDER(
        Descendants(
            [Product].[Product Categories].[All Products],
            3,
            SELF_AND_BEFORE
        ),
        [Measures].[Hierarchy Level],  -- Сортируем по уровню иерархии
        ASC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Продвинутые техники сортировки

Многоступенчатая сортировка

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

WITH 
-- Первичный критерий: категория продукта
MEMBER [Measures].[Primary Sort] AS
    CASE [Product].[Category].CurrentMember.Name
        WHEN "Bikes" THEN 1
        WHEN "Components" THEN 2
        WHEN "Clothing" THEN 3
        WHEN "Accessories" THEN 4
        ELSE 5
    END

-- Вторичный критерий: продажи
MEMBER [Measures].[Secondary Sort] AS
    [Measures].[Internet Sales Amount]

-- Объединенный критерий
MEMBER [Measures].[Combined Sort] AS
    [Measures].[Primary Sort] * 1000000 +  -- Умножаем для приоритета
    [Measures].[Secondary Sort]

SELECT 
    {[Measures].[Internet Sales Amount],
     [Product].[Category].CurrentMember.Name} ON COLUMNS,
    ORDER(
        CrossJoin(
            [Product].[Category].[Category].Members,
            [Product].[Subcategory].[Subcategory].Members
        ),
        [Measures].[Combined Sort],
        ASC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Условная сортировка

Изменение направления в зависимости от условий:

WITH 
-- Определяем прибыль
MEMBER [Measures].[Profit] AS
    [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]

-- Флаг прибыльности
MEMBER [Measures].[Is Profitable] AS
    IIF([Measures].[Profit] > 0, 1, 0)

-- Критерий сортировки с условной логикой
MEMBER [Measures].[Smart Sort] AS
    IIF(
        [Measures].[Is Profitable] = 1,
        [Measures].[Profit],  -- Прибыльные сортируем по прибыли
        [Measures].[Internet Sales Amount] * -1  -- Убыточные по продажам (инверсия)
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Profit],
     [Measures].[Is Profitable]} ON COLUMNS,
    ORDER(
        NON EMPTY [Product].[Product].[Product].Members,
        [Measures].[Smart Sort],
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Практические примеры

Пример 1: Топ-N анализ с детализацией

WITH 
-- Находим топ-5 категорий
SET [Top5Categories] AS
    Head(
        ORDER(
            [Product].[Category].[Category].Members,
            [Measures].[Internet Sales Amount],
            DESC
        ),
        5
    )

-- Для каждой топ-категории берем топ-3 подкатегории
SET [DetailedTop] AS
    Generate(
        [Top5Categories],
        {[Product].[Category].CurrentMember} +
        Head(
            ORDER(
                Filter(
                    [Product].[Subcategory].[Subcategory].Members,
                    [Product].[Subcategory].CurrentMember.Parent = 
                    [Product].[Category].CurrentMember
                ),
                [Measures].[Internet Sales Amount],
                DESC
            ),
            3
        )
    )

-- Добавляем ранги
MEMBER [Measures].[Category Rank] AS
    Rank(
        [Product].[Category].CurrentMember,
        [Top5Categories]
    )

MEMBER [Measures].[Subcategory Local Rank] AS
    IIF(
        [Product].[Subcategory].CurrentMember IS [Product].[Subcategory].[All],
        NULL,
        Rank(
            [Product].[Subcategory].CurrentMember,
            ORDER(
                Filter(
                    [Product].[Subcategory].[Subcategory].Members,
                    [Product].[Subcategory].CurrentMember.Parent = 
                    [Product].[Category].CurrentMember
                ),
                [Measures].[Internet Sales Amount],
                DESC
            )
        )
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Category Rank],
     [Measures].[Subcategory Local Rank]} ON COLUMNS,
    [DetailedTop] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Пример 2: Парето-анализ (правило 80/20)

WITH 
-- Сортируем продукты по продажам
SET [SortedProducts] AS
    ORDER(
        NON EMPTY [Product].[Product].[Product].Members,
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Общая сумма
MEMBER [Measures].[Total Revenue] AS
    SUM([SortedProducts], [Measures].[Internet Sales Amount])

-- Накопительная сумма
MEMBER [Measures].[Running Total] AS
    SUM(
        Head(
            [SortedProducts],
            Rank([Product].[Product].CurrentMember, [SortedProducts])
        ),
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Currency"

-- Накопительный процент
MEMBER [Measures].[Running %] AS
    [Measures].[Running Total] / [Measures].[Total Revenue],
    FORMAT_STRING = "Percent"

-- Определяем группу Парето
MEMBER [Measures].[Pareto Group] AS
    IIF(
        [Measures].[Running %] <= 0.8,
        "Vital Few (80%)",
        "Trivial Many (20%)"
    )

-- Количество продуктов для достижения 80% продаж
MEMBER [Measures].[Products for 80%] AS
    COUNT(
        Filter(
            [SortedProducts],
            [Measures].[Running %] <= 0.8
        )
    )

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Running Total],
     [Measures].[Running %],
     [Measures].[Pareto Group]} ON COLUMNS,
    Head([SortedProducts], 50) ON ROWS  -- Показываем первые 50
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Пример 3: Динамическая сортировка с переключением

WITH 
-- Параметр для выбора критерия (в реальности может быть параметром отчета)
MEMBER [Measures].[Sort Mode] AS 2  -- 1=Sales, 2=Margin, 3=Volume

-- Расчет маржи
MEMBER [Measures].[Margin %] AS
    IIF(
        [Measures].[Internet Sales Amount] = 0,
        0,
        ([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]) /
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Percent"

-- Универсальный критерий сортировки
MEMBER [Measures].[Dynamic Sort Value] AS
    CASE [Measures].[Sort Mode]
        WHEN 1 THEN [Measures].[Internet Sales Amount]
        WHEN 2 THEN [Measures].[Margin %] * 1000000  -- Умножаем для масштабирования
        WHEN 3 THEN [Measures].[Order Quantity]
        ELSE [Measures].[Internet Sales Amount]
    END

-- Название текущего критерия
MEMBER [Measures].[Sort Criteria Name] AS
    CASE [Measures].[Sort Mode]
        WHEN 1 THEN "Revenue"
        WHEN 2 THEN "Margin %"
        WHEN 3 THEN "Volume"
        ELSE "Default"
    END

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Margin %],
     [Measures].[Order Quantity],
     [Measures].[Sort Criteria Name]} ON COLUMNS,
    ORDER(
        NON EMPTY [Product].[Subcategory].[Subcategory].Members,
        [Measures].[Dynamic Sort Value],
        DESC
    ) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

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

Ключевые принципы эффективной сортировки

1. Минимизация набора перед сортировкой:

-- НЕЭФФЕКТИВНО: сортировка всех элементов
WITH SET [AllSorted] AS
    ORDER(
        [Customer].[Customer].[Customer].Members,  -- 18,484 клиента!
        [Measures].[Internet Sales Amount],
        DESC
    )

-- ЭФФЕКТИВНО: фильтрация перед сортировкой
WITH SET [FilteredSorted] AS
    ORDER(
        NON EMPTY 
            FILTER(
                [Customer].[Customer].[Customer].Members,
                [Measures].[Internet Sales Amount] > 1000
            ),
        [Measures].[Internet Sales Amount],
        DESC
    )

SELECT 
    [Measures].[Internet Sales Amount] ON COLUMNS,
    Head([FilteredSorted], 100) ON ROWS
FROM [Adventure Works]

2. Кэширование отсортированных наборов:

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

-- Используем кэшированный набор многократно
MEMBER [Measures].[Position] AS
    Rank([Product].[Product].CurrentMember, [CachedSorted])

MEMBER [Measures].[In Top 10] AS
    IIF([Measures].[Position] <= 10, "Yes", "No")

MEMBER [Measures].[Percentile] AS
    ([Measures].[Position] - 1) / (COUNT([CachedSorted]) - 1) * 100,
    FORMAT_STRING = "#,##0.00"

SELECT 
    {[Measures].[Internet Sales Amount],
     [Measures].[Position],
     [Measures].[In Top 10],
     [Measures].[Percentile]} ON COLUMNS,
    [CachedSorted] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Распространенные ошибки

Ошибка 1: Сортировка с NULL в критерии

-- ПРОБЛЕМА: NULL нарушает порядок
ORDER(
    [Product].[Product].Members,
    [Measures].[Some Ratio],  -- Может содержать NULL
    DESC
)

-- РЕШЕНИЕ: Обработка NULL
ORDER(
    [Product].[Product].Members,
    IIF(
        IsEmpty([Measures].[Some Ratio]),
        -999999,  -- Явное значение для NULL
        [Measures].[Some Ratio]
    ),
    DESC
)

Ошибка 2: Избыточная вложенность ORDER

-- ПРОБЛЕМА: Множественные ORDER неэффективны
ORDER(
    ORDER(
        ORDER(
            [Product].[Product].Members,
            [Measures].[Sales],
            DESC
        ),
        [Measures].[Profit],
        DESC
    ),
    [Measures].[Quantity],
    DESC
)

-- РЕШЕНИЕ: Комбинированный критерий
WITH MEMBER [Measures].[Combined] AS
    [Measures].[Sales] * 1000000 +
    [Measures].[Profit] * 1000 +
    [Measures].[Quantity]

SELECT * FROM [Adventure Works]
ORDER(
    [Product].[Product].Members,
    [Measures].[Combined],
    DESC
)

Заключение

Сортировка в MDX — это мощный инструмент, который выходит далеко за рамки простого упорядочивания. Мы изучили:

  • Различные режимы сортировки и их влияние на иерархии
  • Работу с NULL значениями и специальными случаями
  • Создание сложных критериев сортировки
  • Комбинирование сортировки с фильтрацией
  • Техники оптимизации для больших наборов данных
  • Реализацию многоуровневой и условной сортировки

Правильное применение сортировки превращает сырые данные в информативные отчеты, где важная информация всегда на виду, тренды очевидны, а аномалии легко обнаружить.

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

Задание 1: Иерархическая сортировка

Создайте отчет, где страны отсортированы по общим продажам, а внутри каждой страны регионы отсортированы по средней сумме заказа.

Задание 2: Адаптивная сортировка

Реализуйте запрос, который автоматически меняет критерий сортировки в зависимости от выбранного измерения (продукты сортируются по марже, клиенты — по частоте покупок).

Задание 3: Комплексный ABC-анализ

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

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

  1. Когда следует использовать BDESC вместо DESC?
  2. Как эффективно обрабатывать NULL при сортировке?
  3. Можно ли реализовать сортировку по нескольким критериям одновременно?
  4. Как оптимизировать сортировку очень больших наборов?
  5. В чем преимущество кэширования отсортированных наборов?
  6. Как реализовать условную сортировку с разными направлениями?
  7. Какие функции часто используются совместно с ORDER?

Модуль 4: Фильтрация, сортировка и ранжирование • Урок 4.2

Следующий урок!
Прошлый урок