在本指南中，你将了解如何在 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 视频：