Оформление
Вычисляемые поля
Вычисляемое поле — новая колонка, значение которой считается выражением на основе других полей: например, сумма заказа с НДС или доля от общего. Такое поле создаётся либо в датасете и доступно всем виджетам на нём, либо в одном виджете.
Вычисляемое поле датасета
- В окне Настройка полей нажмите Вычисляемое поле.
- Заполните:
- Идентификатор — латиницей, без пробелов; под этим именем поле хранится в датасете;
- Псевдоним — как поле будет называться в списке;
- Тип — как трактовать результат: число, дробное, строка, дата или логический;
- SQL-выражение — сама формула.
- Нажмите Создать, затем Применить и сохраните датасет.
Как писать выражение
Выражение пишется на языке формул платформы: набор функций одинаков для всех источников, а в SQL нужной базы платформа переводит его сама. Ссылка на поле берётся в квадратные скобки:
sql
ROUND([amount] * 1.2, 2)Слева в окне есть список всех доступных полей с поиском — по нему удобно сверять имена.
По какому имени ссылаться. Работают обе формы: [orders.amount] с именем таблицы и [amount] без него. Первая надёжнее — она не сломается, если в другой таблице датасета появится поле с тем же именем. Псевдоним поля в формуле не работает: ссылаться нужно на имя поля, а не на то, как оно подписано в списке: SUM([Сумма]) отвечает «field [Сумма] not found» и перечисляет доступные имена.
Агрегат внутри агрегата допускается: SUM(SUM([orders.amount])) считается и даёт общий итог. Это же выражение с модификатором области окна — SUM(SUM([orders.amount]) TOTAL) — даёт тот же результат, но читается однозначнее.
Тип поля должен подходить выражению
Если объявленный тип не сходится с тем, что возвращает формула, отказ приходит уже при построении виджета и выглядит как «Внутренняя ошибка сервера» — без объяснения. Например, INT([products.category]) от текстового поля падает именно так. Проверяйте тип до сохранения: сообщение не подскажет, в чём дело.
Родные функции источника в формуле не работают
В выражении доступны только функции из Справочника. Имя функции самой СУБД — lowerUTF8, toStartOfQuarter и подобные — виджет не примет и назовёт причину: «Функция 'LOWERUTF8' пока не поддержана».
Обратное тоже верно: одна и та же формула на разных источниках может вести себя по-разному, потому что переводится в разный SQL.
Справочник функций
Кнопка Справочник открывает список функций по категориям. Выберите функцию — справа появятся её синтаксис и описание. Например, для ROUND показано ROUND( number [, precision ] ) и пояснение «Округление до заданной точности».
Часть функций справочника источник не исполняет
У некоторых функций под названием стоит пометка: «Источник данных пока не исполняет эту функцию — выборка с ней ответит ошибкой». В интерфейсе так помечены пятнадцать функций.
На самом деле не исполняются десять: MEDIAN, QUANTILE, QUANTILE_APPROX, ALL_CONCAT, TOP_CONCAT, UTF8, BOOL, DATE_PARSE, DATETIME_PARSE и DB_CAST. Формула с ними сохранится без ошибок, а сбой появится при построении виджета: там, где SUM([orders.amount]) вернёт сумму, MEDIAN([orders.amount]) ответит отказом «Функция 'MEDIAN' пока не поддержана».
Остальные пять — STDEV, STDEVP, VAR, VARP и COUNTD_APPROX — помечены в интерфейсе, но считаются и возвращают значение; автодополнение их при этом не предлагает.
Что мешает каждой функции, указано в справочнике функций.
Полный список всех функций с синтаксисом, описанием и особенностями — справочник функций: там же видно, какие из них не исполняются и какие нельзя агрегировать.
Категории справочника:
| Категория | Для чего |
|---|---|
| Агрегатные функции | Суммы, средние, количества |
| Уровень детализации (LOD) | Расчёты с гранулярностью, отличной от виджета |
| Оконные функции | Расчёты по окну строк — нарастающие итоги, ранги |
| Область окна | Управление границами окна |
| Функции времени | Работа с интервалами |
| Логические функции | Условия и ветвления |
| Математические функции | Округление, степени, модуль |
| Строковые функции | Обрезка, склейка, поиск подстроки |
| Функции даты и времени | Извлечение частей даты, сдвиги |
| Преобразование типов | Приведение значения к другому типу |
LOWER, UPPER, ICONTAINS, ISTARTSWITH и IENDSWITH работают и с русскими буквами: LOWER([products.category]) от «Компьютеры» даёт «компьютеры» — в том числе на ClickHouse, где раньше кириллица оставалась без изменений.
Уровень детализации (LOD)
Три слова, без которых дальше не разобраться. Мера — числовое поле, которое агрегируется: сумма, среднее, количество. Измерение — поле, по которому данные режутся на группы: категория, город, месяц. Разрез — набор измерений, которые сейчас лежат на полках виджета; именно в нём считается мера. Подробнее — полки конструктора.
Обычная мера считается в разрезе тех измерений, что лежат на полках виджета. LOD меняет этот разрез, не трогая сам виджет:
| Функция | Синтаксис | Что делает |
|---|---|---|
| FIXED | SUM([value] FIXED [dim], …) | Считает со строго заданной группировкой, независимо от измерений виджета |
| INCLUDE | SUM([value] INCLUDE [dim], …) | Добавляет измерения к разрезу виджета — считает детальнее |
| EXCLUDE | SUM([value] EXCLUDE [dim], …) | Убирает измерения из разреза — так считают долю от целого |
| BEFORE FILTER BY | SUM([value] BEFORE FILTER BY [dim], …) | Считает до применения фильтров по перечисленным полям |
Последняя пригодится, когда нужно показать долю выбранного среди всех: фильтр обрежет строки, но знаменатель посчитается по полному набору.
Оконные функции
Считают не по строке, а по окну соседних строк: нарастающие итоги, ранги, сравнение с предыдущим значением.
| Группа | Функции |
|---|---|
| Ранги | RANK, RANK_DENSE, RANK_UNIQUE, RANK_PERCENTILE, ROW_NUMBER |
| Соседние значения | FIRST, LAST, LAG, LEAD |
| Нарастающие итоги | RSUM, RCOUNT, RAVG, RMIN, RMAX |
| Скользящие по окну строк | MSUM, MAVG, MCOUNT, MMIN, MMAX |
Например, RSUM( number ) — нарастающая сумма, MAVG( number, rows ) — скользящее среднее по заданному числу строк, LAG( value [, offset ] ) — значение со смещением назад.
У RANK одинаковые значения получают общий ранг, а следующий идёт с пропуском; RANK_DENSE пропусков не делает, RANK_UNIQUE всегда даёт разные ранги.
Область окна
Задаёт, что считать окном для оконной функции:
| Функция | Синтаксис | Окно |
|---|---|---|
| TOTAL | SUM( SUM([value]) TOTAL ) | Вся выборка целиком — так считают общий итог для доли |
| WITHIN | SUM( SUM([value]) WITHIN [dim], … ) | Группы по перечисленным измерениям |
| AMONG | SUM( SUM([value]) AMONG [dim], … ) | Измерения виджета, кроме перечисленных |
Доля от общего пишется так — сумма долей по всем категориям даёт 100 %:
sql
100.0 * SUM([orders.amount]) / SUM(SUM([orders.amount]) TOTAL)Вычисляемые поля виджета
Если поле нужно только в одной визуализации, создайте его прямо в конструкторе виджета — блок Вычисляемые поля виджета внизу левой панели. Такое поле не попадает в датасет и не влияет на другие виджеты.
Платформа предлагает два способа: SQL-выражение — написать формулу с нуля, или клонировать поле — взять за основу существующее и изменить его.
В окне слева перечислены поля по таблицам, а под редактором — шаблоны, вставляющие готовый фрагмент: SUM(x), COUNTD(x), SUM_IF(x, cond), IF … THEN … ELSE … END, IFNULL(a, b), ROUND(x, 2), YEAR(x), CONCAT(a, b), а также более сложные заготовки — SUM(x FIXED [dim]), SUM(x EXCLUDE [dim]), SUM(SUM(x) TOTAL), RSUM(x), RANK(x).
Сохраните виджет, иначе поле не посчитается
Положив только что созданное поле на полку, вы увидите «Не удалось загрузить данные виджета — Определение вычисляемого поля не найдено». Это не ошибка формулы: определение ещё не сохранено на сервере. Нажмите Сохранить — виджет построится.
Что дальше
- Справочник функций — все 133 функции по категориям, с примерами.
- Настройка полей — остальные настройки полей датасета.
- Поля, фильтры и сортировка — как поле попадает на полки виджета.