如何在保留product_id列的同时，按user_id和order_date聚合生成数组列？

假设您有一组购买数据如下：

需要按user_id和order_date分组，使用COLLECT_SET将product_id聚合为一个数组列product_ids，同时保留原product_id列，期望得到如下结果：

可以通过以下查询创建数组列，但无法保留每一行的product_id列：

SELECT user_id, 
       order_date, 
       COLLECT_SET(product_id) AS product_ids
FROM table t
GROUP BY user_id, order_date

如果将product_id加入查询并分组，product_ids列将始终是长度为1的数组：

SELECT user_id,
       order_date, 
       product_id, 
       COLLECT_SET(product_id) AS product_ids
FROM table t
GROUP BY user_id, order_date, product_id

示例结果：

若在GROUP BY中排除product_id，会报错：Expression not in GROUP BY key 'product_id'

是否可以通过单个简单查询实现需求，无需创建临时表再按user_id和order_date进行关联？

可以使用窗口函数实现需求，无需分组聚合或临时表关联。通过OVER (PARTITION BY user_id, order_date)子句，将COLLECT_SET的计算范围限定在每个user_id + order_date组内，同时保留原表的每一行数据：

SELECT user_id,
       order_date,
       product_id,
       COLLECT_SET(product_id) OVER (PARTITION BY user_id, order_date) AS product_ids
FROM table t

这个查询会直接返回符合预期的结果：

• 同一user_id和order_date的所有行，product_ids列会返回相同的集合数组
• 完整保留原product_id列的每一行数据

这种方式避免了分组导致的行压缩，也不需要额外的关联操作，是最简洁的实现方式。

内容的提问来源于stack exchange，提问作者Ben Nouhan