IN 运算符

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)。当外部数据随查询一起发送时使用此方法。例如，查询可以与加载到users临时表的一组user IDs一起发送，该临时表应该被过滤。

如果运算符的右边是一个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子句中的子查询总是只在单个服务器上运行一次。没有依赖子查询。

NULL处理

在请求处理期间，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有两种选项（类似于JOIN）：常规的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类型的distributed_table表，集群中所有服务器都可以看到。

查询distributed_table，查询将被发送到所有远程服务器，并使用local_table在远程服务器上运行。

例如，查询

SELECT uniq(UserID) FROM distributed_table

将以下列SQL发往远程服务器

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子句中的数据集将在每个服务器上单独采集，所有数据本地存储在每个服务器上。

如果您已经为这种情况做好了准备，并且已经将数据分布到整个集群服务器上，这样单个UserID的数据就完全驻留在单个服务器上，那么这种方法将能够正确和最佳地工作。在这种情况下，所有必要的数据在每个服务器上本地可用。否则，结果将不准确。我们将查询的这种变体称为“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

将结果放在内存中的一个临时表中。然后请求将以如下形式发送到每个远程服务器

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

临时表_data1会和查询一起发送到每个远程服务器（临时表的名称是由实现定义的）。

这比使用正常的IN更优。不过，请记住以下几点：

1.在创建临时表时，数据不是惟一的。若要减少通过网络传输的数据量，在子查询中指定DISTINCT。（你不需要为一个正常的IN做这个。）
2.临时表将被发送到所有远程服务器。传输不考虑网络拓扑。例如，如果一个数据中心中有了10台远程服务器，而该数据中心相对于请求者服务器来说非常遥远，那么数据将通过通道发送10次到远程数据中心。在使用GLOBAL IN时，尽量避免使用大型数据集。
3.在向远程服务器传输数据时，网络带宽的限制是不可配置的。可能会使网络过载。
4.尝试将数据分布到整个集群服务器，这样就不需要固定使用GLOBAL IN。
5.如果需要经常使用GLOBAL IN，需要规划ClickHouse集群的位置，以便单个副本组不会跨数据中心中，它们之间有一个快速的网络，以便查询可以完全在单个数据中心内处理。

如果这个本地表仅在请求服务器上可用，并且希望在远程服务器上使用它的数据，那么在GLOBAL IN子句中指定本地表也是合理的。

「如果这篇文章对你有用，请支持一下哦」

ClickHouse官方文档翻译_SQL参考_IN运算符

TOC

IN 运算符

NULL处理

分布式子查询

FEATURED TAGS

FRIENDS