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

Выражения

Статья создана

Конкатенация строк

Выполняется через бинарный оператор ||.

Как и у других бинарных операторов, если в данных с одной из сторон оказался NULL, то и результат будет NULL.

Не следует путать этот оператор с логическим «или», в SQL оно обозначается ключевым словом OR. Также не стоит пытаться делать конкатенацию через +.

Примеры

SELECT "fo" || "o";

Проверка строки на соответствие шаблону

REGEXP и RLIKE являются алиасами и представляют собой короткий способ записи для вызова HyperScan::Grep. MATCH — аналогично для HyperScan::Match.

LIKE работает по следующим принципам:

  • В шаблонах используется два спецсимвола:
    • % — ноль и более любых символов;
    • _ — ровно один любой символ.
      Все остальные символы выступают как литералы, то есть обозначают сами себя.
  • В отличие от REGEXP, с шаблоном LIKE строка должна совпасть полностью. Например, для поиска подстроки в середине нужно добавить % в начале и в конце шаблона.
  • ILIKE является не чувствительной к регистру версией LIKE.
  • Если LIKE применяется к ключевой колонке сортированной таблицы и шаблон начинается не со спецсимвола, то фильтрация по префиксу опускается прямо до кластера, что в некоторых случаях позволяет не сканировать всю таблицу целиком. Для ILIKE данная оптимизация отключена.
  • Чтобы заэкранировать спецсимволы, необходимо после шаблона указать символ для экранирования с помощью ключевого слова ESCAPE '?'. Вместо ? можно использовать любой символ, кроме % и _. Например, если в качестве экранирующего символа используется знак вопроса, то выражения ?%, ?_ и ?? в шаблоне совпадут со своим вторым символом: процент, подчеркивание и знак вопроса, соответственно. По умолчанию экранирующий символ не определен.

Наиболее популярный способ использования ключевых слов LIKE и REGEXP — фильтрация таблицы в выражениях с WHERE. Однако ограничения на использование шаблонов именно в этом контексте нет, и их можно использовать в большинстве контекстов при работе со строками, наравне, например, с конкатенацией с помощью ||.

Примеры

SELECT * FROM my_table
WHERE string_column REGEXP '\\d+';
-- второй слеш нужен, так как все
-- стандартные строковые литералы в SQL
-- могут принимать С-escaped строки

SELECT
    string_column LIKE '___!_!_!_!!!!!!' ESCAPE '!'
    -- ищет строку из ровно 9 символов:
    -- 3 произвольных,
    -- затем 3 подчеркивания
    -- и 3 восклицательных знака
FROM my_table;

SELECT * FROM my_table
WHERE key LIKE 'foo%bar';
-- вероятно, физически просканирует только ключи,
-- начинающиеся на foo, и затем среди них
-- оставит только заканчивающиеся на bar

Арифметические операторы

Операторы +, -, *, /, % определены для примитивных типов данных, являющихся разновидностями чисел.

Для типа данных Decimal используется банковское округление (до ближайшего четного).

Примеры

SELECT 2 + 2;


SELECT 0.0 / 0.0;

Операторы сравнения

Операторы =, ==, !=, <>, >, < определены для:

  • Примитивных типов данных за исключением Yson и Json.
  • Кортежей и структур с одинаковым набором полей. Для структур не определен порядок, но можно проверять на (не-)равенство, а кортежи сравниваются поэлементно слева направо.

Примеры

SELECT 2 > 1;

Логические операторы

С помощью операторов AND, OR, XOR осуществляются логические операции над булевыми значениями (Bool).

Примеры

SELECT 3 > 0 AND false;

Битовые операторы

Битовые операции над числами:

  • &, |, ^ — AND, OR и XOR соответственно. Не следует путать битовые операции с аналогичными ключевыми словами. Ключевые слова AND, OR и XOR используются только для булевых значений, но не для чисел;
  • ~ — отрицание;
  • <<, >> — сдвиги влево-вправо;
  • |<<, >>| — кольцевые сдвиги влево-вправо.

Примеры

SELECT
    key << 10 AS key,
    ~value AS value
FROM my_table;

IS [NOT] NULL

Проверка на пустое значение (NULL). Так как NULL является особым значением, которое ничему не равно, то обычные операторы сравнения для этой задачи не подходят.

Примеры

SELECT key FROM my_table
WHERE value IS NOT NULL;

BETWEEN

Проверка на вхождение значения в диапазон. Эквивалентно паре условий с >= и <=, то есть границы диапазона включаются. Может использоваться с префиксом NOT для инверсии.

Примеры

SELECT * FROM my_table
WHERE key BETWEEN 10 AND 20;

IN

Проверка вхождения одного значения в набор значений. Логически эквивалентно цепочке сравнений на равенство через OR, но сложность операции не зависит от размера набора значений, так как в реализации IN используется поиск в хеш-таблице.

Внимание

В отличие от аналогичного ключевого слова в Python, в YQL IN НЕ является поиском подстроки в строке. Для поиска подстроки можно использовать функцию String::Contains или описанные выше LIKE / REGEXP.

Сразу после IN можно указать хинт COMPACT.
Если COMPACT не указан, то IN с подзапросом по возможности выполняется как соответствующий JOIN (LEFT SEMI для IN и LEFT ONLY для NOT IN).
Наличие хинта COMPACT форсирует in-memory стратегию выполнения: из содержимого правой части IN в памяти сразу строится хеш-таблица, по которой затем фильтруется левая часть.

Хинтом COMPACT следует пользоватся с осторожностью. Поскольку хеш-таблица строится в памяти, то запрос может упасть, если правая часть IN содержит много больших и/или различных элементов.

Так как в YQL есть лимит на размер запроса в байтах (порядка 1Мб), для больших списков значений нужно прикладывать их к запросу через URL и пользоваться функцией ParseFile.

Примеры

SELECT column IN (1, 2, 3)
FROM my_table;

SELECT * FROM my_table
WHERE string_column IN ("a", "b", "c");

$foo = AsList(1, 2, 3);
SELECT 1 IN $foo;

$values = (SELECT column + 1 FROM table);
SELECT * FROM my_table WHERE
   -- фильтрация по in-memory хеш-таблице на основе table
   column1 IN COMPACT $values AND
   -- с последующим LEFT ONLY JOIN с other_table
   column2 NOT IN (SELECT other_column FROM other_table);

AS

Может использоваться в следующих сценариях:

  • Присвоение короткого имени (алиаса) столбцам или таблицам в рамках запроса.
  • Указание именованных аргументов при вызове функций.
  • При явном приведении типов данных для указания целевого типа, см. CAST.

Примеры

SELECT key AS k FROM my_table;

SELECT t.key FROM my_table AS t;

SELECT
    MyFunction(key, 123 AS my_optional_arg)
FROM my_table;

CAST

Пробует привести значение к указанному простому типу. Попытка может оказаться неуспешной и вернуть NULL. Для чисел может потерять точность или старшие биты.

Для параметрического типа данных Decimal дополнительно указывается два аргумента:

  • общее число десятичных знаков (до 35, включительно);
  • число десятичных знаков после запятой (из общего числа, то есть строго не больше предыдущего аргумента).

Примеры

SELECT
    CAST("12345" AS Double),         -- 12345.0
    CAST(1.2345 AS Int8),            -- 1
    CAST(12345 AS String),           -- "12345"
    CAST("1.2345" AS Decimal(5, 2)), -- 1.23
    CAST("xyz" AS Uint64) IS NULL;   -- true, так как не удалось

CASE

Условные выражения и ветвление. Аналог if, switch и тернарных операторов в императивных языках программирования.

Так как синтаксис достаточно громоздкий, зачастую удобнее пользоваться встроенной функцией IF.

Примеры

SELECT
  CASE
    WHEN value > 0
    THEN "positive"
    ELSE "negative"
  END
FROM my_table;

SELECT
  CASE value
    WHEN 0 THEN "zero"
    WHEN 1 THEN "one"
    ELSE "not zero or one"
  END
FROM my_table;

Числовые литералы

  • Целочисленные литералы по умолчанию имеют тип Int32, если попадают в его диапазон, и в противном случае автоматически расширяются до Int64.
  • C помощью следующих суффиксов можно явно управлять типом литерала:
    • l — Int64;
    • s — Int16;
    • t — Int8.
  • Добавление суффикса u превращает тип в соответствующий беззнаковый:
    • ul — Uint64;
    • u — Uint32;
    • us — Uint16;
    • ut — Uint8.
  • Также для целочисленных литералов доступна запись в шестнадцатеричной, восьмеричной и двоичной форме с помощью префиксов 0x, 0o и 0b соответственно. Их можно произвольным образом комбинировать с описанными выше суффиксами.
  • Литералы с плавающей точкой по умолчанию имеют тип Double, но с помощью суффикса f его можно сузить до Float.

Примеры

SELECT
  CAST(123l AS Int64),
  CAST(0b01u AS Uint32),
  CAST(0xfful AS Uint64),
  CAST(0o7ut AS Uint8),
  CAST(456s AS Int16),
  CAST(1.2345f AS Float);

Комментарии

В запросах поддерживаются комментарии в стиле SQL и в стиле многострочных комментариев в языке C.

Примеры

-- comment

/* comment */

Экранирование

Если заключить литерал в квадратные скобки или backtick, то в нём можно использовать любые спецсимволы в формате C-escaping.

Примеры

SELECT [Some column] FROM [Some-Table];

SELECT `Some column` FROM `Some\tTable`;

Именованные выражения

Сложные запросы, написанные строго по стандарту SQL, могут выглядеть крайне громоздко и содержать много уровней вложенности и/или повторяющихся частей. Упростить сложный запрос можно с помощью именованных выражений вида $foo.

Функционально именованные выражения в YQL напоминают макросы, так как перед исполнением запроса их значения подставляются во все точки использования. Таким образом допустимо использование имен колонок таблиц, которые появятся в запросе лишь позже. Разрешено повторное присвоение в одно и то же именованное выражение, в таком случае происходит подстановка последнего присвоенного перед точкой использования значения.

Примечание

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

Если выражение в правой части представляет собой кортеж, то его можно автоматически распаковать, указав в левой части несколько именованных выражений через запятую. Число выражений должно совпадать с размером кортежа в этом случае.

Примеры

$multiplier = 712;
SELECT
  a * $multipler, -- $multiplier is 712
  b * $multiplier,
  (a + b) * $multiplier
FROM abc_table;
$multiplier = c;
SELECT
  a * $multipler -- $multiplier is column c
FROM abc_table;

$intermediate = (
  SELECT
    value * value AS square,
    value
  FROM my_table
);
SELECT a.square * b.value
FROM $intermediate AS a
INNER JOIN $intermediate AS b
ON a.value == b.square;

$a, $b, $c = AsTuple(1, 5u, "test"); -- распаковка кортежа
SELECT $a, $b, $c;

$x, $y = AsTuple($y, $x); -- swap значений выражений

Lambda функции

Позволяют комбинировать несколько выражений в одно вызываемое значение.

В круглых скобках перечисляются аргументы, далее после стрелки указывается тело lambda в фигурных скобках. Тело lambda состоит из цепочки выражений с присвоением именованных выражений и результата вызова после ключевого слова RETURN в последнем выражении.

Область видимости для тела lambda — сначала локальные именованные выражения, затем аргументы, затем именованные выражения, определенные выше lambda на верхнем уровне запроса.

В теле lambda можно использовать только чистые выражения — в том числе другие lambda, возможно, переданные через аргументы. Но нельзя использовать SELECT, INSERT INTO и прочие выражения верхнего уровня.

Примеры

$x = ($y) -> {
    $prefix = "x";
    RETURN $prefix || $y;
};

SELECT $x("y"); -- "xy"

Многострочные строковые литералы

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

Примеры

$text = @@some
multiline
text@@;
SELECT LENGTH($text);

Обращение к контейнерам

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

  • Struct<> и Tuple<> — используется точка. Набор ключей известен в момент компиляции запроса. В момент обращения выполняется проверка, что ключ валиден.
  • List<> и Dict<> — используются фигурные скобки. Набор ключей (для списка — индексов) известен только во время выполнения запроса. Валидность ключа не проверяется в момент запроса.

Если значение не найдено, будет возвращено пустое значение (NULL).

Примечание

Список доступных контейнеров приведён в разделе Контейнеры.

При использовании этого синтаксиса для обращения к контейнерам в столбцах таблиц обязательно нужно указывать полное имя столбца, включая имя или алиас таблицы через точку (см. первый пример ниже).

Примеры

SELECT
  t.struct.member,
  t.tuple.7,
  t.dict{"key"},
  t.list{7}
FROM my_table AS t;

SELECT
  Sample::ReturnsStruct().member;
В этой статье: