Opérateurs

ClickHouse transforme les opérateurs en fonctions correspondantes lors de l’analyse syntaxique des requêtes, en fonction de leur priorité, de leur préséance et de leur associativité.

a[N] – Accès à un élément d’un tableau. Fonction arrayElement(a, N). N peut également être un tableau d’entiers, auquel cas les éléments situés à toutes ces positions sont renvoyés sous forme de tableau, comme avec arrayMap(i -> a[i], N). Les positions peuvent être nullables. Une position NULL produit NULL lorsque le type d’élément peut être encapsulé dans Nullable ; pour les types d’éléments qui ne peuvent pas être contenus dans Nullable (comme Array, Map), elle produit la valeur par défaut du type d’élément, comme pour un index NULL scalaire.

a.N – Accès à un élément d’un tuple. Fonction tupleElement(a, N).

-a – La fonction negate (a).

Pour la négation d’un Tuple : tupleNegate.

a * b – La fonction multiply (a, b).

Pour multiplier un Tuple par un nombre : tupleMultiplyByNumber ; pour le produit scalaire : dotProduct.

a / b – La fonction divide(a, b).

Pour diviser un Tuple par un nombre : tupleDivideByNumber.

a % b – La fonction modulo(a, b).

a + b – La fonction plus(a, b).

Pour l’addition de tuples : tuplePlus.

a - b – La fonction minus(a, b).

Pour la soustraction de tuples : tupleMinus.

a = b – La fonction equals(a, b).

a == b – La fonction equals(a, b).

a != b – la fonction notEquals(a, b).

a <> b – la fonction notEquals(a, b).

a <= b – La fonction lessOrEquals(a, b).

a >= b – La fonction greaterOrEquals(a, b).

a < b – La fonction less(a, b).

a > b – La fonction greater(a, b).

a LIKE b – La fonction like(a, b).

a NOT LIKE b – La fonction notLike(a, b).

a ILIKE b – La fonction ilike(a, b).

a REGEXP b – Fonction match(a, b).

a ~ b – Fonction match(a, b) (correspondance d’expression régulière de type PostgreSQL).

a !~ b – Fonction notMatch(a, b) (vérifie que a ne correspond pas à l’expression régulière b).

a ~* b – Fonction matchCaseInsensitive(a, b) (correspondance d’expression régulière insensible à la casse).

a !~* b – La fonction notMatchCaseInsensitive(a, b) (vérifie que a ne correspond pas à l’expression régulière b, sans distinction entre majuscules et minuscules).

a BETWEEN b AND c – Identique à a >= b AND a <= c.

a NOT BETWEEN b AND c – Identique à a < b OR a > c.

L’opérateur <=> est l’opérateur d’égalité compatible avec NULL, équivalent à IS NOT DISTINCT FROM. Il fonctionne comme l’opérateur d’égalité classique (=), mais considère les valeurs NULL comme comparables. Deux valeurs NULL sont considérées comme égales, et une valeur NULL comparée à n’importe quelle valeur non NULL renvoie 0 (false) plutôt que NULL.

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

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

• OVERLAY(string PLACING replacement FROM offset) - La fonction overlay(string, replacement, offset).
• OVERLAY(string PLACING replacement FROM offset FOR length) - La fonction overlay(string, replacement, offset, length).
• OVERLAYUTF8(string PLACING replacement FROM offset) - La fonction overlayUTF8(string, replacement, offset).
• OVERLAYUTF8(string PLACING replacement FROM offset FOR length) - La fonction overlayUTF8(string, replacement, offset, length).

Voir les opérateurs IN et l’opérateur EXISTS.

a IN ... – La fonction in(a, b).

a NOT IN ... – La fonction notIn(a, b).

a GLOBAL IN ... – La fonction globalIn(a, b).

a GLOBAL NOT IN ... – La fonction globalNotIn(a, b).

a = ANY (subquery) – La fonction in(a, subquery).

a != ANY (subquery) – Équivaut à a NOT IN (SELECT singleValueOrNull(*) FROM subquery).

a = ALL (subquery) – Identique à a IN (SELECT singleValueOrNull(*) FROM subquery).

a != ALL (subquery) – La fonction notIn(a, subquery).

Exemples

Requête avec ALL :

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

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

Requête avec ANY :

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

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

En plus de la forme avec sous-requête décrite ci-dessus, le côté droit de SOME / ALL peut être une expression de tableau (un littéral de tableau, une colonne de type tableau ou toute expression renvoyant un tableau). Il s'agit de la syntaxe de quantificateur de tableau de style PostgreSQL. Elle est reconnue lors de l'analyse syntaxique et réécrite en fonctions de tableau, donc aucune réécriture manuelle n'est nécessaire :

SOME est le quantificateur existentiel (le synonyme SQL de ANY). = et <> sont traités à part et réécrits en has / NOT has, car ils disposent d'une implémentation optimisée ; la forme générale s'appuie sur les fonctions d'ordre supérieur arrayExists / arrayAll.

La forme tableau est reconnue pour les opérateurs de comparaison =, ==, !=, <>, <=>, <, <=, >, >=, les prédicats de comparaison à mot-clé IS DISTINCT FROM et IS NOT DISTINCT FROM, ainsi que les prédicats de recherche de chaînes LIKE, ILIKE, NOT LIKE, NOT ILIKE et REGEXP. Les prédicats de comparaison à mot-clé et les prédicats de recherche de chaînes ne sont reconnus que pour la forme tableau, pas pour la forme avec sous-requête (qui est ramenée à IN/NOT IN). Les opérateurs qui n'ont pas de signification de quantificateur de tableau — par exemple IN lui-même — ne sont pas réécrits et conservent leur sens habituel.

Les prédicats de recherche de chaînes fonctionnent parce que MatchImpl (l'implémentation sous-jacente à LIKE / ILIKE / REGEXP) prend en charge un haystack constant avec une needle non constante. Par exemple, 'abc' LIKE SOME(['a%', 'b%']) est réécrit en arrayExists(x -> 'abc' LIKE x, ['a%', 'b%']), et 'abc' NOT LIKE ALL(['x%', 'y%']) en arrayAll(x -> 'abc' NOT LIKE x, ['x%', 'y%']). Cela permet de faire correspondre une chaîne à plusieurs patterns ; pour une correspondance en un seul passage combiné, vous pouvez toujours utiliser une fonction de recherche multi-pattern telle que multiMatchAny (expressions régulières) 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);

Extrait des composantes d’une date donnée. Par exemple, vous pouvez récupérer le mois d’une date donnée, ou les secondes d’une heure.

Le paramètre part spécifie quelle composante de la date récupérer. Les valeurs suivantes sont disponibles :

• NANOSECOND — La nanoseconde. Valeurs possibles : 0–999999999.
• MICROSECOND — La microseconde. Valeurs possibles : 0–999999.
• MILLISECOND — La milliseconde. Valeurs possibles : 0–999.
• SECOND — La seconde. Valeurs possibles : 0–59.
• MINUTE — La minute. Valeurs possibles : 0–59.
• HOUR — L’heure. Valeurs possibles : 0–23.
• DAY — Le jour du mois. Valeurs possibles : 1–31.
• WEEK — Le numéro de semaine ISO 8601. Valeurs possibles : 1–53.
• MONTH — Le numéro du mois. Valeurs possibles : 1–12.
• QUARTER — Le trimestre. Valeurs possibles : 1–4.
• YEAR — L’année.
• EPOCH — Le timestamp Unix (secondes écoulées depuis 1970-01-01 00:00:00 UTC). Remarque : pour DateTime64, la partie sous-seconde est tronquée.
• DOW — Le jour de la semaine (compatible PostgreSQL). 0 = dimanche, 6 = samedi.
• DOY — Le jour de l’année. Valeurs possibles : 1–366.
• ISODOW — Le jour ISO de la semaine. 1 = lundi, 7 = dimanche.
• ISOYEAR — L’année de numérotation des semaines ISO 8601.
• CENTURY — Le siècle. Par exemple, l’année 2024 appartient au XXIe siècle.
• DECADE — La décennie (année divisée par 10). Par exemple, l’année 2024 a pour décennie 202.
• MILLENNIUM — Le millénaire. Par exemple, l’année 2024 appartient au IIIe millénaire.
• TIMEZONE_HOUR — La partie heure signée du décalage UTC du fuseau horaire de l’opérande. Par exemple, +5:30 renvoie 5, -3:30 renvoie -3.
• TIMEZONE_MINUTE — La partie minute signée du décalage UTC du fuseau horaire de l’opérande. Par exemple, +5:30 renvoie 30, -3:30 renvoie -30.

Le paramètre part n’est pas sensible à la casse.

Le paramètre date spécifie la valeur à traiter. Les types Date, Date32, DateTime, DateTime64 et Interval sont pris en charge. Lorsque date est un Interval, la part demandée doit correspondre à l’unité stockée par l’intervalle (par exemple, EXTRACT(DAY FROM INTERVAL 5 DAY) est autorisé ; EXTRACT(HOUR FROM INTERVAL 5 DAY) est rejeté, car les intervalles ClickHouse ne stockent qu’un seul type d’unité). Le résultat pour un opérande Interval est Int64.

Exemples :

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

Dans l’exemple suivant, nous créons une table et y insérons une valeur de type 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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

Vous trouverez d’autres exemples dans les tests.

Crée une valeur de type Interval, à utiliser dans des opérations arithmétiques avec des valeurs de type Date et DateTime.

Types d’intervalles :

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

Vous pouvez également utiliser une chaîne littérale pour définir la valeur INTERVAL. Par exemple, INTERVAL 1 HOUR est identique à INTERVAL '1 hour' ou INTERVAL '1' hour.

Exemples :

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

Exemples :

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

Voir aussi

• Interval type de données
• toInterval fonctions de conversion de type

Une valeur Date ou Date32 peut être ajoutée à une valeur Time ou Time64 à l’aide de l’opérateur +. Le résultat est un DateTime ou un DateTime64 représentant cette date à l’heure indiquée. L’opération est commutative.

Le type de résultat dépend des types des opérandes :

Exemples :

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

Les opérateurs postfixes AT TIME ZONE et AT LOCAL convertissent une valeur DateTime ou DateTime64 vers un autre fuseau horaire. Ils constituent un sucre syntaxique de la fonction existante toTimeZone :

zone peut être n’importe quelle expression de chaîne constante qui s’évalue en un nom de fuseau horaire valide (par ex. 'America/Denver', 'UTC' ou concat('America', '/', 'Denver')). Comme AT TIME ZONE est réécrit en toTimeZone, les mêmes règles s’appliquent aux arguments de fuseau horaire : les expressions non constantes, comme une référence de colonne, nécessitent allow_nonconst_timezone_arguments = 1.

AT LOCAL utilise le fuseau horaire de session actuel (ou le fuseau horaire par défaut du serveur si aucun fuseau horaire de session n’est défini). Sur les tables Distributed, session_timezone doit être défini explicitement ; lorsqu’il est vide, timeZone() est local au shard et ne peut pas être utilisé comme argument constant de toTimeZone, ce qui provoque une exception ILLEGAL_COLUMN.

AT TIME ZONE a une préséance d’opérateur de 13 (au-dessus de *///% à 12, et au-dessus de +/- à 11), comme dans PostgreSQL. Cela signifie que a * ts AT TIME ZONE 'tz' s’associe comme a * (ts AT TIME ZONE 'tz'), et que ts + interval AT TIME ZONE 'tz' s’associe comme ts + (interval AT TIME ZONE 'tz'). Pour appliquer la conversion de fuseau horaire après l’opération arithmétique, utilisez des parenthèses explicites :

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

Exemples :

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

Voir aussi

• toTimeZone
• timeZone

Syntaxe SELECT a AND b — effectue la conjonction logique de a et b à l’aide de la fonction and.

Syntaxe SELECT a OR b — calcule la disjonction logique de a et b à l’aide de la fonction or.

Syntaxe SELECT NOT a — renvoie la négation logique de a à l’aide de la fonction not.

a ? b : c – La fonction if(a, b, c).

Remarque :

L’opérateur conditionnel évalue les valeurs de b et de c, vérifie ensuite si la condition a est vraie, puis renvoie la valeur correspondante. Si b ou C est une fonction arrayJoin(), chaque ligne sera dupliquée quelle que soit la condition « a ».

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

Si x est spécifié, la fonction transform(x, [a, ...], [b, ...], c) est utilisée. Sinon, multiIf(a, b, ..., c).

S'il n'y a pas de clause ELSE c dans l'expression, la valeur par défaut est NULL.

La fonction transform ne fonctionne pas avec NULL.

s1 || s2 – La fonction concat(s1, s2).

x -> expr – La fonction lambda(x, expr).

Les opérateurs suivants n’ont pas de priorité, car il s’agit de parenthèses :

[x1, ...] – La fonction array(x1, ...).

(x1, x2, ...) – La fonction tuple(x2, x2, ...).

Tous les opérateurs binaires sont associatifs à gauche. Par exemple, 1 + 2 + 3 est transformé en plus(plus(1, 2), 3). Il arrive parfois que cela ne fonctionne pas comme prévu. Par exemple, SELECT 4 > 2 > 3 renverra 0.

Pour des raisons d’efficacité, les fonctions and et or acceptent un nombre quelconque d’arguments. Les suites correspondantes d’opérateurs AND et OR sont transformées en un unique appel à ces fonctions.

ClickHouse prend en charge les opérateurs IS NULL et IS NOT NULL.

• Pour les valeurs de type Nullable, l’opérateur IS NULL renvoie :
  • 1 si la valeur est NULL.
  • 0 sinon.
• Pour les autres valeurs, l’opérateur IS NULL renvoie toujours 0.

Cela peut être optimisé en activant le paramètre optimize_functions_to_subcolumns. Avec optimize_functions_to_subcolumns = 1, la fonction lit uniquement la sous-colonne null, au lieu de lire et de traiter l’ensemble des données de la colonne. La requête SELECT n IS NULL FROM table est transformée en SELECT n.null FROM TABLE.

SELECT x+100 FROM t_null WHERE y IS NULL

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

• Pour les valeurs du type Nullable, l’opérateur IS NOT NULL renvoie :
  • 0 si la valeur est NULL.
  • 1 dans le cas contraire.
• Pour toutes les autres valeurs, l’opérateur IS NOT NULL renvoie toujours 1.

SELECT * FROM t_null WHERE y IS NOT NULL

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

Peut être optimisé en activant le paramètre optimize_functions_to_subcolumns. Avec optimize_functions_to_subcolumns = 1, la fonction lit uniquement la sous-colonne null, au lieu de lire et de traiter l’ensemble des données de la colonne. La requête SELECT n IS NOT NULL FROM table se transforme en SELECT NOT n.null FROM TABLE.

ClickHouse prend en charge les opérateurs IS TRUE, IS FALSE, IS UNKNOWN, IS NOT TRUE, IS NOT FALSE et IS NOT UNKNOWN. Ils s’utilisent avec les expressions Bool et Nullable(Bool).

• expr IS TRUE renvoie 1 uniquement si expr vaut true.
• expr IS FALSE renvoie 1 uniquement si expr vaut false.
• expr IS UNKNOWN renvoie 1 uniquement si expr vaut NULL.
• expr IS NOT TRUE renvoie 1 si expr vaut false ou NULL.
• expr IS NOT FALSE renvoie 1 si expr vaut true ou NULL.
• expr IS NOT UNKNOWN renvoie 1 si expr n’est pas NULL.

Pour les expressions booléennes, IS UNKNOWN est équivalent à IS NULL et IS NOT UNKNOWN est équivalent à 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;