ClickHouse 数组使用指南 - ClickHouse Documentation

在本指南中，你将了解如何在 ClickHouse 中使用数组，以及一些最常用的数组函数。

数组简介

数组是一种内存中的数据结构，用于将多个值组织在一起。我们将这些值称为数组的元素，每个元素都可以通过索引来引用，索引表示该元素在这一组值中的位置。 ClickHouse 中的数组可以使用 array 函数构造：

array(T)

或者，也可以使用 []：

[]

例如，您可以创建一个由数字组成的数组：

SELECT array(1, 2, 3) AS numeric_array

┌─numeric_array─┐
│ [1,2,3]       │
└───────────────┘

或者，一个由 String 组成的数组：

SELECT array('hello', 'world') AS string_array

┌─string_array──────┐
│ ['hello','world'] │
└───────────────────┘

或者是嵌套类型的数组，例如元组：

SELECT array(tuple(1, 2), tuple(3, 4))

┌─[(1, 2), (3, 4)]─┐
│ [(1,2),(3,4)]    │
└──────────────────┘

你可能会想要像这样创建一个包含不同类型的数组：

SELECT array('Hello', 'world', 1, 2, 3)

不过，数组元素始终应具有一个共同超类型，即能够无损表示两种或多种不同类型的值的最小数据类型，这样它们才能一起使用。如果没有共同超类型，尝试构造数组时就会引发异常：

Received exception:
Code: 386. DB::Exception: There is no supertype for types String, String, UInt8, UInt8, UInt8 because some of them are String/FixedString/Enum and some of them are not: In scope SELECT ['Hello', 'world', 1, 2, 3]. (NO_COMMON_TYPE)

在即时创建数组时，ClickHouse 会选择能够兼容所有元素的最窄类型。例如，如果你创建一个同时包含整数和浮点数的数组，则会选择浮点数的超类型：

SELECT [1::UInt8, 2.5::Float32, 3::UInt8] AS mixed_array, toTypeName([1, 2.5, 3]) AS array_type;

┌─mixed_array─┬─array_type─────┐
│ [1,2.5,3]   │ Array(Float64) │
└─────────────┴────────────────┘

创建不同类型的数组

你可以使用 use_variant_as_common_type 设置来更改上文所述的默认行为。这样一来，当参数类型没有公共类型时，你可以将 Variant 类型用作 if/multiIf/array/map 函数的结果类型。例如：

SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    toTypeName(array)
SETTINGS use_variant_as_common_type = 1;

┌─array────────────────────────────────┬─toTypeName(array)────────────────────────────┐
│ [1,'ClickHouse',['Another','Array']] │ Array(Variant(Array(String), String, UInt8)) │
└──────────────────────────────────────┴──────────────────────────────────────────────┘

然后，你还可以按类型名称从数组中读取对应类型的值：

SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    array.UInt8,
    array.String,
    array.`Array(String)`
SETTINGS use_variant_as_common_type = 1;

┌─array────────────────────────────────┬─array.UInt8───┬─array.String─────────────┬─array.Array(String)─────────┐
│ [1,'ClickHouse',['Another','Array']] │ [1,NULL,NULL] │ [NULL,'ClickHouse',NULL] │ [[],[],['Another','Array']] │
└──────────────────────────────────────┴───────────────┴──────────────────────────┴─────────────────────────────┘

使用 [] 索引是一种便捷的数组元素访问方式。在 ClickHouse 中，需要特别注意的是，数组索引始终从 1 开始。这可能与你熟悉的其他编程语言不同，因为那些语言中的数组通常是从零开始索引的。例如，给定一个数组，你可以这样写来选取数组的第一个元素：

WITH array('hello', 'world') AS string_array
SELECT string_array[1];

┌─arrayElement⋯g_array, 1)─┐
│ hello                    │
└──────────────────────────┘

也可以使用负索引。这样，你就可以相对于最后一个元素来选择元素：

WITH array('hello', 'world') AS string_array
SELECT string_array[-1];

┌─arrayElement⋯g_array, -1)─┐
│ world                     │
└───────────────────────────┘

尽管数组的索引从 1 开始，你仍然可以访问位置 0 的元素。返回的将是该数组类型的默认值。在下面的示例中，返回的是空字符串，因为这是 String 类型的默认值：

WITH ['hello', 'world', 'arrays are great aren\'t they?'] AS string_array
SELECT string_array[0]

┌─arrayElement⋯g_array, 0)─┐
│                          │
└──────────────────────────┘

数组函数

ClickHouse 提供了许多用于处理数组的实用函数。本节将介绍其中一些最常用的函数，从最简单的开始，逐步深入到更复杂的用法。

length、arrayEnumerate、indexOf、has* 函数

length 函数用于返回数组中的元素个数：

WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT length(string_array);

┌─length(string_array)─┐
│                    3 │
└──────────────────────┘

你也可以使用 arrayEnumerate 函数，返回由各元素索引组成的数组：

WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT arrayEnumerate(string_array);

┌─arrayEnumerate(string_array)─┐
│ [1,2,3]                      │
└──────────────────────────────┘

如果你想查找特定值的索引，可以使用 indexOf 函数：

SELECT indexOf([4, 2, 8, 8, 9], 8);

┌─indexOf([4, 2, 8, 8, 9], 8)─┐
│                           3 │
└─────────────────────────────┘

请注意，如果数组中有多个相同的值，此函数会返回它遇到的第一个索引。如果数组元素已按升序排序，则可以使用 indexOfAssumeSorted 函数。函数 has、hasAll 和 hasAny 可用于判断数组是否包含给定值。请看下面的示例：

WITH ['Airbus A380', 'Airbus A350', 'Airbus A220', 'Boeing 737', 'Boeing 747-400'] AS airplanes
SELECT
    has(airplanes, 'Airbus A350') AS has_true,
    has(airplanes, 'Lockheed Martin F-22 Raptor') AS has_false,
    hasAny(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAny_true,
    hasAny(airplanes, ['Lockheed Martin F-22 Raptor', 'Eurofighter Typhoon']) AS hasAny_false,
    hasAll(airplanes, ['Boeing 737', 'Boeing 747-400']) AS hasAll_true,
    hasAll(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAll_false
FORMAT Vertical;

has_true:     1
has_false:    0
hasAny_true:  1
hasAny_false: 0
hasAll_true:  1
hasAll_false: 0

使用数组函数探索航班数据

到目前为止，这些示例都比较简单。而数组的真正价值，在处理真实世界的数据集时才会充分体现出来。我们将使用 ontime dataset，其中包含来自美国交通统计局 (Bureau of Transportation Statistics) 的航班数据。你可以在 SQL playground 中找到这个数据集。我们选择这个数据集，是因为数组通常非常适合处理时间序列数据，并且有助于简化原本较为复杂的查询。

点击下方的“play”按钮，可直接在文档中运行这些查询并实时查看结果。

groupArray

这个数据集中有很多列，但我们将重点关注其中的部分列。运行下面的查询，看看数据是什么样的：我们来看一下美国在某个随机选定的日期 (例如 '2024-01-01') 最繁忙的 10 个机场。我们想了解每个机场有多少架航班起飞。数据中每个航班对应一行，但如果能按始发机场对数据进行分组，并将目的地机场汇总到一个数组中，会更方便。为此，我们可以使用 groupArray 聚合函数。它会从每一行中提取指定列的值，并将这些值分组到一个数组中。运行下面的查询，看看它是如何工作的：上述查询中的 toStringCutToZero 用于去除某些机场三字码后面出现的空字符。在这种数据格式下，我们可以通过查看汇总后 “Destinations” 数组的长度，轻松找出最繁忙机场的顺序：

arrayMap 和 arrayZip

我们在前一个查询中看到，在所选的这一天，Denver International Airport 是出港航班最多的机场。下面来看看这些航班中，有多少是准点的、延误 15–30 分钟的，或者延误超过 30 分钟的。 ClickHouse 中的许多数组函数都是所谓的”高阶函数”，并且将 lambda 函数作为第一个参数。 arrayMap 函数就是这样一种高阶函数：它对原数组中的每个元素应用 lambda 函数，并基于给定数组返回一个新数组。运行下面的查询。该查询使用 arrayMap 函数来查看哪些航班延误、哪些航班准点。对于每组起点/目的地组合，它会显示每个航班的机尾编号和状态：在上面的查询中，arrayMap 函数接收一个单元素数组 [DepDelayMinutes]，并应用 lambda 函数 d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME' 对其进行分类。然后再用 [DepDelayMinutes][1] 提取结果数组中的第一个元素。 arrayZip 函数会将 Tail_Number 数组和 statuses 数组合并成一个数组。

arrayFilter

接下来，我们只看 DEN、ATL 和 DFW 这几个机场中延误 30 分钟或以上的航班数量：在上面的查询中，我们将一个 Lambda 函数作为 arrayFilter 函数的第一个参数传入。这个 Lambda 函数以延误分钟数 (d) 作为输入，满足条件时返回 1，否则返回 0。

d -> d >= 30

arraySort 和 arrayIntersect

接下来，我们将借助 arraySort 和 arrayIntersect 函数，找出哪些美国主要机场组合拥有最多的共同目的地。 arraySort 接收一个数组，默认按升序对元素进行排序，不过你也可以向它传入一个 lambda 函数来定义排序顺序。 arrayIntersect 接收多个数组，并返回一个数组，其中包含所有这些数组中都存在的元素。运行下面的查询，看看这两个数组函数的实际效果：这个查询主要分为两个阶段。首先，它使用公用表表达式 (CTE) 创建一个名为 airport_routes 的临时数据集，查看 2024 年 1 月 1 日的所有航班，并为每个始发机场构建一个排好序的唯一目的地列表，也就是该机场所服务的每一个不同目的地。例如，在 airport_routes 结果集中，DEN 可能有一个数组，包含它飞往的所有城市，比如 ['ATL', 'BOS', 'LAX', 'MIA', ...] 等等。在第二阶段，查询选取五个美国主要枢纽机场 (DEN、ATL、DFW、ORD 和 LAS) ，并比较它们所有可能的两两组合。它通过 cross join 来实现这一点，这会生成这些机场的所有组合。然后，对于每一对机场，它使用 arrayIntersect 函数找出同时出现在两个机场列表中的目的地。 length 函数则用于统计它们共有多少个目的地。条件 a1.Origin < a2.Origin 可确保每一对机场只出现一次。如果没有这个条件，你会同时得到 JFK-LAX 和 LAX-JFK 这两条单独结果，而这是冗余的，因为它们表示的是同一次比较。最后，查询会对结果进行排序，显示哪些机场组合拥有最多的共同目的地，并只返回前 10 条。这揭示了哪些主要枢纽的航线网络重叠最多，这可能意味着多家航空公司正在同一城市对之间竞争，也可能意味着这些枢纽服务于相似的地理区域，并可能作为旅客中转时的替代连接点。

arrayReduce

在查看延误情况时，我们再使用一个高阶数组函数 arrayReduce，找出从丹佛国际机场出发的每条航线的平均延误和最大延误：在上面的示例中，我们使用 arrayReduce 计算了从 DEN 出发的各条航线的平均延误和最大延误。 arrayReduce 会将函数第一个参数中指定的聚合函数应用到提供的数组元素上，而该数组由函数的第二个参数指定。

arrayJoin

ClickHouse 中的常规函数有一个特性：返回的行数与接收的行数相同。不过，有一个有趣且独特的函数打破了这条规则，也很值得了解——arrayJoin 函数。 arrayJoin 会将数组“展开”，为数组中的每个元素生成单独的一行。这类似于其他数据库中的 UNNEST 或 EXPLODE SQL 函数。与大多数返回数组或标量值的数组函数不同，arrayJoin 会通过增加行数从根本上改变结果集。看下面这个查询，它返回一个从 0 到 100、step 为 10 的值数组。我们可以把这个数组看作不同的延误时长：0 分钟、10 分钟、20 分钟，依此类推。我们可以编写一个使用 arrayJoin 的查询，计算两个机场之间延误达到某个分钟数及以上的航班有多少。下面的查询使用累计延误桶，创建一个直方图，展示 2024 年 1 月 1 日从 Denver (DEN) 到 Miami (MIA) 的航班延误分布：在上面的查询中，我们通过 CTE 子句 (WITH 子句) 返回一个延误数组。 Destination 会将目的地代码转换为字符串。我们使用 arrayJoin 将延误数组展开成多行。 delay 数组中的每个值都会变成单独的一行，并使用别名 del，因此会得到 10 行：一行对应 del=0，一行对应 del=10，一行对应 del=20，等等。对于每个延误阈值 (del) ，查询会使用 countIf(DepDelayMinutes >= del)，统计延误大于或等于该阈值的航班数量。 arrayJoin 还有一个等价的 SQL 命令：ARRAY JOIN。下面用这个等价的 SQL 命令形式重写上述查询，便于对比：

后续步骤

恭喜！你已经学会了如何在 ClickHouse 中使用数组，从基本的数组创建和索引，到 groupArray、arrayFilter、arrayMap、arrayReduce 和 arrayJoin 等强大函数。要继续深入学习，请查阅完整的数组函数参考，了解更多函数，例如 arrayFlatten、arrayReverse 和 arrayDistinct。你可能还想了解一些可与数组配合使用的相关数据结构，例如 元组、JSON 和 Map 类型。请练习将这些概念应用到你自己的数据集中，并在 SQL playground 或其他示例数据集上尝试不同的查询。数组是 ClickHouse 的一项基础特性，可实现高效的分析查询——随着你越来越熟悉数组函数，你会发现它们能够显著简化复杂的聚合和时间序列分析。如果你想进一步了解数组的更多用法，我们推荐观看下面这段由我们的常驻数据专家 Mark 带来的 YouTube 视频：

​数组简介

​数组函数

​length、arrayEnumerate、indexOf、has* 函数

​使用数组函数探索航班数据

​groupArray

​arrayMap 和 arrayZip

​arrayFilter

​arraySort 和 arrayIntersect

​arrayReduce

​arrayJoin

​后续步骤

数组简介

数组函数

length、arrayEnumerate、indexOf、has* 函数

使用数组函数探索航班数据

groupArray

arrayMap 和 arrayZip

arrayFilter

arraySort 和 arrayIntersect

arrayReduce

arrayJoin

后续步骤