Типы данных для построения виджетов

При написании SQL-запросов важно понимать тип значения, возвращаемого из ClickHouse, так как это определяет, как данные будут интерпретированы и как их можно будет визуализировать и форматировать для конечного пользователя.

Типы данных для SQL-запросов

Числовой тип

Числовые типы используются для расчетов, агрегаций и построения оси Y (значений) на графиках.

Числовые типы данных в Pyrus представлены в ClickHouse следующими типами: Int8, Int16, Int32, UInt32, Int64, Nullable(Int64), UInt64, Float64, Nullable(Float64).

Поле типа Деньги

Пример: в форме для ведения продаж есть поле типа Деньги с кодом ${Деньги}, в котором менеджеры указывают сумму заключённой сделки. Нам необходимо определить, сколько открытых задач приходится на каждую сумму сделки.

SELECT
    ${Деньги} AS "Сумма сделки",
    COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Деньги}
ORDER BY ${Деньги}
LIMIT 100;

Поле типа Контакт

Пример: в форме Обработка заявок клиентов есть поле типа Контакт с кодом ${Ответственный}, в котором указывается сотрудник, назначенный ответственным за выполнение заявки. Мы хотим увидеть загрузку сотрудников: сколько активных заявок приходится на каждого ответственного.

Поле типа Контакт возвращает ID пользователя. Чтобы вместо цифр увидеть имя сотрудника, используем параметры форматирования #person_full_name.

SELECT
  ${Ответственный} AS "#person_full_name('Ответственный')",
  COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Ответственный}
ORDER BY COUNT(task_id) DESC
LIMIT 100;

Поле типа Выбор

Пример: в форме Help Desk есть поле типа Выбор*» с кодом ${Канал}, в котором оператор указывает, откуда пришло обращение: электронная почта, звонок, чат или мессенджер. Мы хотим увидеть, какие каналы приносят больше всего открытых обращений.

По умолчанию все значения поля типа Выбор являются числовыми (ID вариантов). Чтобы в отчёте отображались названия каналов, а не цифры, используем псевдоним #choice_name, где Канал обращения — название колонки в таблице, а ${Канал} — код поля типа Выбор в форме.

SELECT
  ${Канал} AS "#choice_name(${Канал},'Канал обращения')",
  COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Канал}
ORDER BY COUNT(task_id) DESC
LIMIT 100;

Поле типа Форма

Пример: в форме Проекты есть поле типа Форма с кодом ${Форма}, через которое к проекту прикрепляется задача из формы Договоры. Мы хотим увидеть, на какие договоры приходится наибольшее количество активных проектов.

По умолчанию значения поля типа Форма — числовые ID задач. Чтобы вместо ID видеть название договора и ссылку на него, используем псевдоним #task, где Заголовок — наименование столбца в таблице, а ${Форма} — код поля типа Форма.

SELECT
  ${Форма} AS "#task('Заголовок')",
  COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE  is_closed = false
GROUP BY ${Форма}
ORDER BY COUNT(task_id) DESC
LIMIT 100;

Дата и Время

Формат Дата и Время используется для построения графика типа Временной ряд.

Дата и Время в Pyrus представлены в ClickHouse следующими типами: DateTime64(3, 'UTC'), Nullable(DateTime64(9, 'UTC')).

Пример: посмотрим, как распределены открытые и закрытые задачи для значений поля типа Дата и Время в заданном интервале времени.

В отчёте должны отображаться все задачи, в которых есть заполненное поле с кодом ${Дата} и указаны Дата начала и дата окончания события или процесса.

Пример:

SELECT
    DATE(${Дата}) AS "Пользовательская дата",
    COUNT(task_id) FILTER (WHERE is_closed = false) AS "Открытых задач",
    COUNT(task_id) FILTER (WHERE is_closed = true) AS "Закрытых задач"
FROM #tasks
WHERE ${Дата} >= @period_start AND ${Дата} <= @period_end
GROUP BY DATE(${Дата})
ORDER BY DATE(${Дата})
LIMIT 100;

Логический тип

Используется для формирования логических выборок по условиям запроса. Логический тип данных представлен в ClickHouse типами Bool и Nullable(Bool).

Поле типа Галочка

Пример: отсортируем задачи по выбранному значению в поле Галочка, выведем значение поля типа Галочка. ${Галочка} — код поля типа Галочка в форме Pyrus.

SELECT
  ${Галочка} AS "Значение галочки",
  COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE  is_closed = false
GROUP BY ${Галочка}
ORDER BY COUNT(task_id) DESC
LIMIT 100;

Текстовый тип

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

Текстовый тип данных представлен в ClickHouse типом Array(Nullable(String)).

Поле типа Текст

Пример: в форме Help Desk есть два текстовых поля: ${Тема} — для краткого описания сути обращения отправителем, и ${Сообщение} — для подробного изложения проблемы, которую необходимо решить службе поддержки. Мы хотим вывести список последних обращений с темами и текстами сообщений.

Поле типа Текст возвращает введённую строку.

SELECT
  task_id AS "#task('Задача')",
  ${Тема} AS "Тема",
${Сообщение} AS "Сообщение"
FROM #tasks
WHERE is_closed = false
AND ${Тема} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;

Поле типа Телефон

Пример: в форме Help Desk есть поле типа Телефон с кодом ${Телефон}, в котором указывается номер телефона отправителя для обратной связи. Мы хотим вывести список последних обращений с указанием номеров телефонов.

Поле типа Телефон возвращает введённый номер в текстовом виде.

SELECT
    task_id AS "#task('Задача')",
    ${Телефон} AS "Телефон"
FROM #tasks
WHERE is_closed = false
AND ${Телефон} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;

Поле типа Эл. почта

Пример: в форме Help Desk есть поле типа Эл. почта с кодом ${Эл. почта}, в котором указывается адрес отправителя. При необходимости в это поле можно ввести несколько адресов через запятую (например, если письмо было отправлено с нескольких адресов или переслано). Мы хотим выводить в отчёте как первый адрес из списка, так и полный перечень всех адресов.

Поле типа Эл. почта возвращает введённый адрес почты в текстовом виде.

SELECT
    task_id AS "#task('Задача')",
    ${Эл. почта} AS "Первый адрес",
    ${Эл. почта[*]} AS "Все адреса"
FROM #tasks
WHERE is_closed = false
AND ${Эл. почта} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;

Поле типа Справочник

Пример: рассчитаем число открытых HR-службой задач так, чтобы они группировались по категориям в зависимости от типа подписываемых документов (согласно значениям поля типа Справочник). Также мы хотим, чтобы в отчёте отображалось название документа — текстовое значение выбранного поля.

В отчёте по SQL-запросу поля типа Справочник возвращают текстовые значения выбранной строки справочника для каждой задачи. Извлечем значения из третьей колонки справочника с кодом ${Справочник}. В этой колонке перечислены типы кадровых документов согласно приказу Минтруда РФ № 578Н.

SELECT
  ${Справочник[3]} AS "Код документа по 578Н",
  COUNT(task_id) AS "Количество задач"
FROM #tasks
WHERE  is_closed = false
GROUP BY ${Справочник[3]}
ORDER BY COUNT(task_id) DESC
LIMIT 100;

Обзор типов визуализации

После того как вы убедились, что в SQL-запросе возвращены все необходимые данные в правильном формате и количестве, вы можете настроить их отображение, выбрав тип виджета.

Таблица (формат по умолчанию)

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

Требования к запрашиваемым данным

• Допустимы любые типы данных. Каждая именованная переменная (колонка) в вашем запросе (SELECT Col_A, Col_B) создаст отдельный столбец в отображаемой таблице.

  

Настройка отображения таблицы

• Переименование данных: используйте псевдонимы в SELECT AS.
• Группировка: позволяет имитировать иерархическое представление данных.

Круговая диаграмма

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

Требования к запрашиваемым данным

• Колонка значений: должна содержать данные с типом Число, при этом все значения должны быть больше нуля.

• Колонка названий: может быть представлена любым типом данных.

  

Настройка отображения

• Необходимо вручную указать, какая колонка из результатов запроса соответствует Значению (размер сектора) и какая — Названию.

• При наведении курсора отображается название сектора и его точное числовое значение.

  

Столбчатая диаграмма

Используется для прямого сравнения дискретных данных. Поддерживает отображение нескольких рядов данных.

Требования к запрашиваемым данным

• Колонка(и) значений (Ось Y): тип данных — Число. Доступно для выбора несколько наборов данных.

• Колонка названий (Ось X): может быть представлена любым типом данных.

  

Настройка отображения

• Необходимо вручную указать, какая колонка из результатов запроса соответствует Значению (высота столбца) и какая — Названию.

• При наведении курсора отображается название столбцы и выбранные значения.

  

Временной ряд

Временной ряд отображает последовательность значений, привязанную к Дате. Это ключевой инструмент для анализа трендов.

Требования к данным

• Колонка времени (Ось Х). Ожидаемый формат данных — Дата и время.

• Колонка значений (Ось Y). Ожидаемый формат данных — Число.

  

Настройка отображения

• Необходимо вручную указать, какая колонка из результатов запроса соответствует времени и какая — значению.

• При наведении курсора отображается выбранные значения по обеим осям.

  

Типы форматирования осей

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

Числовые типы

При отображении числовых типов данных доступны следующие варианты форматирования:

• по умолчанию: используются параметры, заданные в поле формы;
• проценты: для отображения долей;
• минуты / секунды: для удобного отображения временных интервалов;
• деньги: отображается с учетом того, какая валюта указана в поля Деньги в форме.

Дата и Время

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

• Дата и время: полная метка (DD.MM.YYYY HH:MM).
• Дата: только календарная дата (DD.MM.YYYY).
• Время: только время суток (HH:MM).

Форматирование иных типов данных не предусмотрено.