Пользовательские меры (SQL и агрегация)¶

Введение¶

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

Типы параметров с вычисляемыми мерами¶

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

Добавление в существующий параметр с обычными мерами:
Вычисляемая мера добавляется к уже существующим мерам параметра
Колонка для вычисляемой меры не создается в таблице фактов
Значения рассчитываются динамически при запросе данных
Создание нового параметра только с вычисляемыми мерами:
Параметр содержит только вычисляемые меры
Таблица фактов для параметра не создается
Все данные получаются через SQL-запросы или агрегацию

Типы вычисляемых мер¶

1. SQL-запрос¶

Позволяет создать меру на основе произвольного SQL-запроса.

Настройки: - Метод расчёта: SQL-запрос - SQL: Текст SQL-запроса, который должен возвращать данные в формате таблицы фактов

Важно: SQL-запрос должен возвращать результат в том же формате, что и таблица фактов параметра: - dim-колонки для всех измерений параметра (например, dim_product_id, dim_month_id) - m-колонки для всех мер параметра (например, m_quantity, m_amount)

Пример SQL-запроса для параметра с измерениями "Товар" и "Месяц":

SELECT 
    dim_product_id,
    dim_month_id,
    SUM(fact_sales.m_quantity * conversion_factors.factor) as m_calculated_quantity,
    SUM(fact_sales.m_amount) as m_total_amount
FROM fact_sales 
JOIN conversion_factors ON fact_sales.dim_product_id = conversion_factors.product_id
GROUP BY dim_product_id, dim_month_id

Структура SQL-запроса для вычисляемых мер:

dim-колонки: Должны присутствовать все измерения параметра
dim_product_id — измерение "Товар"
dim_month_id — измерение "Месяц"
dim_warehouse_id — измерение "Склад" (если есть)
m-колонки: Должны присутствовать все меры параметра
m_quantity — мера "Количество"
m_amount — мера "Сумма"
m_calculated_quantity — вычисляемая мера
GROUP BY: Должен включать все dim-колонки для правильной группировки

2. Пользовательская агрегация¶

Позволяет создать меру на основе агрегации данных с использованием различных функций.

Настройки: - Метод расчёта: Пользовательская агрегация - Конфигурация расчёта: JSON с параметрами агрегации

Поддерживаемые функции агрегации:

Календарная агрегация (для функций first и last): - first — первое значение в периоде (например, запасы на начало недели) - last — последнее значение в периоде (например, запасы на конец недели)

Скользящая агрегация (для функций sum, avg, min, max): - sum — сумма значений за диапазон периодов - avg — среднее значение за диапазон периодов - min — минимальное значение за диапазон периодов - max — максимальное значение за диапазон периодов

Пример конфигурации для скользящей агрегации:

{
    "agg_func": "sum",
    "source_parameter": "sales",
    "source_measure": "quantity"
}

Пример конфигурации для календарной агрегации:

{
    "agg_func": "last",
    "source_measure": "qty"
}

Важно: Для календарной агрегации (first/last) указывается только source_measure - ключ меры в исходном параметре. Система автоматически определяет исходный параметр по соотношению календарных измерений.

Соотношение измерений в календарной агрегации¶

При использовании календарной агрегации (first/last) система автоматически определяет соотношение между измерениями календаря:

Исходный параметр должен содержать измерение календаря с более детальным уровнем (например, day)
Целевой параметр должен содержать измерение календаря с более общим уровнем (например, week или month)
Система автоматически находит соответствие между уровнями календаря и выбирает нужные значения

Требования: - Календарные измерения должны быть родственными (один уровень должен быть предком другого) - Остальные измерения (не календарные) должны совпадать по типу между параметрами

Создание вычисляемой меры¶

Выбор сценария¶

Перед созданием вычисляемой меры определите подходящий сценарий:

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

Через административную панель¶

Перейдите в раздел "Параметры" → "Меры"
Нажмите "Добавить меру"
Заполните основные поля:
Ключ: Уникальный идентификатор меры
Параметр: Параметр, к которому привязана мера (существующий или новый)
Имя: Отображаемое название меры
Описание: Описание назначения меры
Настройте вычисляемую меру:
Вычисляемая мера: Установите галочку
Метод расчёта: Выберите тип вычисления
Конфигурация расчёта: Заполните JSON-конфигурацию (для пользовательской агрегации)
SQL: Введите SQL-запрос (для SQL-запросов)
Сохраните меру

Примечание: Если вы создаёте новый параметр только с вычисляемыми мерами, таблица фактов для этого параметра не будет создана при синхронизации.

Через код¶

from planiqum.core.parameters.models import Measure, Parameter

# Создание меры с SQL-запросом
measure = Measure.objects.create(
    key='custom_sql_measure',
    parameter=parameter,
    shortname='Вычисляемая мера',
    description='Мера на основе SQL-запроса',
    is_calculated=True,
    calculation_method='raw_sql',
    raw_sql='SELECT SUM(m_qty) FROM fact_sales WHERE dim_month_id = 202401'
)

# Создание меры с пользовательской агрегацией
measure = Measure.objects.create(
    key='custom_agg_measure',
    parameter=parameter,
    shortname='Агрегированная мера',
    description='Мера на основе агрегации',
    is_calculated=True,
    calculation_method='custom_agg',
    calculation_config={
        'agg_func': 'sum',
        'source_parameter': 'sales',
        'source_measure': 'quantity'
    }
)

Использование в отчётах¶

Вычисляемые меры используются в отчётах так же, как обычные меры:

Создайте отчёт в дашборде
Добавьте слой данных и выберите вычисляемую меру
Настройте измерения и фильтры
Сохраните отчёт

Система автоматически выполнит расчёт значений для вычисляемой меры при загрузке данных отчёта.

Ограничения и особенности¶

SQL-запросы¶

Структура результата: SQL-запрос должен возвращать данные в формате таблицы фактов с dim-колонками для измерений и m-колонками для мер
Безопасность: SQL-запросы выполняются в контексте базы данных с ограниченными правами
Производительность: Сложные запросы могут влиять на скорость загрузки отчётов
Совместимость: Запросы должны быть совместимы с используемой СУБД (PostgreSQL/MySQL)

Пользовательская агрегация¶

Доступность данных: Агрегация работает только с существующими данными в системе
Производительность: Для больших объёмов данных может потребоваться оптимизация
Кэширование: Результаты агрегации могут кэшироваться для улучшения производительности

Календарная агрегация (first/last): - Соотношение измерений: Календарные измерения должны быть родственными (один уровень должен быть предком другого) - Автоматическое определение: Система автоматически находит исходный параметр по соотношению календарных измерений - Ограничение: Работает только с календарными измерениями

Скользящая агрегация (sum/avg/min/max): - Диапазон периодов: Требует указания offset_from и offset_to для определения диапазона агрегации - Поддержка ключей: Поддерживает как числовые сдвиги, так и ключи уровней (например, horizon__month) - Ограничение: Не поддерживает два ключа периода одновременно

Синхронизация параметров¶

Параметры с обычными мерами: При добавлении вычисляемой меры колонка для неё не создаётся в таблице фактов
Параметры только с вычисляемыми мерами: Таблица фактов не создаётся при синхронизации параметра
Синхронизация: Выполняется стандартная синхронизация параметра, но структура таблиц зависит от типа мер

Подробнее о синхронизации: См. Синхронизация параметров — как работает синхронизация для разных типов параметров и мер.

Примеры использования¶

Пример 1: Расчёт среднего чека¶

Цель: Создать меру "Средний чек" на основе данных о продажах.

SQL-запрос для параметра с измерениями "Товар" и "Месяц":

SELECT 
    dim_product_id,
    dim_month_id,
    CASE 
        WHEN SUM(m_quantity) > 0 
        THEN SUM(m_amount) / SUM(m_quantity)
        ELSE 0 
    END as m_avg_check
FROM fact_sales 
GROUP BY dim_product_id, dim_month_id

Пример 2: Агрегация запасов по периодам¶

Сценарий: У вас есть параметр stock_history_daily с измерениями "item" и "day", мера "qty" содержит запасы по дням. Нужно создать агрегированные параметры по неделям и месяцам.

Вариант 2.1: Запасы на конец недели¶

Цель: Создать параметр stock_history_weekly с измерениями "item" и "week", где мера "qty" содержит запасы на последний день недели.

Шаги: 1. Создайте параметр stock_history_weekly с измерениями: - item (тот же тип, что и в исходном параметре) - week (календарное измерение, уровень недели)

Создайте вычисляемую меру в параметре stock_history_weekly:
Ключ: qty
Метод расчёта: Пользовательская агрегация

Конфигурация:

{
    "agg_func": "last",
    "source_measure": "qty"
}

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

Вариант 2.2: Запасы на конец месяца¶

Цель: Создать параметр stock_history_monthly с измерениями "item" и "month", где мера "qty" содержит запасы на последний день месяца.

Шаги: 1. Создайте параметр stock_history_monthly с измерениями: - item (тот же тип, что и в исходном параметре) - month (календарное измерение, уровень месяца)

Создайте вычисляемую меру в параметре stock_history_monthly:
Ключ: qty
Метод расчёта: Пользовательская агрегация

Конфигурация:

{
    "agg_func": "last",
    "source_measure": "qty"
}

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

Пример 3: Запасы на начало периода¶

Цель: Создать параметр с запасами на начало недели/месяца.

Конфигурация агрегации:

{
    "agg_func": "first",
    "source_measure": "qty"
}

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

Пример 4: Расчёт конверсии¶

Цель: Создать меру "Конверсия" (отношение продаж к заявкам).