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 子查询。

ClickHouse 允许 IN 子查询的左侧和右侧部分类型不同。在这种情况下，它会将左侧的值转换为右侧的类型，就像应用了 accurateCastOrNull 函数一样。这意味着，数据类型变为 Nullable，如果无法执行转换，则返回 NULL。

示例

查询

SELECT '1' IN (SELECT 1);

结果

┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘

如果运算符的右侧是表名（例如，UserID IN users），则等效于子查询 UserID IN (SELECT * FROM users)。当使用随查询一起发送的外部数据时，请使用此选项。例如，查询可以与加载到 ‘users’ 临时表中的一组用户 ID 一起发送，这些用户 ID 应该被过滤。

如果运算符的右侧是具有 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 在运算符的右侧还是左侧。如果 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) 集中，返回 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 JOIN 时，首先会为 GLOBAL IN / GLOBAL JOIN 运行所有子查询，并将结果收集在临时表中。然后，临时表将发送到每个远程服务器，在这些服务器上，将使用此临时数据运行查询。

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

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

让我们看一些示例。假设集群中的每个服务器都有一个普通的 local_table。每个服务器还有一个 distributed_table 表，其类型为 Distributed，它指向集群中的所有服务器。

对于 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 子句中的数据集将在每个服务器上独立收集，仅跨存储在每个服务器本地的数据。

如果您为此情况做好准备，并将数据分散在集群服务器上，以便单个 UserID 的数据完全驻留在单个服务器上，则这将正确且最佳地工作。在这种情况下，所有必要的数据将在每个服务器本地可用。否则，结果将不准确。我们将查询的这种变体称为“本地 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。（对于普通 IN，您无需执行此操作。）
临时表将发送到所有远程服务器。传输不考虑网络拓扑。例如，如果 10 个远程服务器驻留在一个相对于请求服务器非常远的远程数据中心，则数据将通过通道 10 次发送到远程数据中心。尝试在使用 GLOBAL IN 时避免大型数据集。
将数据传输到远程服务器时，网络带宽限制是不可配置的。您可能会使网络过载。
尝试将数据分散在服务器上，以便您无需经常使用 GLOBAL IN。
如果您需要经常使用 GLOBAL IN，请规划 ClickHouse 集群的位置，以便单个副本组最多驻留在一个数据中心内，并且它们之间具有快速网络，以便查询可以在单个数据中心内完全处理。

在 GLOBAL IN 子句中指定本地表也是有意义的，以防此本地表仅在请求服务器上可用，并且您希望在远程服务器上使用来自它的数据。

分布式子查询和 max_rows_in_set

您可以使用 max_rows_in_set 和 max_bytes_in_set 来控制在分布式查询期间传输多少数据。

如果 GLOBAL IN 查询返回大量数据，这一点尤其重要。考虑以下 SQL

select * from table1 where col1 global in (select col1 from table2 where <some_predicate>)

如果 some_predicate 的选择性不够好，它将返回大量数据并导致性能问题。在这种情况下，明智的做法是限制通过网络的数据传输。另请注意，set_overflow_mode 默认设置为 throw，这意味着当达到这些阈值时会引发异常。

分布式子查询和 max_parallel_replicas

当 max_parallel_replicas 大于 1 时，分布式查询将进一步转换。

例如，以下内容

SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3

在每个服务器上转换为

SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M

其中 M 在 1 到 3 之间，具体取决于本地查询正在哪个副本上执行。

这些设置会影响查询中的每个 MergeTree 系列表，并且具有与在每个表上应用 SAMPLE 1/3 OFFSET (M-1)/3 相同的效果。

因此，只有当两个表具有相同的复制方案，并且按 UserID 或其子键进行采样时，添加 max_parallel_replicas 设置才会产生正确的结果。特别是，如果 local_table_2 没有采样键，则会产生不正确的结果。相同的规则适用于 JOIN。

如果 local_table_2 不符合要求，一种解决方法是使用 GLOBAL IN 或 GLOBAL JOIN。

如果表没有采样键，则可以使用更灵活的 parallel_replicas_custom_key 选项，这些选项可以产生不同且更优化的行为。

NULL 处理​

分布式子查询​

分布式子查询和 max_rows_in_set​

分布式子查询和 max_parallel_replicas​

NULL 处理

分布式子查询

分布式子查询和 max_rows_in_set

分布式子查询和 max_parallel_replicas