Operadores - ClickHouse Documentation

ClickHouse transforma los operadores en sus funciones correspondientes durante la fase de análisis sintáctico de la consulta, según su prioridad, precedencia y asociatividad.

Operadores de acceso

a[N] – Acceso a un elemento de un array. La función arrayElement(a, N). a.N – Acceso a un elemento de una tupla. La función tupleElement(a, N).

Operador de negación numérica

-a – la función negate (a). Para la negación de tupla: tupleNegate.

Operadores de multiplicación y división

a * b – La función multiply (a, b). Para multiplicar una tupla por un número: tupleMultiplyByNumber; para el producto escalar: dotProduct. a / b – La función divide(a, b). Para dividir una tupla por un número: tupleDivideByNumber. a % b – La función modulo(a, b).

Operadores de suma y resta

a + b – La función plus(a, b). Para la suma de tuplas: tuplePlus. a - b – La función minus(a, b). Para la resta de tuplas: tupleMinus.

Operadores de comparación

función equals

a = b – La función equals(a, b). a == b – La función equals(a, b).

función notEquals

a != b: la función notEquals(a, b). a <> b: la función notEquals(a, b).

función lessOrEquals

a <= b: la función lessOrEquals(a, b).

función greaterOrEquals

a >= b – La función greaterOrEquals(a, b).

función less

a < b – La función less(a, b).

función greater

a > b – La función greater(a, b).

función like

a LIKE b – La función like(a, b).

función notLike

a NOT LIKE b – La función notLike(a, b).

función ilike

a ILIKE b – La función ilike(a, b).

Función BETWEEN

a BETWEEN b AND c – Equivale a a >= b AND a <= c. a NOT BETWEEN b AND c – Equivale a a < b OR a > c.

operador `is not distinct from` (`<=>`)

A partir de la versión 25.10, puedes usar <=> igual que cualquier otro operador. Antes de la versión 25.10, solo se podía usar en expresiones JOIN, por ejemplo:

CREATE TABLE a (x String) ENGINE = Memory;
INSERT INTO a VALUES ('ClickHouse');

SELECT * FROM a AS a1 JOIN a AS a2 ON a1.x <=> a2.x;

┌─x──────────┬─a2.x───────┐
│ ClickHouse │ ClickHouse │
└────────────┴────────────┘

El operador <=> es el operador de igualdad seguro para NULL, equivalente a IS NOT DISTINCT FROM. Funciona como el operador de igualdad normal (=), pero trata los valores NULL como comparables. Dos valores NULL se consideran iguales, y un NULL comparado con cualquier valor distinto de NULL devuelve 0 (falso) en lugar de NULL.

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

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

Operadores para trabajar con cadenas

OVERLAY

OVERLAY(string PLACING replacement FROM offset) - La función overlay(string, replacement, offset).
OVERLAY(string PLACING replacement FROM offset FOR length) - La función overlay(string, replacement, offset, length).
OVERLAYUTF8(string PLACING replacement FROM offset) - La función overlayUTF8(string, replacement, offset).
OVERLAYUTF8(string PLACING replacement FROM offset FOR length) - La función overlayUTF8(string, replacement, offset, length).

Operadores para trabajar con conjuntos de datos

Consulte los operadores IN y el operador EXISTS.

función in

a IN ... – La función in(a, b).

función notIn

a NOT IN ... – La función notIn(a, b).

función globalIn

a GLOBAL IN ... – La función globalIn(a, b).

función globalNotIn

a GLOBAL NOT IN ... – La función globalNotIn(a, b).

función in subconsulta

a = ANY (subquery) – La función in(a, subquery).

notIn subconsulta function

a != ANY (subquery) – Igual que a NOT IN (SELECT singleValueOrNull(*) FROM subquery).

función in subconsulta

a = ALL (subquery) – Lo mismo que a IN (SELECT singleValueOrNull(*) FROM subquery).

función notIn en subconsulta

a != ALL (subquery) – La función notIn(a, subquery). Ejemplos Consulta con ALL:

Query

SELECT number AS a FROM numbers(10) WHERE a > ALL (SELECT number FROM numbers(3, 3));

Response

┌─a─┐
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘

Consulta con ANY:

Query

SELECT number AS a FROM numbers(10) WHERE a > ANY (SELECT number FROM numbers(3, 3));

Response

┌─a─┐
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘

`SOME` / `ALL` en arrays

Además de la forma de subconsulta descrita anteriormente, el lado derecho de SOME / ALL puede ser una expresión de array (un literal de array, una columna de tipo array o cualquier expresión que devuelva un array). Esta es la sintaxis de cuantificador de array al estilo de PostgreSQL. Se reconoce durante el análisis y se reescribe como funciones de array, por lo que no hace falta ninguna reescritura manual:

Sintaxis	Reescrito como
`expr = SOME(arr)`	`has(arr, expr)`
`expr <> ALL(arr)`	`NOT has(arr, expr)`
`expr OP SOME(arr)` (cualquier otro operador de comparación)	`arrayExists(x -> expr OP x, arr)`
`expr OP ALL(arr)` (cualquier otro operador de comparación)	`arrayAll(x -> expr OP x, arr)`

SOME es el cuantificador existencial (el sinónimo de SQL de ANY). = y <> se tratan de forma especial como has / NOT has porque tienen una implementación optimizada; en la forma general se usan las funciones de orden superior arrayExists / arrayAll. La forma de array se reconoce solo para los operadores de comparación =, ==, !=, <>, <=>, <, <=, >, y >=. Otros operadores que aceptan un array a la derecha —por ejemplo, LIKE, ILIKE, NOT LIKE, REGEXP e IN— no se reescriben como cuantificadores de array y conservan su significado habitual. Por ejemplo, 'abc' LIKE SOME(['a%', 'b%']) no es la sintaxis del cuantificador de array; use arrayExists / arrayAll directamente en esos casos.

ANY no es compatible con la forma de arraySolo SOME y ALL aceptan un array en el lado derecho. ANY se excluye porque any también es una función de agregación, por lo que una expresión con la forma expr = any(x) conserva el significado de una llamada de función. Use SOME para el cuantificador de array.

Query

SELECT
= SOME([1, 2, 3, 4]) AS in_array,
< SOME([1, 2, 6])    AS less_than_some,
> ALL([1, 2, 3])     AS greater_than_all;

Response

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

El manejo de NULL difiere de la forma con subconsultaComo la forma de array se reescribe en el analizador sintáctico (donde la configuración de la consulta, como transform_null_in, no está disponible, y una columna array por fila no puede usar la ruta IN null-safe del analyzer), utiliza la semántica de dos valores de has (para = / <>) y arrayExists / arrayAll (que reducen a 0 un resultado desconocido en una comparación con NULL). Esto puede diferir de la forma con subconsulta, cuyo manejo de NULL se implementa mediante IN / NOT IN y depende de transform_null_in:

SELECT NULL = SOME([NULL]);   -- has([NULL], NULL)                  -> 1
SELECT NULL <> ALL([NULL]);   -- NOT has([NULL], NULL)              -> 0
SELECT NULL < SOME([1]);      -- arrayExists(x -> NULL < x, [1])    -> 0
SELECT NULL > ALL([1]);       -- arrayAll(x -> NULL > x, [1])       -> 0

Operadores para trabajar con fechas y horas

EXTRACT

EXTRACT(part FROM date);

Extrae componentes de una fecha determinada. Por ejemplo, puedes obtener el mes de una fecha determinada o el segundo de una hora. El parámetro part especifica qué componente de la fecha se debe obtener. Se admiten los siguientes valores:

NANOSECOND — El nanosegundo. Posibles valores: 0–999999999.
MICROSECOND — El microsegundo. Posibles valores: 0–999999.
MILLISECOND — El milisegundo. Posibles valores: 0–999.
SECOND — El segundo. Posibles valores: 0–59.
MINUTE — El minuto. Posibles valores: 0–59.
HOUR — La hora. Posibles valores: 0–23.
DAY — El día del mes. Posibles valores: 1–31.
WEEK — El número de semana ISO 8601. Posibles valores: 1–53.
MONTH — El número del mes. Posibles valores: 1–12.
QUARTER — El trimestre. Posibles valores: 1–4.
YEAR — El año.
EPOCH — La marca de tiempo Unix (segundos desde 1970-01-01 00:00:00 UTC). Nota: para DateTime64, la parte de subsegundos se trunca.
DOW — El día de la semana (compatible con PostgreSQL). 0 = domingo, 6 = sábado.
DOY — El día del año. Posibles valores: 1–366.
ISODOW — El día de la semana según ISO. 1 = lunes, 7 = domingo.
ISOYEAR — El año de numeración de semanas ISO 8601.
CENTURY — El siglo. Por ejemplo, el año 2024 está en el siglo XXI.
DECADE — La década (año dividido entre 10). Por ejemplo, el año 2024 corresponde a la década 202.
MILLENNIUM — El milenio. Por ejemplo, el año 2024 está en el 3.er milenio.
TIMEZONE_HOUR — La parte horaria con signo del desplazamiento UTC de la zona horaria del operando. Por ejemplo, +5:30 devuelve 5, -3:30 devuelve -3.
TIMEZONE_MINUTE — La parte en minutos con signo del desplazamiento UTC de la zona horaria del operando. Por ejemplo, +5:30 devuelve 30, -3:30 devuelve -30.

El parámetro part no distingue entre mayúsculas y minúsculas. El parámetro date especifica el valor que se debe procesar. Se admiten los tipos Date, Date32, DateTime, DateTime64 e Interval. Cuando date es un Interval, el part solicitado debe coincidir con el tipo almacenado del intervalo (por ejemplo, se permite EXTRACT(DAY FROM INTERVAL 5 DAY); EXTRACT(HOUR FROM INTERVAL 5 DAY) se rechaza, porque los intervalos de ClickHouse son de un solo tipo). El resultado para un operando Interval es Int64. Ejemplos:

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

En el siguiente ejemplo creamos una tabla e insertamos en ella un valor de tipo 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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

Puedes ver más ejemplos en tests.

INTERVAL

Crea un valor de tipo Interval que debe usarse en operaciones aritméticas con valores de tipo Date y DateTime. Tipos de intervalos:

SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

También puedes usar una cadena literal al definir el valor de INTERVAL. Por ejemplo, INTERVAL 1 HOUR es idéntico a INTERVAL '1 hour' o INTERVAL '1' hour.

Los intervalos de distintos tipos no pueden combinarse. No puedes usar expresiones como INTERVAL 4 DAY 1 HOUR. Especifica los intervalos en unidades menores o iguales que la unidad mínima del intervalo; por ejemplo, INTERVAL 25 HOUR. Puedes usar operaciones consecutivas, como en el ejemplo siguiente.

Ejemplos:

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

Siempre se prefieren la sintaxis INTERVAL o la función addDays. La suma o resta simple (con sintaxis como now() + ...) no tiene en cuenta la configuración horaria. Por ejemplo, el horario de verano.

Ejemplos:

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

Véase también

tipo de dato Interval
funciones de conversión de tipos toInterval

Suma de fecha y hora

Se puede sumar un valor Date o Date32 a un valor Time o Time64 mediante el operador +. El resultado es un DateTime o DateTime64 que representa la fecha a la hora del día especificada. La operación es conmutativa. El tipo de resultado depende de los tipos de los operandos:

Operando izquierdo	Operando derecho	Tipo de resultado
`Date`	`Time`	`DateTime`
`Date`	`Time64(s)`	`DateTime64(s)`
`Date32`	`Time`	`DateTime64(0)`
`Date32`	`Time64(s)`	`DateTime64(s)`

El resultado utiliza la zona horaria de la sesión (o la zona horaria predeterminada del servidor si no se ha configurado ninguna para la sesión). La configuración date_time_overflow_behavior controla qué sucede cuando el resultado queda fuera del rango representable.

Ejemplos:

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

Operador lógico AND

Sintaxis SELECT a AND b — calcula la conjunción lógica entre a y b con la función and.

Operador lógico OR

Sintaxis SELECT a OR b — calcula la disyunción lógica entre a y b mediante la función or.

Operador de negación lógica

Sintaxis SELECT NOT a — calcula la negación lógica de a mediante la función not.

Operador condicional

a ? b : c – La función if(a, b, c). Nota: El operador condicional calcula los valores de b y c, luego comprueba si se cumple la condición a y después devuelve el valor correspondiente. Si b o C es una función arrayJoin(), cada fila se replicará independientemente de la condición «a».

Expresión condicional

CASE [x]
    WHEN a THEN b
    [WHEN ... THEN ...]
    [ELSE c]
END

Si se especifica x, se usa la función transform(x, [a, ...], [b, ...], c). De lo contrario, se usa multiIf(a, b, ..., c). Si no hay una cláusula ELSE c en la expresión, el valor predeterminado es NULL. La función transform no funciona con NULL.

Operador de concatenación

s1 || s2 – La función concat(s1, s2).

Operador de creación de lambda

x -> expr – La función lambda(x, expr). Los siguientes operadores no tienen prioridad, ya que son corchetes:

Operador de creación de Array

[x1, ...] – La función array(x1, ...).

Operador de creación de tupla

(x1, x2, ...) – La función tuple(x2, x2, ...).

Asociatividad

Todos los operadores binarios tienen asociatividad por la izquierda. Por ejemplo, 1 + 2 + 3 se transforma en plus(plus(1, 2), 3). A veces esto no funciona como cabría esperar. Por ejemplo, SELECT 4 > 2 > 3 dará como resultado 0. Por motivos de eficiencia, las funciones and y or aceptan cualquier número de argumentos. Las cadenas correspondientes de operadores AND y OR se transforman en una única llamada a estas funciones.

Comprobación de `NULL`

ClickHouse admite los operadores IS NULL y IS NOT NULL.

IS NULL

Para los valores de tipo Nullable, el operador IS NULL devuelve:
- 1 si el valor es NULL.
- 0 en caso contrario.
Para cualquier otro valor, el operador IS NULL siempre devuelve 0.

Puede optimizarse habilitando la configuración optimize_functions_to_subcolumns. Con optimize_functions_to_subcolumns = 1, la función lee solo la subcolumna null en lugar de leer y procesar todos los datos de la columna. La consulta SELECT n IS NULL FROM table se transforma en SELECT n.null FROM TABLE.

SELECT x+100 FROM t_null WHERE y IS NULL

┌─plus(x, 100)─┐
│          101 │
└──────────────┘

IS NOT NULL

Para los valores de tipo Nullable, el operador IS NOT NULL devuelve:
- 0 si el valor es NULL.
- 1 en caso contrario.
Para otros valores, el operador IS NOT NULL siempre devuelve 1.

SELECT * FROM t_null WHERE y IS NOT NULL

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

Se puede optimizar habilitando el ajuste optimize_functions_to_subcolumns. Con optimize_functions_to_subcolumns = 1, la función lee solo la subcolumna null en lugar de leer y procesar los datos de toda la columna. La consulta SELECT n IS NOT NULL FROM table se convierte en SELECT NOT n.null FROM TABLE.

Verificación de valores booleanos

ClickHouse admite los operadores IS TRUE, IS FALSE, IS UNKNOWN, IS NOT TRUE, IS NOT FALSE e IS NOT UNKNOWN. Se utilizan con expresiones Bool y Nullable(Bool).

expr IS TRUE devuelve 1 solo si expr es true.
expr IS FALSE devuelve 1 solo si expr es false.
expr IS UNKNOWN devuelve 1 solo si expr es NULL.
expr IS NOT TRUE devuelve 1 si expr es false o NULL.
expr IS NOT FALSE devuelve 1 si expr es true o NULL.
expr IS NOT UNKNOWN devuelve 1 si expr no es NULL.

En las expresiones booleanas, IS UNKNOWN equivale a IS NULL, e IS NOT UNKNOWN equivale a 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;

​Operadores de acceso

​Operador de negación numérica

​Operadores de multiplicación y división

​Operadores de suma y resta

​Operadores de comparación

​función equals

​función notEquals

​función lessOrEquals

​función greaterOrEquals

​función less

​función greater

​función like

​función notLike

​función ilike

​Función BETWEEN

​operador is not distinct from (<=>)

​Operadores para trabajar con cadenas

​OVERLAY

​Operadores para trabajar con conjuntos de datos

​función in

​función notIn

​función globalIn

​función globalNotIn

​función in subconsulta

​notIn subconsulta function

​función in subconsulta

​función notIn en subconsulta

​SOME / ALL en arrays

​Operadores para trabajar con fechas y horas

​EXTRACT

​INTERVAL

​Suma de fecha y hora

​Operador lógico AND

​Operador lógico OR

​Operador de negación lógica

​Operador condicional

​Expresión condicional

​Operador de concatenación

​Operador de creación de lambda

​Operador de creación de Array

​Operador de creación de tupla

​Asociatividad

​Comprobación de NULL

​IS NULL

​IS NOT NULL

​Verificación de valores booleanos

Operadores de acceso

Operador de negación numérica

Operadores de multiplicación y división

Operadores de suma y resta

Operadores de comparación

función equals

función notEquals

función lessOrEquals

función greaterOrEquals

función less

función greater

función like

función notLike

función ilike

Función BETWEEN

operador `is not distinct from` (`<=>`)

Operadores para trabajar con cadenas

OVERLAY

Operadores para trabajar con conjuntos de datos

función in

función notIn

función globalIn

función globalNotIn

función in subconsulta

notIn subconsulta function

función in subconsulta

función notIn en subconsulta

`SOME` / `ALL` en arrays

Operadores para trabajar con fechas y horas

EXTRACT

INTERVAL

Suma de fecha y hora

Operador lógico AND

Operador lógico OR

Operador de negación lógica

Operador condicional

Expresión condicional

Operador de concatenación

Operador de creación de lambda

Operador de creación de Array

Operador de creación de tupla

Asociatividad

Comprobación de `NULL`

IS NULL

IS NOT NULL

Verificación de valores booleanos