Важно: Данный раздел актуален для Платформы данных в Публичном облаке и On-Premise.
Функции бывают как минимум* двух видов - обычные функции (называются просто, функциями) и агрегатные функции. Это совершенно разные вещи. Обычные функции работают так, как будто применяются к каждой строке по отдельности (для каждой строки, результат вычисления функции не зависит от других строк). Агрегатные функции аккумулируют множество значений из разных строк (то есть, зависят от целого множества строк).
В этом разделе речь пойдёт об обычных функциях. Для агрегатных функций, смотрите раздел «Агрегатные функции».
* - есть ещё третий вид функций, к которым относится функция arrayJoin; также можно отдельно иметь ввиду табличные функции.
В RT.WideStore, в отличие от стандартного SQL, типизация является строгой. То есть, не производится неявных преобразований между типами. Все функции работают для определённого набора типов. Это значит, что иногда вам придётся использовать функции преобразования типов.
Все выражения в запросе, имеющие одинаковые AST (одинаковую запись или одинаковый результат синтаксического разбора), считаются имеющими одинаковые значения. Такие выражения склеиваются и исполняются один раз. Одинаковые подзапросы тоже склеиваются.
Все функции возвращают одно (не несколько, не ноль) значение в качестве результата. Тип результата обычно определяется только типами аргументов, но не значениями аргументов. Исключение - функция tupleElement (оператор a.N), а также функция toFixedString.
Для простоты, некоторые функции могут работать только с константами в качестве некоторых аргументов. Например, правый аргумент оператора LIKE должен быть константой. Почти все функции возвращают константу для константных аргументов. Исключение - функции генерации случайных чисел. Функция now возвращает разные значения для запросов, выполненных в разное время, но результат считается константой, так как константность важна лишь в пределах одного запроса. Константное выражение также считается константой (например, правую часть оператора LIKE можно сконструировать из нескольких констант).
Функции могут быть по-разному реализованы для константных и не константных аргументов (выполняется разный код). Но результат работы для константы и полноценного столбца, содержащего только одно такое же значение, должен совпадать.
Функции имеют следующие виды поведения:
Функции не могут поменять значения своих аргументов - любые изменения возвращаются в качестве результата. Соответственно, от порядка записи функций в запросе, результат вычислений отдельных функций не зависит.
Функции высшего порядка, в качестве своего функционального аргумента могут принимать только лямбда-функции. Чтобы передать лямбда-функцию в функцию высшего порядка, используйте оператор ->. Слева от стрелочки стоит формальный параметр — произвольный идентификатор, или несколько формальных параметров — произвольные идентификаторы в кортеже. Справа от стрелочки стоит выражение, в котором могут использоваться эти формальные параметры, а также любые столбцы таблицы.
Примеры:
x -> 2 * x
str -> str != RefererВ функции высшего порядка может быть передана лямбда-функция, принимающая несколько аргументов. В этом случае в функцию высшего порядка передаётся несколько массивов одинаковой длины, которым эти аргументы будут соответствовать.
Для некоторых функций первый аргумент (лямбда-функция) может отсутствовать. В этом случае подразумевается тождественное отображение.
Функции можно создавать из лямбда выражений с помощью CREATE FUNCTION. Для удаления таких функций используется выражение DROP FUNCTION.
RT.WideStore может вызывать любую внешнюю исполняемую программу или скрипт для обработки данных.
Конфигурация исполняемых пользовательских функций может находиться в одном или нескольких xml-файлах. Путь к конфигурации указывается в параметре user_defined_executable_functions_config.
Конфигурация функции содержит следующие настройки:
Команда должна читать аргументы из STDIN и выводить результат в STDOUT. Обработка должна выполняться в цикле. То есть после обработки группы аргументов команда должна ожидать следующую группу.
Пример:
Создание test_function с использованием конфигурации XML. Файл test_function.xml.
<functions>
<function>
<type>executable</type>
<name>test_function_python</name>
<return_type>String</return_type>
<argument>
<type>UInt64</type>
<name>value</name>
</argument>
<format>TabSeparated</format>
<command>test_function.py</command>
</function>
</functions>Файл скрипта внутри папки user_scripts test_function.py.
#!/usr/bin/python3
import sys
if __name__ == '__main__':
for line in sys.stdin:
print("Value " + line, end='')
sys.stdout.flush()Запрос:
SELECT test_function_python(toUInt64(2));Результат:
┌─test_function_python(2)─┐
│ Value 2 │
└───────────────────────────┘Создание test_function_sum, указав для execute_direct значение 0, используя конфигурацию XML. File test_function.xml.
<functions>
<function>
<type>executable</type>
<name>test_function_sum</name>
<return_type>UInt64</return_type>
<argument>
<type>UInt64</type>
<name>lhs</name>
</argument>
<argument>
<type>UInt64</type>
<name>rhs</name>
</argument>
<format>TabSeparated</format>
<command>cd /; widestore-local --input-format TabSeparated --output-format TabSeparated --structure 'x UInt64, y UInt64' --query "SELECT x + y FROM table"</command>
<execute_direct>0</execute_direct>
</function>
</functions>Запрос:
SELECT test_function_sum(2, 2);Результат:
┌─test_function_sum(2, 2)─┐
│ 4 │
└───────────────────────────┘Создание test_function_sum_json с именноваными аргументами и форматом JSONEachRow с использованием конфигурации XML. Файл test_function.xml.
<functions>
<function>
<type>executable</type>
<name>test_function_sum_json</name>
<return_type>UInt64</return_type>
<return_name>result_name</return_name>
<argument>
<type>UInt64</type>
<name>argument_1</name>
</argument>
<argument>
<type>UInt64</type>
<name>argument_2</name>
</argument>
<format>JSONEachRow</format>
<command>test_function_sum_json.py</command>
</function>
</functions>Файл скрипта внутри папки user_scripts test_function_sum_json.py.
#!/usr/bin/python3
import sys
import json
if __name__ == '__main__':
for line in sys.stdin:
value = json.loads(line)
first_arg = int(value['argument_1'])
second_arg = int(value['argument_2'])
result = {'result_name': first_arg + second_arg}
print(json.dumps(result), end='\n')
sys.stdout.flush()Запрос:
SELECT test_function_sum_json(2, 2);Результат:
┌─test_function_sum_json(2, 2)─┐
│ 4 │
└─────────────────────────────────┘Исполняемые пользовательские функции могут принимать константные параметры, их конфигурация является частью настройки command (работает только для пользовательских функций с типом executable). Файл test_function_parameter_python.xml.
<functions>
<function>
<type>executable</type>
<name>test_function_parameter_python</name>
<return_type>String</return_type>
<argument>
<type>UInt64</type>
</argument>
<format>TabSeparated</format>
<command>test_function_parameter_python.py {test_parameter:UInt64}</command>
</function>
</functions>Файл скрипта внутри папки user_scripts test_function_parameter_python.py.
#!/usr/bin/python3
import sys
if __name__ == "__main__":
for line in sys.stdin:
print("Parameter " + str(sys.argv[1]) + " value " + str(line), end="")
sys.stdout.flush()Запрос:
SELECT test_function_parameter_python(1)(2);Результат:
┌─test_function_parameter_python(1)(2)─┐
│ Parameter 1 value 2 │
└─────────────────────────────────────────┘Некоторые функции могут кидать исключения в случае ошибочных данных. В этом случае, выполнение запроса прерывается, и текст ошибки выводится клиенту. При распределённой обработке запроса, при возникновении исключения на одном из серверов, на другие серверы пытается отправиться просьба тоже прервать выполнение запроса.
В почти всех языках программирования, для некоторых операторов может не вычисляться один из аргументов. Обычно - для операторов &&, ||, ?:. Но в RT.WideStore, аргументы функций (операторов) вычисляются всегда. Это связано с тем, что вычисления производятся не по отдельности для каждой строки, а сразу для целых кусочков столбцов.
При распределённой обработке запроса, как можно большая часть стадий выполнения запроса производится на удалённых серверах, а оставшиеся стадии (слияние промежуточных результатов и всё, что дальше) - на сервере-инициаторе запроса.
Это значит, что выполнение функций может производиться на разных серверах. Например, в запросе SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),
distributed_table имеет хотя бы два шарда, то функции g и h выполняются на удалённых серверах, а функция f - на сервере-инициаторе запроса;distributed_table имеет только один шард, то все функции f, g, h выполняются на сервере этого шарда.Обычно результат выполнения функции не зависит от того, на каком сервере её выполнить. Но иногда это довольно важно. Например, функции, работающие со словарями, будут использовать словарь, присутствующий на том сервере, на котором они выполняются. Другой пример - функция hostName вернёт имя сервера, на котором она выполняется, и это можно использовать для служебных целей - чтобы в запросе SELECT сделать GROUP BY по серверам.
Если функция в запросе выполняется на сервере-инициаторе запроса, а вам нужно, чтобы она выполнялась на удалённых серверах, вы можете обернуть её в агрегатную функцию any или добавить в ключ в GROUP BY.
Для всех арифметических функций, тип результата вычисляется, как минимальный числовой тип, который может вместить результат, если такой тип есть. Минимум берётся одновременно по числу бит, знаковости и «плавучести». Если бит не хватает, то берётся тип максимальной битности.
Пример:
SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)
┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8 │ UInt16 │ UInt32 │ UInt64 │
└────────────────┴──────────────────────────┴────────────────────────────────────┴──────────────────────────────────────────────┘Арифметические функции работают для любой пары типов из UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64.
Переполнение производится также, как в C++.
Вычисляет сумму чисел.
Также можно складывать целые числа с датой и датой-с-временем. В случае даты, прибавление целого числа означает прибавление соответствующего количества дней. В случае даты-с-временем - прибавление соответствующего количества секунд.
Вычисляет разность чисел. Результат всегда имеет знаковый тип.
Также можно вычитать целые числа из даты и даты-с-временем. Смысл аналогичен - смотрите выше для plus.
Вычисляет произведение чисел.
Вычисляет частное чисел. Тип результата всегда является типом с плавающей запятой.
То есть, деление не целочисленное. Для целочисленного деления, используйте функцию intDiv.
При делении на ноль получится inf, -inf или nan.
Вычисляет частное чисел. Деление целочисленное, с округлением вниз (по абсолютному значению).
При делении на ноль или при делении минимального отрицательного числа на минус единицу, кидается исключение.
Отличается от intDiv тем, что при делении на ноль или при делении минимального отрицательного числа на минус единицу, возвращается ноль.
Вычисляет остаток от деления.
Если аргументы – числа с плавающей запятой, то они предварительно преобразуются в целые числа, путём отбрасывания дробной части.
Берётся остаток в том же смысле, как это делается в C++. По факту, для отрицательных чисел, используется truncated division.
При делении на ноль или при делении минимального отрицательного числа на минус единицу, кидается исключение.
В отличие от modulo, возвращает ноль при делении на ноль.
Вычисляет число, обратное по знаку. Результат всегда имеет знаковый тип.
Вычисляет абсолютное значение для числа a. То есть, если a \< 0, то возвращает -a.
Для беззнаковых типов ничего не делает. Для чисел типа целых со знаком, возвращает число беззнакового типа.
Вычисляет наибольший общий делитель чисел.
При делении на ноль или при делении минимального отрицательного числа на минус единицу, кидается исключение.
Вычисляет наименьшее общее кратное чисел.
При делении на ноль или при делении минимального отрицательного числа на минус единицу, кидается исключение.
Возвращает 1 для пустого массива, и 0 для непустого массива.
Тип результата – UInt8.
Функция также работает для строк.
Возвращает 0 для пустого массива, и 1 для непустого массива.
Тип результата – UInt8.
Функция также работает для строк.
Возвращает количество элементов в массиве.
Тип результата – UInt64.
Функция также работает для строк.
Принимает ноль аргументов и возвращает пустой массив соответствующего типа.
Принимает пустой массив и возвращает массив из одного элемента, равного значению по умолчанию.
Возвращает массив чисел от 0 до N-1.
На всякий случай, если на блок данных, создаются массивы суммарной длины больше 100 000 000 элементов, то кидается исключение.
Создаёт массив из аргументов функции.
Аргументы должны быть константами и иметь типы, для которых есть наименьший общий тип. Должен быть передан хотя бы один аргумент, так как иначе непонятно, какого типа создавать массив. То есть, с помощью этой функции невозможно создать пустой массив (для этого используйте функции emptyArray*, описанные выше).
Возвращает результат типа Array(T), где T – наименьший общий тип от переданных аргументов.
Объединяет массивы, переданные в качестве аргументов.
arrayConcat(arrays)Параметры:
Пример:
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res
┌─res────────────┐
│ [1,2,3,4,5,6] │
└────────────────┘Достаёт элемент с индексом n из массива arr. n должен быть любым целочисленным типом.
Индексы в массиве начинаются с единицы.
Поддерживаются отрицательные индексы. В этом случае, будет выбран соответствующий по номеру элемент с конца. Например, arr[-1] - последний элемент массива.
Если индекс выходит за границы массива, то возвращается некоторое значение по умолчанию (0 для чисел, пустая строка для строк и т. п.), кроме случая с неконстантным массивом и константным индексом 0 (в этом случае будет ошибка Array indices are 1-based).
Проверяет наличие элемента elem в массиве arr.
Возвращает 0, если элемента в массиве нет, или 1, если есть.
NULL обрабатывается как значение.
SELECT has([1, 2, NULL], NULL)
┌─has([1, 2, NULL], NULL)─┐
│ 1 │
└───────────────────────────┘Проверяет, является ли один массив подмножеством другого.
Синтаксис:
hasAll(set, subset)Параметры:
Возвращаемые значения:
Особенности:
Примеры:
SELECT hasAll([], []) возвращает 1.
SELECT hasAll([1, Null], [Null]) возвращает 1.
SELECT hasAll([1.0, 2, 3, 4], [1, 3]) возвращает 1.
SELECT hasAll(['a', 'b'], ['a']) возвращает 1.
SELECT hasAll([1], ['a']) возвращает 0.
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]) возвращает 0.
Проверяет, имеют ли два массива хотя бы один общий элемент.
Синтаксис:
hasAny(array1, array2)Параметры:
array1 – массив любого типа с набором элементов.
array2 – массив любого типа с набором элементов.
Возвращаемые значения:
Особенности:
Примеры:
SELECT hasAny([1], []) возвращает 0.
SELECT hasAny([Null], [Null, 1]) возвращает 1.
SELECT hasAny([-128, 1., 512], [1]) возвращает 1.
SELECT hasAny([[1, 2], [3, 4]], ['a', 'c']) возвращает 0.
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [1, 2]]) возвращает 1.
Возвращает индекс первого элемента x (начиная с 1), если он есть в массиве, или 0, если его нет.
Пример:
SELECT indexOf([1, 3, NULL, NULL], NULL)
┌─indexOf([1, 3, NULL, NULL], NULL)─┐
│ 3 │
└──────────────────────────────────────┘Элементы, равные NULL, обрабатываются как обычные значения.
Возвращает количество элементов массива arr, для которых функция func возвращает не 0. Если func не указана - возвращает количество ненулевых элементов массива.
Функция arrayCount является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Возвращает количество элементов массива, равных x. Эквивалентно arrayCount(elem -> elem = x, arr).
NULL обрабатывается как значение.
Пример:
SELECT countEqual([1, 2, NULL, NULL], NULL)
┌─countEqual([1, 2, NULL, NULL], NULL)─┐
│ 2 │
└─────────────────────────────────────────┘Возвращает массив [1, 2, 3, …, length(arr)].
Эта функция обычно используется совместно с ARRAY JOIN. Она позволяет, после применения ARRAY JOIN, посчитать что-либо только один раз для каждого массива. Пример:
SELECT
count() AS Reaches,
countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
GoalsReached,
arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└──────────┴───────┘В этом примере, Reaches – число достижений целей (строк, получившихся после применения ARRAY JOIN), а Hits – число хитов (строк, которые были до ARRAY JOIN). В данном случае, тот же результат можно получить проще:
SELECT
sum(length(GoalsReached)) AS Reaches,
count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└──────────┴───────┘Также эта функция может быть использована в функциях высшего порядка. Например, с её помощью можно достать индексы массива для элементов, удовлетворяющих некоторому условию.
Возвращает массив, такого же размера, как исходный, где для каждого элемента указано, какой он по счету среди элементов с таким же значением.
Например: arrayEnumerateUniq([10, 20, 10, 30]) = [1, 1, 2, 1].
Эта функция полезна при использовании ARRAY JOIN и агрегации по элементам массива.
Пример:
SELECT
Goals.ID AS GoalID,
sum(Sign) AS Reaches,
sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
Goals,
arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10
┌──GoalID─┬─Reaches─┬─Visits─┐
│ 53225 │ 3214 │ 1097 │
│ 2825062 │ 3188 │ 1097 │
│ 56600 │ 2803 │ 488 │
│ 1989037 │ 2401 │ 365 │
│ 2830064 │ 2396 │ 910 │
│ 1113562 │ 2372 │ 373 │
│ 3270895 │ 2262 │ 812 │
│ 1084657 │ 2262 │ 345 │
│ 56599 │ 2260 │ 799 │
│ 3271094 │ 2256 │ 812 │
└──────────┴──────────┴────────┘В этом примере, для каждого идентификатора цели, посчитано количество достижений целей (каждый элемент вложенной структуры данных Goals является достижением целей) и количество визитов. Если бы не было ARRAY JOIN, мы бы считали количество визитов как sum(Sign). Но в данном случае, строчки были размножены по вложенной структуре Goals, и чтобы после этого учесть каждый визит один раз, мы поставили условие на значение функции arrayEnumerateUniq(Goals.ID).
Функция arrayEnumerateUniq может принимать несколько аргументов - массивов одинаковых размеров. В этом случае, уникальность считается для кортежей элементов на одинаковых позициях всех массивов:
SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]) AS res
┌─res────────────┐
│ [1,2,1,1,2,1] │
└────────────────┘Это нужно при использовании ARRAY JOIN с вложенной структурой данных и затем агрегации по нескольким элементам этой структуры.
Удаляет последний элемент из массива.
Синтаксис:
arrayPopBack(array)Параметры:
Пример:
SELECT arrayPopBack([1, 2, 3]) AS res
┌─res────┐
│ [1,2] │
└────────┘Удаляет первый элемент из массива:
arrayPopFront(array)Параметры:
Пример:
SELECT arrayPopFront([1, 2, 3]) AS res
┌─res────┐
│ [2,3] │
└────────┘Добавляет один элемент в конец массива.
Синтаксис:
arrayPushBack(array, single_value)Параметры:
Пример:
SELECT arrayPushBack(['a'], 'b') AS res
┌─res────────┐
│ ['a','b'] │
└────────────┘Добавляет один элемент в начало массива.
Синтаксис:
arrayPushFront(array, single_value)Параметры:
Пример:
SELECT arrayPushFront(['b'], 'a') AS res
┌─res────────┐
│ ['a','b'] │
└────────────┘Изменяет длину массива.
Синтаксис:
arrayResize(array, size[, extender])Параметры:
Возвращаемое значение:
Массив длины size.
Примеры вызовов:
SELECT arrayResize([1], 3)
┌─arrayResize([1], 3)─┐
│ [1,0,0] │
└───────────────────────┘
SELECT arrayResize([1], 3, NULL)
┌─arrayResize([1], 3, NULL)─┐
│ [1,NULL,NULL] │
└─────────────────────────────┘Возвращает срез массива.
Синтаксис:
arraySlice(array, offset[, length])Параметры:
Пример:
SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res
┌─res─────────┐
│ [2,NULL,4] │
└─────────────┘Элементы массива равные NULL обрабатываются как обычные значения.
Возвращает массив arr, отсортированный в восходящем порядке. Если задана функция func, то порядок сортировки определяется результатом применения этой функции на элементы массива arr. Если func принимает несколько аргументов, то в функцию arraySort нужно передавать несколько массивов, которые будут соответствовать аргументам функции func. Подробные примеры рассмотрены в конце описания arraySort.
Пример сортировки целочисленных значений:
SELECT arraySort([1, 3, 3, 0])
┌─arraySort([1, 3, 3, 0])─┐
│ [0,1,3,3] │
└───────────────────────────┘Пример сортировки строковых значений:
SELECT arraySort(['hello', 'world', '!'])
┌─arraySort(['hello', 'world', '!'])─┐
│ ['!','hello','world'] │
└───────────────────────────────────────┘Значения NULL, NaN и Inf сортируются по следующему принципу:
SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
┌─arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf])─┐
│ [-inf,-4,1,2,3,inf,nan,nan,NULL,NULL] │
└────────────────────────────────────────────────────────────────┘
Функция arraySort является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию. В этом случае порядок сортировки определяется результатом применения лямбда-функции на элементы массива.
Рассмотрим пример:
SELECT arraySort((x) -> -x, [1, 2, 3]) as res;
┌─res──────┐
│ [3,2,1] │
└──────────┘Для каждого элемента исходного массива лямбда-функция возвращает ключ сортировки, то есть [1 –> -1, 2 –> -2, 3 –> -3]. Так как arraySort сортирует элементы в порядке возрастания ключей, результат будет [3, 2, 1]. Как можно заметить, функция x –> -x устанавливает обратный порядок сортировки.
Лямбда-функция может принимать несколько аргументов. В этом случае, в функцию arraySort нужно передавать несколько массивов, которые будут соответствовать аргументам лямбда-функции (массивы должны быть одинаковой длины). Следует иметь в виду, что результат будет содержать элементы только из первого массива; элементы из всех последующих массивов будут задавать ключи сортировки. Например:
SELECT arraySort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;
┌─res──────────────────┐
│ ['world', 'hello'] │
└──────────────────────┘Элементы, указанные во втором массиве ([2,1]), определяют ключ сортировки для элементов из исходного массива ([‘hello’, ‘world’]), то есть [‘hello’ –> 2, ‘world’ –> 1]. Так как лямбда-функция не использует x, элементы исходного массива не влияют на порядок сортировки. Таким образом, ‘hello’ будет вторым элементом в отсортированном массиве, а ‘world’ – первым.
Ниже приведены другие примеры:
SELECT arraySort((x, y) -> y, [0, 1, 2], ['c', 'b', 'a']) as res;
┌─res──────┐
│ [2,1,0] │
└──────────┘
SELECT arraySort((x, y) -> -y, [0, 1, 2], [1, 2, 3]) as res;
┌─res──────┐
│ [2,1,0] │
└──────────┘Примечание: Для улучшения эффективности сортировки применяется преобразование Шварца.
Возвращает массив arr, отсортированный в нисходящем порядке. Если указана функция func, то массив arr сначала сортируется в порядке, который определяется функцией func, а затем отсортированный массив переворачивается. Если функция func принимает несколько аргументов, то в функцию arrayReverseSort необходимо передавать несколько массивов, которые будут соответствовать аргументам функции func. Подробные примеры рассмотрены в конце описания функции arrayReverseSort.
Пример сортировки целочисленных значений:
SELECT arrayReverseSort([1, 3, 3, 0]);
┌─arrayReverseSort([1, 3, 3, 0])─┐
│ [3,3,1,0] │
└───────────────────────────────────┘Пример сортировки строковых значений:
SELECT arrayReverseSort(['hello', 'world', '!']);
┌─arrayReverseSort(['hello', 'world', '!'])─┐
│ ['world','hello','!'] │
└───────────────────────────────────────────────┘Значения NULL, NaN и Inf сортируются в следующем порядке:
SELECT arrayReverseSort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]) as res;
┌─res──────────────────────────────────────┐
│ [inf,3,2,1,-4,-inf,nan,nan,NULL,NULL] │
└──────────────────────────────────────────┘Значения Inf идут в начале массива.
Функция arrayReverseSort является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию. Например:
SELECT arrayReverseSort((x) -> -x, [1, 2, 3]) as res;
┌─res──────┐
│ [1,2,3] │
└──────────┘В этом примере, порядок сортировки устанавливается следующим образом:
Лямбда-функция может принимать на вход несколько аргументов. В этом случае, в функцию arrayReverseSort нужно передавать несколько массивов, которые будут соответствовать аргументам лямбда-функции (массивы должны быть одинаковой длины). Следует иметь в виду, что результат будет содержать элементы только из первого массива; элементы из всех последующих массивов будут определять ключи сортировки. Например:
SELECT arrayReverseSort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;
┌─res─────────────────┐
│ ['hello','world'] │
└─────────────────────┘В этом примере, массив сортируется следующим образом:
Ниже приведены ещё примеры:
SELECT arrayReverseSort((x, y) -> y, [0, 1, 2], ['c', 'b', 'a']) as res;
┌─res─────┐
│ [0,1,2] │
└──────────┘
SELECT arrayReverseSort((x, y) -> -y, [4, 3, 5], [1, 2, 3]) AS res;
┌─res─────┐
│ [4,3,5] │
└──────────┘Если передан один аргумент, считает количество разных элементов в массиве.
Если передано несколько аргументов, считает количество разных кортежей из элементов на соответствующих позициях в нескольких массивах.
Если необходимо получить список уникальных элементов массива, можно воспользоваться arrayReduce(‘groupUniqArray’, arr).
Особенная функция. Смотрите раздел «Функция arrayJoin».
Это совсем необычная функция.
Обычные функции не изменяют множество строк, а лишь изменяют значения в каждой строке (map). Агрегатные функции выполняют свёртку множества строк (fold, reduce). Функция arrayJoin выполняет размножение каждой строки в множество строк (unfold).
Функция принимает в качестве аргумента массив, и размножает исходную строку в несколько строк - по числу элементов массива. Все значения в столбцах просто копируются, кроме значения в столбце с применением этой функции - он заменяется на соответствующее значение массива.
Пример:
SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
┌─dst─┬─\'Hello\'─┬─src─────┐
│ 1 │ Hello │ [1,2,3] │
│ 2 │ Hello │ [1,2,3] │
│ 3 │ Hello │ [1,2,3] │
└─────┴────────────┴──────────┘Функция arrayJoin влияет на все секции запроса, включая секцию WHERE. Обратите внимание на результат 2, хотя подзапрос вернул 1 строку.
Пример:
SELECT sum(1) AS impressions
FROM
(
SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities
)
WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin'];
┌─impressions─┐
│ 2 │
└──────────────┘В запросе может быть использовано несколько функций arrayJoin. В этом случае, соответствующее преобразование делается несколько раз и строки перемножаются.
Пример:
SELECT
sum(1) AS impressions,
arrayJoin(cities) AS city,
arrayJoin(browsers) AS browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
2,
3
┌─impressions─┬─city─────┬─browser─┐
│ 2 │ Istanbul │ Chrome │
│ 1 │ Istanbul │ Firefox │
│ 2 │ Berlin │ Chrome │
│ 1 │ Berlin │ Firefox │
│ 2 │ Bobruisk │ Chrome │
│ 1 │ Bobruisk │ Firefox │
└──────────────┴───────────┴──────────┘Обратите внимание на синтаксис ARRAY JOIN в запросе SELECT, который предоставляет более широкие возможности. ARRAY JOIN позволяет преобразовать несколько массивов с одинаковым количеством элементов за раз.
Пример:
SELECT
sum(1) AS impressions,
city,
browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
ARRAY JOIN
cities AS city,
browsers AS browser
GROUP BY
2,
3
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Istanbul │ Firefox │
│ 1 │ Berlin │ Chrome │
│ 1 │ Bobruisk │ Chrome │
└──────────────┴───────────┴──────────┘Или можно использовать Tuple
Пример:
SELECT
sum(1) AS impressions,
(arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city,
t.2 AS browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
2,
3
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Istanbul │ Firefox │
│ 1 │ Berlin │ Chrome │
│ 1 │ Bobruisk │ Chrome │
└──────────────┴───────────┴──────────┘Вычисляет разность между соседними элементами массива. Возвращает массив, где первым элементом будет 0, вторым – разность a[1] - a[0] и т. д. Тип элементов результирующего массива определяется правилами вывода типов при вычитании (напр. UInt8 - UInt8 = Int16).
Синтаксис:
arrayDifference(array)Параметры:
Возвращаемое значение
Возвращает массив разностей между соседними элементами.
Пример:
Запрос:
SELECT arrayDifference([1, 2, 3, 4])Ответ:
┌─arrayDifference([1, 2, 3, 4])─┐
│ [0,1,1,1] │
└──────────────────────────────────┘Пример переполнения из-за результирующего типа Int64:
Запрос:
SELECT arrayDifference([0, 10000000000000000000])Ответ:
┌─arrayDifference([0, 10000000000000000000])─┐
│ [0,-8446744073709551616] │
└────────────────────────────────────────────────┘Принимает массив, возвращает массив, содержащий уникальные элементы.
Синтаксис:
arrayDistinct(array)Параметры:
Возвращаемое значение:
Возвращает массив, содержащий только уникальные элементы исходного массива.
Пример:
Запрос:
SELECT arrayDistinct([1, 2, 2, 3, 1])Ответ:
┌─arrayDistinct([1, 2, 2, 3, 1])─┐
│ [1,2,3] │
└───────────────────────────────────┘Возвращает массив того же размера, что и исходный массив, с индексами исходного массива, указывающими, где каждый элемент впервые появляется в исходном массиве.
Пример:
SELECT arrayEnumerateDense([10, 20, 10, 30])
┌─arrayEnumerateDense([10, 20, 10, 30])─┐
│ [1,2,1,3] │
└──────────────────────────────────────────┘Принимает несколько массивов, возвращает массив с элементами, присутствующими во всех исходных массивах. Элементы на выходе следуют в порядке следования в первом массиве.
Пример:
SELECT
arrayIntersect([1, 2], [1, 3], [2, 3]) AS no_intersect,
arrayIntersect([1, 2], [1, 3], [1, 4]) AS intersect
┌─no_intersect─┬─intersect─┐
│ [] │ [1] │
└───────────────┴────────────┘Применяет агрегатную функцию к элементам массива и возвращает ее результат. Имя агрегирующей функции передается как строка в одинарных кавычках 'max', 'sum'. При использовании параметрических агрегатных функций, параметр указывается после имени функции в круглых скобках 'uniqUpTo(6)'.
Синтаксис:
arrayReduce(agg_func, arr1, arr2, ..., arrN)Параметры:
Возвращаемое значение:
Пример:
Запрос:
SELECT arrayReduce('max', [1, 2, 3])Ответ:
┌─arrayReduce('max', [1, 2, 3])─┐
│ 3 │
└──────────────────────────────────┘Если агрегатная функция имеет несколько аргументов, то эту функцию можно применять к нескольким массивам одинакового размера.
Пример:
Запрос:
SELECT arrayReduce('maxIf', [3, 5], [1, 0])Ответ:
┌─arrayReduce('maxIf', [3, 5], [1, 0])─┐
│ 3 │
└─────────────────────────────────────────┘Пример с параметрической агрегатной функцией:
Запрос:
SELECT arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10])Ответ:
┌───arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10])─┐
│ 4 │
└─────────────────────────────────────────────────────────────────────┘Применяет агрегатную функцию к элементам массива в заданных диапазонах и возвращает массив, содержащий результат, соответствующий каждому диапазону. Функция вернет тот же результат, что и несколько arrayReduce(agg_func, arraySlice(arr1, index, length), ...).
Синтаксис:
arrayReduceInRanges(agg_func, ranges, arr1, arr2, ..., arrN)Параметры:
Возвращаемое значение:
Тип: Array.
Пример:
Запрос:
SELECT arrayReduceInRanges(
'sum',
[(1, 5), (2, 3), (3, 4), (4, 4)],
[1000000, 200000, 30000, 4000, 500, 60, 7]
) AS resОтвет:
┌─res───────────────────────────┐
│ [1234500,234000,34560,4567] │
└───────────────────────────────┘Возвращает массив того же размера, что и исходный массив, содержащий элементы в обратном порядке.
Пример:
SELECT arrayReverse([1, 2, 3])
┌─arrayReverse([1, 2, 3])─┐
│ [3,2,1] │
└───────────────────────────┘Синоним для «arrayReverse».
Преобразует массив массивов в плоский массив.
Функция:
Результирующий массив содержит все элементы исходных массивов.
Синтаксис:
flatten(array_of_arrays)Синоним: flatten.
Параметры:
Примеры:
SELECT flatten([[[1]], [[2], [3]]])
┌─flatten(array(array([1]), array([2], [3])))─┐
│ [1,2,3] │
└─────────────────────────────────────────────────┘Удаляет последовательно повторяющиеся элементы из массива. Порядок результирующих значений определяется порядком в исходном массиве.
Синтаксис:
arrayCompact(arr)Параметры:
Возвращаемое значение:
Массив без последовательных дубликатов.
Тип: Array.
Пример:
Запрос:
SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3])Ответ:
┌─arrayCompact([1, 1, nan, nan, 2, 3, 3, 3])─┐
│ [1,nan,nan,2,3] │
└────────────────────────────────────────────────┘Объединяет несколько массивов в один. Результирующий массив содержит соответственные элементы исходных массивов, сгруппированные в кортежи в указанном порядке аргументов.
Синтаксис:
arrayZip(arr1, arr2, ..., arrN)Параметры:
Функция принимает любое количество массивов, которые могут быть различных типов. Все массивы должны иметь одинаковую длину.
Возвращаемое значение:
Тип: Массив.
Пример:
Запрос:
SELECT arrayZip(['a', 'b', 'c'], [5, 2, 1])Ответ:
┌─arrayZip(['a', 'b', 'c'], [5, 2, 1])─┐
│ [('a',5),('b',2),('c',1)] │
└─────────────────────────────────────────┘Возвращает массив, полученный на основе результатов применения функции func к каждому элементу массива arr.
Примеры:
SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;
┌─res──────┐
│ [3,4,5] │
└──────────┘Следующий пример показывает, как создать кортежи из элементов разных массивов:
SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res
┌─res───────────────────┐
│ [(1,4),(2,5),(3,6)] │
└───────────────────────┘Функция arrayMap является функцией высшего порядка – в качестве первого аргумента ей нужно передать лямбда-функцию, и этот аргумент не может быть опущен.
Возвращает массив, содержащий только те элементы массива arr1, для которых функция func возвращает не 0.
Примеры:
SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res
┌─res────────────┐
│ ['abc World'] │
└────────────────┘
SELECT
arrayFilter(
(i, x) -> x LIKE '%World%',
arrayEnumerate(arr),
['Hello', 'abc World'] AS arr)
AS res
┌─res─┐
│ [2] │
└─────┘Функция arrayFilter является функцией высшего порядка – в качестве первого аргумента ей нужно передать лямбда-функцию, и этот аргумент не может быть опущен.
Возвращает 1, если существует хотя бы один элемент массива arr, для которого функция func возвращает не 0. Иначе возвращает 0.
Функция arrayExists является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Возвращает 1, если для всех элементов массива arr, функция func возвращает не 0. Иначе возвращает 0.
Функция arrayAll является функцией высшего порядка - в качестве первого аргумента ей можно передать лямбда-функцию.
Возвращает первый элемент массива arr1, для которого функция func возвращает не 0.
Функция arrayFirst является функцией высшего порядка – в качестве первого аргумента ей нужно передать лямбда-функцию, и этот аргумент не может быть опущен.
Возвращает индекс первого элемента массива arr1, для которого функция func возвращает не 0.
Функция arrayFirstIndex является функцией высшего порядка – в качестве первого аргумента ей нужно передать лямбда-функцию, и этот аргумент не может быть опущен.
Возвращает значение минимального элемента в исходном массиве.
Если передана функция func, возвращается минимум из элементов массива, преобразованных этой функцией.
Функция arrayMin является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Синтаксис:
arrayMin([func,] arr)Параметры:
Возвращаемое значение:
Тип: если передана func, соответствует типу ее возвращаемого значения, иначе соответствует типу элементов массива.
Примеры:
Запрос:
SELECT arrayMin([1, 2, 4]) AS res;Результат:
┌─res─┐
│ 1 │
└─────┘Запрос:
SELECT arrayMin(x -> (-x), [1, 2, 4]) AS res;Результат:
┌─res─┐
│ -4 │
└─────┘Возвращает значение максимального элемента в исходном массиве.
Если передана функция func, возвращается максимум из элементов массива, преобразованных этой функцией.
Функция arrayMax является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Синтаксис:
Параметры:
Возвращаемое значение
Тип: если передана func, соответствует типу ее возвращаемого значения, иначе соответствует типу элементов массива.
Примеры:
Запрос:
SELECT arrayMax([1, 2, 4]) AS res;Результат:
┌─res─┐
│ 4 │
└─────┘Запрос:
SELECT arrayMax(x -> (-x), [1, 2, 4]) AS res;Результат:
┌─res─┐
│ -1 │
└─────┘Возвращает сумму элементов в исходном массиве.
Если передана функция func, возвращается сумма элементов массива, преобразованных этой функцией.
Функция arraySum является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Синтаксис:
arraySum([func,] arr)Параметры:
Возвращаемое значение:
Тип: для Decimal чисел в исходном массиве (если функция func была передана, то для чисел, преобразованных ею) – Decimal128, для чисел с плавающей точкой – Float64, для беззнаковых целых чисел – UInt64, для целых чисел со знаком – Int64.
Примеры:
Запрос:
SELECT arraySum([2, 3]) AS res;Результат:
┌─res─┐
│ 5 │
└─────┘Запрос:
SELECT arraySum(x -> x*x, [2, 3]) AS res;Результат:
┌─res─┐
│ 13 │
└─────┘Возвращает среднее значение элементов в исходном массиве.
Если передана функция func, возвращается среднее значение элементов массива, преобразованных этой функцией.
Функция arrayAvg является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Синтаксис:
arrayAvg([func,] arr)Параметры:
Возвращаемое значение:
Тип: Float64.
Примеры:
Запрос:
SELECT arrayAvg([1, 2, 4]) AS res;Результат:
┌─────────────────res─┐
│ 2.3333333333333335 │
└──────────────────────┘Запрос:
SELECT arrayAvg(x -> (x * x), [2, 4]) AS res;Результат:
┌─res─┐
│ 10 │
└─────┘Синтаксис:
arraySum(arr)Возвращаемое значение:
Параметры:
Примеры:
Запрос:
SELECT arraySum([2,3]) AS res;Результат:
┌─res─┐
│ 5 │
└─────┘Запрос:
SELECT arraySum(x -> x*x, [2, 3]) AS res;Результат:
┌─res─┐
│ 13 │
└─────┘Возвращает массив из частичных сумм элементов исходного массива (сумма с накоплением). Если указана функция func, то значения элементов массива преобразуются этой функцией перед суммированием.
Функция arrayCumSum является функцией высшего порядка – в качестве первого аргумента ей можно передать лямбда-функцию.
Пример:
SELECT arrayCumSum([1, 1, 1, 1]) AS res
┌─res───────────┐
│ [1, 2, 3, 4] │
└───────────────┘Вычисляет площадь под кривой.
Синтаксис:
arrayAUC(arr_scores, arr_labels)Параметры:
Возвращаемое значение:
Тип данных: Float64.
Пример:
Запрос:
select arrayAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])Ответ:
┌─arrayAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐
│ 0.75 │
└──────────────────────────────────────────────---──┘Функции сравнения возвращают всегда 0 или 1 (UInt8).
Сравнивать можно следующие типы:
Например, вы не можете сравнить дату со строкой. Надо использовать функцию преобразования строки в дату или наоборот.
Строки сравниваются побайтово. Более короткая строка меньше всех строк, начинающихся с неё и содержащих ещё хотя бы один символ.
Замечание. До версии 1.1.54134 сравнение знаковых и беззнаковых целых чисел производилось также, как в C++. То есть, вы могли получить неверный результат в таких случаях: SELECT 9223372036854775807 > -1. С версии 1.1.54134 поведение изменилось и стало математически корректным.
Логические функции принимают любые числовые типы, а возвращают число типа UInt8, равное 0 или 1.
Ноль в качестве аргумента считается «ложью», а любое ненулевое значение - «истиной»:
Вычисляет результат логической конъюнкции между двумя и более значениями. Соответствует оператору логического "И".
Синтаксис:
and(val1, val2...)Чтобы вычислять функцию and по короткой схеме, используйте настройку short_circuit_function_evaluation. Если настройка включена, то выражение vali вычисляется только для строк, где условие (val1 AND val2 AND ... AND val{i-1}) верно. Например, при выполнении запроса SELECT and(number = 2, intDiv(1, number)) FROM numbers(10) не будет сгенерировано исключение из-за деления на ноль.
Аргументы:
val1, val2, ... — список из как минимум двух значений. Int, UInt, Float или Nullable.Возвращаемое значение:
0, если среди аргументов есть хотя бы один нуль.NULL.1, в остальных случаях.Тип: UInt8 или Nullable(UInt8).
Пример:
Запрос:
SELECT and(0, 1, -2);Результат:
┌─and(0, 1, -2)─┐
│ 0 │
└────────────────┘Со значениями NULL:
SELECT and(NULL, 1, 10, -2);Результат:
┌─and(NULL, 1, 10, -2)─┐
│ ᴺᵁᴸᴸ │
└───────────────────────┘Вычисляет результат логической дизъюнкции между двумя и более значениями. Соответствует оператору логического "ИЛИ".
Синтаксис:
or(val1, val2...)Чтобы вычислять функцию or по короткой схеме, используйте настройку short_circuit_function_evaluation. Если настройка включена, то выражение vali вычисляется только для строк, где условие ((NOT val1) AND (NOT val2) AND ... AND (NOT val{i-1})) верно. Например, при выполнении запроса SELECT or(number = 0, intDiv(1, number) != 0) FROM numbers(10) не будет сгенерировано исключение из-за деления на ноль.
Аргументы:
val1, val2, ... — список из как минимум двух значений. Int, UInt, Float или Nullable.Возвращаемое значение:
1, если среди аргументов есть хотя бы одно ненулевое число.0, если среди аргументов только нули.NULL, если среди аргументов нет ненулевых значений, и есть NULL.Тип: UInt8 или Nullable(UInt8).
Пример:
Запрос:
SELECT or(1, 0, 0, 2, NULL);Результат:
┌─or(1, 0, 0, 2, NULL)─┐
│ 1 │
└────────────────────────┘Со значениями NULL:
SELECT or(0, NULL);Результат:
┌─or(0, NULL)─┐
│ ᴺᵁᴸᴸ │
└──────────────┘Вычисляет результат логического отрицания аргумента. Соответствует оператору логического отрицания.
Синтаксис:
not(val);Аргументы:
val — значение. Int, UInt, Float или Nullable.Возвращаемое значение:
1, если val — это 0.0, если val — это ненулевое число.NULL, если val — это NULL.Тип: UInt8 или Nullable(UInt8).
Пример:
Запрос:
SELECT NOT(1);Результат:
┌─not(1)─┐
│ 0 │
└─────────┘Вычисляет результат логической исключающей дизъюнкции между двумя и более значениями. При более чем двух значениях функция работает так: сначала вычисляет XOR для первых двух значений, а потом использует полученный результат при вычислении XOR со следующим значением и так далее.
Синтаксис:
xor(val1, val2...)Аргументы:
val1, val2, ... — список из как минимум двух значений. Int, UInt, Float или Nullable.Возвращаемое значение:
1, для двух значений: если одно из значений является нулем, а второе нет.0, для двух значений: если оба значения одновременно нули или ненулевые числа.NULL, если среди аргументов хотя бы один NULL.Тип: UInt8 или Nullable(UInt8).
Пример:
Запрос:
SELECT xor(0, 1, 1);Результат:
┌─xor(0, 1, 1)─┐
│ 0 │
└───────────────┘При преобразовании значения из одного типа в другой необходимо помнить, что в общем случае это небезопасная операция, которая может привести к потере данных. Потеря данных может произойти при попытке сконвертировать тип данных значения от большего к меньшему или при конвертировании между различными классами типов данных.
Поведение RT.WideStore при конвертировании похоже на поведение C++ программ.
Преобразует входное значение к типу Int. Семейство функций включает:
Параметры:
Возвращаемое значение:
Функции используют округление к нулю, т.е. обрезают дробную часть числа.
Поведение функций для аргументов NaN и Inf не определено. При использовании функций помните о возможных проблемах при преобразовании чисел.
Пример:
Запрос:
SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);Результат:
┌─────────toInt64(nan)──┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│ -9223372036854775808 │ 32 │ 16 │ 8 │
└────────────────────────┴──────────────┴─────────────────┴──────────────┘
Принимает аргумент типа String и пытается его распарсить в Int(8|16|32|64|128|256). Если не удалось – возвращает 0.
Пример:
Запрос:
SELECT toInt64OrZero('123123'), toInt8OrZero('123qwe123');Результат:
┌─toInt64OrZero('123123')─┬─toInt8OrZero('123qwe123')─┐
│ 123123 │ 0 │
└───────────────────────────┴──────────────────────────────┘Принимает аргумент типа String и пытается его распарсить в Int(8|16|32|64|128|256). Если не удалось - возвращает NULL.
Пример:
Запрос:
SELECT toInt64OrNull('123123'), toInt8OrNull('123qwe123');Результат:
┌─toInt64OrNull('123123')─┬─toInt8OrNull('123qwe123')─┐
│ 123123 │ ᴺᵁᴸᴸ │
└───────────────────────────┴─────────────────────────────┘Преобразует входное значение к типу UInt. Семейство функций включает:
Параметры:
Возвращаемое значение:
Функции используют округление к нулю, т.е. обрезают дробную часть числа.
Поведение функций для аргументов NaN и Inf не определено. Если передать строку, содержащую отрицательное число, например '-32', RT.WideStore генерирует исключение. При использовании функций помните о возможных проблемах при преобразовании чисел.
Пример:
Запрос:
SELECT toUInt64(nan), toUInt32(-32), toUInt16('16'), toUInt8(8.8);Результат:
┌───────toUInt64(nan)─┬─toUInt32(-32)─┬─toUInt16('16')──┬─toUInt8(8.8)─┐
│ 9223372036854775808 │ 4294967264 │ 16 │ 8 │
└───────────────────────┴────────────────┴──────────────────┴───────────────┘Синоним: DATE.
Преобразует value к типу данных Decimal с точностью S. value может быть числом или строкой. Параметр S (scale) задаёт число десятичных знаков:
Преобразует входную строку в значение с типом данных Nullable (Decimal (P, S)). Семейство функций включает в себя:
Эти функции следует использовать вместо функций toDecimal*(), если при ошибке обработки входного значения вы хотите получать NULL вместо исключения.
Параметры:
Возвращаемое значение:
Значение содержит:
Примеры:
Запрос:
SELECT toDecimal32OrNull(toString(-1.111), 5) AS val, toTypeName(val);Результат:
┌──────val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 5))──┐
│ -1.11100 │ Nullable(Decimal(9, 5)) │
└───────────┴─────────────────────────────────────────────────────────┘Запрос:
SELECT toDecimal32OrNull(toString(-1.111), 2) AS val, toTypeName(val);Результат:
┌──val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 2))─┐
│ ᴺᵁᴸᴸ │ Nullable(Decimal(9, 2)) │
└──────┴─────────────────────────────────────────────────────────┘Преобразует тип входного значения в Decimal (P, S). Семейство функций включает в себя:
Эти функции следует использовать вместо функций toDecimal*(), если при ошибке обработки входного значения вы хотите получать 0 вместо исключения.
Параметры:
Возвращаемое значение:
Значение типа Nullable(Decimal(P,S)). P равно числовой части имени функции. Например, для функции toDecimal32OrZero, P = 32. Значение содержит:
Пример:
Запрос:
SELECT toDecimal32OrZero(toString(-1.111), 5) AS val, toTypeName(val);Результат:
┌──────val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 5))──┐
│ -1.11100 │ Decimal(9, 5) │
└───────────┴─────────────────────────────────────────────────────────┘Запрос:
SELECT toDecimal32OrZero(toString(-1.111), 2) AS val, toTypeName(val);Результат:
┌──val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 2))─┐
│ 0.00 │ Decimal(9, 2) │
└──────┴─────────────────────────────────────────────────────────┘Функции преобразования между числами, строками (ноне фиксированными строками), датами и датами-с-временем.
Все эти функции принимают один аргумент.
При преобразовании в строку или из строки, производится форматирование или парсинг значения по тем же правилам, что и для формата TabSeparated (и почти всех остальных текстовых форматов). Если распарсить строку не удаётся - кидается исключение и выполнение запроса прерывается.
При преобразовании даты в число или наоборот, дате соответствует число дней от начала unix эпохи.
При преобразовании даты-с-временем в число или наоборот, дате-с-временем соответствует число секунд от начала unix эпохи.
Форматы даты и даты-с-временем для функций toDate/toDateTime определены следующим образом:
YYYYY-MM-DD
YYYY-MM-DD hh:mm:ssВ качестве исключения, если делается преобразование из числа типа UInt32, Int32, UInt64, Int64 в Date, и если число больше или равно 65536, то число рассматривается как unix timestamp (а не как число дней) и округляется до даты. Это позволяет поддержать распространённый случай, когда пишут toDate(unix_timestamp), что иначе было бы ошибкой и требовало бы написания более громоздкого toDate(toDateTime(unix_timestamp))
Преобразование между датой и датой-с-временем производится естественным образом: добавлением нулевого времени или отбрасыванием времени.
Преобразование между числовыми типами производится по тем же правилам, что и присваивание между разными числовыми типами в C++.
Дополнительно, функция toString от аргумента типа DateTime может принимать второй аргумент String - имя тайм-зоны. Пример: Asia/Yekaterinburg в этом случае, форматирование времени производится согласно указанной тайм-зоне.
Пример:
Запрос:
SELECT
SELECT
now() AS now_local,
toString(now(), 'Asia/Yekaterinburg') AS now_yekat;Результат:
┌───────────now_local──┬─now_yekat────────────┐
│ 2016-06-15 00:11:21 │ 2016-06-15 02:11:21 │
└───────────────────────┴───────────────────────┘Также смотрите функцию toUnixTimestamp.
Преобразует аргумент типа String в тип FixedString(N) (строку фиксированной длины N). N должно быть константой.
Если строка имеет меньше байт, чем N, то она дополняется нулевыми байтами справа. Если строка имеет больше байт, чем N - кидается исключение.
Принимает аргумент типа String или FixedString. Возвращает String, вырезая содержимое строки до первого найденного нулевого байта.
Примеры:
Запрос:
SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut;Результат:
┌─s──────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo │
└────────────────┴────────┘Запрос:
SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut;Результат:
┌─s───────────┬─s_cut─┐
│ foo\0bar\0 │ foo │
└─────────────┴────────┘Функции принимают строку интерпретируют байты, расположенные вначале строки, как число в host order (little endian). Если строка имеет недостаточную длину, то функции работают так, как будто строка дополнена необходимым количеством нулевых байт. Если строка длиннее, чем нужно, то лишние байты игнорируются. Дата интерпретируется, как число дней с начала unix-эпохи, а дата-с-временем – как число секунд с начала unix-эпохи.
Функция принимает число или дату, или дату-с-временем и возвращает строку, содержащую байты, представляющие соответствующее значение в host order (little endian). При этом, отбрасываются нулевые байты с конца. Например, значение 255 типа UInt32 будет строкой длины 1 байт.
Функция принимает шестнадцатибайтную строку и интерпретирует ее байты в network order (big-endian). Если строка имеет недостаточную длину, то функция работает так, как будто строка дополнена необходимым количеством нулевых байт с конца. Если строка длиннее, чем шестнадцать байт, то игнорируются лишние байты с конца.
Синтаксис:
reinterpretAsUUID(fixed_string)Параметры:
Возвращаемое значение:
Примеры:
Интерпретация строки как UUID.
Запрос:
SELECT reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')));Результат:
┌─reinterpretAsUUID(reverse(unhex('000102030405060708090a0b0c0d0e0f')))─┐
│ 08090a0b-0c0d-0e0f-0001-020304050607 │
└─────────────────────────────────────────────────────────────────────────────┘Переход в UUID и обратно.
Запрос:
WITH
generateUUIDv4() AS uuid,
identity(lower(hex(reverse(reinterpretAsString(uuid))))) AS str,
reinterpretAsUUID(reverse(unhex(str))) AS uuid2
SELECT uuid = uuid2;Результат:
┌─equals(uuid, uuid2)─┐
│ 1 │
└───────────────────────┘Преобразует входное значение x в указанный тип данных T.
Поддерживается также синтаксис CAST(x AS t).
Обратите внимание, что если значение x не может быть преобразовано к типу T, возникает переполнение. Например, CAST(-1, 'UInt8') возвращает 255.
Пример:
Запрос:
SELECT
'2016-06-15 23:00:00' AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string;Результат:
┌┌─timestamp───────────┬────────────datetime──┬───────date──┬─string───────────────┬─fixed_string───────────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└───────────────────────┴───────────────────────┴─────────────┴───────────────────────┴─────────────────────────────┘Преобразование в FixedString(N) работает только для аргументов типа String или FixedString(N).
Поддержано преобразование к типу Nullable и обратно.
Примеры:
Запрос:
SELECT toTypeName(x) FROM t_null;Результат:
┌─toTypeName(x)─┐
│ Int8 │
│ Int8 │
└────────────────┘Запрос:
SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null;Результат:
┌─toTypeName(CAST(x, 'Nullable(UInt16)'))─┐
│ Nullable(UInt16) │
│ Nullable(UInt16) │
└─────────────────────────────────────────────┘См. также:
Преобразует входное значение x в указанный тип данных T.
В отличие от функции cast(x, T), accurateCast не допускает переполнения при преобразовании числовых типов. Например, accurateCast(-1, 'UInt8') вызовет исключение.
Примеры:
Запрос:
SELECT cast(-1, 'UInt8') as uint8; Результат:
┌─uint8─┐
│ 255 │
└────────┘
Запрос:
```sql
SELECT accurateCast(-1, 'UInt8') as uint8;Результат:
Code: 70. DB::Exception: Received from localhost:9000. DB::Exception:
Value in column Int8 cannot be safely converted into type UInt8:
While processing accurateCast(-1, 'UInt8') AS uint8.Преобразует входное значение x в указанный тип данных T.
Всегда возвращает тип Nullable. Если исходное значение не может быть преобразовано к целевому типу, возвращает NULL.
Синтаксис:
accurateCastOrNull(x, T)Параметры:
Возвращаемое значение:
Примеры:
Запрос:
SELECT toTypeName(accurateCastOrNull(5, 'UInt8'));Результат:
┌─toTypeName(accurateCastOrNull(5, 'UInt8'))─┐
│ Nullable(UInt8) │
└────────────────────────────────────────────────┘Запрос:
SELECT
accurateCastOrNull(-1, 'UInt8') as uint8,
accurateCastOrNull(128, 'Int8') as int8,
accurateCastOrNull('Test', 'FixedString(2)') as fixed_string;Результат:
┌─uint8─┬─int8─┬─fixed_string─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└────────┴──────┴───────────────┘Приводит аргумент из числового типа данных к типу данных IntervalType.
Синтаксис:
toIntervalSecond(number)
toIntervalMinute(number)
toIntervalHour(number)
toIntervalDay(number)
toIntervalWeek(number)
toIntervalMonth(number)
toIntervalQuarter(number)
toIntervalYear(number)Параметры:
Возвращаемые значения:
Пример:
Запрос:
WITH
toDate('2019-01-01') AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week
SELECT
date + interval_week,
date + interval_to_week;Результат:
┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┐
│ 2019-01-08 │ 2019-01-08 │
└─────────────────────────────┴─────────────────────────────────┘Преобразует дату и время в строковом представлении к типу данных DateTime.
Функция распознаёт форматы ISO 8601, RFC 1123 - 5.2.14 RFC-822 Date and Time Specification, формат даты времени RT.WideStore’s а также некоторые другие форматы.
Синтаксис:
parseDateTimeBestEffort(time_string[, time_zone])Параметры:
Поддерживаемые нестандартные форматы:
Для всех форматов с разделителями функция распознаёт названия месяцев, выраженных в виде полного англоязычного имени месяца или в виде первых трёх символов имени месяца. Примеры: 24/DEC/18, 24-Dec-18, 01-September-2018.
Возвращаемое значение:
Примеры:
Запрос:
SELECT parseDateTimeBestEffort('12/12/2020 12:12:57')
AS parseDateTimeBestEffort;Результат:
┌─parseDateTimeBestEffort─┐
│ 2020-12-12 12:12:57 │
└───────────────────────────┘Запрос:
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Europe/Moscow')
AS parseDateTimeBestEffort;Результат:
┌─parseDateTimeBestEffort─┐
│ 2018-08-18 10:22:16 │
└───────────────────────────┘Запрос:
SELECT parseDateTimeBestEffort('1284101485')
AS parseDateTimeBestEffort;Результат:
┌─parseDateTimeBestEffort─┐
│ 2015-07-07 12:04:41 │
└───────────────────────────┘Запрос:
SELECT parseDateTimeBestEffort('2018-12-12 10:12:12')
AS parseDateTimeBestEffort;Результат:
┌─parseDateTimeBestEffort─┐
│ 2018-12-12 10:12:12 │
└───────────────────────────┘Запрос:
SELECT parseDateTimeBestEffort('10 20:19');Результат:
┌─parseDateTimeBestEffort('10 20:19')─┐
│ 2000-01-10 20:19:00 │
└────────────────────────────────────────┘См. также:
Эта функция похожа на ‘parseDateTimeBestEffort’, но разница состоит в том, что в она предполагает американский формат даты (MM/DD/YYYY etc.) в случае неоднозначности.
Синтаксис:
parseDateTimeBestEffortUS(time_string [, time_zone])Параметры:
Поддерживаемые нестандартные форматы:
Возвращаемое значение:
Примеры:
Запрос:
SELECT parseDateTimeBestEffortUS('09/12/2020 12:12:57')
AS parseDateTimeBestEffortUS;Результат:
┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└───────────────────────────––┘Запрос:
SELECT parseDateTimeBestEffortUS('09-12-2020 12:12:57')
AS parseDateTimeBestEffortUS;Результат:
┌─p┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└─────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUS('09.12.2020 12:12:57')
AS parseDateTimeBestEffortUS;Результат:
┌─parseDateTimeBestEffortUS─┐
│ 2020-09-12 12:12:57 │
└──────────────────────────────┘Работает аналогично функции parseDateTimeBestEffortUS, но в отличие от нее возвращает NULL, если входная строка не может быть преобразована в тип данных DateTime.
Синтаксис:
parseDateTimeBestEffortUSOrNull(time_string[, time_zone])Параметры:
Поддерживаемые нестандартные форматы:
Возвращаемые значения:
Примеры:
Запрос:
SELECT parseDateTimeBestEffortUSOrNull('02/10/2021 21:12:57') AS parseDateTimeBestEffortUSOrNull;Результат:
┌─pa┌─parseDateTimeBestEffortUSOrNull─┐
│ 2021-02-10 21:12:57 │
└────────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrNull('02-10-2021 21:12:57 GMT', 'Europe/Moscow') AS parseDateTimeBestEffortUSOrNull;Результат:
┌─parseDateTimeBestEffortUSOrNull─┐
│ 2021-02-11 00:12:57 │
└────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrNull('02.10.2021') AS parseDateTimeBestEffortUSOrNull;Результат:
┌─parseDateTimeBestEffortUSOrNull─┐
│ 2021-02-10 00:00:00 │
└────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrNull('10.2021') AS parseDateTimeBestEffortUSOrNull;Результат:
┌─parseDateTimeBestEffortUSOrNull─┐
│ ᴺᵁᴸᴸ │
└────────────────────────────────────┘Работает аналогично функции parseDateTimeBestEffortUS, нов отличие от нее возвращает нулевую дату (1970-01-01) или нулевую дату со временем (1970-01-01 00:00:00), если входная строка не может быть преобразована в тип данных DateTime.
Синтаксис:
parseDateTimeBestEffortUSOrZero(time_string[, time_zone])Параметры:
Поддерживаемые нестандартные форматы:
Возвращаемые значения:
Примеры:
Запрос:
SELECT parseDateTimeBestEffortUSOrZero('02/10/2021 21:12:57') AS parseDateTimeBestEffortUSOrZero;Результат:
┌─parseDateTimeBestEffortUSOrZero─┐
│ 2021-02-10 21:12:57 │
└────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrZero('02-10-2021 21:12:57 GMT', 'Europe/Moscow') AS parseDateTimeBestEffortUSOrZero;Результат:
┌─parseDateTimeBestEffortUSOrZero─┐
│ 2021-02-11 00:12:57 │
└────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrZero('02.10.2021') AS parseDateTimeBestEffortUSOrZero;Результат:
┌─parseDateTimeBestEffortUSOrZero─┐
│ 2021-02-10 00:00:00 │
└────────────────────────────────────┘Запрос:
SELECT parseDateTimeBestEffortUSOrZero('02.2021') AS parseDateTimeBestEffortUSOrZero;Результат:
┌─parseDateTimeBestEffortUSOrZero─┐
│ 1970-01-01 00:00:00 │
└────────────────────────────────────┘Преобразует значение DateTime64 в значение Int64 с фиксированной точностью менее одной секунды.
Входное значение округляется соответствующим образом вверх или вниз в зависимости от его точности. Обратите внимание, что возвращаемое значение – это временная метка в UTC, а не в часовом поясе DateTime64.
Синтаксис:
toUnixTimestamp64Milli(value)Параметры:
Возвращаемое значение:
Примеры:
Запрос:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Milli(dt64);Результат:
┌─toUnixTimestamp64Milli(dt64)─┐
│ 1568650812345 │
└─────────────────────────────────┘Запрос:
WITH toDateTime64('2019-09-16 19:20:12.345678910', 6) AS dt64
SELECT toUnixTimestamp64Nano(dt64);Результат:
┌─toUnixTimestamp64Nano(dt64)─┐
│ 1568650812345678000 │
└────────────────────────────────┘Преобразует значение Int64 в значение DateTime64 с фиксированной точностью менее одной секунды и дополнительным часовым поясом. Входное значение округляется соответствующим образом вверх или вниз в зависимости от его точности. Обратите внимание, что входное значение обрабатывается как метка времени UTC, а не метка времени в заданном (или неявном) часовом поясе.
Синтаксис:
fromUnixTimestamp64Milli(value [, ti])Параметры:
Возвращаемое значение:
Пример:
Запрос:
WITH CAST(1234567891011, 'Int64') AS i64
SELECT fromUnixTimestamp64Milli(i64, 'UTC');Результат:
┌─fromUnixTimestamp64Milli(i64, 'UTC')─┐
│ 2009-02-13 23:31:31.011 │
└─────────────────────────────────────────┘Преобразует входные данные в версию LowCardianlity того же типа данных.
Чтобы преобразовать данные из типа LowCardinality, используйте функцию CAST. Например, CAST(x as String).
Синтаксис:
toLowCardinality(expr)Параметры:
Возвращаемое значение:
Тип: LowCardinality(expr_result_type).
Пример:
Запрос:
SELECT toLowCardinality('1');Результат:
┌─toLowCardinality('1')─┐
│ 1 │
└─────────────────────────┘Преобразует произвольные выражения в строку заданного формата.
Синтаксис:
formatRow(format, x, y, ...)Параметры:
Возвращаемое значение:
Пример:
Запрос:
SELECT formatRow('CSV', number, 'good')
FROM numbers(3);Результат:
┌─formatRow('CSV', number, 'good')─┐
│ 0,"good" │
│ 1,"good" │
│ 2,"good" │
└─────────────────────────────────────┘Преобразует произвольные выражения в строку заданного формата. При этом удаляет лишние переводы строк \n, если они появились.
Синтаксис:
formatRowNoNewline(format, x, y, ...)Параметры:
Пример:
Запрос:
SELECT formatRowNoNewline('CSV', number, 'good')
FROM numbers(3);Результат:
┌─formatRowNoNewline('CSV', number, 'good')─┐
│ 0,"good" │
│ 1,"good" │
│ 2,"good" │
└───────────────────────────────────────────────┘