Array functions（数组函数）¶

This topic highlights array functions that you may want to use in expressions in Contour.

About arrays in Contour¶

You should note that arrays are only supported in the Expressions board in Contour. After working with arrays in column expressions, you should use array_join to convert the resulting array to a string so you can use it in other Contour boards.

Array length¶

You can use the array_length function to get the length of an array. This can be helpful when filtering. For example, if you have an array column items_array, you can use the Filter expression board with the array_length function to get the length of the array, as indicated below.

array_length(items_array) > 0

Joining values¶

You can use the array_agg function to combine the values in a column. Say you have a table of purchases made. You want to create a new column with an array of all items purchased by a particular customer. If your table looks like the following:

customer_id	item
123	bread
123	eggs
444	milk
444	bananas
444	bread

You can create a new column with the joined values with the following function:

array_join( array_agg("item") OVER (PARTITION BY "customer_id"), ', ' )

To break this into its parts:

array_agg returns an array of all the values in a given column. We’ve given it a window function as the column argument. So instead of

array_agg("item")

which would aggregate all values in the column, we have

array_agg("item") OVER (PARTITION BY "customer_id")

which will aggregate the values by customer_id.

array_agg_distinct returns an array of all distinct values in a given column. Unlike array_agg, this function ensures that each value appears only once in the resulting array.
array_join is a transform function and joins the items in an array into a string, separated by a delimiter. Generically, this looks like:

array_join(<array>, <delimiter>)

So [milk, bananas, bread] becomes "milk, bananas, bread".

The resulting column, items_array, will look like this:

customer_id	items_array
123	eggs, bread
444	milk, bananas, bread

Exploding arrays¶

If you already have a column mapping a primary key to an array of values, you can break these out using the explode function, which will create a new row for each value in the array, so given:

customer_id	items_array
123	eggs, bread
444	milk, bananas, bread

Open the Expression Editor and choose 'Replace column'. The code explode(items_array) will result in the following table:

customer_id	items_array
123	eggs
123	bread
444	milk
444	bananas
444	bread

Note that the explode function will drop nulls. To keep nulls, use the explode_outer function.

中文翻译¶

数组函数¶

本主题介绍您可能在 Contour 的表达式中使用的数组函数（array functions）。

关于 Contour 中的数组¶

请注意，数组仅在 Contour 的表达式面板中受支持。在列表达式中处理数组后，应使用 array_join 将生成的数组转换为字符串，以便在其他 Contour 面板中使用。

数组长度¶

您可以使用 array_length 函数获取数组的长度。这在筛选数据时非常有用。例如，如果您有一个数组列 items_array，可以使用筛选表达式面板配合 array_length 函数来获取数组的长度，如下所示：

array_length(items_array) > 0

合并值¶

您可以使用 array_agg 函数合并列中的值。假设您有一个购买记录表，想要创建一个新列，其中包含特定客户购买的所有商品数组。如果您的表格如下所示：

customer_id	item
123	bread
123	eggs
444	milk
444	bananas
444	bread

您可以使用以下函数创建一个包含合并值的新列：

array_join( array_agg("item") OVER (PARTITION BY "customer_id"), ', ' )

分解说明如下：

array_agg 返回指定列中所有值的数组。我们为其提供了一个窗口函数作为列参数。因此，不同于

array_agg("item")

（这会聚合列中所有值），我们使用

array_agg("item") OVER (PARTITION BY "customer_id")

（这会按 customer_id 聚合值）。

array_agg_distinct 返回指定列中所有不同值的数组。与 array_agg 不同，此函数确保每个值在结果数组中只出现一次。
array_join 是一个转换函数，用于将数组中的元素连接成一个字符串，并用分隔符分隔。其通用形式如下：

array_join(<数组>, <分隔符>)

因此，[milk, bananas, bread] 会变成 "milk, bananas, bread"。

生成的新列 items_array 将如下所示：

customer_id	items_array
123	eggs, bread
444	milk, bananas, bread

展开数组¶

如果您已经有一个将主键映射到值数组的列，可以使用 explode 函数将其拆分。该函数会为数组中的每个值创建新行。例如，给定以下数据：

customer_id	items_array
123	eggs, bread
444	milk, bananas, bread

打开表达式编辑器并选择"替换列"。代码 explode(items_array) 将生成以下表格：

customer_id	items_array
123	eggs
123	bread
444	milk
444	bananas
444	bread

请注意，explode 函数会丢弃空值。如需保留空值，请使用 explode_outer 函数。