ClickHouse SQL操作符全面解析

ClickHouse SQL操作符全面解析

ClickHouse ClickHouse® 是一个免费的大数据分析型数据库管理系统。 项目地址: https://gitcode.com/gh_mirrors/cli/ClickHouse

概述

ClickHouse作为一款高性能的列式数据库管理系统，提供了丰富的SQL操作符来处理各种数据操作。本文将全面介绍ClickHouse中支持的各种操作符，包括它们的语法、功能和使用场景。

操作符基础

在ClickHouse中，操作符在查询解析阶段会根据优先级、结合性等规则转换为对应的函数。这种设计既保持了SQL语法的简洁性，又实现了高效的内部处理。

访问操作符

数组元素访问

• 语法：a[N]
• 等价函数：arrayElement(a, N)
• 功能：访问数组a的第N个元素（索引从1开始）

SELECT [10,20,30][2] -- 返回20

元组元素访问

• 语法：a.N
• 等价函数：tupleElement(a, N)
• 功能：访问元组a的第N个元素

SELECT (1,'a',now()).2 -- 返回'a'

算术操作符

数值取反

• 语法：-a
• 等价函数：negate(a)
• 元组取反：tupleNegate

乘除运算

• 乘法：a * b → multiply(a, b)
• 除法：a / b → divide(a, b)
• 取模：a % b → modulo(a, b)
• 元组运算：tupleMultiplyByNumber, tupleDivideByNumber

加减运算

• 加法：a + b → plus(a, b)
• 减法：a - b → minus(a, b)
• 元组运算：tuplePlus, tupleMinus

比较操作符

ClickHouse支持标准的比较操作符，它们都会转换为对应的函数：

| 操作符 | 等价函数 | 描述 | |--------|----------|------| | 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) | 大于 |

模糊匹配

• LIKE：a LIKE s → like(a, b)
• NOT LIKE：a NOT LIKE s → notLike(a, b)
• ILIKE：不区分大小写的LIKE

BETWEEN

• a BETWEEN b AND c → a >= b AND a <= c
• a NOT BETWEEN b AND c → a < b OR a > c

集合操作符

ClickHouse提供了丰富的集合操作符来处理IN查询：

| 操作符 | 等价函数 | 描述 | |--------|----------|------| | a IN ... | in(a, b) | 判断是否在集合中 | | a NOT IN ... | notIn(a, b) | 判断是否不在集合中 | | a GLOBAL IN ... | globalIn(a, b) | 分布式环境下的IN查询 | | a GLOBAL NOT IN ... | globalNotIn(a, b) | 分布式环境下的NOT IN |

子查询操作符

-- 等价于IN子查询
a = ANY (subquery) → in(a, subquery)

-- 等价于NOT IN子查询
a != ANY (subquery) → a NOT IN (SELECT singleValueOrNull(*) FROM subquery)

-- 与ALL结合使用
a = ALL (subquery) → a IN (SELECT singleValueOrNull(*) FROM subquery)
a != ALL (subquery) → notIn(a, subquery)

日期时间操作符

EXTRACT操作符

EXTRACT(part FROM date)

提取日期时间的特定部分，支持：

• DAY：月份中的日（1-31）
• MONTH：月份（1-12）
• YEAR：年份
• SECOND：秒（0-59）
• MINUTE：分钟（0-59）
• HOUR：小时（0-23）

示例：

SELECT EXTRACT(MONTH FROM toDate('2023-06-15')) -- 返回6

INTERVAL操作符

用于日期时间的加减运算，支持的时间单位：

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

示例：

SELECT now() + INTERVAL 3 DAY + INTERVAL 2 HOUR

注意：不同时间单位的INTERVAL不能直接组合，需要分开写。

逻辑操作符

| 操作符 | 等价函数 | 描述 | |--------|----------|------| | a AND b | and(a, b) | 逻辑与 | | a OR b | or(a, b) | 逻辑或 | | NOT a | not(a) | 逻辑非 |

ClickHouse对逻辑操作进行了优化，连续的AND或OR会被合并为单个函数调用以提高性能。

条件操作符

三元操作符

• 语法：a ? b : c
• 等价函数：if(a, b, c)

注意：无论条件a是否满足，b和c都会被计算。

CASE表达式

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

如果指定x，则使用transform(x, [a, ...], [b, ...], c)函数，否则使用multiIf(a, b, ..., c)。

其他操作符

字符串连接

• 语法：s1 || s2
• 等价函数：concat(s1, s2)

Lambda表达式

• 语法：x -> expr
• 等价函数：lambda(x, expr)

数据结构创建

• 数组：[x1, ...] → array(x1, ...)
• 元组：(x1, x2, ...) → tuple(x2, x2, ...)

NULL值检查

IS NULL

对于Nullable类型：

• 值为NULL时返回1
• 否则返回0

对于非Nullable类型总是返回0。

IS NOT NULL

对于Nullable类型：

• 值为NULL时返回0
• 否则返回1

对于非Nullable类型总是返回1。

可以通过设置optimize_functions_to_subcolumns来优化NULL检查的性能。

总结

ClickHouse提供了丰富而强大的操作符集，覆盖了从基本算术运算到复杂条件判断的各种场景。了解这些操作符及其对应的函数实现，有助于编写更高效、更清晰的ClickHouse查询语句。在实际使用中，可以根据具体需求选择合适的操作符形式或直接使用对应的函数。

ClickHouse ClickHouse® 是一个免费的大数据分析型数据库管理系统。 项目地址: https://gitcode.com/gh_mirrors/cli/ClickHouse