Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

ClickHouse 数组使用指南

View as MarkdownEdit this pageReport an issueSet up docs MCP

在本指南中，你将了解如何在 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 中找到这个数据集。

我们选择这个数据集，是因为数组通常非常适合处理时间序列数据，并且有助于简化 原本较为复杂的查询。

groupArray

这个数据集中有很多列，但我们将重点关注其中的部分列。 运行下面的查询，看看数据是什么样的：

-- SELECT
-- *
-- FROM ontime.ontime LIMIT 100

SELECT
    FlightDate,
    Origin,
    OriginCityName,
    Dest,
    DestCityName,
    DepTime,
    DepDelayMinutes,
    ArrTime,
    ArrDelayMinutes
FROM ontime.ontime LIMIT 5

我们来看一下美国在某个随机选定的日期 (例如 '2024-01-01') 最繁忙的 10 个机场。 我们想了解每个机场有多少架航班起飞。 数据中每个航班对应一行，但如果能按始发机场对数据进行分组，并将目的地机场汇总到一个数组中，会更方便。

为此，我们可以使用 groupArray 聚合函数。它会从每一行中提取指定列的值，并将这些值分组到一个数组中。

运行下面的查询，看看它是如何工作的：

SELECT
    FlightDate,
    Origin,
    groupArray(toStringCutToZero(Dest)) AS Destinations
FROM ontime.ontime
WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA') AND FlightDate='2024-01-01'
GROUP BY FlightDate, Origin
ORDER BY length(Destinations)

上述查询中的 toStringCutToZero 用于去除某些机场三字码后面出现的空字符。

在这种数据格式下，我们可以通过查看汇总后 “Destinations” 数组的长度，轻松找出最繁忙机场的顺序：

WITH
    '2024-01-01' AS date,
    busy_airports AS (
    SELECT
    FlightDate,
    Origin,
    groupArray(toStringCutToZero(Dest)) AS Destinations
    FROM ontime.ontime
    WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA')
    AND FlightDate = date
    GROUP BY FlightDate, Origin
    ORDER BY length(Destinations)
    )
SELECT
    Origin,
    length(Destinations) AS outward_flights
FROM busy_airports
ORDER BY outward_flights DESC

arrayMap 和 arrayZip

我们在前一个查询中看到，在所选的这一天，Denver International Airport 是出港航班最多的机场。 下面来看看这些航班中，有多少是准点的、延误 15–30 分钟的，或者延误超过 30 分钟的。

ClickHouse 中的许多数组函数都是所谓的"高阶函数"，并且将 lambda 函数作为第一个参数。 arrayMap 函数就是这样一种高阶函数：它对原数组中的每个元素应用 lambda 函数，并基于给定数组返回一个新数组。

运行下面的查询。该查询使用 arrayMap 函数来查看哪些航班延误、哪些航班准点。 对于每组起点/目的地组合，它会显示每个航班的机尾编号和状态：

WITH arrayMap(
              d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME')),
              groupArray(DepDelayMinutes)
    ) AS statuses

SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    arrayZip(groupArray(Tail_Number), statuses) as tailNumberStatuses
FROM ontime.ontime
WHERE Origin = 'DEN'
  AND FlightDate = '2024-01-01'
  AND DepTime IS NOT NULL
  AND DepDelayMinutes IS NOT NULL
GROUP BY ALL

在上面的查询中，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 分钟或以上的航班数量：

SELECT
    Origin,
    OriginCityName,
    length(arrayFilter(d -> d >= 30, groupArray(ArrDelayMinutes))) AS num_delays_30_min_or_more
FROM ontime.ontime
WHERE Origin IN ('DEN', 'ATL', 'DFW')
    AND FlightDate = '2024-01-01'
GROUP BY Origin, OriginCityName
ORDER BY num_delays_30_min_or_more DESC

在上面的查询中，我们将一个 Lambda 函数作为 arrayFilter 函数的第一个参数传入。 这个 Lambda 函数以延误分钟数 (d) 作为输入，满足条件时返回 1，否则返回 0。

d -> d >= 30

arraySort 和 arrayIntersect

接下来，我们将借助 arraySort 和 arrayIntersect 函数，找出哪些美国主要机场组合拥有最多的共同目的地。 arraySort 接收一个数组，默认按升序对元素进行排序，不过你也可以向它传入一个 lambda 函数 来定义排序顺序。 arrayIntersect 接收多个数组，并返回一个数组，其中包含所有这些数组中都存在的元素。

运行下面的查询，看看这两个数组函数的实际效果：

WITH airport_routes AS (
    SELECT 
        Origin,
        arraySort(groupArray(DISTINCT toStringCutToZero(Dest))) AS destinations
    FROM ontime.ontime
    WHERE FlightDate = '2024-01-01'
    GROUP BY Origin
)
SELECT 
    a1.Origin AS airport1,
    a2.Origin AS airport2,
    length(arrayIntersect(a1.destinations, a2.destinations)) AS common_destinations
FROM airport_routes a1
CROSS JOIN airport_routes a2
WHERE a1.Origin < a2.Origin
    AND a1.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
    AND a2.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
ORDER BY common_destinations DESC
LIMIT 10

这个查询主要分为两个阶段。 首先，它使用公用表表达式 (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，找出从丹佛国际机场出发的每条航线的平均延误和最大延误：

SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    groupArray(DepDelayMinutes) AS delays,
    round(arrayReduce('avg', groupArray(DepDelayMinutes)), 2) AS avg_delay,
    round(arrayReduce('max', groupArray(DepDelayMinutes)), 2) AS worst_delay
FROM ontime.ontime
WHERE Origin = 'DEN'
    AND FlightDate = '2024-01-01'
    AND DepDelayMinutes IS NOT NULL
GROUP BY Origin, Destination
ORDER BY avg_delay DESC

在上面的示例中，我们使用 arrayReduce 计算了从 DEN 出发的各条航线的平均延误和最大延误。 arrayReduce 会将函数第一个参数中指定的聚合函数应用到提供的数组元素上，而该数组由函数的第二个参数指定。

arrayJoin

ClickHouse 中的常规函数有一个特性：返回的行数与接收的行数相同。 不过，有一个有趣且独特的函数打破了这条规则，也很值得了解——arrayJoin 函数。

arrayJoin 会将数组“展开”，为数组中的每个元素生成单独的一行。 这类似于其他数据库中的 UNNEST 或 EXPLODE SQL 函数。

与大多数返回数组或标量值的 数组函数 不同，arrayJoin 会通过增加行数从根本上改变结果集。

看下面这个查询，它返回一个从 0 到 100、step 为 10 的值数组。 我们可以把这个数组看作不同的延误时长：0 分钟、10 分钟、20 分钟，依此类推。

WITH range(0, 100, 10) AS delay
SELECT delay

我们可以编写一个使用 arrayJoin 的查询，计算两个机场之间延误达到某个分钟数及以上的航班有多少。 下面的查询使用累计延误桶，创建一个直方图，展示 2024 年 1 月 1 日从 Denver (DEN) 到 Miami (MIA) 的航班延误分布：

WITH range(0, 100, 10) AS delay,
    toStringCutToZero(Dest) AS Destination

SELECT
    'Up to ' || arrayJoin(delay) || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= arrayJoin(delay)) AS flightsDelayed
FROM ontime.ontime
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY delayTime
ORDER BY flightsDelayed DESC

在上面的查询中，我们通过 CTE 子句 (WITH 子句) 返回一个延误数组。 Destination 会将目的地代码转换为字符串。

我们使用 arrayJoin 将延误数组展开成多行。 delay 数组中的每个值都会变成单独的一行，并使用别名 del， 因此会得到 10 行：一行对应 del=0，一行对应 del=10，一行对应 del=20，等等。 对于每个延误阈值 (del) ，查询会使用 countIf(DepDelayMinutes >= del)， 统计延误大于或等于该阈值的航班数量。

arrayJoin 还有一个等价的 SQL 命令：ARRAY JOIN。 下面用这个等价的 SQL 命令形式重写上述查询，便于对比：

WITH range(0, 100, 10) AS delay, 
     toStringCutToZero(Dest) AS Destination

SELECT    
    'Up to ' || del || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= del) flightsDelayed
FROM ontime.ontime
ARRAY JOIN delay AS del
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY ALL
ORDER BY flightsDelayed DESC

后续步骤

恭喜！你已经学会了如何在 ClickHouse 中使用数组，从基本的数组创建和索引，到 groupArray、arrayFilter、arrayMap、arrayReduce 和 arrayJoin 等强大函数。 要继续深入学习，请查阅完整的数组函数参考，了解更多函数，例如 arrayFlatten、arrayReverse 和 arrayDistinct。 你可能还想了解一些可与数组配合使用的相关数据结构，例如 元组、JSON 和 Map 类型。 请练习将这些概念应用到你自己的数据集中，并在 SQL playground 或其他示例数据集上尝试不同的查询。

数组是 ClickHouse 的一项基础特性，可实现高效的分析查询——随着你越来越熟悉数组函数，你会发现它们能够显著简化复杂的聚合和时间序列分析。 如果你想进一步了解数组的更多用法，我们推荐观看下面这段由我们的常驻数据专家 Mark 带来的 YouTube 视频：

Navigation