Функции для формул
8 800 555-89-02
Войти
Документация
CTRL+K
Standalone2410
SaaS

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

В этой статье

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

Ключевые функции для формул и примеры их использования представлены ниже. С полным списком функций 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)Выбирает первое попавшееся значение xany("case_table"."duration")
argMax(arg, val)Вычисляет значение arg при максимальном значении valargMax("case_table"."duration", "case_table"."duration_type")
argMaxIf(arg, val, condition)Вычисляет значение arg при максимальном значении val с условием -ifargMaxIf("case_table"."duration", "case_table"."duration_type", "case_table"."case_crm_type_lvl2"='Отсутствие услуги телефонии')
argMin(arg, val)Вычисляет значение arg при минимальном значении valargMin("case_table"."duration", "case_table"."duration_type")
argMinIf(arg, val, condition)Вычисляет значение arg при минимальном значении val с условием -ifargMinIf("case_table"."duration", "case_table"."duration_type", "case_table"."case_crm_type_lvl2"='Отсутствие услуги телефонии')
avg(x)Вычисляет среднее значение. Работает только для чисел. Результат всегда с типом Float64avg("case_table"."duration")
avgIf(x, condition)Вычисляет среднее значение с условием -if. Работает только для чисел. Результат всегда с типом Float64avgIf("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)Вычисляет максимум с условием -ifmaxIf("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ')
median(x)Вычисляет медиануmedian("case_table"."duration")
medianIf(x, condition)Вычисляет медиану с условием -ifmedianIf("case_table"."duration", "case_table"."rf_name"='ИРКУТСКИЙ ФИЛИАЛ')
min(x)Вычисляет минимумmin("case_table"."duration")
minIf(x, condition)Вычисляет минимум с условием -ifminIf("case_table"."duration", "case_table"."rf_name"='ТОМСКИЙ ФИЛИАЛ')
quantile(level)(x)Вычисляет квантиль уровня level — значение, которое заданная случайная величина не превышает с фиксированной вероятностью. Для этого все переданные значения складываются в массив, который затем частично сортируетсяquantile(0.5)("case_table"."duration")
quantileIf(level)(x, condition)Вычисляет квантиль уровня level точно с условием -ifquantileIf(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)Находит модуль числа xabs (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 на ymodulo(1789, 2)
pow(x, y)Возводит x в степень ypow(14, 7)
round(x[, n])Округляет число x до ближайшего целого или до n знаков после запятойround(1278.546, 1)
sqrt(x)Находит квадратный корень из xsqrt(1089)

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

ФункцияОписаниеПример
arrayStringConcat(arr, [separator])Соединяет элементы массива в строку
concat(s1,s2..)Склеивает строки, перечисленные в аргументах, без разделителейconcat("case_table"."mrf_name", "case_table"."rf_name")
domainWithoutWWW(url)Извлекает домен из URLdomainWithoutWWW(```'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 на replacementreplaceRegexpOne("case_table"."start", '\\.', '\\-')
replaceRegexpAll(s, pattern, replacement)Заменяет все вхождения паттерна в строке s на replacementreplaceRegexpAll("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 возвращает не 0arrayFilter(x -> x >=3 , "case_table"."num_arr")
arrayFlatten(arr)Преобразует массив массивов в плоский массивarrayFlatten("case_table"."flt_arr")
arrayMap(func, arr)Возвращает массив, полученный на основе результатов применения функции func к каждому элементу массива arrarrayMap(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 — отступ от края массива. Положительное значение — отступ слева, отрицательное значение — отступ справа. Отсчет элементов массива начинается с 1arraySlice("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-х битное значение CityHashcityHash64("case_table"."num_arr")
coalesce(x, ...)Поиск первого не NULLcoalesce("case_table"."mrf_name", "case_table"."rf_name")
CRC32(x)Используется для обезличивания данных вместе с hexCRC32("case_table"."mrf_name")
empty(x)Проверяет, является ли входная строка пустой. Возвращает 1 для пустой строки и 0 — для непустой строки. Работает и для массивовempty("case_table"."num_arr")
hex(x)Используется для обезличивания данных вместе с CRC32hex("case_table"."mrf_name")
if(cond, then, else)cond должно иметь тип UInt8, а then и else должны иметь тип, для которого есть наименьший общий тип
isNan(x)Принимает типы данных Float32 или Float64 и возвращает UInt8, равный 1, если аргумент является NaN, в остальных случаях — 0isNaN("ext_activity"."balance")
isNull(x)Проверяет на NULLisNull("case_table"."rf_name")
JSONExtract(s)Анализирует JSON и извлекает значение указанного типа данных ClickHouseJSONExtract("monitoring_activity_json"."location_name", 'Array(String)')
toJSONString(value)Сериализует значение в представлении JSONtoJSONString(['abc'])
length(x)Возвращает длину строки в байтах. Тип результата — UInt64. Функция также работает для массивовlength("case_table"."mrf_name")
multiIf(cond_1, then_1, cond_2, then_2...else)Позволяет более компактно записать оператор CASE в запросе
toInt32(expr)Преобразует входное значение к типу Int. Возвращает значение типа Int32toInt32("activity"."name")
toInt64(expr)Преобразует входное значение к типу Int. Возвращает значение типа Int64toInt64("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.

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

Да
Нет
Предыдущая
Редактор формул
8 (800) 555-89-028 (495) 150-31-45team@infomaximum.com
Для бизнеса
© 20102024. ООО «Инфомаксимум»
Мы используем файлы cookies, чтобы сайт был лучше для вас.