1.概述
转载:添加链接描述
1.常见操作:四则运算和拼接
运算符 | 运算符 | 函数 |
|
加 | a + b | plus(a, b) |
|
减 | a - b | minus(a, b) |
|
乘 |
| multiply (a, b) |
|
除 |
| divide(a, b) |
|
取模 | a % b | modulo(a, b) |
|
取负数 |
| negate (a) |
|
连接 | s1 || s2 | concat(s1, s2) |
|
Lambda | x -> expr | lambda(x, expr) |
|
2.比较运算符:
比较运算符 | 示例 | 函数 |
等于 | a = b | |
等于 | a == b |
|
不等于 | a != b | |
不等于 | a <> b | notEquals(a, b) |
大于 | a > b | greater(a, b) |
大于等于 | a >= b | greaterOrEquals(a, b) |
小于 |
| less(a, b) |
小于等于 | a <= b | lessOrEquals(a, b) |
匹配操作 | a LIKE s | like(a, b) |
不匹配操作 |
| notLike(a, b) |
区间 | a BETWEEN b AND c |
|
不在区间 | a NOT BETWEEN b AND c | a < b OR a > c |
3.逻辑运算符:
说明 | 示例 | 函数 |
非 | NOT a | not(a) |
与 | a AND b | and(a, b) |
或 | a OR b | or(a, b) |
4.IN操作
操作符 | 函数示例 |
a IN ... | in(a, b) |
a NOT IN ... | notIn(a, b) |
a GLOBAL IN ... | globalIn(a, b) |
a GLOBAL NOT IN ... | globalNotIn(a, b) |
5.日期时间操作符:
EXTRACT(part FROM date)
说明 | 参数 | Part | INTERVAL | Date | Datetime | Range |
年 | YEAR |
|
|
|
|
|
季 | QUARTER |
|
|
|
| 1-4 |
月 | MONTH |
|
|
|
| 1-12 |
周 | WEEK |
|
|
|
| 1-52 |
日 | DAY |
|
|
|
| 1-31 |
时 | HOUR |
|
|
|
| 0-23 |
分 | MINUTE |
|
|
|
| 0-59 |
秒 | SECOND |
|
|
|
| 0-59 |
select extract(year from now()) Y, extract(month from now()) m, extract(day from now()) d,\n
extract(hour from now()) h,extract(minute from now()) mi,extract(second from now()) s,now() now;
SELECT
toYear(now()) AS Y,
toMonth(now()) AS m,
toDayOfMonth(now()) AS d,
toHour(now()) AS h,
toMinute(now()) AS mi,
toSecond(now()) AS s,
now() AS now
┌────Y─┬─m─┬─d─┬─h─┬─mi─┬──s─┬─────────────────now─┐
│ 2020 │ 6 │ 9 │ 8 │ 20 │ 29 │ 2020-06-09 08:20:29 │
└──────┴───┴───┴───┴────┴────┴─────────────────────┘
6.条件运算符:
条件运算符:
a ? b : c – The if(a, b, c) function
条件表达式:
Conditional Expression
CASE [x]
WHEN a THEN b
[WHEN ... THEN ...]
[ELSE c]
END
If x is specified, then transform(x, [a, ...], [b, ...], c) function is used. Otherwise – multiIf(a, b, ..., c).
If there is no ELSE c clause in the expression, the default value is NULL.
The transform function does not work with NULL.
7.NULL运算符:
NULL操作
支持IS NULL 和IS NOT NULL
使用nullable类型返回0和1
8.数组和元祖操作符:
| 示例 | 函数 |
|
数组 |
| array(x1, ...) |
|
元祖 |
| tuple(x2, x2, ...) |
|
数组访问 | a[N] | arrayElement(a, N) |
|
元祖访问 | a.N | tupleElement(a, N) |
|