二十六、clcikhouse的in以及其他操作符

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

--1.下标运算符
a[N] – 数组中的第N个元素; 对应函数 arrayElement(a, N)

a.N – 元组中第N个元素; 对应函数 tupleElement(a, N)

--2.负号
-a – 对应函数 negate(a)

--3.乘号、除号和取余
a * b – 对应函数 multiply(a, b)

a / b – 对应函数 divide(a, b)

a % b – 对应函数 modulo(a, b)

--4.加号和减号
a + b – 对应函数 plus(a, b)

a - b – 对应函数 minus(a, b)

--5.关系运算符
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 BETWEEN b AND c – 等价于 a >= b AND a <= c

--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)

运算符的左侧是单列或元组。

例:

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

 

如果左侧是索引中的单列，而右侧是一组常量，则系统将使用索引处理查询。

请不要列举太多具体的常量 (比方说 几百万条)。如果数据集非常大，请把它放在一张临时表里（例如，参考章节用于查询处理的外部数据），然后使用子查询。

运算符的右侧可以是一组常量表达式、一组带有常量表达式的元组（如上面的示例所示），或括号中的数据库表或SELECT子查询的名称。

如果运算符的右侧是表的名称（例如, UserID IN users），这相当于子查询 UserID IN (SELECT * FROM users). 使用与查询一起发送的外部数据时，请使用此选项。 例如，查询可以与一组用户Id一起发送到 ‘users’ 应过滤的临时表。

如果运算符的右侧是具有Set引擎的表名（始终位于RAM中的准备好的数据集），则不会为每个查询重新创建数据集。

子查询可以指定多个用于筛选元组的列。
示例:

SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...

 

IN运算符左侧和右侧的列应具有相同的类型。

IN运算符和子查询可能出现在查询的任何部分，包括聚合函数和lambda函数。
示例:

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 │
└────────────┴──────────┘

 

对于3月17日后的每一天，计算3月17日访问该网站的用户所做的浏览量百分比。
IN子句中的子查询始终只在单个服务器上运行一次。 没有依赖子查询。

空处理
在请求处理过程中， IN 运算符假定运算的结果 NULL 总是等于 0，无论是否 NULL 位于操作员的右侧或左侧。 NULL 值不包含在任何数据集中，彼此不对应，并且在以下情况下无法进行比较 transform_null_in=0.

--下面是一个例子 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) 设置，返回 0 作为操作的结果，和 SELECT 从最终输出中排除此行。

SELECT y IN (NULL, 3)
FROM t_null
┌─in(y, tuple(NULL, 3))─┐
│ 0 │
│ 1 │
└───────────────────────┘

 

分布式子查询
带子查询的IN-s有两个选项（类似于连接）：normal IN / JOIN 和 GLOBAL IN / GLOBAL JOIN. 它们在分布式查询处理的运行方式上有所不同。

注意

请记住，下面描述的算法可能会有不同的工作方式取决于 设置 distributed_product_mode 设置。

当使用常规IN时，查询被发送到远程服务器，并且它们中的每个服务器都在运行子查询 IN 或 JOIN 条款

使用时 GLOBAL IN / GLOBAL JOINs，首先所有的子查询都运行 GLOBAL IN / GLOBAL JOINs，并将结果收集在临时表中。 然后将临时表发送到每个远程服务器，其中使用此临时数据运行查询。

对于非分布式查询，请使用常规 IN / JOIN.

在使用子查询时要小心 IN / JOIN 用于分布式查询处理的子句。

让我们来看看一些例子。 假设集群中的每个服务器都有一个正常的 local_table. 每个服务器还具有 distributed_table 表与 分布 类型，它查看群集中的所有服务器。

对于查询 distributed_table，查询将被发送到所有远程服务器，并使用以下命令在其上运行 local_table.

例如，查询

SELECT uniq(UserID) FROM distributed_table
--将被发送到所有远程服务器

SELECT uniq(UserID) FROM local_table
--并且并行运行它们中的每一个，直到达到可以结合中间结果的阶段。 然后将中间结果返回给请求者服务器并在其上合并，并将最终结果发送给客户端。

--现在让我们检查一个查询IN:

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)
--换句话说，IN子句中的数据集将在每台服务器上独立收集，仅在每台服务器上本地存储的数据中收集。

--如果您已经为此情况做好准备，并且已经将数据分散到群集服务器上，以便单个用户Id的数据完全驻留在单个服务器上，则这将正常和最佳地工作。 在这种情况下，所有必要的数据将在每台服务器上本地提供。 否则，结果将是不准确的。 我们将查询的这种变体称为 “local IN”.

--若要更正数据在群集服务器上随机传播时查询的工作方式，可以指定 distributed_table 在子查询中。 查询如下所示:

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台服务器的集群，则执行整个查询将需要10,000个基本请求，这通常被认为是不可接受的。

--在这种情况下，应始终使用GLOBAL IN而不是IN。 让我们来看看它是如何工作的查询

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
--请求者服务器将运行子查询

SELECT UserID FROM distributed_table WHERE CounterID = 34
--结果将被放在RAM中的临时表中。 然后请求将被发送到每个远程服务器

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1
--和临时表 _data1 将通过查询发送到每个远程服务器（临时表的名称是实现定义的）。

 

这比使用正常IN更优化。 但是，请记住以下几点:

创建临时表时，数据不是唯一的。 要减少通过网络传输的数据量，请在子查询中指定DISTINCT。 （你不需要为正常人做这个。)
临时表将被发送到所有远程服务器。 传输不考虑网络拓扑。 例如，如果10个远程服务器驻留在与请求者服务器非常远程的数据中心中，则数据将通过通道发送10次到远程数据中心。 使用GLOBAL IN时尽量避免使用大型数据集。
将数据传输到远程服务器时，无法配置网络带宽限制。 您可能会使网络过载。
尝试跨服务器分发数据，以便您不需要定期使用GLOBAL IN。
如果您需要经常使用GLOBAL IN，请规划ClickHouse集群的位置，以便单个副本组驻留在不超过一个数据中心中，并且它们之间具有快速网络，以便可以完全在单个数据中心内处理查询。
这也是有意义的，在指定一个本地表 GLOBAL IN 子句，以防此本地表仅在请求者服务器上可用，并且您希望在远程服务器上使用来自它的数据。

--7.逻辑非
NOT a – 对应函数 not(a)

--8.逻辑与
a AND b – 对应函数and(a, b)

--9.逻辑或
a OR b – 对应函数 or(a, b)

--10.条件运算符
a ? b : c – 对应函数 if(a, b, c)

注意:

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

使用日期和时间的操作员
EXTRACT
EXTRACT(part FROM date);
从给定日期中提取部件。 例如，您可以从给定日期检索一个月，或从时间检索一秒钟。

该 part 参数指定要检索的日期部分。 以下值可用:

DAY — The day of the month. Possible values: 1–31.
MONTH — The number of a month. Possible values: 1–12.
YEAR — The year.
SECOND — The second. Possible values: 0–59.
MINUTE — The minute. Possible values: 0–59.
HOUR — The hour. Possible values: 0–23.
该 part 参数不区分大小写。

该 date 参数指定要处理的日期或时间。 无论是 日期 或 日期时间 支持类型。

例:

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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘
--你可以看到更多的例子 测试.

INTERVAL
--创建一个 间隔-应在算术运算中使用的类型值 日期 和 日期时间-类型值。

--示例:

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))─┐
│ 2019-10-23 11:16:28 │ 2019-10-27 14:16:28 │
└─────────────────────┴────────────────────────────────────────────────────────┘

 

间隔 数据类型
toInterval 类型转换函数
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.连接运算符
s1 || s2 – 对应函数 concat(s1, s2)

--12.创建 Lambda 函数
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 运算符会转换成其对应的单个函数。

--13.判断是否为 NULL
ClickHouse 支持 IS NULL 和 IS NOT NULL 。

--14.IS NULL
对于 可为空 类型的值， IS NULL 会返回：
1 值为 NULL
0 否则
对于其他类型的值， IS NULL 总会返回 0

:) SELECT x+100 FROM t_null WHERE y IS NULL

SELECT x + 100
FROM t_null
WHERE isNull(y)

┌─plus(x, 100)─┐
│ 101 │
└──────────────┘

1 rows in set. Elapsed: 0.002 sec.

 

--15.IS NOT NULL
对于 可为空 类型的值， IS NOT NULL 会返回：
0 值为 NULL
1 否则
对于其他类型的值，IS NOT NULL 总会返回 1

:) SELECT * FROM t_null WHERE y IS NOT NULL

SELECT *
FROM t_null
WHERE isNotNull(y)

┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

1 rows in set. Elapsed: 0.002 sec.