Вычисляемые меры ¶

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

Обзор подходов к вычислениям ¶

Подход	Применение	Структура в БД	Производительность
Вычисляемый параметр	Комплексные аналитические расчеты на основе других таблиц	PostgreSQL VIEW/MATERIALIZED VIEW	Высокая (особенно materialized)
Generated Columns	Простые вычисления на уровне строки таблицы	Колонки в fact-таблице	Очень высокая
Вычисляемые меры	Гибкие вычисления с календарными агрегациями	Динамические подзапросы	Низкая, но универсальная

Вычисляемый параметр ¶

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

Настройка вычисляемого параметра¶

В админке Django: 1. ✅ Вычисляемый параметр (is_calculated = True) 2. 📝 SQL для расчёта данных — SQL-запрос, который должен возвращать колонки для всех измерений и мер параметра 3. ☐ Использовать материализованный VIEW (опционально) — для ускорения запросов

Ограничения для вычисляемых параметров¶

Нельзя создавать отдельные вычисляемые меры — все расчёты должны быть описаны в SQL параметра
Нельзя загружать файлы — данные формируются только SQL-запросом
SQL должен возвращать ВСЕ колонки параметра в формате dim_{dimension.key} и m_{measure.key}

Пример создания вычисляемого параметра¶

from planiqum.core.parameters.models import Parameter

analytics_param = Parameter.objects.create(
    key='sales_analytics',
    name='Аналитика продаж',
    is_calculated=True,
    calculation_sql="""
        SELECT 
            dim_product_id,
            dim_month_id,
            dim_region_id,
            AVG(amount) OVER (
                PARTITION BY dim_product_id 
                ORDER BY dim_month_id 
                ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
            ) as m_rolling_avg_12m,
            SUM(amount) OVER (
                PARTITION BY dim_region_id
            ) as m_total_by_region
        FROM fact_sales_daily
        WHERE amount IS NOT NULL
    """,
    use_materialized_view=False  # или True для материализованного VIEW
)

# Синхронизация создаст VIEW fact_sales_analytics  
analytics_param.sync()

Вычисляемые меры (классический подход) ¶

Мера в модели planiqum.core.parameters.models.Measure может быть помечена как "кастомная" (вычисляемая).

В этом случае в отчёте вместо прямого обращения к fact-таблице для слоя, использующего эту меру, будет строиться подзапрос с помощью классов из модуля planiqum.core.parameters.libs.calculated_measures.

Примечание: Этот подход используется для параметров с is_calculated = False, когда нужна большая гибкость в вычислениях.

Виды вычисляемых мер ¶

1. Вычисление кастомного SQL ¶

Описание

Вычисление кастомных SQL-запросов реализовано в классе planiqum.core.parameters.libs.calculated_measures.raw_sql.RawSQLCalculation.

В настройках меры требуется указать только SQL-запрос, который будет выполняться.

Запрос должен возвращать набор колонок, в котором будут:

колонки, соответствующие всем измерениям параметра (формат: f"dim_{dimension.key}")
колонка, соответствующая кастомной мере (формат: f"m_{measure.key}")

Варианты использования

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

Для нового параметра прописывается набор измерений и кастомная мера, вычисляемая как SQL-запрос.

Если в параметре нет других мер кроме кастомных, fact-таблица для такого параметра не создаётся.

Добавление кастомной меры к существующему параметру¶

Для существующего параметра (с прописанным набором измерений и мер) добавляется кастомная мера, вычисляемая как SQL-запрос.

Колонка в fact-таблице для такой меры не создаётся.

2. Вычисление кастомных календарных агрегаций ¶

Описание

Вычисление сводится к расчёту подзапроса, но построение запроса упрощается - используется описание того, какую агрегацию ожидает пользователь.

Агрегация проводится по измерению, построенному на уровне иерархии типа "календарь" (для каждого элемента определены дата начала и окончания).

Поддерживаются два типа агрегации:

period_snapshot - для функций first и last (значение на начало/конец периода)
period_window_aggregation - для функций sum, avg, count, min, max (скользящие агрегации)

Ограничения по уровням иерархии ¶

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

В обоих случаях набор некалендарных измерений должен совпадать полностью между source_measure и target_measure.

Ограничения для period_snapshot¶

Для period_snapshot можно создавать параметры на другом уровне иерархии по отношению к source_measure, но при этом:

Календарное измерение source_measure должно быть предком календарного измерения target_measure (включая самого себя)
Будет использоваться метод иерархии от контекста (если в отчёте мера выводится на другом уровне иерархии, то будет использоваться уровень иерархии из отчёта)

Пример: Можно создать меру на уровне недели, ссылающуюся на меру уровня дня, и использовать её в отчётах на уровне месяца.

Ограничения для period_window_aggregation¶

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

Уровни иерархии для календарных измерений должны совпадать
Можно создать меру в новом параметре (сигнатуры должны совпадать с исходным) или добавить меру в существующий параметр

Конфигурация period_snapshot ¶

Описание¶

period_snapshot используется для получения значения показателя на начало или конец временного периода.

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

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

или

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

Логика агрегации и контекстная зависимость¶

Принцип работы¶

Важно: Данные хранятся только в исходной мере (например, запасы по дням), а в вычисляемых мерах строится запрос к таблице с исходными значениями.

Контекстная зависимость¶

Вычисляемые меры period_snapshot адаптируются к контексту использования в отчёте:

Вариант 1: Добавление меры в существующий параметр

Если у нас есть мера с запасами по дням (stock_history_daily__qty), то мы можем добавить в неё вычисляемую меру, которая будет возвращать запасы на первый/последний день периода в зависимости от того, какой уровень иерархии используется в отчёте:

В отчёте на уровне "день" - значение на начало/конец дня
В отчёте на уровне "неделя" - значение на начало/конец недели
В отчёте на уровне "месяц" - значение на начало/конец месяца

Вариант 2: Создание нового параметра

Можно создать новый параметр на уровне иерархии "месяц", в котором будут содержаться запасы на начало/конец месяца. Если этот параметр будет использоваться в отчёте на уровне "год", то будет, соответственно, начало/конец года (зависимость от контекста).

Функции агрегации¶

"agg_func": "first" - значение на начало периода
"agg_func": "last" - значение на конец периода

Конфигурация period_window_aggregation ¶

Описание¶

period_window_aggregation используется для скользящих агрегаций (сумма, среднее, количество, минимум, максимум) за определенный период.

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

{
    "agg_func": "sum",
    "source_measure": "sales_history_daily__amount",
    "offset_from": -3,
    "offset_to": 0
}

Использование offset_from и offset_to ¶

Целочисленные смещения¶

Целочисленные offset_from и offset_to означают смещение в тех периодах, на которых определено календарное измерение:

Отрицательные числа - смещение "в прошлое" относительно "текущего периода"
Положительные числа - смещение "в будущее" относительно "текущего периода"

Логика значений по умолчанию

Если указано только одно значение, а второе не указано, то оно автоматически подставляется по логике "до/от текущего элемента включительно":

Если указан только offset_from - offset_to автоматически становится 0
Если указан только offset_to - offset_from автоматически становится 0

Исключение текущего элемента

Если мы не хотим включать текущий элемент в агрегацию, то нужно указать -1:

offset_from: -4, offset_to: 0 - 3 периода до и текущий (всего 4 периода)
offset_from: -4, offset_to: -1 - 3 периода до без текущего (всего 3 периода)

Маркеры периодов¶

Могут использоваться "маркеры" для задания "начала или конца периода":

Доступные маркеры

Маркер	Описание	Применение
`начн`	Начало недели	offset_from
`конн`	Конец недели	offset_to
`начм`	Начало месяца	offset_from
`конм`	Конец месяца (при сдвигах всегда возвращает конец месяца)	offset_to
`начк`	Начало квартала	offset_from
`конк`	Конец квартала	offset_to
`начкв`	Начало квартала (альтернативное обозначение)	offset_from
`конкв`	Конец квартала (альтернативное обозначение)	offset_to
`начг`	Начало года	offset_from
`конг`	Конец года	offset_to

Комбинирование маркеров с числами

Если одна из границ задана как "маркер", то вторая может быть числом:

offset_from: "начм", offset_to: 0 - от начала месяца до текущего периода включительно
offset_from: "начм", offset_to: -1 - от начала месяца до текущего периода не включая
offset_from: 0, offset_to: "конм" - от текущего периода до конца месяца включительно
offset_from: "начк", offset_to: "конк" - от начала до конца квартала
offset_from: "начкв", offset_to: "конкв" - от начала до конца квартала (альтернативное обозначение)

Ограничения на маркеры

Можно задать обе границы как маркер, но в этом случае они должны относиться к одному и тому же уровню иерархии, иначе это будет ошибка:

✅ offset_from: "начм", offset_to: "конм" - корректно
✅ offset_from: "начк", offset_to: "конк" - корректно
✅ offset_from: "начкв", offset_to: "конкв" - корректно
❌ offset_from: "начн", offset_to: "конм" - ошибка (разные уровни)

Особенности поведения маркеров:

конм (конец месяца) - при сдвигах всегда возвращает конец месяца:
offset_from: "конм", offset_to: 1 - от конца месяца до конца следующего месяца
offset_from: "конм", offset_to: -1 - от конца месяца до конца предыдущего месяца

Примеры бизнес-задач ¶

Запасы на начало/конец временного периода¶

Исходная мера: stock_history_daily__qty - история запасов по дням

Создаваемые параметры:

Запасы на начало недели

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

Запасы на конец недели

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

Запасы на начало месяца

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

Скользящие агрегации для плана продаж¶

Исходная мера: sales_plan_monthly__amount - план продаж по месяцам

Создаваемые параметры:

Скользящее среднее за 12 месяцев

{
    "agg_func": "avg",
    "source_measure": "sales_plan_monthly__amount",
    "offset_from": -11,
    "offset_to": 0
}

Нарастающая сумма с начала года

{
    "agg_func": "sum",
    "source_measure": "sales_plan_monthly__amount",
    "offset_from": "начг",
    "offset_to": 0
}

Убывающая сумма до конца года

{
    "agg_func": "sum",
    "source_measure": "sales_plan_monthly__amount", 
    "offset_from": 0,
    "offset_to": "конг"
}

Продажи за последние 3 месяца (без текущего)

{
    "agg_func": "sum",
    "source_measure": "sales_plan_monthly__amount",
    "offset_from": -3,
    "offset_to": -1
}

Продажи за текущий квартал

{
    "agg_func": "sum",
    "source_measure": "sales_plan_monthly__amount",
    "offset_from": "начк",
    "offset_to": "конк"
}

Типичные ошибки и их устранение ¶

Ошибки валидации period_snapshot¶

"Календарное измерение исходной меры не является предком календарного измерения целевой меры"

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

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

"Среди измерений параметра не найдено календарное измерение"

Причина: В параметре отсутствует измерение с календарным уровнем иерархии

Решение: Добавьте календарное измерение в параметр

"Набор некалендарных измерений должен совпадать между исходной и целевой мерами"

Причина: Исходная и целевая меры имеют разные наборы некалендарных измерений

Решение: Убедитесь, что параметры содержат одинаковый набор некалендарных измерений

Ошибки валидации period_window_aggregation¶

"Набор некалендарных измерений должен совпадать между исходной и целевой мерами"

Причина: Исходная и целевая меры имеют разные наборы некалендарных измерений

Решение: Убедитесь, что параметры содержат одинаковый набор некалендарных измерений

"Уровни иерархии для offset_from и offset_to должны совпадать"

Причина: Использование маркеров разных уровней иерархии

Решение: Используйте маркеры одного уровня (например, только месячные или только недельные)

"offset_to должен быть больше offset_from"

Причина: Неправильный порядок границ

Решение: Убедитесь, что offset_to больше offset_from

"Должна быть указана хотя бы одна граница (offset_from или offset_to)"

Причина: Не указаны границы агрегации

Решение: Укажите хотя бы одну из границ

Ошибки SQL-валидации¶

"Доступ к таблице запрещён"

Причина: Использование неразрешённых таблиц

Решение: Используйте только таблицы с префиксами fact_, dim_ или core_hierarchy_item

"Отсутствуют поля измерений"

Причина: SQL-запрос не возвращает поля в ожидаемом формате

Решение: Убедитесь, что запрос возвращает поля в формате dim_{dimension.key} и m_{measure.key}

Архитектурные принципы ¶

Выбор подходящего метода¶

Используйте вычисляемый параметр если: - Нужны комплексные аналитические расчеты - Данные берутся из нескольких таблиц - Требуется высокая производительность - Все данные параметра рассчитываются одним SQL-запросом

Используйте классические вычисляемые меры если: - Нужны календарные агрегации - Требуется максимальная гибкость - Часто изменяется логика расчетов - Приемлема более низкая производительность

Ограничения и конфликты¶

Вычисляемый параметр: нельзя создавать отдельные вычисляемые меры
Классические вычисляемые меры: нельзя использовать в вычисляемых параметрах

Ссылки на код ¶

Модели и валидация¶

/src/planiqum/core/parameters/models.py :: Parameter /src/planiqum/core/parameters/models.py :: Measure

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

/src/planiqum/core/parameters/libs/sync.py :: ParameterTableSynchronizer /src/planiqum/core/parameters/libs/parameter_view_builder.py :: ParameterViewBuilder

Основные классы вычисляемых мер¶

/src/planiqum/core/parameters/libs/calculated_measures/base.py :: BaseCalculationMethod /src/planiqum/core/parameters/libs/calculated_measures/registry.py :: get_method_instance

Методы вычисления¶

/src/planiqum/core/parameters/libs/calculated_measures/raw_sql.py :: RawSQLCalculation /src/planiqum/core/parameters/libs/calculated_measures/period_snapshot.py :: PeriodSnapshotCalculation /src/planiqum/core/parameters/libs/calculated_measures/period_window_aggregation.py :: PeriodWindowAggregationCalculation

Валидация¶

/src/planiqum/core/parameters/libs/calculated_measures/validators.py :: PeriodAggregationValidator /src/planiqum/core/parameters/libs/calculated_measures/validators.py :: SQLValidator

Generated column (вычисляемые колонки) ¶

Новое в PLQM-1161! Поддержка generated column (вычисляемых колонок) теперь реализована в системе.

Описание¶

Generated columns — это колонки в PostgreSQL, значения которых автоматически вычисляются на основе других колонок таблицы. Они обеспечивают очень высокую производительность, так как вычисления происходят на уровне базы данных.

Настройка generated columns¶

В админке Django: 1. Выберите меру в параметре 2. Установите Метод расчёта = generated_column 3. Введите SQL-запрос для вычисления значения 4. Сохраните изменения

Пример SQL-запроса:

-- Вычисление суммы двух мер
m_revenue + m_cost

-- Вычисление с условием
CASE WHEN m_qty > 0 THEN m_revenue / m_qty ELSE 0 END

-- Вычисление с функциями
ROUND(m_revenue * 1.2, 2)

Преимущества generated columns¶

Очень высокая производительность — вычисления на уровне БД
Автоматическое обновление — значения пересчитываются при изменении исходных данных
Индексация — можно создавать индексы на generated columns
Целостность данных — значения всегда актуальны

Ограничения¶

Только простые вычисления — нельзя использовать подзапросы или JOIN
Только колонки той же таблицы — нельзя ссылаться на другие таблицы
PostgreSQL 12+ — требует современную версию PostgreSQL
Неизменяемость — значения нельзя изменять напрямую

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

from planiqum.core.parameters.models import Measure

# Создание меры с generated column
profit_measure = Measure.objects.create(
    key='profit',
    parameter=parameter,
    shortname='Прибыль',
    type=0,  # числовая
    is_calculated=True,
    calculation_method='generated_column',
    raw_sql='m_revenue - m_cost'  # SQL для вычисления
)

# Синхронизация создаст generated column
parameter.sync()

Синхронизация generated columns¶

При синхронизации параметра система: 1. Проверяет наличие мер с calculation_method='generated_column' 2. Создает generated columns в таблице фактов 3. Обновляет структуру таблицы при изменениях 4. Удаляет generated columns при удалении мер

Тестирование¶

Generated columns тестируются в следующих тестах: - test_generated_column_creation — создание generated columns - test_generated_column_cycle — проверка циклических зависимостей - test_generated_column_dependency_detection — обнаружение зависимостей

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