ClickHouse 14.常用操作符介绍

clickhouse

发布日期: 2021-08-05

1. 介绍 ClickHouse 的操作符

所有的操作符（运算符）都会在查询时，依据他们的优先级及其结合顺序，在被解析时转换为对应的函数。
下面按优先级从高到低列出各组运算符及其对应的函数：

2. 下标运算符

-- 数组中的下标运算符
-- a[N] – 数组中的第N个元素
-- 对应函数 arrayElement(a, N)
select array(1,2,3) as a, a[1] as a1,arrayElement(a, 1) as func_a1;
┌─a───────┬─a1─┬─func_a1─┐
│ [1,2,3] │  1 │       1 │
└─────────┴────┴─────────┘

-- 元组中的下标运算符
-- a.N – 元组中第N个元素
-- 对应函数 tupleElement(a, N)
select tuple(1,'a') as a, a.1 as a1,tupleElement(a, 2) as func_a1
┌─a───────┬─a1─┬─func_a1─┐
│ (1,'a') │  1 │ a       │
└─────────┴────┴─────────┘

3. 负号

-- 负号 -a
-- 对应函数 negate(a)
select -(-3) as n1,negate(n1) as n2;
┌─n1─┬─n2─┐
│  3 │ -3 │
└────┴────┘

4. 乘、除、取余、加、减、

-- 乘号 '*'
-- 对应函数 multiply(a, b)

select 5 * 5 as n1,multiply(n1, 5) as n2;
┌─n1─┬──n2─┐
│ 25 │ 125 │
└────┴─────┘

--  除号 '/'
-- 对应函数 divide(a, b)
select 5 / 5 as n1,divide(n1, 5) as n2;
┌─n1─┬──n2─┐
│  1 │ 0.2 │
└────┴─────┘

-- 取余 '%' 
-- 对应函数 modulo(a, b)
select 8 % 5 as n1, modulo(n1, 2) as n2;
┌─n1─┬─n2─┐
│  3 │  1 │
└────┴────┘

-- 加号 '+'
-- 对应函数 plus(a, b)
select 5 + 5 as n1, plus(n1, 5) as n2;
┌─n1─┬─n2─┐
│ 10 │ 15 │
└────┴────┘

-- 减号 '-'
-- 对应函数 minus(a, b)
select 10 - 5 as n1, minus(n1, 5) as n2;
┌─n1─┬─n2─┐
│  5 │  0 │
└────┴────┘

5. 关系运算符

-- 相等 '=' ，'=='
-- 对应函数 equals(a, b)
select 'a' = 'b' as n1,'a' == 'b' as n2, equals('a','a') as n3,toTypeName(n1) as type;
┌─n1─┬─n2─┬─n3─┬─type──┐
│  0 │  0 │  1 │ UInt8 │
└────┴────┴────┴───────┘

-- 不等 '!=','<>'
-- 对应函数 notEquals(a, b)
select 'a' != 'b' as n1,'a' <> 'b' as n2, notEquals('a','a') as n3,toTypeName(n1) as type;
┌─n1─┬─n2─┬─n3─┬─type──┐
│  1 │  1 │  0 │ UInt8 │
└────┴────┴────┴───────┘

-- 小于等于 '<=',''
-- 对应函数 lessOrEquals(a, b)
select 'a' <= 'b' as n1, lessOrEquals('a','a') as n2,toTypeName(n1) as type;
┌─n1─┬─n2─┬─type──┐
│  1 │  1 │ UInt8 │
└────┴────┴───────┘

-- 大于等于 '>='
-- 对应函数 greaterOrEquals(a, b)
select 'a' >= 'b' as n1, lessOrEquals('a','a') as n2,toTypeName(n1) as type;

-- 小于（如果前者小于后者，则返回1；否则返回0）
-- 对应函数 less(a, b)
select 'a' < 'b' as n1, less('a','a') as n2,toTypeName(n1) as type;
┌─n1─┬─n2─┬─type──┐
│  1 │  0 │ UInt8 │
└────┴────┴───────┘

-- 大于（如果前者大于后者，则返回1；否则返回0）
-- 对应函数 greater(a, b)
select 'a' > 'b' as n1, greater('a','a') as n2,toTypeName(n1) as type;
┌─n1─┬─n2─┬─type──┐
│  0 │  0 │ UInt8 │
└────┴────┴───────┘

-- 相似 'like'
-- 对应函数 like(a, b)
select 's1' like 's2' as n1, like('s1','s1') as n2;
┌─n1─┬─n2─┐
│  0 │  1 │
└────┴────┘

-- 不相似 'NOT LIKE'
-- 对应函数 notLike(a, b)
select 's1' not like 's2' as n1, notLike('s1','s1') as n2;
┌─n1─┬─n2─┐
│  1 │  0 │
└────┴────┘

-- 区间 'a BETWEEN b AND c'
-- 等价与  a >= b AND a <= c
SELECT
    ('b' >= 'a') AND ('b' <= 'c') AS n1,
    ('b' <= 'a') AND ('b' >= 'c') AS n2;

┌─n1─┬─n2─┐
│  1 │  0 │
└────┴────┘

6. 集合关系运算符

a IN ... – 对应函数 in(a, b)
a NOT IN ... – 对应函数 notIn(a, b)
a GLOBAL IN ... – 对应函数 globalIn(a, b)
a GLOBAL NOT IN ... – 对应函数 globalNotIn(a, b)

7. 逻辑非或非

-- 逻辑与 'and'
-- 对应函数and(a, b)
select 'a'='a' and 'b'='b' as n1,and('a'='b','b'='c') as n2;
┌─n1─┬─n2─┐
│  1 │  0 │
└────┴────┘

-- 逻辑或
-- 对应函数 or(a, b)
select 'a'='a' or 'b'='b' as n1,or('a'='b','c'='c') as n2;
┌─n1─┬─n2─┐
│  1 │  1 │
└────┴────┘

8. 条件运算符

条件运算符会先计算表达式b和表达式c的值，再根据表达式a的真假，返回相应的值。
如果表达式b和表达式c是 arrayJoin() 函数，则不管表达式a是真是假，每行都会被复制展开。

-- 条件运算符 a ? b : c
-- 对应函数 if(a, b, c)
select 1 < 2 ? 'yes' : 'no' as s1, if(1 > 2 ,'no','yes') as s2;
┌─s1──┬─s2──┐
│ yes │ yes │
└─────┴─────┘

9. 日期时间运算符

9.1. EXTRACT

-- 时间提取 ,从指定时间中提取某一部分
-- 从给定日期中提取部件。 例如，您可以从给定日期检索一个月，或从时间检索一秒钟。
EXTRACT(part FROM date);

-- date 参数指定要处理的日期或时间。 无论是 日期 或 日期时间 支持类型。
-- part 指定要检索的日期部分，参数不区分大小写。一下为 part 支持类型
YEAR — 获取年
QUARTER - 获取季度，1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
MONTH — 获取月份 1–12.
DAY — 获取日 1–31.
HOUR — 获取小时 0–23.
MINUTE — 获取分钟 0–59.
SECOND — 获取秒钟 0–59.

使用示例

-- 获取年 EXTRACT(YEAR FROM date)
-- 对应函数 toYear(date)
SELECT toDate('2021-08-05 21:50:25') as dt , EXTRACT(YEAR FROM dt) as d1,toYear(dt) as d2;
┌─────────dt─┬───d1─┬───d2─┐
│ 2021-08-05 │ 2021 │ 2021 │
└────────────┴──────┴──────┘

-- 获取季度 EXTRACT(QUARTER FROM date)
-- 对应函数 toQuarter(date)
SELECT toDate('2021-08-05 21:50:25') as dt , EXTRACT(QUARTER FROM dt) as d1, toQuarter(dt) as d2;
┌─────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 │  3 │  3 │
└────────────┴────┴────┘

-- 获取月  EXTRACT(MONTH FROM date)
-- 对应函数 toMonth(date)
SELECT toDate('2021-08-05 21:50:25') as dt , EXTRACT(MONTH FROM dt) as d1,toMonth(dt) as d2;
┌─────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 │  8 │  8 │
└────────────┴────┴────┘


-- 获取日 EXTRACT(DAY FROM date)
-- 对应函数 toDayOfMonth(date)
SELECT toDate('2021-08-05 21:50:25') as dt , EXTRACT(DAY FROM dt) as d1,toDayOfMonth(dt) as d2;
┌─────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 │  5 │  5 │
└────────────┴────┴────┘

-- 获取小时 EXTRACT(HOUR FROM date)
-- 对应函数 toHour(date)
SELECT toDateTime('2021-08-05 21:50:25') as dt ,EXTRACT(HOUR FROM dt) as d1, toHour(dt) as d2;
┌──────────────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 21:50:25 │ 21 │ 21 │
└─────────────────────┴────┴────┘

-- 获取分钟 EXTRACT(MINUTE FROM date)
-- 对应函数 toMinute(date)
SELECT toDateTime('2021-08-05 21:50:25') as dt , EXTRACT(MINUTE FROM dt) as d1,toMinute(dt) as d2;
┌──────────────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 21:50:25 │ 50 │ 50 │
└─────────────────────┴────┴────┘

-- 获取秒 EXTRACT(SECOND FROM date)
-- 对应函数 toSecond(date)
SELECT toDateTime('2021-08-05 21:50:25') as dt , EXTRACT(SECOND FROM dt) as d1,toSecond(dt) as d2;
┌──────────────────dt─┬─d1─┬─d2─┐
│ 2021-08-05 21:50:25 │ 25 │ 25 │
└─────────────────────┴────┴────┘

9.2. INTERVAL

进行日期计算的间隔，
向后日期计算使用正数。后一天 INTERVAL 1 DAY
向前计算时间时候复数。前一天 INTERVAL -1 DAY

-- 年间隔 INTERVAL 1 YEAR
-- 对应函数  addYears(date,years)
select toDate('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 YEAR as d1,addYears(dt,1) d2;
┌─────────dt─┬─────────d1─┬─────────d2─┐
│ 2021-08-05 │ 2022-08-05 │ 2022-08-05 │
└────────────┴────────────┴────────────┘

-- 季度间隔 INTERVAL 1 QUARTER 1个季度为三个月
-- 对应函数  addQuarters(date,years) 
select toDate('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 QUARTER as d1,addQuarters(dt,1) d2;
┌─────────dt─┬─────────d1─┬─────────d2─┐
│ 2021-08-05 │ 2021-11-05 │ 2021-11-05 │
└────────────┴────────────┴────────────┘

-- 月份间隔 INTERVAL 1 MONTH 
-- 对应函数  addMonths(date,months) 
select toDate('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 MONTH as d1,addMonths(dt,1) d2;
┌─────────dt─┬─────────d1─┬─────────d2─┐
│ 2021-08-05 │ 2021-09-05 │ 2021-09-05 │
└────────────┴────────────┴────────────┘

-- 周间隔 INTERVAL 1 WEEk 
-- 对应函数  addWeeks(date,weeks) 
select toDate('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 WEEk as d1,addWeeks(dt,1) d2;
┌─────────dt─┬─────────d1─┬─────────d2─┐
│ 2021-08-05 │ 2021-08-12 │ 2021-08-12 │
└────────────┴────────────┴────────────┘

-- 日间隔 INTERVAL 1 DAY
-- 对应函数  addDays(date,days) 
select toDate('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 DAY as d1,addDays(dt,1) d2;
┌─────────dt─┬─────────d1─┬─────────d2─┐
│ 2021-08-05 │ 2021-08-06 │ 2021-08-06 │
└────────────┴────────────┴────────────┘

-- 小时间隔 INTERVAL 1 HOUR
-- 对应函数 addHours(date,hours) 
select toDateTime('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 HOUR as d1,addHours(dt,1) d2;
┌──────────────────dt─┬──────────────────d1─┬──────────────────d2─┐
│ 2021-08-05 21:50:25 │ 2021-08-05 22:50:25 │ 2021-08-05 22:50:25 │
└─────────────────────┴─────────────────────┴─────────────────────┘

-- 分间隔 INTERVAL 1 MINUTE
-- 对应函数 addMinutes(date,minutes) 
select toDateTime('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 MINUTE as d1,addMinutes(dt,1) d2;
┌──────────────────dt─┬──────────────────d1─┬──────────────────d2─┐
│ 2021-08-05 21:50:25 │ 2021-08-05 21:51:25 │ 2021-08-05 21:51:25 │
└─────────────────────┴─────────────────────┴─────────────────────┘

-- 秒间隔 INTERVAL 1 SECOND
-- 对应函数 addSeconds(date,seconds) 
select toDateTime('2021-08-05 21:50:25') as dt, dt + INTERVAL 1 SECOND as d1,addSeconds(dt,1) d2;
┌──────────────────dt─┬──────────────────d1─┬──────────────────d2─┐
│ 2021-08-05 21:50:25 │ 2021-08-05 21:50:26 │ 2021-08-05 21:50:26 │
└─────────────────────┴─────────────────────┴─────────────────────┘

10. CASE条件表达式

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

11. 连接运算符

-- 连接符 '||'
-- 对应函数 concat(s1, s2)
select 'hello ' || 'world' as s1, concat('hello ','world') as s2;
┌─s1──────────┬─s2──────────┐
│ hello world │ hello world │
└─────────────┴─────────────┘

12. 创建 Lambda 函数

-- x -> expr – 
-- 对应函数 lambda(x, expr)

SELECT arraySort((x) -> x, [5,4,3,1,2,3]) as res;
┌─res───────────┐
│ [1,2,3,3,4,5] │
└───────────────┘

13. 创建数组

-- [x1, ...]
-- 对应函数 array(x1, ...)
select [1,1,3] as a1,array(1,2,3) as a2,toTypeName(a1) as type;
┌─a1──────┬─a2──────┬─type─────────┐
│ [1,1,3] │ [1,2,3] │ Array(UInt8) │
└─────────┴─────────┴──────────────┘

14. 创建元组

-- (x1, x2, ...)
-- 对应函数 tuple(x2, x2, ...)
select (1,'b',3.2) as t1,tuple(1,'a',0.1) as t2,toTypeName(t1) as type;
┌─t1──────────┬─t2──────────┬─type──────────────────────────┐
│ (1,'b',3.2) │ (1,'a',0.1) │ Tuple(UInt8, String, Float64) │
└─────────────┴─────────────┴───────────────────────────────┘

15. 结合方式

所有的同级操作符从左到右结合。例如， 1 + 2 + 3 会转换成 plus(plus(1, 2), 3)。

所以，有时他们会跟我们预期的不太一样。例如， SELECT 4 > 2 > 3 的结果是0。

为了高效， and 和 or 函数支持任意多参数，一连串的 AND 和 OR 运算符会转换成其对应的单个函数。

select 1 + 2 + 3;
┌─plus(plus(1, 2), 3)─┐
│                   6 │
└─────────────────────┘

SELECT 4 > 2 > 3;
┌─greater(greater(4, 2), 3)─┐
│                         0 │
└───────────────────────────┘

16. 判断是否为NULL

ClickHouse 支持 IS NULL 和 IS NOT NULL

16.1. IS NULL

值为 NULL 返回 1（true）
值不为 NULL 返回 0（false）


select * from (
  select 'null is true' as s1, NULL as n1, isNull(n1) as isn
) t where n1 is NULL;
┌─s1───────────┬─n1───┬─isn─┐
│ null is true │ ᴺᵁᴸᴸ │   1 │
└──────────────┴──────┴─────┘

16.2. IS NOT NULL

值不为 NULL 返回 1（true）
值为 NULL 返回 0（false）

select * from (
  select 'null is true' as s1, NULL as n1, 'hello' as n2, isNotNull(n2) as isn
) t where n2 is NOT NULL;
┌─s1───────────┬─n1───┬─n2────┬─isn─┐
│ null is true │ ᴺᵁᴸᴸ │ hello │   1 │
└──────────────┴──────┴───────┴─────┘