Функции для формул

В этой статье

Функции для формул

Для создания формул и выполнения расчетов в системе поддерживается два типа функций:

Стандартные функции ClickHouse, предоставляемые этой СУБД для вычислений и обработки данных
Собственные функции Proceset, разработанные для решения специфичных задач

Ключевые функции для формул и примеры их использования представлены ниже. С полным списком функций ClickHouse вы можете ознакомиться на официальном сайте.

Выбор функций для пользовательских показателей

Для создания пользовательских показателей доступны следующие функции:

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

Оператор distinct позволяет производить расчеты только по уникальным значениям в колонке таблицы:

count(distinct "case_table"."case_id")

Условное выражение If используется для построения сложных показателей с учетом условий: if(cond, then, else).

Аргументы выражения If:

cond – условие, которое может быть равно 0 или нет
then – возвращается результат выражения, если условие cond истинно
else – возвращается результат выражения, если условие cond ложно

Например:

if("activity"."element_ctrl" = 10, 'Кнопка', 'Текстовое поле')

Поддержка синтаксиса

Система поддерживает следующие возможности для написания формул:

Конкатенация строк — объединение нескольких строк в одну с помощью ||. Например: "activity"."program" || ' > ' || "activity"."file_path"
Тернарный оператор — оператор с тремя аргументами, который выполняет проверку условия и присваивает переменной одно из двух значений в зависимости от истинности условия: cond ? then : else. Первый аргумент — это условие. Если оно истинно (true), оператор возвращает второй аргумент, если ложно (false) — третий. Например: "activity"."element_ctrl" = 10 ? 'Кнопка' : 'Текстовое поле'

Заметка

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

Функции ClickHouse

Функции последовательности

Функция	Описание	Пример
`retention(cond1, cond2, ..., cond32)`	Показывает, насколько выдерживаются те или иные условия, например, удержание динамики/уровня посещаемости сайта. Функция принимает набор логических условий от 1 до 32 и применяет их к заданному набору данных	`retention("activity"."date" = '2024-05-02', "activity"."date" = '2024-05-03',"activity"."date" = '2024-05-04')`
`sequenceCount(pattern)(time, cond1, cond2, ...)`	Вычисляет количество цепочек событий, соответствующих шаблону. Функция обнаруживает только непересекающиеся цепочки событий. Она начинает искать следующую цепочку только после того, как полностью совпала текущая цепочка событий	`sequenceCount('(?1).*(?2)')("activity"."time","activity"."type" = 10, "activity"."type" = 14)`
`sequenceMatch(pattern)(timestamp, cond1, cond2, ...)`	Проверяет, содержит ли последовательность событий цепочку, которая соответствует указанному шаблону	`sequenceMatch('(?1).*(?2)')("activity"."time","activity"."type" = 10, "activity"."type" = 14)`

Функции для работы с датами и временем

Функция	Описание	Пример
`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'))`
`date_trunc(time_unit, x)`	Усекает отметку времени до нужной части	`date_trunc('hour', now())`
`formatDateTime(x, format)`	Выводит отметку времени в произвольном формате	`formatDateTime(toDate(2022-10-31), ' %D)`
`now()`	Возвращает текущую дату и время	`now ()`
`now(timezone)`	Возвращает текущую дату и время в указанном часовом поясе. `timezone` — часовой пояс для возвращаемого значения	`now ('Europe/Moscow')`
`parseDateTimeBestEffortOrZero`	Время в загруженных таблицах в произвольном формате	`parseDateTimeBestEffortOrZero('2022-10-31 18:00:00-5:00')`
`parseDateTime64BestEffortOrZero`	Время в загруженных таблицах в произвольном формате	`parseDateTime64BestEffortOrZero('2022-10-31 18:00:00-5:00')`
`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())`
`toInterval(Year — Quarter — Month — Week — Day — Hour — Minute — Second)`	Создает временной интервал указанного типа (годы, кварталы, месяцы, недели, дни, часы, минуты или секунды) для операций со временем	`toDate('2022-10-31')+toIntervalWeek(1)`
`toMonday(x)`	Округляет дату или дату-с-временем вниз до ближайшего понедельника. Возвращается дата	`toMonday(toDate('2022-10-30 17:10:00'))`
`toMonth(x)`	Переводит дату или дату-с-временем в число типа UInt8, содержащее номер месяца (1-12)	`toMonth(toDate('2022-10-31 17:10:00'))`
`toStartOfDay(x)`	Округляет дату-с-временем вниз до начала дня. Возвращается дата-с-временем	`toStartOfDay(toDate('2022-10-30 17:10:00'))`
`toUnixTimestamp` `toUnixTimestamp64Milli`	Преобразует в секунды и миллисекунды	`toUnixTimestamp('2022-10-31 08:07:47')`
`toYear(x)`	Переводит дату или дату-с-временем в число типа UInt16, содержащее номер года (AD)	`toYear(toDate('2022-10-31 17:10:00'))`

Агрегатные функции

Функция	Описание	Пример
`any(x)`	Выбирает первое попавшееся значение `x`	`any("case_table"."duration")`
`argMax(arg, val)`	Вычисляет значение `arg` при максимальном значении `val`	`argMax("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"='Отсутствие услуги телефонии')`
`argMin(arg, val)`	Вычисляет значение `arg` при минимальном значении `val`	`argMin("case_table"."duration", "case_table"."duration_type")`
`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"='ТОМСКИЙ ФИЛИАЛ')`
`median(x)`	Вычисляет медиану	`median("case_table"."duration")`
`medianIf(x, condition)`	Вычисляет медиану с условием `-if`	`medianIf("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"='ТОМСКИЙ ФИЛИАЛ')`
`quantile(level)(x)`	Вычисляет квантиль уровня `level` — значение, которое заданная случайная величина не превышает с фиксированной вероятностью. Для этого все переданные значения складываются в массив, который затем частично сортируется	`quantile(0.5)("case_table"."duration")`
`quantileIf(level)(x, condition)`	Вычисляет квантиль уровня `level` точно с условием `-if`	`quantileIf(0.5)("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ')`
`stddevPop (x)`	Рассчитывает стандартное отклонение	`stddevPop("case_table"."duration")`
`sum(x)`	Вычисляет сумму. Работает только для чисел	`sum(100)`
`sumIf(x, condition)`	Вычисляет сумму с условием `-if`. Работает только для чисел	`sumIf("case_table"."duration", "case_table"."duration_type"='1 - 5 дней')`
`topK(N)(column)`	Возвращает массив наиболее часто встречающихся значений в указанном столбце. `N` - Кол-во значений в массиве	`topK(3)("case_table"."case_crm_type_lvl3")`
`uniq(x)`	Возвращает количество уникальных записей	`uniq("case_table"."case_crm_type_lvl3")`

Математические функции

Функция	Описание	Пример
`abs(x)`	Находит модуль числа `x`	`abs (325 / 76)`
`ceil(x [, n])`	Округляет число `x` вверх до ближайшего целого или до `n` знаков после запятой	`ceil(1278.544, 1)`
`floor(x [, n])`	Округляет число `x` вниз до ближайшего целого или до `n` знаков после запятой	`floor(1278.446, 1)`
`intDiv(x, y)`	Делит `x` на `y` и возвращает только целую часть результата	`IntDiv(34320, 156)`
`modulo(x, y)`	Возвращает остаток от деления `x` на `y`	`modulo(1789, 2)`
`pow(x, y)`	Возводит `x` в степень `y`	`pow(14, 7)`
`round(x[, n])`	Округляет число `x` до ближайшего целого или до `n` знаков после запятой	`round(1278.546, 1)`
`sqrt(x)`	Находит квадратный корень из `x`	`sqrt(1089)`

Строковые функции

Функция	Описание	Пример
`arrayStringConcat(arr, [separator])`	Соединяет элементы массива в строку	—
`concat(s1,s2..)`	Склеивает строки, перечисленные в аргументах, без разделителей	`concat("case_table"."mrf_name", "case_table"."rf_name")`
`domainWithoutWWW(url)`	Извлекает домен из URL	domainWithoutWWW(```'https://infomaximum.com/business-intelligence/'```)
`extract(s, pattern)`	Извлекает паттерн из строки	`extract("case_table"."mrf_name", '\\+')`
`extractAll(s, pattern)`	Извлекает все паттерны из строки. Возвращается массив строк, состоящий из всех соответствий регулярному выражению	`extractAll("case_table"."mrf_name", '\+')`
`ilike(s, pattern)`	Ищет шаблона без учета регистра	`ilike("case_table"."rf_name", '\\ФИЛИАЛ')`
`leftPad(s, length, pad_s)`	Дополняет текущую строку слева пробелами или указанной строкой (несколько раз, если необходимо), пока результирующая строка не достигнет заданной длины	`leftPad("case_table"."rf_name", 7, ’*’)`
`like(haystack, pattern)`	Проверяет строку на соответствие простому регулярному выражению. Регулярное выражение может содержать метасимволы «%» и «_»	`ilike("case_table"."mrf_name", '\\_')`
`lowerUTF8(s)`	Переводит строку в нижний регистр	`lowerUTF8("case_table"."dir_services_def")`
`match(s, pattern)`	Ищет паттерн в строке	`match("case_table"."rf_name", '\\+')`
`rightPad(s, length, pad_s)`	Дополняет текущую строку справа пробелами или указанной строкой (несколько раз, если необходимо), пока результирующая строка не достигнет заданной длины	`rightPad("case_table"."rf_name", 7, ’*’)`
`replaceRegexpOne(s, pattern, replacement)`	Заменяет первое вхождение паттерна в строке `s` на `replacement`	`replaceRegexpOne("case_table"."start", '\\.', '\\-')`
`replaceRegexpAll(s, pattern, replacement)`	Заменяет все вхождения паттерна в строке `s` на `replacement`	`replaceRegexpAll("case_table"."start", '\\.', '\\-')`
`substringUTF8(s, offset, length)`	Извлекает часть строки	`substringUTF8("case_table"."mrf_name", 1, 100000)`
`trim(s)`	Очищает строку от лишних пробелов	`trim("case_table"."rf_name")`
`upperUTF8(s)`	Переводит строку в верхний регистр	`upperUTF8("case_table"."dir_services_def")`

Функции для работы с массивами

Функция	Описание	Пример
`arrayAvg(arr)`	Возвращает среднее значение элементов в исходном массиве	`arrayAvg("case_table"."str_arr")`
`arrayDistinct(arr)`	Возвращает массив, содержащий уникальные элементы	`arrayDistinct("case_table"."str_arr")`
`arrayEnumerate(arr)`	Возвращает массив `[1, 2, 3, ..., length(arr)]`	`arrayEnumerate("case_table"."str_arr")`
`arrayEnumerateUniq(arr)`	Возвращает массив, такого же размера, как исходный, где для каждого элемента указано, какой он по счету среди элементов с таким же значением	`arrayEnumerateUniq("case_table"."str_arr")`
`arrayFill(func, arr)`	Перебирает `arr` от первого элемента к последнему и заменяет `arr1[i]` на `arr1[i - 1]`, если `func` возвращает 0. Первый элемент `arr` остается неизменным	`arrayFill(x -> not isNull(x), "case_table"."str_arr")`
`arrayFilter(func, arr)`	Возвращает массив, содержащий только те элементы массива `arr`, для которых функция `func` возвращает не 0	`arrayFilter(x -> x >=3 , "case_table"."num_arr")`
`arrayFlatten(arr)`	Преобразует массив массивов в плоский массив	`arrayFlatten("case_table"."flt_arr")`
`arrayMap(func, arr)`	Возвращает массив, полученный на основе результатов применения функции `func` к каждому элементу массива `arr`	`arrayMap(x -> x + 1, "case_table"."num_arr")`
`arrayMax(arr)`	Возвращает значение максимального элемента в исходном массиве	`arrayMax("case_table"."str_arr")`
`arrayMin(arr)`	Возвращает значение минимального элемента в исходном массиве	`arrayMin("case_table"."str_arr")`
`arrayPopBack(arr)`	Удаляет последний элемент массива	`arrayPopBack("case_table"."str_arr")`
`arrayPopFront(arr)`	Удаляет первый элемент массива	`arrayPopFront("case_table"."str_arr")`
`arrayPushBack(arr, x)`	Добавляет один элемент в конец массива	`arrayPushBack("case_table"."str_arr", 'x')`
`arrayPushFront(arr, x)`	Добавляет один элемент в начало массива	`arrayPushFront("case_table"."str_arr", 'x')`
`arrayReverse(arr)`	Возвращает массив того же размера, что и исходный массив, содержащий элементы в обратном порядке	`arrayReverse("case_table"."str_arr")`
`arrayReverseSort(arr)`	Возвращает массив `arr`, отсортированный в нисходящем порядке	`arrayReverseSort("case_table"."str_arr")`
`arrayReduce(agg_func, arr)`	Применяет агрегатную функцию к элементам массива и возвращает ее результат	`arrayReduce('sum', "case_table"."num_arr")`
`arraySlice(arr, offset, length)`	Возвращает срез массива. `offset` — отступ от края массива. Положительное значение — отступ слева, отрицательное значение — отступ справа. Отсчет элементов массива начинается с 1	`arraySlice("case_table"."str_arr", 2, 2)`
`arraySort(arr)`	Возвращает массив `arr`, отсортированный в восходящем порядке	`arraySort("case_table"."str_arr")`
`arraySum(arr)`	Возвращает сумму элементов в исходном массиве	`arraySum("case_table"."str_arr")`
`arrayUniq(arr)`	Cчитает количество уникальных элементов в массиве	`arrayUniq("case_table"."str_arr")`
`groupArray(x)`	Составляет массив из значений аргумента	`groupArray("activity"."type")`
`groupUniqArray(x)`	Составляет массив из уникальных значений аргумента	`groupUniqArray("activity"."type")`
`has(arr,x)`	Проверяет наличие элемента `x` в массиве arr. Возвращает 0, если элемента в массиве нет, или 1, если есть	`has("case_table"."str_arr",'a')`
`hasAll(arr, sub_arr)`	Проверяет, является ли один массив подмножеством другого	`hasAll("case_table"."str_arr",['a','b'])`
`indexOf(arr, x)`	Возвращает индекс первого элемента `x` (начиная с 1), если он есть в массиве, или 0, если его нет	`indexOf("case_table"."str_arr", 'a')`

Прочие функции

Функция	Описание	Пример
`cityHash64(x)`	Генерирует 64-х битное значение CityHash	`cityHash64("case_table"."num_arr")`
`coalesce(x, ...)`	Поиск первого не `NULL`	`coalesce("case_table"."mrf_name", "case_table"."rf_name")`
`CRC32(x)`	Используется для обезличивания данных вместе с `hex`	`CRC32("case_table"."mrf_name")`
`empty(x)`	Проверяет, является ли входная строка пустой. Возвращает 1 для пустой строки и 0 — для непустой строки. Работает и для массивов	`empty("case_table"."num_arr")`
`hex(x)`	Используется для обезличивания данных вместе с `CRC32`	`hex("case_table"."mrf_name")`
`if(cond, then, else)`	`cond` должно иметь тип UInt8, а `then` и `else` должны иметь тип, для которого есть наименьший общий тип	—
`isNan(x)`	Принимает типы данных Float32 или Float64 и возвращает UInt8, равный 1, если аргумент является NaN, в остальных случаях — 0	`isNaN("ext_activity"."balance")`
`isNull(x)`	Проверяет на `NULL`	`isNull("case_table"."rf_name")`
`JSONExtract(s)`	Анализирует JSON и извлекает значение указанного типа данных ClickHouse	`JSONExtract("monitoring_activity_json"."location_name", 'Array(String)')`
`toJSONString(value)`	Сериализует значение в представлении JSON	`toJSONString(['abc'])`
`length(x)`	Возвращает длину строки в байтах. Тип результата — `UInt64`. Функция также работает для массивов	`length("case_table"."mrf_name")`
`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())`

Собственные функции 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")`

Агрегатные функции

Функция	Описание	Пример
`process(aggr_func, column)`	Вычисляет агрегирующее выражение по колонке разреза	`process(sum("case_table"."duration"), "case_table"."duration_type")`

Подробное описание функции process, ее параметры и примеры использования представлены в разделе Функция process.

Была ли статья полезна?

Да

Нет

Редактор формул

Функция process

.css-wsrjzs{display:inline;margin-right:12px;}@media screen and (max-width: 691px){.css-wsrjzs{display:inline-block;margin-right:0;}}Функции для формул

Выбор функций для пользовательских показателей

Поддержка синтаксиса

Функции ClickHouse

Функции последовательности

Функции для работы с датами и временем

Агрегатные функции

Математические функции

Строковые функции

Функции для работы с массивами

Прочие функции

Собственные функции Proceset

Функции последовательности

Агрегатные функции

Функции для формул