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

При написании 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;

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

Пример: рассчитаем число открытых задач, сгруппированных по категориям (значением поля типа Выбор), с отображением наименования выбранного значения поля типа Выбор.

По умолчанию все значения поля типа Выбор — числовые, поэтому для получения текстового значения из поля типа Выбор используем псевдоним #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)).

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

Пример: рассчитаем число открытых 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).

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