clickhouse的SQL参考——（六）操作符类

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函数。

--对于3月17日之后的每一天，计算3月17日访问的用户占所有用户的百分比是多少
--IN子句中的子查询始终仅在一台服务器上运行一次。
--没有相关的子查询。
SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC
┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘

NULL值处理

在请求处理期间，IN运算符假定使用NULL进行运算的结果始终等于0，无论NULL在运算符的右侧还是左侧。

NULL值不包含在任何数据集中，彼此不对应，并且如果transform_null_in = 0则无法比较。

--含有null值的表t_null：
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘
 
SELECT x FROM t_null WHERE y IN (NULL,3)
┌─x─┐
│ 2 │
└───┘
--可以看出 y = NULL的值被舍去，因为clickhouse不能判断NULL是不是包含在(NULL,3)中
 
SELECT y IN (NULL, 3) FROM t_null
┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

分布式子查询

子查询中的in有两个选项（join也是）：IN / JOIN 和 GLOBAL IN / GLOBAL JOIN

两种方式在分布式子查询中的运行方式不同。

注意，distributed_product_mode不同，以下算法可能会不同。

当使用 IN / JOIN 时，查询会被发送到远程服务器，并且每个服务器都在IN或JOIN子句中运行子查询。

当使用 GLOBAL IN / GLOBAL JOIN 时，首先会全局运行子查询生成临时表，然后将临时表发送到每个远程服务器，并在使用临时表运行查询。

对于非分布式查询，请使用常规的IN / JOIN。在IN / JOIN子句中使用子查询进行分布式查询处理时要小心。

对分布式表的查询，查询将被发送到所有远程服务器，并使用local_table在各个机子上运行。

SELECT uniq(UserID) FROM distributed_table
--将被作为以下语句发送到远程服务器
SELECT uniq(UserID) FROM local_table
--在每个远程节点分别计算，然后将结果返回请求服务器，并进行合并。
 
--考虑下面查询，计算两个网站的受众交集
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
--将被作为以下语句发送到远程服务器
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
--在每台服务器单独执行，并返回结果，将结果进行合并
 
--若要更正当数据在群集服务器中随机分布时查询的工作方式，可以在子查询中指定分布式表。 查询如下所示：
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
--将被作为以下语句发送到远程服务器
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
--子查询将在每个服务器上执行。由于子查询使用分布式表，每个服务器上的子查询将重新发到所有服务器，使用语句如下。
SELECT UserID FROM local_table WHERE CounterID = 34
--按照这种方式，如果有100个服务器，执行完这个查询，将要执行10000次请求，不可接受。
--为了处理这种情况，应该使用GLOBAL IN
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
--请求者服务器将会运行以下子查询，并将结果存储在RAM中，保存为临时表_data1：
SELECT UserID FROM distributed_table WHERE CounterID = 34
--之后会将查询连临时表一起发送到远程服务器：
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

这样会比使用普通的IN/JOIN更好。

注意以下几点：

1.子查询创建的临时表里数据不是唯一的，可能会浪费网络带宽，可以指定DISTINCT

2.临时表将被发送到所有远程服务器。传输不考虑网络拓扑。例如，如果有10个服务器位于与请求者服务器相距非常远的数据中心，clickhouse还是会发送10次到远程数据中心。使用global in时，应尽量避免使用大数据集合

3.将数据传输到远程服务器时，对网络带宽的限制是不可配置的。您可能会使网络超载。

4.可以尝试在服务器之间合理的分配数据，这样可以减少使用GLOBAL IN

5.如果您需要经常使用GLOBAL IN，请规划ClickHouse群集的位置，单个副本组放在一个数据中心中，且他们之间有高速网络，这样一个global in 查询可以在一个数据中心中完成。

在GLOBAL IN中指定本地表也很有意义，如果IN的表只在本地存在，远程服务器里没有，那么可以指定本地表。