Подключиться
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(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 — обратная операция.

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

  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://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 — мягкая проверка соответствия типа выражения, работающая по тем же правилам, что и неявное приведение типов.

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

Аргументы:

  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 есть следующие отличия от стандарта:

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

Для параметрического типа данных 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.

Аргументы:

  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.

В этой статье: