如何通过数组列过滤 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) 函数，返回 arr 数组中包含 elems 数组所有元素的行。

过滤该表，返回 ['tag1', 'tag2'] 数组中包含 tags_string 数组所有元素的行。

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) 函数，返回 arr 数组中存在 elems 数组里至少一个元素的行。

对表进行过滤，返回 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 function，通过 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]  │
└────┴────────────────────────┴──────────┘