运算符

ClickHouse 会在解析查询时，根据运算符的优先级、先后顺序和结合性，将其转换为相应的函数。

a[N] – 访问数组中的元素。对应 arrayElement(a, N) 函数。 N 也可以是整数数组，此时会将这些位置上的所有元素作为数组返回， 与 arrayMap(i -> a[i], N) 的行为相同。这些位置可以为 Nullable。当元素类型 可封装在 Nullable 中时，NULL 位置会返回 NULL；对于不能包含在 Nullable 中的元素类型 (如 Array、Map) ，则会返回 该元素类型的默认值，与标量 NULL 索引时的行为相同。

a.N – 访问 Tuple 元素。对应 tupleElement(a, N) 函数。

-a – negate(a) 函数。

有关 Tuple 取负，请参见：tupleNegate。

a * b – multiply(a, b) 函数。

如需将Tuple乘以数值，请参见 tupleMultiplyByNumber；如需计算标量积，请参见 dotProduct。

a / b – divide(a, b) 函数。

如需将Tuple除以数值，请参见 tupleDivideByNumber。

a % b – modulo(a, b) 函数。

a + b – plus(a, b) 函数。

对于 Tuple 加法，请参见：tuplePlus。

a - b – minus(a, b) 函数。

对于 Tuple 减法，请参见：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 背后的实现) 支持常量 haystack 与非常量 needle。例如，'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%'])。这会将一个字符串与多个 pattern 进行匹配；如果要通过一次组合扫描完成匹配，仍然可以使用多 pattern 搜索函数，例如 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 年属于 21 世纪。
• DECADE — 十年 (年份除以 10) 。例如，2024 年的十年值为 202。
• MILLENNIUM — 千年。例如，2024 年属于第 3 个千年。
• 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 必须与该 Interval 存储的种类一致 (例如，允许 EXTRACT(DAY FROM INTERVAL 5 DAY)；会拒绝 EXTRACT(HOUR FROM INTERVAL 5 DAY)，因为 ClickHouse 的 interval 只支持单一种类) 。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 可以是任何求值结果为有效时区名称的常量字符串表达式 (例如 '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 — 使用函数 and 计算 a 与 b 的逻辑与。

语法 SELECT a OR b — 使用函数 or 计算 a 与 b 的逻辑或。

语法 SELECT NOT a — 使用函数 not 对 a 进行逻辑非运算。

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 运算符返回：
  • 如果值为 NULL，则返回 1。
  • 否则返回 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 运算符返回：
  • 如果该值为 NULL，则返回 0。
  • 否则返回 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 仅当 expr 为 true 时返回 1。
• expr IS FALSE 仅当 expr 为 false 时返回 1。
• expr IS UNKNOWN 仅当 expr 为 NULL 时返回 1。
• expr IS NOT TRUE 在 expr 为 false 或 NULL 时返回 1。
• expr IS NOT FALSE 在 expr 为 true 或 NULL 时返回 1。
• expr IS NOT UNKNOWN 在 expr 不为 NULL 时返回 1。

对于布尔表达式，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;