Операторы

ClickHouse преобразует операторы в соответствующие им функции на этапе разбора запроса с учётом их приоритета, старшинства и ассоциативности.

a[N] — доступ к элементу массива. Функция arrayElement(a, N). N также может быть массивом целых чисел; в этом случае элементы по всем указанным позициям возвращаются в виде массива, аналогично arrayMap(i -> a[i], N). Позиции могут быть допускающими значение NULL. Позиция NULL возвращает NULL, если тип элемента можно обернуть в Nullable; для типов элементов, которые не могут находиться внутри Nullable (например, Array, Map), возвращается значение типа элемента по умолчанию, как и для скалярного индекса NULL.

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 b — функция like(a, b).

a NOT LIKE b — функция notLike(a, b).

a ILIKE b — функция ilike(a, b).

a REGEXP b — функция match(a, b).

a ~ b — функция match(a, b) (сопоставление по регулярному выражению в стиле PostgreSQL).

a !~ b — функция notMatch(a, b) (проверяет, что a не соответствует регулярному выражению b).

a ~* b — функция matchCaseInsensitive(a, b) (регистронезависимое сопоставление по регулярному выражению).

a !~* b — функция notMatchCaseInsensitive(a, b) (проверяет, что a не соответствует регулярному выражению b без учёта регистра).

a BETWEEN b AND c – То же, что и a >= b AND a <= c.

a NOT BETWEEN b AND c – То же, что и a < b OR a > c.

Оператор <=> — это оператор равенства, безопасный для NULL, эквивалентный IS NOT DISTINCT FROM. Он работает как обычный оператор равенства (=), но при этом считает значения NULL сопоставимыми. Два значения NULL считаются равными, а сравнение NULL с любым значением, отличным от NULL, возвращает 0 (false), а не NULL.

SELECT
  'ClickHouse' <=> NULL,
  NULL <=> NULL

┌─isNotDistinc⋯use', NULL)─┬─isNotDistinc⋯NULL, NULL)─┐
│                        0 │                        1 │
└──────────────────────────┴──────────────────────────┘

• OVERLAY(string PLACING replacement FROM offset) - функция overlay(string, replacement, offset).
• OVERLAY(string PLACING replacement FROM offset FOR length) - функция overlay(string, replacement, offset, length).
• OVERLAYUTF8(string PLACING replacement FROM offset) - функция overlayUTF8(string, replacement, offset).
• OVERLAYUTF8(string PLACING replacement FROM offset FOR length) - функция overlayUTF8(string, replacement, offset, length).

См. операторы 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 │
└───┘

Помимо формы с подзапросом, описанной выше, в правой части SOME / ALL может использоваться выражение массива (литерал массива, столбец типа массива или любое выражение, возвращающее массив). Это синтаксис квантификатора массива в стиле PostgreSQL. Он распознаётся на этапе разбора и переписывается в функции для работы с массивами, поэтому вручную ничего переписывать не нужно:

SOME — это квантификатор существования (SQL-синоним ANY). Для = и <> предусмотрена специальная обработка через has / NOT has, поскольку для них есть оптимизированная реализация; в общем случае используются функции высшего порядка arrayExists / arrayAll.

Форма с массивом распознаётся для операторов сравнения =, ==, !=, <>, <=>, <, <=, >, >=, предикатов сравнения с ключевыми словами IS DISTINCT FROM и IS NOT DISTINCT FROM, а также строковых предикатов поиска LIKE, ILIKE, NOT LIKE, NOT ILIKE и REGEXP. Предикаты сравнения с ключевыми словами и строковые предикаты поиска распознаются только для формы с массивом, но не для формы с подзапросом (которая сводится к IN/NOT IN). Операторы, для которых квантификатор массива не имеет смысла, — например, сам IN — не переписываются и сохраняют своё обычное значение.

Строковые предикаты поиска работают, потому что MatchImpl (реализация, лежащая в основе LIKE / ILIKE / REGEXP) поддерживает константную строку с неконстантным шаблоном. Например, 'abc' LIKE SOME(['a%', 'b%']) переписывается в arrayExists(x -> 'abc' LIKE x, ['a%', 'b%']), а 'abc' NOT LIKE ALL(['x%', 'y%']) — в arrayAll(x -> 'abc' NOT LIKE x, ['x%', 'y%']). Это позволяет сопоставить одну строку с несколькими шаблонами; если же нужно сопоставление за один общий проход, вы по-прежнему можете использовать функцию поиска по нескольким шаблонам, такую как multiMatchAny (регулярные выражения) или multiSearchAny (подстроки).

SELECT
    3 = SOME([1, 2, 3, 4])         AS in_array,
    5 < SOME([1, 2, 6])            AS less_than_some,
    5 > ALL([1, 2, 3])             AS greater_than_all,
    'abc' LIKE SOME(['a%', 'z%'])  AS like_some;

┌─in_array─┬─less_than_some─┬─greater_than_all─┬─like_some─┐
│        1 │              1 │                1 │         1 │
└──────────┴────────────────┴──────────────────┴───────────┘

EXTRACT(part FROM date);

Извлекает части заданной даты. Например, можно получить месяц из даты или секунду из времени.

Параметр part указывает, какую часть даты нужно извлечь. Доступны следующие значения:

• NANOSECOND — Наносекунда. Возможные значения: 0–999999999.
• MICROSECOND — Микросекунда. Возможные значения: 0–999999.
• MILLISECOND — Миллисекунда. Возможные значения: 0–999.
• SECOND — Секунда. Возможные значения: 0–59.
• MINUTE — Минута. Возможные значения: 0–59.
• HOUR — Час. Возможные значения: 0–23.
• DAY — День месяца. Возможные значения: 1–31.
• WEEK — Номер недели по ISO 8601. Возможные значения: 1–53.
• MONTH — Номер месяца. Возможные значения: 1–12.
• QUARTER — Квартал. Возможные значения: 1–4.
• YEAR — Год.
• EPOCH — Unix-временная метка (секунды с 1970-01-01 00:00:00 UTC). Примечание: для DateTime64 дробная часть секунды отбрасывается.
• DOW — День недели (совместимо с PostgreSQL). 0 = воскресенье, 6 = суббота.
• DOY — День года. Возможные значения: 1–366.
• ISODOW — День недели по ISO. 1 = понедельник, 7 = воскресенье.
• ISOYEAR — Год нумерации недель по ISO 8601.
• CENTURY — Век. Например, 2024 год относится к XXI веку.
• DECADE — Десятилетие (год, делённый на 10). Например, для 2024 года десятилетие равно 202.
• MILLENNIUM — Тысячелетие. Например, 2024 год относится к III тысячелетию.
• TIMEZONE_HOUR — Знаковая часовая часть смещения от UTC для часового пояса операнда. Например, +5:30 возвращает 5, -3:30 возвращает -3.
• TIMEZONE_MINUTE — Знаковая минутная часть смещения от UTC для часового пояса операнда. Например, +5:30 возвращает 30, -3:30 возвращает -30.

Параметр part регистронезависимый.

Параметр date задаёт значение для обработки. Поддерживаются типы Date, Date32, DateTime, DateTime64 и Interval. Если date — это Interval, запрошенная часть part должна соответствовать виду интервала (например, EXTRACT(DAY FROM INTERVAL 5 DAY) допустим, а EXTRACT(HOUR FROM INTERVAL 5 DAY) отклоняется, поскольку интервалы ClickHouse поддерживают только один вид). Результат для операнда Interval имеет тип Int64.

Примеры:

SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));
SELECT EXTRACT(EPOCH FROM toDateTime('2024-01-15 12:30:45', 'UTC'));
SELECT EXTRACT(DOW FROM toDate('2024-01-15'));
SELECT EXTRACT(CENTURY FROM toDate('2024-01-01'));
SELECT EXTRACT(TIMEZONE_HOUR   FROM toDateTime('2024-01-15 12:00:00', 'Asia/Kolkata'));    -- 5
SELECT EXTRACT(TIMEZONE_MINUTE FROM toDateTime('2024-01-15 12:00:00', 'Asia/Kolkata'));    -- 30
SELECT EXTRACT(DAY   FROM INTERVAL 40 DAY);                                                -- 40
SELECT EXTRACT(MONTH FROM INTERVAL 7 MONTH);                                               -- 7

В следующем примере мы создаём таблицу и вставляем в неё значение типа DateTime.

CREATE TABLE test.Orders
(
    OrderId UInt64,
    OrderName String,
    OrderDate DateTime
) ENGINE = MergeTree
ORDER BY ();

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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

Больше примеров можно найти в tests.

Создаёт значение типа Interval, которое используется в арифметических операциях со значениями типов Date и DateTime.

Типы интервалов:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

При указании значения INTERVAL также можно использовать строковый литерал. Например, INTERVAL 1 HOUR эквивалентно INTERVAL '1 hour' или INTERVAL '1' 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 │
└─────────────────────┴────────────────────────────────────────────────────────────┘

Примеры:

SELECT toDateTime('2014-10-26 00:00:00', 'Asia/Istanbul') 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 │
└─────────────────────┴─────────────────────┴─────────────────────┘

См. также

• тип данных Interval
• функции преобразования типов toInterval

Значение Date или Date32 можно сложить со значением Time или Time64 с помощью оператора +. В результате получается DateTime или DateTime64, представляющий дату с указанным временем суток. Эта операция коммутативна.

Тип результата зависит от типов операндов:

Примеры:

SET use_legacy_to_time = 0;
SELECT toDate('2024-07-15') + toTime('14:30:25') AS dt, toTypeName(dt);

┌──────────────────dt─┬─toTypeName(dt)─┐
│ 2024-07-15 14:30:25 │ DateTime       │
└─────────────────────┴────────────────┘

SELECT toDate('2024-07-15') + toTime64('14:30:25.123456', 6) AS dt, toTypeName(dt);

┌─────────────────────────dt─┬─toTypeName(dt)─┐
│ 2024-07-15 14:30:25.123456 │ DateTime64(6)  │
└────────────────────────────┴────────────────┘

SELECT toTime64('23:59:59.999', 3) + toDate32('2024-07-15') AS dt, toTypeName(dt);

┌──────────────────────dt─┬─toTypeName(dt)─┐
│ 2024-07-15 23:59:59.999 │ DateTime64(3)  │
└─────────────────────────┴────────────────┘

Постфиксные операторы AT TIME ZONE и AT LOCAL преобразуют значение DateTime или DateTime64 в другой часовой пояс. Это синтаксический сахар для существующей функции toTimeZone:

zone может быть любым константным строковым expression, которое вычисляется в корректное имя часового пояса (например, 'America/Denver', 'UTC' или concat('America', '/', 'Denver')). Поскольку AT TIME ZONE сводится к toTimeZone, действуют те же правила для аргумента часового пояса: неконстантные выражения, такие как ссылка на столбец, требуют allow_nonconst_timezone_arguments = 1.

AT LOCAL использует текущий часовой пояс сеанса (или часовой пояс сервера по умолчанию, если часовой пояс сеанса не задан). В таблицах Distributed параметр session_timezone должен быть задан явно; если он пуст, timeZone() определяется на уровне сегмента и не может использоваться как константный аргумент toTimeZone, что приводит к исключению ILLEGAL_COLUMN.

AT TIME ZONE имеет старшинство 13 (выше *///% со старшинством 12 и выше +/- со старшинством 11), как и в PostgreSQL. Это означает, что a * ts AT TIME ZONE 'tz' связывается как a * (ts AT TIME ZONE 'tz'), а ts + interval AT TIME ZONE 'tz' — как ts + (interval AT TIME ZONE 'tz'). Чтобы применить преобразование часового пояса после арифметической операции, используйте явные круглые скобки:

-- Explicit parens required to add first, then convert timezone
SELECT (TIMESTAMP '2001-02-16 20:38:40' + INTERVAL 1 HOUR) AT TIME ZONE 'America/Denver';
-- Equivalent to:
SELECT toTimeZone(TIMESTAMP '2001-02-16 20:38:40' + INTERVAL 1 HOUR, 'America/Denver');

Примеры:

SET session_timezone = 'UTC';

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';

┌─toTimeZone(toDateTime('2001-02-16 20:38:40'), 'America/Denver')─┐
│ 2001-02-16 13:38:40                                              │
└──────────────────────────────────────────────────────────────────┘

SELECT TIMESTAMP '2001-02-16 20:38:40' AT LOCAL;

┌─toTimeZone(toDateTime('2001-02-16 20:38:40'), timeZone())─┐
│ 2001-02-16 20:38:40                                        │
└────────────────────────────────────────────────────────────┘

См. также

• toTimeZone
• timeZone

Синтаксис 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 или C — функция arrayJoin(), каждая строка будет продублирована независимо от условия a.

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 > 2 > 3 вернёт 0.

Для повышения эффективности функции and и or принимают произвольное число аргументов. Соответствующие цепочки операторов AND и OR преобразуются в один вызов этих функций.

ClickHouse поддерживает операторы IS NULL и IS NOT NULL.

• Для значений типа Nullable оператор 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 │
└──────────────┘

• Для значений типа Nullable оператор IS NOT NULL возвращает:
  • 0, если значение — NULL.
  • 1 в противном случае.
• Для всех остальных значений оператор IS NOT NULL всегда возвращает 1.

SELECT * FROM t_null WHERE y IS NOT NULL

┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

Можно повысить производительность, включив настройку optimize_functions_to_subcolumns. При optimize_functions_to_subcolumns = 1 функция читает только подстолбец null вместо чтения и обработки всех данных столбца. Запрос SELECT n IS NOT NULL FROM table преобразуется в SELECT NOT n.null FROM TABLE.

ClickHouse поддерживает операторы IS TRUE, IS FALSE, IS UNKNOWN, IS NOT TRUE, IS NOT FALSE и IS NOT UNKNOWN. Они используются с выражениями Bool и Nullable(Bool).

• expr IS TRUE возвращает 1 только в том случае, если expr равно true.
• expr IS FALSE возвращает 1 только в том случае, если expr равно false.
• expr IS UNKNOWN возвращает 1 только в том случае, если expr равно NULL.
• expr IS NOT TRUE возвращает 1, если expr равно false или NULL.
• expr IS NOT FALSE возвращает 1, если expr равно true или NULL.
• expr IS NOT UNKNOWN возвращает 1, если expr не равно NULL.

Для булевых выражений IS UNKNOWN эквивалентно IS NULL, а IS NOT UNKNOWN — IS NOT NULL.

CREATE TABLE t_bool (x Nullable(Bool)) ENGINE = Memory;
INSERT INTO t_bool VALUES (true), (false), (NULL);

SELECT
    x,
    x IS TRUE,
    x IS FALSE,
    x IS UNKNOWN,
    x IS NOT TRUE,
    x IS NOT FALSE,
    x IS NOT UNKNOWN
FROM t_bool;