Подключиться
Yandex Database

Базовые встроенные функции

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

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.

Аргументы:

  1. Выражение, в котором нужно произвести замену.
  2. Значение, на которое нужно заменить 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(some_column + 1) 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;

AddTimezone

Добавление информации о временной зоне к дате/времени, заданных в UTC. При выводе в результате SELECT или после CAST в String будут применены правила временной зоны по вычислению смещения времени.

Аргументы:

  1. Дата - тип Date/Datetime/Timestamp;
  2. IANA имя временной зоны.

Тип результата - TzDate/TzDatetime/TzTimestamp, в зависимости от типа данных входа.

Примеры

SELECT AddTimezone(Datetime("2018-02-01T12:00:00Z"), "Europe/Moscow");

RemoveTimezone

Удаляет информацию о временной зоне и переводит в дату/время, заданные в UTC.

Аргументы:

Дата - тип TzDate/TzDatetime/TzTimestamp.

Тип результата:

Date/Datetime/Timestamp, в зависимости от типа данных входа.

Примеры

SELECT RemoveTimezone(TzDatetime("2018-02-01T12:00:00,Europe/Moscow"));

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 tuple,
  AsStruct(
    1 AS a,
    2 AS b,
    "3" AS c
  ) AS struct,
  AsList(1, 2, 3) AS list,
  AsDict(
    AsTuple("a", 1),
    AsTuple("b", 2),
    AsTuple("c", 3)
  ) AS dict;

AsTagged и Untag

Оборачивает значение в Tagged тип данных с указаной меткой с сохранением физического типа данных. Untag — обратная операция.

Обязательные аргументы:

  1. Значение произвольного типа;
  2. Имя метки.

Возвращает копию значения из первого аргумента с указанной меткой в типе данных.

Примеры сценариев использования:

  • Возвращение на клиент для отображения в веб-интерфейсе медиа-файлов из 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://infra.yandex-team.ru" AS href,
        "Infra services status" AS text,
        "Status" 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 — мягкая проверка соответствия типа выражения, работающая по тем же правилам, что и неявное приведение типов.

Если проверка не прошла успешно, то весь запрос завершается с ошибкой.

Аргументы:

  1. Выражение, которое станет результатом вызова функции в случае успеха проверки. Оно же подвергается проверке на тип данных в соответствующих функциях.
  2. В Ensure — булевый предикат, который проверяется на true. В остальных функциях — тип данных, который может быть получен через предназначенные для этого функции, либо строковый литерал с текстовым описанием типа.
  3. Опциональная строка с комментарием к ошибке, которая попадет в общее сообщение об ошибке при завершении запроса. Для проверок типов не может использовать сами данные, так как они выполняются на этапе валидации запроса, а для 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 есть следующие отличия от стандарта:

  • поддерживается отрицательный знак для сдвигов в прошлое;
  • микросекунды могут быть записаны как дробная часть секунд;
  • единицы измерения больше суток не доступны;
  • не поддерживаются варианты с началом/концом интервала, а также повторами.

Для типов данных TzDate, TzDatetime, TzTimestamp литералы также задаются в формате, соответствующем ISO 8601, но вместо опционального суффикса Z через запятую указывается IANA имя временной зоны, например, GMT или Europe/Moscow.

Для параметрического типа данных 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"),
  TzDate("2017-11-27,Europe/Moscow"),
  TzDatetime("2017-11-27T13:24:00,America/Los_Angeles"),
  TzTimestamp("2017-11-27T13:24:00.123456,GMT"),
  Uuid("f9d5cc3f-f1dc-4d9c-b97e-766e57ca4ccb");

Доступ к метаданным текущей операции

При запуске YQL операций через веб-интерфейс или API, предоставляется доступ к следующей информации:

  • CurrentOperationId — приватный идентификатор операции;
  • CurrentOperationSharedId — публичный идентификатор операции;
  • CurrentAuthenticatedUser — логин текущего пользователя.

Аргументов нет.

При отсутствии данной информации, например при запуске в embedded режиме, возвращают строку.

Примеры

SELECT
    CurrentOperationId(),
    CurrentOperationSharedId(),
    CurrentAuthenticatedUser();

ToBytes и FromBytes

Конвертация простых типов данных в строку со своим бинарным представлением и обратно. Числа представляются в little endian.

Примеры

SELECT
    ToBytes(123), -- "\u0001\u0000\u0000\u0000"
    FromBytes(
        "\xd2\x02\x96\x49\x00\x00\x00\x00",
        "Uint64"
    ); -- 1234567890ul

ByteAt

Получение значение байта в строке по индексу от её начала. В случае некорректного индекса возвращается NULL.

Аргументы:

  1. Строка: String или Utf8;
  2. Индекс: Uint32.

Примеры

SELECT
    ByteAt("foo", 0) -- 102
    ByteAt("foo", 1) -- 111
    ByteAt("foo", 9) -- NULL

TestBit, ClearBit, SetBit и FlipBit

Проверить, сбросить, установить или инвертировать бит в беззнаковом числе по указанному порядковому номеру бита.

Аргументы:

  1. Беззнаковое число, над которым выполнять требуемую операцию. TestBit также реализован и для строк.
  2. Номер бита.

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.

Аргументы:

  1. Значение для преобразования;
  2. Опциональная строка с комментарием для текста ошибки.

Обратная операция — Just.

Примеры

$value = Just("value");

SELECT Unwrap($value, "Unexpected NULL for $value");

Nothing

Создать пустое значение указанного optional типа данных.

Примеры

SELECT
  Nothing(ParseType("String?")); -- пустое значение (NULL) с типом String?

Подробнее о ParseType и других функциях для работы с типами данных читайте в разделе Типы данных.

AGGREGATION_FACTORY

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

Аргументы:

  1. Строка в кавычках, являющаяся именем агрегационной функции, например "MIN".
  2. Опциональные параметры агрегационной функции, которые не зависят от данных. Например, значение 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.