Базовые встроенные функции
- COALESCE
- LENGTH
- SUBSTRING
- IF
- NANVL
- Random, RandomNumber, RandomUuid
- CurrentUtcDate, CurrentUtcDatetime и CurrentUtcTimestamp
- MAX_OF, MIN_OF, GREATEST и LEAST
- AsTuple, AsStruct, AsList, AsDict, AsListStrict и AsDictStrict
- AsTagged и Untag
- TableRow
- Ensure, EnsureType и EnsureConvertibleTo
- Создание литералов простых типов на основе строкового литерала
- Доступ к метаданным текущей операции
- ToBytes и FromBytes
- ByteAt
- TestBit, ClearBit, SetBit и FlipBit
- Abs
- Just
- Unwrap
- Nothing
- AGGREGATION_FACTORY
- LIKELY
- GROUPING
Ниже описаны функции общего назначения, а для специализированных функций есть отдельные статьи: агрегатные, оконные, а также статьи для работы со списками, словарями и типами данных.
COALESCE
Перебирает аргументы слева направо и возвращает первый найденный непустой аргумент. Чтобы результат получился гарантированно непустым (не optional типа), самый правый аргумент должен быть такого типа (зачастую используют литерал). При одном аргументе возвращает его без изменений.
Позволяет передавать потенциально пустые значения в функции, которые не умеют обрабатывать их самостоятельно.
Доступен краткий формат записи в виде оператора ??
, обладающего низким приоритетом (ниже булевых операций). Можно использовать алиас NVL
.
Примеры
SELECT COALESCE(
maybe_empty_column,
"it's empty!"
) FROM my_table;
SELECT
maybe_empty_column ?? "it's empty!"
FROM my_table;
SELECT NVL(
maybe_empty_column,
"it's empty!"
) FROM my_table;
LENGTH
Возвращает длину строки в байтах. Также эта функция доступна под именем LEN
.
Примеры
SELECT LENGTH("foo");
SELECT LEN("bar");
Примечание
Для вычисления длины строки в unicode символах можно воспользоваться функцией Unicode::GetLength.
Для получения числа элементов в списке нужно использовать функцию ListLength.
SUBSTRING
Возвращает подстроку.
Обязательные аргументы:
- Исходная строка;
- Позиция — отступ от начала строки в байтах (целое число).
Опциональные аргументы:
- Длина подстроки — количество байт, начиная с указанной позиции (целое число, по умолчанию «до конца исходной строки»).
Если указанные позиция и длина выходят за пределы строки, возвращает пустую строку.
Примеры
SELECT SUBSTRING("abcdefg", 3, 1); -- d
SELECT SUBSTRING("abcdefg", 3); -- defg
IF
Проверяет условие IF(condition_expression, then_expression, else_expression)
.
Упрощенная альтернатива — CASE WHEN ... THEN ... ELSE ... END.
Аргумент else_expression
можно не указывать. В этом случае, если условие ложно (condition_expression
вернул false
), будет возвращено пустое значение с типом, соответствующим then_expression
и допускающим значение NULL
. Таким образом, у результата получится optional тип данных.
Примеры
SELECT
IF(foo > 0, bar, baz) AS bar_or_baz,
IF(foo > 0, foo) AS only_positive_foo
FROM my_table;
NANVL
Заменяет значения NaN
(not a number) в выражениях типа Float
, Double
или Optional.
Аргументы:
- Выражение, в котором нужно произвести замену.
- Значение, на которое нужно заменить
NaN
.
Если один из агрументов Double
, то в выдаче Double
, иначе Float
. Если один из агрументов Optional
, то и в выдаче Optional
.
Примеры
SELECT
NANVL(double_column, 0.0)
FROM my_table;
Random, RandomNumber, RandomUuid
Генерирует псевдослучайное число:
Random
— число с плавающей точкой (Double) от 0 до 1;RandomNumber
— целое число из всего диапазона Uint64;RandomUuid
— Uuid version 4.
При генерации случайных чисел аргументы не используются и нужны исключительно для управления моментом вызова. В каждый момент вызова возвращается новое случайное число. Поэтому:
- Повторный вызов Random в рамках одного запроса при идентичном наборе аргументов возвращает тот же самый набор случайных чисел. Важно понимать, что речь именно про сами аргументы (текст между круглыми скобками), а не их значения.
- Вызовы Random с одним и тем же набором аргументов в разных запросах вернут разные наборы случайных чисел.
Сценарии использования:
SELECT RANDOM(1);
— получить одно случайное значение на весь запрос и несколько раз его использовать (чтобы получить несколько, можно передать разные константы любого типа);SELECT RANDOM(1) FROM table;
— одно и то же случайное число на каждую строку таблицы;SELECT RANDOM(1), RANDOM(2) FROM table;
— по два случайных числа на каждую строку таблицы, все числа в каждой из колонок одинаковые;SELECT RANDOM(some_column) FROM table;
— разные случайные числа на каждую строку таблицы;SELECT RANDOM(some_column), RANDOM(some_column) FROM table;
— разные случайные числа на каждую строку таблицы, но в рамках одной строки — два одинаковых числа;SELECT RANDOM(some_column), RANDOM(other_column) FROM table;
— две колонки и все с разными числами.
Примеры
SELECT
Random(key) -- [0, 1)
FROM my_table;
SELECT
RandomNumber(key) -- [0, Max<Uint64>)
FROM my_table;
SELECT
RandomUuid(key) -- Uuid version 4
FROM my_table;
SELECT
RANDOM(column) AS rand1,
RANDOM(column) AS rand2, -- same as rand1
RANDOM(column, 1) AS randAnd1, -- different from rand1/2
RANDOM(column, 2) AS randAnd2 -- different from randAnd1
FROM my_table;
CurrentUtcDate, CurrentUtcDatetime и CurrentUtcTimestamp
Получение текущей даты и/или времени в UTC. Тип данных результата указан в конце названия функции.
Аргументы опциональны и работают по тому же принципу, что и у RANDOM.
Примеры
SELECT CurrentUtcDate();
SELECT CurrentUtcTimestamp(TableRow()) FROM my_table;
MAX_OF, MIN_OF, GREATEST и LEAST
Возвращает минимальный или максимальный среди N аргументов. Эти функции позволяют не использовать стандартную для SQL конструкцию CASE WHEN a < b THEN a ELSE b END
, которая была бы особенно громоздкой для N больше двух.
Типы аргументов должны быть приводимы друг к другу и могут допускать значение NULL
.
GREATEST
является синонимом к MAX_OF
, а LEAST
— к MIN_OF
.
Примеры
SELECT MIN_OF(1, 2, 3);
AsTuple, AsStruct, AsList, AsDict, AsListStrict и AsDictStrict
Создает контейнеры соответствующих типов.
Особенности:
- Элементы контейнеров передаются через аргументы, таким образом число элементов результирующего контейнера равно числу переданных аргументов, кроме случая, когда повторяются ключи словаря.
- В
AsTuple
иAsStruct
могут быть вызваны без аргументов, а также аргументы могут иметь разные типы. - Имена полей в
AsStruct
задаются черезAsStruct(field_value AS field_name)
. - Для создания списка требуется хотя бы один аргумент, чтобы вывести типы элементов. Для создания пустого списка используется функция ListCreate.
- Для создания словаря требуется хотя бы один аргумент, чтобы вывести типы элементов. Для создания пустого словаря используется функция DictCreate.
AsList
выводит общий тип элементов списка. При несовместимых типах генерируется ошибка типизации.AsDict
выводит раздельно общие типы ключей и значений. При несовместимых типах генерируется ошибка типизации.AsListStrict
иAsDictStrict
требуют одинакового типа для аргументов.- В
AsDict
иAsDictStrict
в качестве аргументов ожидаютсяTuple
из двух элементов: ключ и значение, соответственно. Если ключи повторяются, в словаре останется только значение для первого ключа.
Примеры
SELECT
AsTuple(1, 2, "3") AS tuplevar,
AsStruct(
1 AS a,
2 AS b,
"3" AS c
) AS structvar,
AsList(1, 2, 3) AS listvar,
AsDict(
AsTuple("a", 1),
AsTuple("b", 2),
AsTuple("c", 3)
) AS dictvar;
AsTagged и Untag
Оборачивает значение в Tagged тип данных с указанной меткой с сохранением физического типа данных. Untag
— обратная операция.
Обязательные аргументы:
- Значение произвольного типа;
- Имя метки.
Возвращает копию значения из первого аргумента с указанной меткой в типе данных.
Примеры сценариев использования:
- Возвращение на клиент для отображения в веб-интерфейсе медиа-файлов из base64-encoded строк;
- Защита на границах вызова UDF от передачи некорректных значений;
- Дополнительные уточнения на уровне типов возвращаемых колонок.
Поддерживаемые веб-интерфейсом mime-type и одноименные метки:
- image/jpeg
- image/png
- image/gif
- image/webp
- image/svg
- image/svg+xml
- audio/mp4
- audio/webm
- video/mp4
- video/webm
Также им поддерживаются кликабельные ссылки и показ медиа-плеера для контента по ссылке, для отображения которых нужно указывать следующие метки:
- url
- imageurl
- audiourl
- videourl
Веб-интерфейс поддерживает расширенный синтаксис для некоторых тегов.
Ссылка с произвольным текстом
Чтобы сформировать в выдаче «красивую» ссылку с произвольным текстом, в тег url
заворачивается структура Struct<href:String?,text:String?,title:String?>
.
Примеры
SELECT AsTagged(
AsStruct(
"https://yandex.ru/" AS href,
"Yandex main page" AS text,
"Yandex" AS title
),
"url"
);
Изображение с заданным размером
Для ограничения размеров картинки в теги image/*
и imageurl
заворачивается структура Struct<src:String,width:Int64?,height:Int64?,maxWidth:Int64?,maxHeight:Int64>
.
Здесь поле src
обязательное, все размеры опциональны и задаются в пикселях.
Примеры
SELECT AsTagged(
AsStruct(
"https://avatars.yandex-team.ru/get-yablog/id1166620212013/normal" AS src,
200 AS maxWidth,
200 AS maxHeighgitt
),
"imageurl"
);
TableRow
Получение всей строки таблицы целиком в виде структуры. Аргументов нет.
Примеры
SELECT TableRow() FROM my_table;
Ensure, EnsureType и EnsureConvertibleTo
Проверка пользовательских условий:
Ensure
— проверка верности предиката во время выполнения запроса;EnsureType
— проверка точного соответствия типа выражения указанному;EnsureConvertibleTo
— мягкая проверка соответствия типа выражения, работающая по тем же правилам, что и неявное приведение типов.
Если проверка не прошла успешно, то весь запрос завершается с ошибкой.
Аргументы:
- Выражение, которое станет результатом вызова функции в случае успеха проверки. Оно же подвергается проверке на тип данных в соответствующих функциях.
- В Ensure — булевый предикат, который проверяется на
true
. В остальных функциях — тип данных, который может быть получен через предназначенные для этого функции, либо строковый литерал с текстовым описанием типа. - Опциональная строка с комментарием к ошибке, которая попадет в общее сообщение об ошибке при завершении запроса. Для проверок типов не может использовать сами данные, так как они выполняются на этапе валидации запроса, а для Ensure — может быть произвольным выражением.
Для проверки условий по финальному результату вычисления Ensure удобно использовать в сочетании с DISCARD SELECT.
Примеры
SELECT Ensure(
value,
value < 100,
"value out or range"
) AS value FROM my_table;
SELECT EnsureType(
value,
TypeOf(other_value),
"expected value and other_value to be of same type"
) AS value FROM my_table;
SELECT EnsureConvertibleTo(
value,
"Double?",
"expected value to be numeric"
) AS value FROM my_table;
Создание литералов простых типов на основе строкового литерала
В отличие от CAST("myString" AS MyType)
:
- Проверка на приводимость литерала к требуемому типу происходит на этапе валидации;
- Результат не является optional.
Для типов данных Date
, Datetime
, Timestamp
и Interval
поддерживаются литералы только в формате, соответствующем ISO 8601. У Interval
есть следующие отличия от стандарта:
- поддерживается отрицательный знак для сдвигов в прошлое;
- микросекунды могут быть записаны как дробная часть секунд;
- единицы измерения больше суток не доступны;
- не поддерживаются варианты с началом/концом интервала, а также повторами.
Для параметрического типа данных Decimal дополнительно указывается два аргумента:
- общее число десятичных знаков (до 35, включительно);
- число десятичных знаков после запятой (из общего числа, то есть строго не больше предыдущего аргумента).
Примеры
SELECT
Bool("true"),
Uint8("0"),
Int32("-1"),
Uint32("2"),
Int64("-3"),
Uint64("4"),
Float("-5"),
Double("6"),
Decimal("1.23", 5, 2), -- до 5 десятичных знаков, из которых 2 после запятой
String("foo"),
Utf8("привет"),
Yson("<a=1>[3;%false]"),
Json(@@{"a":1,"b":null}@@),
Date("2017-11-27"),
Datetime("2017-11-27T13:24:00Z"),
Timestamp("2017-11-27T13:24:00.123456Z"),
Interval("P1DT2H3M4.567890S"),
Uuid("f9d5cc3f-f1dc-4d9c-b97e-766e57ca4ccb");
Доступ к метаданным текущей операции
При запуске YQL операций через веб-интерфейс или API, предоставляется доступ к следующей информации:
CurrentOperationId
— приватный идентификатор операции;CurrentOperationSharedId
— публичный идентификатор операции;CurrentAuthenticatedUser
— логин текущего пользователя.
Аргументов нет.
При отсутствии данной информации, например при запуске в embedded режиме, возвращают строку.
Примеры
SELECT
CurrentOperationId(),
CurrentOperationSharedId(),
CurrentAuthenticatedUser();
ToBytes и FromBytes
Конвертация простых типов данных в строку со своим бинарным представлением и обратно. Числа представляются в little endian.
Примеры
SELECT
ToBytes(7), -- "\u0007\u0000\u0000\u0000"
FromBytes(
"\xd2\x02\x96\x49\x00\x00\x00\x00",
Uint64
); -- 1234567890ul
ByteAt
Получение значение байта в строке по индексу от её начала. В случае некорректного индекса возвращается NULL
.
Аргументы:
- Строка:
String
илиUtf8
; - Индекс:
Uint32
.
Примеры
SELECT
ByteAt("foo", 0), -- 102
ByteAt("foo", 1), -- 111
ByteAt("foo", 9); -- NULL
TestBit, ClearBit, SetBit и FlipBit
Проверить, сбросить, установить или инвертировать бит в беззнаковом числе по указанному порядковому номеру бита.
Аргументы:
- Беззнаковое число, над которым выполнять требуемую операцию.
TestBit
также реализован и для строк. - Номер бита.
TestBit
возвращает true/false
. Остальные функции возвращают копию своего первого аргумента с проведенным соответствующим преобразованием.
Примеры
SELECT
TestBit(1u, 0), -- true
SetBit(8u, 0); -- 9
Abs
Абсолютное значение числа.
Примеры
SELECT Abs(-123); -- 123
Just
Изменить тип данных значения на optional от текущего типа данных (то есть T
превращается в T?
).
Обратная операция — Unwrap.
Примеры
SELECT
Just("my_string"); -- String?
Unwrap
Преобразование значения optional типа данных в соответствующий не-optional тип с ошибкой времени выполнений, если в данных оказался NULL
. Таким образом, T?
превращается в T
.
Аргументы:
- Значение для преобразования;
- Опциональная строка с комментарием для текста ошибки.
Обратная операция — Just.
Примеры
$value = Just("value");
SELECT Unwrap($value, "Unexpected NULL for $value");
Nothing
Создать пустое значение указанного optional типа данных.
Примеры
SELECT
Nothing(ParseType("String?")); -- пустое значение (NULL) с типом String?
Подробнее о ParseType
и других функциях для работы с типами данных читайте в разделе Типы данных.
AGGREGATION_FACTORY
Создать фабрику для агрегационных функций для того чтобы разделить процесс описания того, как агрегрировать данные, и то, к каким данным это применять.
Аргументы:
- Строка в кавычках, являющаяся именем агрегационной функции, например "MIN".
- Опциональные параметры агрегационной функции, которые не зависят от данных. Например, значение percentile в PERCENTILE.
Полученную фабрику можно использовать как второй параметр функции AGGREGATE_BY.
Если агрегационная функция работает на двух колонках вместо одной, как например, MIN_BY, то в AGGREGATE_BY первым аргументом передается Tuple
из двух значений. Подробнее это указано при описании такой агрегационной функции.
Примеры
$factory = AGGREGATION_FACTORY("MIN");
SELECT
AGGREGATE_BY(value, $factory) AS min_value -- применить MIN агрегацию к колонке value
FROM my_table;
LIKELY
Позволяет подсказать оптимизатору, что содержащееся в аргументе булевое значение чаще всего будет true
. На данный момент эта подсказка используется только при выборе стратегии выполнения JOIN.
GROUPING
Позволяет отличить промежуточный итог от NULL
в исходных данных при агрегации. Подробнее см. в документации по ROLLUP, CUBE и GROUPING SETS.