Важно: Данный раздел актуален для Платформы данных в Публичном облаке и On-Premise.
Все операторы преобразуются в соответствующие функции на этапе парсинга запроса, с учётом их приоритетов и ассоциативности. Далее будут перечислены группы операторов в порядке их приоритета (чем выше, тем раньше оператор связывается со своими аргументами).
a[N] - доступ к элементу массива, функция arrayElement(a, N).
a.N - доступ к элементу кортежа, функция tupleElement(a, N).
-a - функция negate(a).
Для чисел в кортеже также может быть использована tupleNegate.
a * b - функция multiply(a, b)
Для умножения кортежа на число также может быть использована tupleMultiplyByNumber, для скалярного произведения: dotProduct.
a / b - функция divide(a, b)
Для деления кортежа на число также может быть использована tupleDivideByNumber.
a % b - функция modulo(a, b)
a + b - функция plus(a, b)
Для сложения кортежей также может быть использована tuplePlus.
a - b - функция minus(a, b)
Для вычитания кортежей также может быть использована tupleMinus.
a = b - функция equals(a, b)
a == b - функция equals(a, b)
a != b - функция notEquals(a, b)
a <> b - функция notEquals(a, b)
a <= b - функция lessOrEquals(a, b)
a >= b - функция greaterOrEquals(a, b)
a < b - функция less(a, b)
a > b - функция greater(a, b)
a LIKE s - функция like(a, b)
a NOT LIKE s - функция notLike(a, b)
a ILIKE s – функция ilike(a, b)
a BETWEEN b AND c - равнозначно a >= b AND a <= c
a NOT BETWEEN b AND c - равнозначно a < b OR a > c
Смотрите операторы IN и оператор EXISTS.
a IN ... - функция in(a, b)
a NOT IN ... - функция notIn(a, b)
a GLOBAL IN ... - функция globalIn(a, b)
a GLOBAL NOT IN ... - функция globalNotIn(a, b)
a = ANY (subquery) – функция in(a, subquery).
a != ANY (subquery) – равнозначно a NOT IN (SELECT singleValueOrNull(*) FROM subquery).
a = ALL (subquery) – равнозначно a IN (SELECT singleValueOrNull(*) FROM subquery).
a != ALL (subquery) – функция notIn(a, subquery).
Примеры:
Запрос с ALL:
SELECT number AS a FROM numbers(10) WHERE a > ALL (SELECT number FROM numbers(3, 3));Результат:
┌─a─┐
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘Запрос с ANY:
SELECT number AS a FROM numbers(10) WHERE a > ANY (SELECT number FROM numbers(3, 3));Результат:
┌─a─┐
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘EXTRACT(part FROM date);Позволяет извлечь отдельные части из переданной даты. Например, можно получить месяц из даты, или минуты из времени.
В параметре part указывается, какой фрагмент даты нужно получить. Доступные значения:
DAY — День. Возможные значения: 1–31.MONTH — Номер месяца. Возможные значения: 1–12.YEAR — Год.SECOND — Секунда. Возможные значения: 0–59.MINUTE — Минута. Возможные значения: 0–59.HOUR — Час. Возможные значения: 0–23.Эти значения могут быть указаны также в нижнем регистре (day, month).
В параметре date указывается исходная дата. Поддерживаются типы Date и DateTime.
Примеры:
SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));В следующем примере создадим таблицу и добавим в неё значение с типом DateTime.
CREATE TABLE test.Orders
(
OrderId UInt64,
OrderName String,
OrderDate DateTime
)
ENGINE = Log;
INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));
SELECT
toYear(OrderDate) AS OrderYear,
toMonth(OrderDate) AS OrderMonth,
toDayOfMonth(OrderDate) AS OrderDay,
toHour(OrderDate) AS OrderHour,
toMinute(OrderDate) AS OrderMinute,
toSecond(OrderDate) AS OrderSecond
FROM test.Orders;
┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour──┬─OrderMinute─┬─OrderSecond─┐
│ 2008 │ 10 │ 11 │ 13 │ 23 │ 44 │
└────────────┴─────────────┴───────────┴────────────┴──────────────┴──────────────┘Создаёт значение типа Interval которое должно использоваться в арифметических операциях со значениями типов Date и DateTime.
Типы интервалов:
SECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARВ качестве значения оператора INTERVAL вы можете также использовать строковый литерал. Например, выражение INTERVAL 1 HOUR идентично выражению INTERVAL '1 hour' или INTERVAL '1' hour.
ВНИМАНИЕ:
Интервалы различных типов нельзя объединять. Нельзя использовать выражения вида `INTERVAL 4 DAY 1 HOUR`. Вместо этого интервалы можно выразить в единицах меньших или равных наименьшей единице интервала, Например, `INTERVAL 25 HOUR`. Также можно выполнять последовательные операции как показано в примере ниже.
Примеры:
SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR;
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:09:50 │ 2020-11-08 01:09:50 │
└───────────────────────┴─────────────────────────────────────────────────────────────┘
SELECT now() AS current_date_time, current_date_time + INTERVAL '4 day' + INTERVAL '3 hour';
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:12:10 │ 2020-11-08 01:12:10 │
└───────────────────────┴─────────────────────────────────────────────────────────────┘
SELECT now() AS current_date_time, current_date_time + INTERVAL '4' day + INTERVAL '3' hour;
┌───current_date_time─┬─plus(plus(now(), toIntervalDay('4')), toIntervalHour('3'))─┐
│ 2020-11-03 22:33:19 │ 2020-11-08 01:33:19 │
└───────────────────────┴─────────────────────────────────────────────────────────────────┘Вы можете изменить дату, не используя синтаксис INTERVAL, а просто добавив или отняв секунды, минуты и часы. Например, чтобы передвинуть дату на один день вперед, можно прибавить к ней значение 60*60*24.
Примечание:
Синтаксис `INTERVAL` или функция `addDays` предпочтительнее для работы с датами. Сложение с числом (например, синтаксис `now() + ...`) не учитывает региональные настройки времени, например, переход на летнее время.
Пример:
SELECT toDateTime('2014-10-26 00:00:00', 'Europe/Moscow') AS time, time + 60 * 60 * 24 AS time_plus_24_hours, time + toIntervalDay(1) AS time_plus_1_day;
┌─────────────────time─┬──time_plus_24_hours──┬─────time_plus_1_day─┐
│ 2014-10-26 00:00:00 │ 2014-10-26 23:00:00 │ 2014-10-27 00:00:00 │
└───────────────────────┴───────────────────────┴───────────────────────┘Смотрите также:
Синтаксис SELECT a AND b — вычисляет логическую конъюнкцию между a и b функцией and.
Синтаксис SELECT a OR b — вычисляет логическую дизъюнкцию между a и b функцией or.
Синтаксис SELECT NOT a — вычисляет логическое отрицание a функцией not.
a ? b : c - функция if(a, b, c)
Примечание:
Условный оператор сначала вычисляет значения b и c, затем проверяет выполнение условия a, и только после этого возвращает соответствующее значение. Если в качестве b или с выступает функция arrayJoin(), то размножение каждой строки произойдет вне зависимости от условия а.
CASE [x] WHEN a THEN b [WHEN ... THEN ...] [ELSE c]ENDВ случае указания x - функция transform(x, [a, ...], [b, ...], c). Иначе — multiIf(a, b, ..., c). При отсутствии секции ELSE c, значением по умолчанию будет NULL.
Примечание:
Функция `transform` не умеет работать с `NULL`.
s1 || s2 - функция concat(s1, s2)
x -> expr - функция lambda(x, expr)
Следующие операторы не имеют приоритета, так как представляют собой скобки:
[x1, ...] - функция array(x1, ...)
(x1, x2, ...) - функция tuple(x2, x2, ...)
Все бинарные операторы имеют левую ассоциативность. Например, 1 + 2 + 3 преобразуется в plus(plus(1, 2), 3). Иногда это работает не так, как ожидается. Например, SELECT 4 > 3 > 2 выдаст 0.
Для эффективности, реализованы функции and и or, принимающие произвольное количество аргументов. Соответствующие цепочки операторов AND и OR, преобразуются в один вызов этих функций.
NULLRT.WideStore поддерживает операторы IS NULL и IS NOT NULL.
IS NULL возвращает:1, если значение — NULL.0 в обратном случае.IS NULL всегда возвращает 0.Оператор можно оптимизировать, если включить настройку optimize_functions_to_subcolumns. При optimize_functions_to_subcolumns = 1 читается только подстолбец null вместо чтения и обработки данных всего столбца. Запрос SELECT n IS NULL FROM table преобразуется к запросу SELECT n.null FROM TABLE.
SELECT x+100 FROM t_null WHERE y IS NULL
┌─plus(x, 100)─┐
│ 101 │
└───────────────┘IS NOT NULL возвращает:0, если значение — NULL.1, в обратном случае.IS NOT NULL всегда возвращает 1.Оператор можно оптимизировать, если включить настройку optimize_functions_to_subcolumns. При optimize_functions_to_subcolumns = 1 читается только подстолбец null вместо чтения и обработки данных всего столбца. Запрос SELECT n IS NOT NULL FROM table преобразуется к запросу SELECT NOT n.null FROM TABLE.
SELECT * FROM t_null WHERE y IS NOT NULL
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴────┘Оператор EXISTS проверяет, сколько строк содержит результат выполнения подзапроса. Если результат пустой, то оператор возвращает 0. В остальных случаях оператор возвращает 1.
EXISTS может быть использован в секции WHERE.
ПРЕДУПРЕЖДЕНИЕ:
Ссылки на таблицы или столбцы основного запроса не поддерживаются в подзапросе.
Синтаксис:
WHERE EXISTS(subquery)Пример:
Запрос с подзапросом, возвращающим несколько строк:
SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 8);Результат:
┌─count()─┐
│ 10 │
└──────────┘Запрос с подзапросом, возвращающим пустой результат:
SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 11);Результат:
┌─count()─┐
│ 0 │
└──────────┘Операторы IN, NOT IN, GLOBAL IN, GLOBAL NOT IN рассматриваются отдельно, так как их функциональность достаточно богатая.
В качестве левой части оператора, может присутствовать как один столбец, так и кортеж.
Примеры:
SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...Если слева стоит один столбец, входящий в индекс, а справа - множество констант, то при выполнении запроса, система воспользуется индексом.
Не перечисляйте слишком большое количество значений (миллионы) явно. Если множество большое - лучше загрузить его во временную таблицу (например, смотрите раздел Внешние данные для обработки запроса), и затем воспользоваться подзапросом.
В качестве правой части оператора может быть множество константных выражений, множество кортежей с константными выражениями (показано в примерах выше), а также имя таблицы или подзапрос SELECT в скобках.
Если типы данных в левой и правой частях подзапроса IN различаются, RT.WideStore преобразует значение в левой части к типу данных из правой части. Преобразование выполняется по аналогии с функцией accurateCastOrNull, т.е. тип данных становится Nullable, а если преобразование не может быть выполнено, возвращается значение NULL.
Пример:
Запрос:
SELECT '1' IN (SELECT 1);Результат:
┌─in('1', _subquery49)─┐
│ 1 │
└────────────────────────┘Если в качестве правой части оператора указано имя таблицы (например, UserID IN users), то это эквивалентно подзапросу UserID IN (SELECT * FROM users). Это используется при работе с внешними данными, отправляемыми вместе с запросом. Например, вместе с запросом может быть отправлено множество идентификаторов посетителей, загруженное во временную таблицу users, по которому следует выполнить фильтрацию.
Если в качестве правой части оператора, указано имя таблицы, имеющей движок Set (подготовленное множество, постоянно находящееся в оперативке), то множество не будет создаваться заново при каждом запросе.
В подзапросе может быть указано более одного столбца для фильтрации кортежей. Пример:
SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...Типы столбцов слева и справа оператора IN должны совпадать.
Оператор IN и подзапрос могут встречаться в любой части запроса, в том числе в агрегатных и лямбда-функциях. Пример:
SELECT
EventDate,
avg(UserID IN
(
SELECT UserID
FROM test.hits
WHERE EventDate = toDate('2014-03-17')
)) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC
┌──EventDate─┬────ratio─┐
│ 2014-03-17 │ 1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└─────────────┴───────────┘за каждый день после 17 марта считаем долю хитов, сделанных посетителями, которые заходили на сайт 17 марта. Подзапрос в секции IN на одном сервере всегда выполняется только один раз. Зависимых подзапросов не существует.
При обработке запроса оператор IN будет считать, что результат операции с NULL всегда равен 0, независимо от того, находится NULL в правой или левой части оператора. Значения NULL не входят ни в какое множество, не соответствуют друг другу и не могут сравниваться, если transform_null_in = 0.
Рассмотрим для примера таблицу t_null:
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 3 │
└───┴───────┘При выполнении запроса SELECT x FROM t_null WHERE y IN (NULL,3) получим следующий результат:
┌─x─┐
│ 2 │
└───┘Видно, что строка, в которой y = NULL, выброшена из результатов запроса. Это произошло потому, что RT.WideStore не может решить входит ли NULL в множество (NULL,3), возвращает результат операции 0, а SELECT выбрасывает эту строку из финальной выдачи.
SELECT y IN (NULL, 3)FROM t_null
┌─in(y, tuple(NULL, 3))─┐
│ 0 │
│ 1 │
└─────────────────────────┘Существует два варианта IN-ов с подзапросами (аналогично для JOIN-ов): обычный IN / JOIN и GLOBAL IN / GLOBAL JOIN. Они отличаются способом выполнения при распределённой обработке запроса.
ВНИМАНИЕ:
Помните, что алгоритмы, описанные ниже, могут работать иначе в зависимости от настройки
distributed_product_mode.
При использовании обычного IN-а, запрос отправляется на удалённые серверы, и на каждом из них выполняются подзапросы в секциях IN / JOIN.
При использовании GLOBAL IN / GLOBAL JOIN-а, сначала выполняются все подзапросы для GLOBAL IN / GLOBAL JOIN-ов, и результаты складываются во временные таблицы. Затем эти временные таблицы передаются на каждый удалённый сервер, и на них выполняются запросы, с использованием этих переданных временных данных.
Если запрос не распределённый, используйте обычный IN / JOIN.
Следует быть внимательным при использовании подзапросов в секции IN / JOIN в случае распределённой обработки запроса.
Рассмотрим это на примерах. Пусть на каждом сервере кластера есть обычная таблица local_table. Пусть также есть таблица distributed_table типа Distributed, которая смотрит на все серверы кластера.
При запросе к распределённой таблице distributed_table, запрос будет отправлен на все удалённые серверы, и на них будет выполнен с использованием таблицы local_table.
Например, запрос
SELECT uniq(UserID) FROM distributed_tableбудет отправлен на все удалённые серверы в виде
SELECT uniq(UserID) FROM local_table, выполнен параллельно на каждом из них до стадии, позволяющей объединить промежуточные результаты; затем промежуточные результаты вернутся на сервер-инициатор запроса, будут на нём объединены, и финальный результат будет отправлен клиенту.
Теперь рассмотрим запрос с IN-ом:
SELECT uniq(UserID) FROM distributed_table
WHERE CounterID = 101500
AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
Этот запрос будет отправлен на все удалённые серверы в виде
SELECT uniq(UserID)
FROM local_table
WHERE CounterID = 101500 AND UserID IN
(
SELECT UserID
FROM local_table
WHERE CounterID = 34
)То есть, множество в секции IN будет собрано на каждом сервере независимо, только по тем данным, которые есть локально на каждом из серверов.
Это будет работать правильно и оптимально, если вы предусмотрели такой случай, и раскладываете данные по серверам кластера таким образом, чтобы данные одного UserID-а лежали только на одном сервере. В таком случае все необходимые данные будут присутствовать на каждом сервере локально. В противном случае результат будет посчитан неточно. Назовём этот вариант запроса «локальный IN».
Чтобы исправить работу запроса, когда данные размазаны по серверам кластера произвольным образом, можно было бы указать distributed_table внутри подзапроса. Запрос будет выглядеть так:
SELECT uniq(UserID)
FROM distributed_table
WHERE CounterID = 101500 AND UserID IN
(
SELECT UserID FROM distributed_table WHERE CounterID = 34
)Этот запрос будет отправлен на все удалённые серверы в виде
SELECT uniq(UserID)
FROM local_table
WHERE CounterID = 101500 AND UserID IN
(
SELECT UserID FROM distributed_table WHERE CounterID = 34
)На каждом удалённом сервере начнёт выполняться подзапрос. Так как в подзапросе используется распределённая таблица, то подзапрос будет, на каждом удалённом сервере, снова отправлен на каждый удалённый сервер, в виде
SELECT UserID FROM local_table WHERE CounterID = 34Например, если у вас кластер из 100 серверов, то выполнение всего запроса потребует 10 000 элементарных запросов, что, как правило, является неприемлемым.
В таких случаях всегда следует использовать GLOBAL IN вместо IN. Рассмотрим его работу для запроса
SELECT uniq(UserID)
FROM distributed_table
WHERE CounterID = 101500 AND UserID GLOBAL IN
(
SELECT UserID FROM distributed_table WHERE CounterID = 34
)На сервере-инициаторе запроса будет выполнен подзапрос
SELECT UserID FROM distributed_table WHERE CounterID = 34, и результат будет сложен во временную таблицу в оперативке. Затем запрос будет отправлен на каждый удалённый сервер в виде
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1, и вместе с запросом, на каждый удалённый сервер будет отправлена временная таблица _data1 (имя временной таблицы - implementation defined).
Это гораздо более оптимально, чем при использовании обычного IN. Но при этом, следует помнить о нескольких вещах:
В секции GLOBAL IN также имеет смысл указывать локальную таблицу - в случае, если эта локальная таблица есть только на сервере-инициаторе запроса, и вы хотите воспользоваться данными из неё на удалённых серверах.
Когда настройка max_parallel_replicas больше чем 1, распределенные запросы преобразуются. Например, следующий запрос:
SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3преобразуется на каждом сервере в
SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=Mгде M значение между 1 и 3 зависящее от того на какой реплике выполняется локальный запрос. Эти параметры влияют на каждую таблицу семейства MergeTree в запросе и имеют тот же эффект, что и применение SAMPLE 1/3 OFFSET (M-1)/3 для каждой таблицы.
Поэтому применение настройки max_parallel_replicas даст корректные результаты если обе таблицы имеют одинаковую схему репликации и семплированы по UserID выражению от UserID. В частности, если local_table_2 не имеет семплирующего ключа, будут получены неверные результаты. Тоже правило применяется для JOIN.
Один из способов избежать этого, если local_table_2 не удовлетворяет требованиям, использовать GLOBAL IN или GLOBAL JOIN.