clickhouse的SQL参考——（六）操作符类参考资料
版本:v20.11
⽬录
Operators
在查询解析阶段，ClickHouse将根据运算符的有限度、位次和关联性，将运算符转换为相应的函数。
访问操作符
a[N] – 访问数组元素，转换为 arrayElement(a, N) 函数.
a.N – 访问元组元素，转换为 tupleElement(a, N) 函数
数值取反运算符
-a – 转换为 negate (a) 函数
乘除取余运算符
a *
b – 转换为 multiply (a, b) 函数
a /
b – 转换为 divide(a, b) 函数
a %
b – 转换为 modulo(a, b) 函数
加减运算符
a +
b – 转换为 plus(a, b) 函数
a -
b – 转换为 minus(a, b) 函数
⽐较运算符
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 s – 转换为 like(a, b) 函数
a NOT LIKE s – 转换为 notLike(a, b) 函数
a ILIKE s – 转换为 ilike(a, b) 函数
a BETWEEN
b AND
c – 转换为 a >= b AND a <= c 函数
a NOT BETWEEN
b AND
c – 转换为 a < b OR a > c 函数
⽤于数据集的运算符
参考 .
a IN ... – 转换为 in(a, b) 函数
a NOT IN ... – 转换为 notIn(a, b) 函数
a GLOBAL IN ... – 转换为 globalIn(a, b) 函数
a GLOBAL NOT IN ... – 转换为 globalNotIn(a, b) 函数
⽇期和时间的运算符
EXTRACT
从给定⽇期提取部分的值。例如，您可以从给定⽇期提取⽉份，或从时间提取秒。
part参数指定要检索⽇期的哪⼀部分。 提供以下值：
DAY — 每⽉中的⽇期，可能值: 1–31.
sql语句查询结果取反MONTH — ⽉份值，可能值: 1–12.
YEAR — 年份.
SECOND — 秒. 可能值: 0–59.
MINUTE — 分钟. 可能值: 0–59.
HOUR — ⼩时. 可能值: 0–23.
part参数不区分⼤⼩写。
date参数指定要处理的⽇期或时间。 ⽀持Date或DateTime类型。
SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));
--在下⾯的⽰例中，我们创建⼀个表并在其中插⼊⼀个DateTime类型的值。CREATE TABLE test.Orders (
OrderId UInt64,
OrderName String,
OrderDate DateTime
)
ENGINE = Log;
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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘
在下⾯的⽰例中，我们创建⼀个表并在其中插⼊⼀个DateTime类型的值。
INTERVAL
间隔类型的值，在Date和DateTime类型的值的算术运算中使⽤。
健哥类型：
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
设置INTERVAL值时，也可以使⽤字符串⽂字。
例如，INTERVAL 1 HOUR等于INTERVAL '1 hour' 或者 INTERVAL '1' hour.
注意
不同类型的间隔⽆法合并。您不能使⽤“ INTERVAL 1 DAY 1 HOUR”之类的表达式，需要转换成“INTERVAL 25 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 │
└─────────────────────┴────────────────────────────────────────────────────────────┘
逻辑关系运算符
NOT a – 转换为 not(a) 函数
a AND
b – 转换为and(a, b) 函数
a OR
b – 转换为 or(a, b) 函数
a ?
b :
c – 转换为 if(a, b, c) 函数
条件表达式
CASE [x]
WHEN a THEN b
[WHEN ... THEN ...]
[ELSE c]
END
如果指定了x，则使⽤ transform(x, [a, ...], [b, ...], c)
否则，使⽤ multiIf(a, b, ..., c).
如果表达式中没有ELSE c⼦句，则默认值为NULL。转换功能不适⽤于NULL。
串联运算符
s1 || s2 – 转换为 concat(s1, s2)函数
Lambda Creation Operator
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运算符链会被转换为单个调⽤。
NULL检查
ClickHouse⽀持IS NULL和IS NOT NULL运算符。
IS NULL
对于Nullable类型值，IS NULL运算符返回：
1，如果值是NULL.
0，其他情况.
对于其他情况，IS NULL 运算符⼀直返回 0.
IS NOT NULL
对于Nullable类型值，IS NOT NULL运算符返回:
0, 如果值是NULL.
1，其他情况.
对于其他情况，IS NOT NULL 运算符⼀直返回 1.
SELECT x+100 FROM t_null WHERE y IS NULL
┌─plus(x, 100)─┐
│ 101 │
└──────────────┘
SELECT * FROM t_null WHERE y IS NOT NULL
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘
IN Operators
由于IN, NOT IN, GLOBAL IN, 和 GLOBAL NOT IN运算符的功能⾮常丰富，因此单独的介绍了这些运算符。
在运算符左侧是单列或元组。
SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...
如果左侧是索引中的单个列，⽽右侧是⼀组常量，系统将使⽤索引来处理查询。
不要明确列出太多常量值（数百万）。如果数据集很⼤，请将其放在临时表中，然后使⽤⼦查询。
运算符的右侧可以是⼀组常量表达式，⼀组具有常量表达式的元组（如上⾯的⽰例所⽰），也可以是数据库表或者SELECT⼦查询。如果运算符的右侧是表名（例如，UserID IN users），则它等效于⼦
查询UserID IN (SELECT * FROM users)。
⼦查询可以指定多个列来过滤元组。
SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...
IN运算符左右列的类型应相同。
IN运算符和⼦查询可能出现在查询的任何部分，包括聚合函数和lambda函数。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论