如何通过数组列过滤 ClickHouse 表？

关于如何通过数组列过滤 ClickHouse 表的知识库文章。

简介

通过数组列过滤 ClickHouse 表是一项常见任务，该产品提供了许多函数来处理数组列。

在本文中，我们将重点介绍通过数组列过滤表，但下面的视频涵盖了许多其他与数组相关的功能

示例

我们将使用一个包含两列 tags_string 和 tags_int 的表的示例，这两列分别包含字符串数组和整数数组。

• 创建一个示例数据库和表。

CREATE DATABASE db1;

• 创建一个示例表

CREATE TABLE db1.tags_table
(
    `id` UInt64,
    `tags_string` Array(String),
    `tags_int` Array(UInt64),
)
ENGINE = MergeTree
ORDER BY id;

• 将一些示例数据插入到表中。

INSERT INTO db1.tags_table VALUES (1, ['tag1', 'tag2', 'tag3'], [1, 2, 3]), (2, ['tag2', 'tag3', 'tag4'], [2, 3, 4]), (3, ['tag1', 'tag3', 'tag5'], [1, 3, 5]);

使用 has(arr, elem) 函数过滤表，以返回 arr 数组包含 elem 元素的行。

过滤表以返回 tags_string 数组包含 tag1 元素的行。

SELECT * FROM db1.tags_table WHERE has(tags_string, 'tag1');

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

使用 hasAll(arr, elems) 函数返回 elems 数组中的所有元素都存在于 arr 数组中的行。

过滤表以返回 tags_string 数组中的所有元素都存在于 ['tag1', 'tag2'] 数组中的行。

SELECT * FROM db1.tags_table WHERE hasAll(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
└────┴────────────────────────┴──────────┘

使用 hasAny(arr, elems) 函数返回 elems 数组中至少一个元素存在于 arr 数组中的行。

过滤表以返回 tags_string 数组中至少一个元素存在于 ['tag1', 'tag2'] 数组中的行。

SELECT * FROM db1.tags_table WHERE hasAny(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

我们可以使用 lambda 函数和 arrayExists(lambda, arr) 函数来过滤表。

过滤表以返回 tags_int 数组中至少一个元素大于 3 的行。

SELECT * FROM db1.tags_table WHERE arrayExists(x -> x > 3, tags_int);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘