Урок 4.6: Работа с большими наборами и пагинация

Теоретические основы работы с подмножествами

Концепция подмножеств в MDX

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

MDX предоставляет три основных подхода к извлечению подмножеств:

• Позиционное извлечение основано на порядковых номерах элементов в наборе. Мы можем взять первые N элементов (функция HEAD), последние N элементов (функция TAIL) или произвольный диапазон от позиции X до позиции Y (функция SUBSET). Этот подход идеально подходит для создания топ-листов, анализа крайних значений и реализации пагинации.
• Случайная выборка позволяет получить репрезентативное подмножество для статистического анализа. Функция SAMPLE случайным образом выбирает заданное количество элементов из набора, что особенно полезно при работе с очень большими данными, когда анализ полного набора занимает слишком много времени.
• Страничное извлечение (пагинация) — это специальный случай позиционного извлечения, когда большой набор разделяется на страницы фиксированного размера. Пользователь может последовательно просматривать страницы, как в книге, что делает работу с большими данными удобной и эффективной.

Важность порядка элементов

Критически важный момент при работе с подмножествами — это порядок элементов в исходном наборе. Функции HEAD, TAIL и SUBSET работают с позициями элементов, а позиции определяются порядком. Если набор не отсортирован, результаты могут быть непредсказуемыми и бессмысленными.

Рассмотрим простой пример: если мы хотим получить топ-10 продуктов по продажам, недостаточно просто применить HEAD к набору продуктов. Сначала нужно отсортировать продукты по убыванию продаж, и только потом брать первые 10. В противном случае мы получим просто первые 10 продуктов в алфавитном порядке или в порядке их внутренних идентификаторов.

Функция HEAD: Извлечение первых элементов

Детальное изучение синтаксиса HEAD

Функция HEAD имеет простой, но мощный синтаксис:

HEAD(Set_Expression, Count)

Где:

• Set_Expression — любое выражение, возвращающее набор
• Count — количество элементов для извлечения

Давайте начнем с самого базового примера:

-- Простейшее использование HEAD
SELECT 
    [Measures].[Internet Sales Amount] ON COLUMNS,
    HEAD(
        [Product].[Product].[Product].Members,
        10
    ) ON ROWS
FROM [Adventure Works]

Этот запрос вернет первые 10 продуктов из измерения Product. Но какие именно это будут продукты? Это зависит от естественного порядка элементов в измерении, который обычно определяется ключами элементов или алфавитным порядком.

HEAD с предварительной сортировкой

Для получения осмысленных результатов практически всегда нужно сочетать HEAD с ORDER:

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

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[Internet Order Count]
    } ON COLUMNS,
    HEAD([SortedProducts], 20) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Здесь мы сначала создаем именованный набор [SortedProducts], отсортированный по убыванию продаж, затем берем первые 20 элементов. Результат — топ-20 продуктов по продажам за 2013 год.

Оптимизация с NON EMPTY

При работе с большими измерениями многие элементы могут не иметь данных. Важно отфильтровать их перед сортировкой:

WITH 
-- Сначала убираем пустые элементы
SET [NonEmptyProducts] AS
    NON EMPTY 
    [Product].[Product].[Product].Members

-- Затем сортируем только непустые
SET [SortedProducts] AS
    ORDER(
        [NonEmptyProducts],
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Добавляем ранг для каждого продукта
MEMBER [Measures].[Rank] AS
    RANK(
        [Product].[Product].CurrentMember,
        [SortedProducts]
    )

SELECT 
    {
        [Measures].[Rank],
        [Measures].[Internet Sales Amount],
        [Measures].[Internet Order Count]
    } ON COLUMNS,
    HEAD([SortedProducts], 15) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Функция TAIL: Анализ последних элементов

Применение TAIL для анализа худших показателей

Функция TAIL симметрична HEAD, но возвращает последние элементы набора. Это особенно полезно для выявления проблемных областей:

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

-- Берем последние 2 категории (худшие по продажам)
SET [WorstCategories] AS
    TAIL([SortedCategories], 2)

-- Вычисляем среднее по всем категориям
MEMBER [Measures].[Average Sales] AS
    AVG(
        [Product].[Category].[Category].Members,
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Currency"

-- Показываем отставание от среднего
MEMBER [Measures].[Below Average] AS
    [Measures].[Average Sales] - [Measures].[Internet Sales Amount],
    FORMAT_STRING = "Currency"

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[Average Sales],
        [Measures].[Below Average]
    } ON COLUMNS,
    [WorstCategories] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Комбинирование HEAD и TAIL

Часто полезно показать одновременно лучшие и худшие элементы:

WITH 
-- Отфильтрованные и отсортированные магазины
SET [SortedStores] AS
    ORDER(
        FILTER(
            [Store].[Store].[Store].Members,
            [Measures].[Store Sales] > 0
        ),
        [Measures].[Store Sales],
        DESC
    )

-- Топ-5 магазинов
SET [Top5Stores] AS
    HEAD([SortedStores], 5)

-- Худшие 5 магазинов
SET [Bottom5Stores] AS
    TAIL([SortedStores], 5)

-- Объединяем оба набора
SET [TopAndBottom] AS
    {[Top5Stores], [Bottom5Stores]}

-- Добавляем индикатор группы
MEMBER [Measures].[Group] AS
    IIF(
        RANK(
            [Store].[Store].CurrentMember,
            [SortedStores]
        ) <= 5,
        "Top 5",
        "Bottom 5"
    )

SELECT 
    {
        [Measures].[Group],
        [Measures].[Store Sales],
        [Measures].[Store Cost]
    } ON COLUMNS,
    [TopAndBottom] ON ROWS
FROM [Adventure Works]

Функция SUBSET: Гибкое извлечение диапазонов

Понимание параметров SUBSET

Функция SUBSET предоставляет максимальную гибкость:

SUBSET(Set_Expression, Start_Position, Count)

Важно помнить, что Start_Position начинается с 0, как в большинстве языков программирования:

WITH 
-- Сортируем всех клиентов по продажам
SET [AllCustomers] AS
    ORDER(
        NON EMPTY [Customer].[Customer].[Customer].Members,
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Извлекаем клиентов с 21 по 30 (позиции 20-29)
SET [Customers21to30] AS
    SUBSET([AllCustomers], 20, 10)

-- Добавляем абсолютный ранг
MEMBER [Measures].[Overall Rank] AS
    RANK(
        [Customer].[Customer].CurrentMember,
        [AllCustomers]
    )

SELECT 
    {
        [Measures].[Overall Rank],
        [Measures].[Internet Sales Amount],
        [Measures].[Internet Order Count]
    } ON COLUMNS,
    [Customers21to30] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Реализация полноценной пагинации

Пагинация — это ключевое применение SUBSET. Вот комплексный пример:

WITH 
-- Параметры пагинации (в реальном приложении это были бы параметры)
MEMBER [Measures].[PageSize] AS 25
MEMBER [Measures].[CurrentPage] AS 2  -- Страница 2

-- Вычисляем стартовую позицию
MEMBER [Measures].[StartPos] AS
    ([Measures].[CurrentPage] - 1) * [Measures].[PageSize]

-- Подготавливаем данные: фильтруем и сортируем
SET [PreparedData] AS
    ORDER(
        FILTER(
            [Product].[Product].[Product].Members,
            [Measures].[Internet Sales Amount] > 100
        ),
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Общее количество записей
MEMBER [Measures].[TotalRecords] AS
    COUNT([PreparedData])

-- Общее количество страниц
MEMBER [Measures].[TotalPages] AS
    IIF(
        [Measures].[TotalRecords] = 0,
        0,
        CEILING([Measures].[TotalRecords] / [Measures].[PageSize])
    )

-- Извлекаем текущую страницу
SET [CurrentPageData] AS
    SUBSET(
        [PreparedData],
        [Measures].[StartPos],
        [Measures].[PageSize]
    )

-- Позиция в общем списке
MEMBER [Measures].[Position] AS
    RANK(
        [Product].[Product].CurrentMember,
        [PreparedData]
    )

SELECT 
    {
        [Measures].[Position],
        [Measures].[Internet Sales Amount],
        [Measures].[Internet Order Count]
    } ON COLUMNS,
    [CurrentPageData] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Функция SAMPLE: Статистический анализ через выборку

Базовое использование SAMPLE

Функция SAMPLE возвращает случайную выборку заданного размера:

WITH 
-- Случайная выборка из 30 клиентов
SET [RandomCustomers] AS
    SAMPLE(
        [Customer].[Customer].[Customer].Members,
        30
    )

-- Анализируем выборку
MEMBER [Measures].[Sample Average] AS
    AVG(
        [RandomCustomers],
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Currency"

MEMBER [Measures].[Sample StdDev] AS
    STDEV(
        [RandomCustomers],
        [Measures].[Internet Sales Amount]
    ),
    FORMAT_STRING = "Currency"

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[Sample Average],
        [Measures].[Sample StdDev]
    } ON COLUMNS,
    HEAD([RandomCustomers], 10) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Практические сценарии и оптимизация

Сценарий 1: Динамический топ-N отчет

WITH 
-- Определяем процент для топа
MEMBER [Measures].[TopPercent] AS 0.1  -- 10%

-- Все активные продукты
SET [ActiveProducts] AS
    FILTER(
        [Product].[Product].[Product].Members,
        [Measures].[Internet Sales Amount] > 0
    )

-- Количество продуктов в топе
MEMBER [Measures].[TopCount] AS
    CEILING(COUNT([ActiveProducts]) * [Measures].[TopPercent])

-- Сортированные продукты
SET [SortedProducts] AS
    ORDER(
        [ActiveProducts],
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Топ 10% продуктов
SET [Top10Percent] AS
    HEAD(
        [SortedProducts],
        IIF(
            [Measures].[TopCount] > 0,
            [Measures].[TopCount],
            1
        )
    )

-- Доля топа в общих продажах
MEMBER [Measures].[Top Share] AS
    SUM([Top10Percent], [Measures].[Internet Sales Amount]) /
    SUM([ActiveProducts], [Measures].[Internet Sales Amount]),
    FORMAT_STRING = "Percent"

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[Top Share]
    } ON COLUMNS,
    HEAD([Top10Percent], 20) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Сценарий 2: Адаптивная пагинация с навигацией

WITH 
-- Параметры
MEMBER [Measures].[RowsPerPage] AS 20
MEMBER [Measures].[RequestedPage] AS 3

-- Базовый набор
SET [BaseSet] AS
    FILTER(
        [Customer].[Customer].[Customer].Members,
        [Measures].[Internet Sales Amount] > 1000
    )

-- Сортированный набор
SET [SortedSet] AS
    ORDER(
        [BaseSet],
        [Measures].[Internet Sales Amount],
        DESC
    )

-- Метаданные
MEMBER [Measures].[TotalRows] AS COUNT([SortedSet])
MEMBER [Measures].[TotalPages] AS 
    CEILING([Measures].[TotalRows] / [Measures].[RowsPerPage])

-- Корректировка запрошенной страницы
MEMBER [Measures].[ActualPage] AS
    IIF(
        [Measures].[RequestedPage] > [Measures].[TotalPages],
        [Measures].[TotalPages],
        IIF(
            [Measures].[RequestedPage] < 1,
            1,
            [Measures].[RequestedPage]
        )
    )

-- Начальная позиция
MEMBER [Measures].[StartRow] AS
    ([Measures].[ActualPage] - 1) * [Measures].[RowsPerPage]

-- Извлекаем страницу
SET [PageData] AS
    SUBSET(
        [SortedSet],
        [Measures].[StartRow],
        [Measures].[RowsPerPage]
    )

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[Internet Order Count]
    } ON COLUMNS,
    [PageData] ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Заключение

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

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

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

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

Базовый уровень

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

Средний уровень

Реализуйте пагинацию для списка магазинов с размером страницы 10 записей, показывая страницу номер 2.

Продвинутый уровень

Создайте запрос со случайной выборкой из 5 продуктов для каждой подкатегории и рассчитайте среднюю цену продажи для каждой выборки.