Функции для формул
В системе поддерживаются разные виды функций. Для создания пользовательского показателя:
- в мерах, показателях событий, показателях переходов, показателях процесса используются функции последовательности, функции для работы с датой и временем, агрегатные, математические, строковые и прочие функции.
- в разрезах используются функции для работы с датой и временем, математические, строковые и прочие функции.
If – условное выражение.
- Аргументы:
- cond – условие, которое может быть равно 0 или нет;
- then – возвращается результат выражения, если условие cond истинно;
- else – возвращается результат выражения, если условие cond ложно.
distinct – если указано DISTINCT, то из всех множеств полностью совпадающих строк результата будет оставляться только одна строка.
Список функций ClickHouse
Агрегатные функции
| Функция | Описание | Пример |
|---|---|---|
| any(x) | Выбирает первое попавшееся значение x | any("case_table"."duration") |
| argMax(arg, val) | Вычисляет значение arg при максимальном значении val | argMax("case_table"."duration", "case_table"."duration_type") |
| argMin(arg, val) | Вычисляет значение arg при минимальном значении val | argMin("case_table"."duration", "case_table"."duration_type") |
| argMaxIf(arg, val, condition) | Вычисляет значение arg при максимальном значении val с условием -if | argMaxIf("case_table"."duration", "case_table"."duration_type", "case_table"."case_crm_type_lvl2"='Отсутствие услуги телефонии') |
| argMinIf(arg, val, condition) | Вычисляет значение arg при минимальном значении val с условием -if | argMinIf("case_table"."duration", "case_table"."duration_type", "case_table"."case_crm_type_lvl2"='Отсутствие услуги телефонии') |
| avg(x) | Вычисляет среднее значение. Работает только для чисел. Результат всегда Float64 | avg("case_table"."duration") |
| avgIf(x, condition) | Вычисляет среднее значение с условием -if. Работает только для чисел. Результат всегда Float64 | avgIf("case_table"."duration", "case_table"."case_crm_type_lvl2"='Отсутствие соединения ШПД') |
| count(x) | Возвращает количество записей | count("case_table"."case_crm_type_lvl3") |
| countIf(x, condition) | Возвращает количество записей с учетом выполнения условия | countIf("case_table"."dir_tech_def", "case_table"."dir_tech_def"='PON') |
| max(x) | Вычисляет максимум | max("case_table"."duration") |
| maxIf(x, condition) | Вычисляет максимум с условием -if | maxIf("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ') |
| min(x) | Вычисляет минимум | min("case_table"."duration") |
| minIf(x, condition) | Вычисляет минимум с условием -if | minIf("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ') |
| median(x) | Вычисляет медиану | median("case_table"."duration") |
| medianIf(x, condition) | Вычисляет медиану с условием -if | medianIf("case_table"."duration", "case_table"."rf_name"='ИРКУТСКИЙ ФИЛИАЛ') |
| quantile(level)(x) | Вычисляет квантиль уровня level – значение, которое заданная случайная величина не превышает с фиксированной вероятностью. Для этого все переданные значения складываются в массив, который затем частично сортируется | quantile(0.5)("case_table"."duration") |
| sum(x) | Вычисляет сумму. Работает только для чисел | sum(100) |
| sumIf(x, condition) | Вычисляет сумму с условием -if. Работает только для чисел | sumIf("case_table"."duration", "case_table"."duration_type"='1 - 5 дней') |
| quantileIf(level)(x, condition) | Вычисляет квантиль уровня level точно с условием -if | quantileIf(0.5)("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ') |
| stddevPop (x) | Рассчитывает стандартное отклонение | stddevPop("case_table"."duration") |
Последовательности
| Функция | Описание |
|---|---|
| sequenceCount(pattern)(time, cond1, cond2, …) | Вычисляет количество цепочек событий, соответствующих шаблону. Функция обнаруживает только непересекающиеся цепочки событий. Она начинает искать следующую цепочку только после того, как полностью совпала текущая цепочка событий |
| sequenceMatch(pattern)(timestamp, cond1, cond2, …) | Проверяет, содержит ли последовательность событий цепочку, которая соответствует указанному шаблону |
| retention(cond1, cond2, …, cond32) | Показывает, насколько выдерживаются те или иные условия. Например, удержание динамики/уровня посещаемости сайта. Функция принимает набор логических условий от 1 до 32 и применяет их к заданному набору данных |
Функции для работы с датами и временем
| Функция | Описание | Пример |
|---|---|---|
| today() | Принимает ноль аргументов и возвращает текущую дату на один из моментов выполнения запроса | today() |
| toDate(х) | Преобразует дату-с-временем в дату | toDate('2022-10-31 17:00:00') |
| toDayOfMonth(x) | Переводит дату или дату-с-временем в число типа UInt8, содержащее номер дня в месяце (1-31) | toDayOfMonth(toDate('2022-10-31')) |
| toDayOfWeek(x) | Переводит дату или дату-с-временем в число типа UInt8, содержащее номер дня в неделе (понедельник — 1, воскресенье — 7) | toDayOfWeek(toDate('2022-10-31 17:00:00')) |
| toHour(x) | Переводит дату-с-временем в число типа UInt8, содержащее номер часа в сутках (0-23). Округляет дату-с-временем вниз до начала минуты | toHour(now()) |
| toMonth(x) | Переводит дату или дату-с-временем в число типа UInt8, содержащее номер месяца (1-12) | toMonth(toDate('2022-10-31 17:10:00')) |
| toYear(x) | Переводит дату или дату-с-временем в число типа UInt16, содержащее номер года (AD) | toYear(toDate('2022-10-31 17:10:00')) |
| toMonday(x) | Округляет дату или дату-с-временем вниз до ближайшего понедельника. Возвращается дата | toMonday(toDate('2022-10-30 17:10:00')) |
| toStartOfDay(x) | Округляет дату-с-временем вниз до начала дня. Возвращается дата-с-временем | toStartOfDay(toDate('2022-10-30 17:10:00')) |
| now(timezone) | timezone — часовой пояс для возвращаемого значения | now ('Europe/Moscow') |
| now() | Возвращает текущую дату и время | now () |
| parseDateTimeBestEffortOrZero | Время в загруженных таблицах в произвольном формате | parseDateTimeBestEffortOrZero('2022-10-31 18:00:00-5:00') |
| parseDateTime64BestEffortOrZero | Время в загруженных таблицах в произвольном формате | parseDateTime64BestEffortOrZero('2022-10-31 18:00:00-5:00') |
| toUnixTimestamp, toUnixTimestamp64Milli | Для преобразования в секунды и миллисекунды | Пример: toUnixTimestamp('2022-10-31 08:07:47') |
| date_trunc(time_unit, x) | Для усечения отметки времени до нужной части | date_trunc('hour', now()) |
| date_add(time_unit, value, x) | Для прибавления произвольной части к отметке времени | date_add(day, 10, toDate('2022-10-31')) |
| dateDiff(time_unit, date1, date2) | Для вычитания одной отметки времени из другой | dateDiff('day', toDateTime('2022-10-21 01:00:00'), toDateTime('2022-10-31 19:00:00')) |
| date_sub(time_unit, value, x) | Для вычитания произвольной части из отметки времени | date_sub(day, 10, toDate('2022-10-31')) |
| formatDateTime(x, format) | Для вывода отметки времени в произвольном формате | formatDateTime(toDate(2022-10-31), ' %D) |
| toInterval(Year — Quarter — Month — Week — Day — Hour — Minute — Second) | Для операций со временем | toDate('2022-10-31')+toIntervalWeek(1) |
Математические функции
| Функция | Описание | Пример |
|---|---|---|
| intDiv(x, y) | Целочисленное деление | IntDiv(34320, 156) |
| modulo(x, y) | Остаток от деления | modulo(1789, 2) |
| sqrt(x) | Извлечение квадратного корня | sqrt(1089) |
| pow(x, y) | Возведение в степень | pow(14, 7) |
| floor(x [, n) | Округление вниз | floor(1278.446, 1) |
| ceil(x [, n]) | Округление вверх | ceil(1278.544, 1) |
| abs(x) | Модуль числа | abs (325 / 76) |
| round(x[, N]) | Округляет значение | round(1278.546, 1) |
Строковые функции
| Функция | Описание | Пример |
|---|---|---|
| lowerUTF8(s) | Перевод строки в нижний регистр | lowerUTF8("case_table"."dir_services_def") |
| upperUTF8(s) | Перевод строки в верхний регистр | upperUTF8("case_table"."dir_services_def") |
| substringUTF8(s, offset, length) | Извлечение части строки | substringUTF8("case_table"."mrf_name", 1, 100000) |
| trim(s) | Очистка строки от лишних пробелов | trim("case_table"."rf_name") |
| match(s, pattern) | Поиск паттерна в строке | match("case_table"."rf_name", '\+') |
| extract(s, pattern) | Извлечение паттерна из строки | extract("case_table"."mrf_name", '\+') |
| replaceRegexpOne(s, pattern, replacement) или replaceRegexpAll(s, pattern, replacement) | Замена паттерна в строке | replaceRegexpOne("case_table"."start", '\.', '\-') |
| arrayStringConcat(arr, [separator]) | Соединение элементов массива в строку | - |
| domainWithoutWWW(url) | Извлечение домена из URL | domainWithoutWWW('https://infomaximum.com/business-intelligence/') |
| ilike(s, pattern) | Поиск шаблона без учёта регистра | ilike("case_table"."rf_name", '\ФИЛИАЛ') |
| concat(s1,s2..) | Склеивает строки, перечисленные в аргументах, без разделителей | concat("case_table"."mrf_name", "case_table"."rf_name") |
| like(haystack, pattern) | Проверка строки на соответствие простому регулярному выражению. Регулярное выражение может содержать метасимволы «%» и «_» | ilike("case_table"."mrf_name", '\_') |
Прочие
| Функция | Описание | Пример |
|---|---|---|
| if(cond, then, else) | cond должно иметь тип UInt8, а then и else должны иметь тип, для которого есть наименьший общий тип | - |
| multiIf(cond_1, then_1, cond_2, then_2…else) | Позволяет более компактно записать оператор CASE в запросе | - |
| toInt32(expr) | Преобразует входное значение к типу Int. Возвращает значение типа Int32 | toInt32("activity"."name") |
| toInt64(expr) | Преобразует входное значение к типу Int. Возвращает значение типа Int64 | toInt64("activity"."name") |
| toString(x) | Функции преобразования между числами, строками (но не фиксированными строками), датами и датами-с-временем. Все функции принимают один аргумент | toString(now()) |
| length(x) | Возвращает длину строки в байтах. Тип результата — UInt64. Функция также работает для массивов | length("case_table"."mrf_name") |
| isNan(x) | Принимает Float32 или Float64 и возвращает UInt8, равный 1, если аргумент является NaN, иначе 0 | isNaN("ext_activity"."balance") |
| CRC32(x) | Используется для обезличивания данных вместе с hex | CRC32("case_table"."mrf_name") |
| hex(x) | Используется для обезличивания данных вместе с CRC32 | hex("case_table"."mrf_name") |
| isNull(x) | Проверка на NULL | isNull("case_table"."rf_name") |
| coalesce(x, …) | Поиск первого не NULL | coalesce("case_table"."mrf_name", "case_table"."rf_name") |
Собственные функции Proceset
Последовательности
| Функция | Описание | Пример |
|---|---|---|
| begin(column/variable) | Начало перехода, на вход принимает название колонки (используются только для показателя перехода) | avg(end("case_table"."finish")-begin("case_table"."start")) |
| end(column/variable) | Конец перехода (используются только для показателя перехода) | avg(end("case_table"."finish")-begin("case_table"."start")) |
| variant(event_name, event_time) | Возвращает сценарий выполнения процесса как строку | variant("case_table"."case_id", "case_table"."duration") |
Поддержка синтаксиса
- Поддержка конкатенации (объединение нескольких строк в одну) через «||»;
- Поддержка тернарного оператора (оператор с тремя аргументами. Первый аргумент — это условие. Если оно истинно (true), оператор вернёт второй аргумент. В ином случае он вернёт третий аргумент. Оператор проверяет условие, а затем присваивает переменной первое или второе выражение в зависимости от истинности этого условия).
Поддержка не отображается, но осуществляется.
Предыдущая
Следующая