在 ClickHouse 中，groupArray 函数用于将一组值聚合到一个数组中。这对于需要将多行数据合并成单个数组的场景非常有用。下面是一些关于 groupArray 的基本用法和示例。

基本用法

groupArray 函数的基本语法如下：

groupArray(expr)
  • expr：要聚合的表达式，可以是列名或其他表达式。

示例

假设我们有一个包含用户购买记录的表 purchases，表结构如下：

CREATE TABLE purchases (
    user_id UInt32,
    product_id UInt32,
    purchase_date Date
) ENGINE = MergeTree()
ORDER BY (user_id, purchase_date);

插入一些示例数据：

INSERT INTO purchases (user_id, product_id, purchase_date) VALUES
(1, 101, '2023-10-01'),
(1, 102, '2023-10-02'),
(1, 103, '2023-10-03'),
(2, 201, '2023-10-01'),
(2, 202, '2023-10-02'),
(3, 301, '2023-10-01');

使用 groupArray 聚合产品 ID

假设我们想按用户 ID 聚合每个用户的购买产品 ID 列表：

SELECT user_id, groupArray(product_id) AS product_ids
FROM purchases
GROUP BY user_id;

输出：

┌─user_id─┬─product_ids──────────┐
│       1 │ [101,102,103]        │
│       2 │ [201,202]            │
│       3 │ [301]                │
└─────────┴──────────────────────┘

使用 groupArray 聚合购买日期

假设我们想按用户 ID 聚合每个用户的购买日期列表：

SELECT user_id, groupArray(purchase_date) AS purchase_dates
FROM purchases
GROUP BY user_id;

输出：

┌─user_id─┬─purchase_dates────────────────────┐
│       1 │ ['2023-10-01','2023-10-02','2023-10-03'] │
│       2 │ ['2023-10-01','2023-10-02']            │
│       3 │ ['2023-10-01']                         │
└─────────┴───────────────────────────────────────┘

使用 groupArray 聚合多个字段

假设我们想按用户 ID 聚合每个用户的购买产品 ID 和购买日期列表：

SELECT user_id, groupArray((product_id, purchase_date)) AS purchases
FROM purchases
GROUP BY user_id;

输出：

┌─user_id─┬─purchases──────────────────────────────────────────────────────┐
│       1 │ [(101,'2023-10-01'),(102,'2023-10-02'),(103,'2023-10-03')]    │
│       2 │ [(201,'2023-10-01'),(202,'2023-10-02')]                        │
│       3 │ [(301,'2023-10-01')]                                           │
└─────────┴────────────────────────────────────────────────────────────────┘

控制数组大小

groupArray 还有一个变体 groupArray(N)，可以限制数组的最大长度。例如，我们只保留每个用户最近的两次购买记录：

SELECT user_id, groupArray(2)(product_id) AS product_ids
FROM purchases
GROUP BY user_id;

输出：

┌─user_id─┬─product_ids──────┐
│       1 │ [102,103]        │
│       2 │ [201,202]        │
│       3 │ [301]            │
└─────────┴──────────────────┘

控制数组顺序

默认情况下，groupArray 不保证数组中的元素顺序。如果需要按某个特定顺序排列，可以使用 orderBy 子句。例如，按购买日期升序排列：

SELECT user_id, groupArray(product_id) AS product_ids
FROM purchases
GROUP BY user_id
ORDER BY purchase_date;

但是，ORDER BY 子句在这里是对整个查询结果进行排序，而不是对每个用户的数组进行排序。为了对每个用户的数组进行排序，可以使用 arraySort 函数：

SELECT user_id, arraySort(groupArray(product_id)) AS product_ids
FROM purchases
GROUP BY user_id;

输出：

┌─user_id─┬─product_ids──────┐
│       1 │ [101,102,103]    │
│       2 │ [201,202]        │
│       3 │ [301]            │
└─────────┴──────────────────┘

总结

  • groupArray(expr)：将一组值聚合到一个数组中。
  • groupArray(N)(expr)：限制数组的最大长度。
  • arraySort(array)：对数组进行排序。

希望这些示例和解释能帮助你更好地理解和使用 ClickHouse 中的 groupArray 函数。如果有任何其他问题，请随时提问！