SQL-движок и схема данных
Движок визуализации Pyrus использует ClickHouse — высокопроизводительную колоночно-ориентированную систему управления базами данных, идеально подходящую для сложных аналитических запросов (OLAP). Это позволяет быстро обрабатывать большие объемы данных и строить отчёты в реальном времени.
Аналитическая модель Pyrus строится на основе двух видов данных:
- системные таблицы: таблица текущего состояния #tasks и таблица истории событий #events;
- пользовательские поля: поля форм, которые дополняют системные таблицы и становятся доступными для SQL-запросов через параметры.
При написании SQL-запроса вы оперируете либо таблицей #tasks, либо таблицей #events, которые дополнены вашими пользовательскими полями формы.
Как работает создание отчёта
Процесс преобразования вашего SQL-запроса в готовый виджет проходит несколько шагов.
- Вы пишите SQL-запрос в редакторе, который может включать псевдонимы и параметры.
- При нажатии кнопки Выполнить параметры заменяются на актуальные значения из интерфейса пользователя (например, выбранные фильтры, значения полей задачи).
- Финальный запрос отправляется в ClickHouse, который формирует выборку из таблиц #tasks или #events в единую таблицу.
- После получения ответа от ClickHouse распознаются и применяются псевдонимы (например, ID задачи преобразуется в активную ссылку на задачу).
- Итоговый, отформатированный результат выводится пользователю в виде виджета.
Обратите внимание: если запрос некорректен, система сразу сообщает об ошибке (например, об ошибках синтаксиса).
Терминология
В SQL-запросах используются два типа специальных маркеров, которые помогают связать статический запрос с динамическим контекстом Pyrus.
| Термин | Описание | Использование в SQL |
|---|---|---|
| Псевдонимы | Преобразуют системные числовые ID (статусы, пользователи, этапы) в понятные человеку названия или ссылки после получения данных от ClickHouse | Исключительно в секции SELECT AS. |
| Параметры | Динамически встраивают в запрос актуальные значения из задач, а также выбранные в интерфейсе значения фильтров до отправки в ClickHouse. | FROM, WHERE, HAVING, ORDER BY. |
Источники данных
Для построения отчётов используются две основные таблицы, отличающиеся по своей природе: таблица текущего состояния #tasks и таблица истории событий #events. Обе таблицы включают в себя все доступные пользовательские поля из ваших форм.
Таблица #tasks
Эта таблица содержит актуальное состояние задач (например, поля формы, статус задачи). Каждой задаче соответствует ровно одна строка.
Подсказка: используйте #tasks для анализа текущих показателей, например: сколько задач находится в работе, каково их текущее состояние и т.д.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| form_id | ID формы | UInt32 | — |
| task_id | ID задачи | UInt32 | — |
| create_date | Поле типа Дата создания | DateTime64(3,'UTC') | — |
| is_closed | Поле типа Открыта / Завершена, true / false | Bool | False |
| due | Поле типа Срок | DateTime64(3,'UTC') | 2200-01-01 |
| is_overdue | Задача просрочена | Bool | False |
| step_due | Срок SLA текущего этапа | DateTime64(3,'UTC') | 2200-01-01 |
| is_step_overdue | SLA этапа просрочено | Bool | False |
| status_due | Срок SLA текущего статуса | DateTime64(3,'UTC') | 2200-01-01 |
| is_status_overdue | SLA статуса просрочено | Bool | False |
| creator_id | Идентификатор автора задачи | UInt32 | Система: 1730 Подпроцесс: 656701 |
| last_note_id | ID последнего комментария | UInt64 | — |
| last_modified_date | Дата последнего изменения | DateTime64(3,'UTC') | — |
| close_date | Дата закрытия задачи | DateTime64(3,'UTC') | 2200-01-01 |
| step | Поле типа Этап, ID этапа | Int16 | 0 |
| status | Поле типа Статус, ID статуса | Int16 | 0 |
| responsible_id | Поле типа Ответственный, ID пользователя | Int32 | 0 |
Таблица #events
Эта таблица хранит хронологию всех изменений по задаче. Каждая строка — это отдельное зафиксированное системное событие (например, смена ответственного, добавление комментария, изменение статуса). При этом одной строке может соответствовать множество изменений внутри системы.
Подсказка: используйте #events для метрик, связанных со временем, переходами и аудитом: сколько времени занял этап, кто и когда внес изменения в задачу и т.д.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| form_id | ID формы | UInt32 | — |
| task_id | ID задачи | UInt32 | — |
| note_id | Идентификатор комментария | UInt64 | — |
| create_date | Поле типа Дата создания | DateTime64(3,'UTC') | — |
| author_id | Идентификатор автора события | UInt32 | 0 |
| task_created | Задача создана этим событием | Bool | False |
| operator_reply | Ответ оператора (исходящее сообщение во внешний канал) | Bool | False |
| is_closed | Поле типа Открыта / Завершена, true / false | Bool | False |
| task_due | Поле типа Срок | DateTime64(3,'UTC') | 2200-01-01 |
| is_task_overdue | Задача просрочена на момент события | Bool | False |
| task_create_date | Дата создания задачи | DateTime64(3,'UTC') | — |
| task_creator_id | Идентификатор создателя задачи | UInt32 | Система: 1730 Подпроцесс: 656701 |
approval_type Текущие значения: 0 – не указано 1 – утверждено 2 – отказано 3 – отложено 4 – просмотрено | Решение: Утверждено, Просмотрено и т.д. | Int8 | 0 |
| spent_time | Затраченное время на задачу, в минутах | Int32 | 0 |
| comment_as_role_id | ID роли, от имени которой комментарий | Int32 | 0 |
| step | Поле типа Этап, ID этапа | Int16 | 0 |
| is_step_changed | Этап изменился в данном событии | Bool | False |
| previous_step | Предыдущий этап | Int16 | -1 |
| seconds_in_step | Сколько в секундах находились на прошлом этапе | Int64 | 0 |
| is_step_frozen | Заморожен ли SLA на прошлом этапе | Bool | False |
| is_step_overdue | Завершился ли прошлый этап после установленного для него срока | Bool | — |
| responsible_id | Поле типа Ответственный, ID пользователя | Int32 | 0 |
| is_status_changed | Статус изменился в данном событии | Bool | False |
| previous_status | Предыдущий статус | Int16 | -1 |
| is_status_frozen | Заморожен ли SLA на прошлом статусе | Bool | False |
| is_status_overdue | Изменился ли прошлый статус после установленного для него срока | Bool | False |
| is_responsible_changed | Ответственный изменился в данном событии | Bool | False |
| previous_responsible_id | Предыдущий ответственный | Int32 | -1 |
Пользовательские поля формы
В этом разделе представлены типы полей, которые вы добавляете в настройках шаблоне формы. В SQL-запросах они доступны как дополнительные параметры системных таблиц.
Обратите внимание: при использовании этих параметров с таблицей #tasks — результатом будет актуальное значение поля формы, при использовании с таблицей #events результатом будет значение поля в рассматриваемом временном интервале.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| ${Выбор} | Поле типа Выбор, ID значения | Nullable(Int64) | Null |
| ${Галочка} | Поле типа Галочка, true / false | Nullable(Bool) | Null |
| ${Справочник:N} | Поле типа Справочник, выбранное текстовое значение из N колонки справочника | Array(Nullable(String)) | Null |
| ${Форма} | Поле типа Форма, ID задачи | Nullable(Int64) | Null |
| ${Число} | Поле типа Число, введенное число | Nullable(Float64) | Null |
| ${Деньги} | Поле типа Деньги, введенное число | Nullable(Float64) | Null |
| ${Дата} | Поле типа Дата, введенная дата | Nullable(DateTime64(9,'UTC')) | Null |
| ${Время} | Поле типа Время, введенное время | Nullable(DateTime64(9,'UTC')) | Null |
| ${Контакт} | Поле типа Контакт, ID пользователя | Nullable(Int64) | Null |
Обратите внимание: в движке визуализации не поддерживаются следующие поля формы:
- пользовательские поля: Текст, Телефон, Эл. почта;
- оформительские поля: Таблица, Примечание.
Система псевдонимов и параметров
Псевдонимы и параметры — это специальные маркеры, которые позволяют связать статический SQL-запрос с динамическим контекстом Pyrus.
Псевдонимы (SELECT AS)
Псевдонимы используются только как алиасы (AS) в секции SELECT. Они преобразуют числовые данные в текст или ссылки.
Подсказка: если вы не используете псевдоним, в колонке будет отображаться исходное числовое значение (ID, код статуса и т.д.).
| Описание псевдонима | Псевдоним | Применение |
|---|---|---|
| ID пользователя → ФИО | #person_full_ | responsible_id AS "#person_full_ |
| ID пользователя → Наименование подразделения | #person_ | responsible_id AS "#person_ |
| ID статуса → Название статуса | #status_name('Заголовок') | status AS "#status_name('Статус')" |
| ID этапа → Название этапа | #step_name('Заголовок') | step AS "#step_name('Этап')" |
| ID поля типа выбор → Значение поля типа выбор | #choice_name(${Код поля}) #choice_name(${Код поля}, 'Заголовок') | ${Канал} AS "#choice_name(${Канал}, |
| ID задачи → Название задачи | #task('Заголовок') | task_id AS "#task('Задача')" |
Параметры (встраиваемые в SQL)
Параметры динамически влияют на то, что и как запрашивается из базы данных.
| Описание параметра | Параметр | Применение |
|---|---|---|
| Выбор источника данных | #tasks / #events | FROM #tasks (для текущего состояния); FROM #events (для истории) |
| Начало/конец выбранного периода дат. | @period_start / @period_end | WHERE create_date >= @period_start |
| Выражение, соответствующее фильтрам, которые пользователь выбрал в интерфейсе сводки для запроса по текущему состоянию задачи | #task_filters | WHERE #task_filters — применяет фильтры, выбранные пользователем в интерфейсе сводки. |
Аналогично макросу #task_filters, но применяется в HAVING при запросах к историческим данным #events | #event_filters | HAVING #event_filters — применяет фильтры, специфичные для работы с таблицей #events |
Используется в ORDER BY для корректной сортировки статусов по их порядку в настройках формы | #ordered_statuses | ORDER BY #ordered_statuses — обеспечивает корректную сортировку статусов в выводе согласно их порядку в настройках формы. |
Ограничения
- Все SQL-запросы к данным выполняются в режиме чтения. Доступны только операции SELECT.
- Максимальное число строк в таблице результатов — 1000.
- Максимальное время обработки SQL-запроса — 5 сек.
Далее рекомендуем ознакомиться с Примерами виджетов