Operadores

O ClickHouse transforma os operadores nas funções correspondentes durante a etapa de análise sintática da consulta, de acordo com sua prioridade, precedência e associatividade.

a[N] – Acesso a um elemento de um array. A função arrayElement(a, N). N também pode ser um array de inteiros; nesse caso, os elementos em todas essas posições são retornados como um array, assim como em arrayMap(i -> a[i], N). As posições podem ser anuláveis. Uma posição NULL produz NULL quando o tipo de elemento pode ser encapsulado em Nullable; para tipos de elemento que não podem estar dentro de Nullable (como Array, Map), produz o valor padrão do tipo de elemento, assim como para um índice NULL escalar.

a.N – Acesso a um elemento de tupla. A função tupleElement(a, N).

-a – A função negate(a).

Para a negação de tuplas: tupleNegate.

a * b – A função multiply(a, b).

Para multiplicar tupla por um número: tupleMultiplyByNumber; para produto escalar: dotProduct.

a / b – A função divide(a, b).

Para dividir tupla por um número: tupleDivideByNumber.

a % b – A função modulo(a, b).

a + b – A função plus(a, b).

Para adição de tuplas: tuplePlus.

a - b – A função minus(a, b).

Para subtração de tuplas: tupleMinus.

a = b – A função equals(a, b).

a == b – A função equals(a, b).

a != b – A função notEquals(a, b).

a <> b – A função notEquals(a, b).

a <= b – a função lessOrEquals(a, b).

a >= b – A função greaterOrEquals(a, b).

a < b – A função less(a, b).

a > b – A função greater(a, b).

a LIKE b – a função like(a, b).

a NOT LIKE b – a função notLike(a, b).

a ILIKE b – a função ilike(a, b).

a REGEXP b – A função match(a, b).

a ~ b – A função match(a, b) (correspondência por expressão regular no estilo PostgreSQL).

a !~ b – A função notMatch(a, b) (verifica se a não corresponde à expressão regular b).

a ~* b – A função matchCaseInsensitive(a, b) (correspondência de expressão regular sem distinção entre maiúsculas e minúsculas).

a !~* b – A função notMatchCaseInsensitive(a, b) (verifica se a não corresponde à expressão regular b, sem diferenciar maiúsculas de minúsculas).

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.

O operador <=> é o operador de igualdade compatível com NULL, equivalente a IS NOT DISTINCT FROM. Ele funciona como o operador de igualdade comum (=), mas trata valores NULL como comparáveis. Dois valores NULL são considerados iguais, e um NULL comparado com qualquer valor não NULL retorna 0 (falso), em vez de NULL.

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

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

• OVERLAY(string PLACING replacement FROM offset) - A função overlay(string, replacement, offset).
• OVERLAY(string PLACING replacement FROM offset FOR length) - A função overlay(string, replacement, offset, length).
• OVERLAYUTF8(string PLACING replacement FROM offset) - A função overlayUTF8(string, replacement, offset).
• OVERLAYUTF8(string PLACING replacement FROM offset FOR length) - A função overlayUTF8(string, replacement, offset, length).

Consulte os operadores IN e o operador EXISTS.

a IN ... – a função in(a, b).

a NOT IN ... – a função notIn(a, b).

a GLOBAL IN ... – a função globalIn(a, b).

a GLOBAL NOT IN ... – A função globalNotIn(a, b).

a = ANY (subquery) – A função in(a, subquery).

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

a = ALL (subquery) – O mesmo que a IN (SELECT singleValueOrNull(*) FROM subquery).

a != ALL (subquery) – A função notIn(a, subquery).

Exemplos

Consulta com ALL:

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

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

Consulta com ANY:

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

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

Além da forma de subconsulta descrita acima, o lado direito de SOME / ALL pode ser uma expressão de array (um literal de array, uma coluna do tipo array ou qualquer expressão que retorne um array). Esta é a sintaxe de quantificador de array no estilo do PostgreSQL. Ela é reconhecida durante o parse e reescrita como funções de array, portanto não é necessária nenhuma reescrita manual:

SOME é o quantificador existencial (o sinônimo em SQL de ANY). = e <> recebem tratamento especial e são reescritos como has / NOT has porque têm uma implementação otimizada; a forma geral recorre às funções de ordem superior arrayExists / arrayAll.

A forma com array é reconhecida para os operadores de comparação =, ==, !=, <>, <=>, <, <=, >, >=, os predicados de comparação com palavras-chave IS DISTINCT FROM e IS NOT DISTINCT FROM, e os predicados de busca em string LIKE, ILIKE, NOT LIKE, NOT ILIKE e REGEXP. Os predicados de comparação com palavras-chave e os predicados de busca em string são reconhecidos apenas para a forma com array, não para a forma com subconsulta (que é reduzida a IN/NOT IN). Operadores que não têm significado de quantificador de array — por exemplo, o próprio IN — não são reescritos e mantêm seu significado normal.

Os predicados de busca em string funcionam porque MatchImpl (a implementação por trás de LIKE / ILIKE / REGEXP) oferece suporte a um haystack constante com uma needle não constante. Por exemplo, 'abc' LIKE SOME(['a%', 'b%']) é reescrito como arrayExists(x -> 'abc' LIKE x, ['a%', 'b%']), e 'abc' NOT LIKE ALL(['x%', 'y%']) como arrayAll(x -> 'abc' NOT LIKE x, ['x%', 'y%']). Isso compara uma string com vários patterns; para fazer a correspondência em uma única passagem combinada, você ainda pode usar uma função de busca multipadrão, como multiMatchAny (expressões regulares) ou multiSearchAny (substrings).

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);

Extrai partes de uma determinada data. Por exemplo, você pode obter o mês de uma data ou os segundos de um horário.

O parâmetro part especifica qual parte da data deve ser recuperada. Os seguintes valores estão disponíveis:

• NANOSECOND — O nanossegundo. Valores possíveis: 0–999999999.
• MICROSECOND — O microssegundo. Valores possíveis: 0–999999.
• MILLISECOND — O milissegundo. Valores possíveis: 0–999.
• SECOND — O segundo. Valores possíveis: 0–59.
• MINUTE — O minuto. Valores possíveis: 0–59.
• HOUR — A hora. Valores possíveis: 0–23.
• DAY — O dia do mês. Valores possíveis: 1–31.
• WEEK — O número da semana ISO 8601. Valores possíveis: 1–53.
• MONTH — O número do mês. Valores possíveis: 1–12.
• QUARTER — O trimestre. Valores possíveis: 1–4.
• YEAR — O ano.
• EPOCH — O Unix timestamp (segundos desde 1970-01-01 00:00:00 UTC). Observação: para DateTime64, a parte fracionária dos segundos é truncada.
• DOW — O dia da semana (compatível com PostgreSQL). 0 = domingo, 6 = sábado.
• DOY — O dia do ano. Valores possíveis: 1–366.
• ISODOW — O dia ISO da semana. 1 = segunda-feira, 7 = domingo.
• ISOYEAR — O ano de numeração de semanas ISO 8601.
• CENTURY — O século. Por exemplo, o ano de 2024 está no século 21.
• DECADE — A década (ano dividido por 10). Por exemplo, o ano de 2024 tem década 202.
• MILLENNIUM — O milênio. Por exemplo, o ano de 2024 está no 3º milênio.
• TIMEZONE_HOUR — A parte da hora com sinal do deslocamento UTC do fuso horário do operando. Por exemplo, +5:30 retorna 5, -3:30 retorna -3.
• TIMEZONE_MINUTE — A parte dos minutos com sinal do deslocamento UTC do fuso horário do operando. Por exemplo, +5:30 retorna 30, -3:30 retorna -30.

O parâmetro part não diferencia maiúsculas de minúsculas.

O parâmetro date especifica o valor a ser processado. Os tipos Date, Date32, DateTime, DateTime64 e Interval são suportados. Quando date é um Interval, o part solicitado deve corresponder ao tipo armazenado no intervalo (por exemplo, EXTRACT(DAY FROM INTERVAL 5 DAY) é permitido; EXTRACT(HOUR FROM INTERVAL 5 DAY) é rejeitado, porque os intervalos do ClickHouse são de tipo único). O resultado para um operando Interval é Int64.

Exemplos:

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

No exemplo a seguir, criamos uma tabela e inserimos nela um valor do 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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

Você pode ver mais exemplos nos testes.

Cria um valor do tipo Interval que deve ser usado em operações aritméticas com valores do tipo Date e DateTime.

Tipos de intervalos:

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

Você também pode usar um literal de string ao definir um valor INTERVAL. Por exemplo, INTERVAL 1 HOUR é idêntico a INTERVAL '1 hour' ou INTERVAL '1' hour.

Exemplos:

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

Exemplos:

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

Veja também

• tipo de dado Interval
• toInterval funções de conversão de tipo

Um valor Date ou Date32 pode ser somado a um valor Time ou Time64 usando o operador +. O resultado é um DateTime ou DateTime64 que representa a data no horário informado. A operação é comutativa.

O tipo do resultado depende dos tipos dos operandos:

Exemplos:

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

Os operadores pós-fixados AT TIME ZONE e AT LOCAL convertem um valor DateTime ou DateTime64 para outro fuso horário. Eles são açúcar sintático para a função toTimeZone já existente:

zone pode ser qualquer expressão constante do tipo string que resulte em um nome de fuso horário válido (por exemplo, 'America/Denver', 'UTC' ou concat('America', '/', 'Denver')). Como AT TIME ZONE é convertido em toTimeZone, aplicam-se as mesmas regras para argumentos de fuso horário: expressões não constantes, como uma referência de coluna, exigem allow_nonconst_timezone_arguments = 1.

AT LOCAL usa o fuso horário da sessão atual (ou o padrão do servidor, se nenhum fuso horário da sessão estiver definido). Em tabelas Distributed, session_timezone deve ser definido explicitamente; quando está vazio, timeZone() é local ao shard e não pode ser usado como argumento constante de toTimeZone, causando uma exceção ILLEGAL_COLUMN.

AT TIME ZONE tem precedência de operador 13 (acima de *///%, que têm 12, e de +/-, que têm 11), em linha com o PostgreSQL. Isso significa que a * ts AT TIME ZONE 'tz' é interpretado como a * (ts AT TIME ZONE 'tz'), e ts + interval AT TIME ZONE 'tz' é interpretado como ts + (interval AT TIME ZONE 'tz'). Para aplicar a conversão de fuso horário após a aritmética, use parênteses explícitos:

-- 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');

Exemplos:

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

Veja também

• toTimeZone
• timeZone

Sintaxe SELECT a AND b — calcula a conjunção lógica entre a e b usando a função and.

Sintaxe SELECT a OR b — calcula a disjunção lógica entre a e b usando a função or.

Sintaxe SELECT NOT a — calcula a negação lógica de a usando a função not.

a ? b : c – A função if(a, b, c).

Observação:

O operador condicional calcula os valores de b e c, verifica se a condição a é satisfeita e, em seguida, retorna o valor correspondente. Se b ou C for a função arrayJoin(), cada linha será replicada independentemente da condição "a".

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

Se x for especificado, será usada a função transform(x, [a, ...], [b, ...], c). Caso contrário, multiIf(a, b, ..., c).

Se não houver a cláusula ELSE c na expressão, o valor padrão será NULL.

A função transform não funciona com NULL.

s1 || s2 – A função concat(s1, s2) function.

x -> expr – a função lambda(x, expr).

Os operadores a seguir não têm prioridade, pois são parênteses:

[x1, ...] – A função array(x1, ...).

(x1, x2, ...) – A função tuple(x2, x2, ...).

Todos os operadores binários têm associatividade à esquerda. Por exemplo, 1 + 2 + 3 é transformado em plus(plus(1, 2), 3). Às vezes, isso não funciona como você espera. Por exemplo, SELECT 4 > 2 > 3 resultará em 0.

Para maior eficiência, as funções and e or aceitam qualquer número de argumentos. As cadeias correspondentes de operadores AND e OR são transformadas em uma única chamada dessas funções.

O ClickHouse oferece suporte aos operadores IS NULL e IS NOT NULL.

• Para valores do tipo Nullable, o operador IS NULL retorna:
  • 1, se o valor for NULL.
  • 0, caso contrário.
• Para outros valores, o operador IS NULL sempre retorna 0.

Pode ser otimizado habilitando a configuração optimize_functions_to_subcolumns. Com optimize_functions_to_subcolumns = 1, a função lê apenas a subcoluna null, em vez de ler e processar todos os dados da coluna. A consulta SELECT n IS NULL FROM table é transformada em SELECT n.null FROM TABLE.

SELECT x+100 FROM t_null WHERE y IS NULL

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

• Para valores do tipo Nullable, o operador IS NOT NULL retorna:
  • 0, se o valor for NULL.
  • 1, caso contrário.
• Para outros valores, o operador IS NOT NULL sempre retorna 1.

SELECT * FROM t_null WHERE y IS NOT NULL

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

Pode ser otimizado ativando a configuração optimize_functions_to_subcolumns. Com optimize_functions_to_subcolumns = 1, a função lê apenas a subcoluna null, em vez de ler e processar todos os dados da coluna. A consulta SELECT n IS NOT NULL FROM table é transformada em SELECT NOT n.null FROM TABLE.

O ClickHouse oferece suporte aos operadores IS TRUE, IS FALSE, IS UNKNOWN, IS NOT TRUE, IS NOT FALSE e IS NOT UNKNOWN. Eles são usados com expressões Bool e Nullable(Bool).

• expr IS TRUE retorna 1 somente se expr for true.
• expr IS FALSE retorna 1 somente se expr for false.
• expr IS UNKNOWN retorna 1 somente se expr for NULL.
• expr IS NOT TRUE retorna 1 se expr for false ou NULL.
• expr IS NOT FALSE retorna 1 se expr for true ou NULL.
• expr IS NOT UNKNOWN retorna 1 se expr não for NULL.

Para expressões 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;