跳至主要内容
跳至主要内容
编辑此页

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 函数应用于右侧一样。

这意味着数据类型变为 可为空，如果无法执行转换，则返回 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 是否位于操作符的右侧或左侧。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 JOIN 时，首先运行所有 GLOBAL IN / GLOBAL 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 完全位于单个服务器上的方式分布数据，这将有效且最佳。在这种情况下，所有必要的数据都将本地可用在每个服务器上。否则，结果将不准确。我们将这种查询变体称为“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

发送到每个远程服务器（临时表的名称由实现定义）。

这比使用常规 IN 更有效。但是，请记住以下几点

  1. 创建临时表时，数据不会变得唯一。为了减少通过网络传输的数据量，请在子查询中指定 DISTINCT。（对于常规 IN，您不需要这样做。）
  2. 临时表将发送到所有远程服务器。传输不考虑网络拓扑。例如，如果 10 个远程服务器位于与请求服务器非常遥远的某个数据中心，则数据将 10 次通过通道发送到远程数据中心。避免在 GLOBAL IN 中使用大型数据集。
  3. 在将数据传输到远程服务器时，无法配置网络带宽限制。您可能会使网络过载。
  4. 尝试在服务器上分布数据，以便您不需要经常使用 GLOBAL IN。
  5. 如果您需要经常使用 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 产生相同的影响。

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

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

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

    © . This site is unofficial and not affiliated with ClickHouse, Inc.