Выражения
- Конкатенация строк
- Проверка строки на соответствие шаблону
- Арифметические операторы
- Операторы сравнения
- Логические операторы
- Битовые операторы
- IS [NOT] NULL
- BETWEEN
- IN
- AS
- CAST
- CASE
- Числовые литералы
- Комментарии
- Экранирование
- Именованные выражения
- Lambda функции
- Многострочные строковые литералы
- Обращение к контейнерам
Конкатенация строк
Выполняется через бинарный оператор ||
.
Как и у других бинарных операторов, если в данных с одной из сторон оказался 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;