SQL-движок и схема данных
Движок визуализации Pyrus использует ClickHouse — высокопроизводительную колоночно-ориентированную систему управления базами данных, идеально подходящую для сложных аналитических запросов (OLAP). Это позволяет быстро обрабатывать большие объемы данных и строить отчёты в реальном времени.
Как работает создание отчёта
Процесс преобразования вашего SQL-запроса в готовый виджет проходит несколько шагов.
- Вы пишите SQL-запрос в редакторе, который может включать псевдонимы и параметры.
- При нажатии кнопки Выполнить параметры заменяются на актуальные значения из интерфейса пользователя (например, выбранные фильтры, значения полей задачи).
- Финальный запрос отправляется в ClickHouse, который формирует выборку из таблиц #tasks или #events в единую таблицу.
- После получения ответа от ClickHouse распознаются и применяются псевдонимы (например, ID задачи преобразуется в активную ссылку на задачу).
- Итоговый, отформатированный результат выводится пользователю в виде виджета.
Обратите внимание: если запрос некорректен, система сразу сообщает об ошибке (например, об ошибках синтаксиса).
Терминология
В SQL-запросах используются два типа специальных маркеров, которые помогают связать статический запрос с динамическим контекстом Pyrus.
| Термин | Описание | Использование в SQL |
|---|---|---|
| Псевдонимы | Преобразуют системные числовые ID (статусы, пользователи, этапы) в понятные человеку названия или ссылки после получения данных от ClickHouse | Исключительно в секции SELECT AS. |
| Параметры | Динамически встраивают в запрос актуальные значения из задач, а также выбранные в интерфейсе значения фильтров до отправки в ClickHouse. | FROM, WHERE, HAVING, ORDER BY. |
Источники данных
Для построения отчётов используются две основные таблицы, отличающиеся по своей природе: таблица текущего состояния #tasks и таблица истории событий #events.
Общие поля для #tasks и #events
В этом разделе представлены все поля, которые доступны для использования в обеих таблицах.
Обратите внимание: при использовании этих параметров с таблицей #tasks результатом будет актуальное состояние поля, при вызове в #events результатом будет значение в рассматриваемом временном интервале.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| form_id | ID формы | UInt32 | — |
| task_id | ID задачи | UInt32 | — |
| ${Выбор} | Поле типа Выбор, 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 |
| is_closed | Поле типа Открыта / Завершена, true / false | Bool | False |
| responsible_id | Поле типа Ответственный, ID пользователя | Int32 | 0 |
| create_date | Поле типа Дата создания | DateTime64(3,'UTC') | — |
| step | Поле типа Этап, ID этапа | Int16 | 0 |
| status | Поле типа Статус, ID статуса | Int16 | 0 |
Таблица #tasks
Эта таблица содержит актуальное состояние задач (например, поля формы, статус задачи). Помимо системных полей, все поля, созданные в форме, доступны для выборки из этой таблицы через их параметры. Каждой задаче соответствует ровно одна строка. Поля, перечисленные в этом разделе, являются уникальными и не доступны к использованию в таблице #events.
Подсказка: используйте #tasks для анализа текущих показателей, например: сколько задач находится в работе, каково их текущее состояние и т.д.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| due | Поле типа Срок | DateTime64(3,'UTC') | 2200-01-01 |
| is_overdue | Задача просрочена | 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 |
Таблица #events
Эта таблица хранит хронологию всех изменений по задаче. Каждая строка — это отдельное зафиксированное системное событие (например, смена ответственного, добавление комментария, изменение статуса). При этом одной строке может соответствовать множество изменений внутри системы.
Подсказка: используйте #events для метрик, связанных со временем, переходами и аудитом: сколько времени занял этап, кто и когда внес изменения в задачу и т.д.
| Поле | Описание | Тип ClickHouse | Значение по умолчанию |
|---|---|---|---|
| note_id | Идентификатор комментария | UInt64 | — |
| author_id | Идентификатор автора события | UInt32 | 0 |
| task_created | Задача создана этим событием | Bool | False |
| operator_reply | Ответ оператора (исходящее сообщение во внешний канал) | 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 |
| is_step_changed | Этап изменился в данном событии | Bool | False |
| previous_step | Предыдущий этап | Int16 | -1 |
| seconds_in_step | Сколько в секундах находились на прошлом этапе | Int64 | 0 |
| is_step_frozen | Заморожен ли SLA на прошлом этапе | Bool | False |
| is_step_overdue | Завершился ли прошлый этап после установленного для него срока | Bool | — |
| 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-запрос с динамическим контекстом 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-запроса — 10 сек.
Далее рекомендуем ознакомиться с Примерами виджетов