如何将EVENTS表按user_id分组并生成带时间戳的event数组

现有EVENTS表结构及数据 #

期望分组结果 #

目前已能通过以下SQL按user_id分组聚合event_name，但无法带上对应时间戳：

SELECT 
    user_id, 
    ARRAYAGG(event_name) WITHIN GROUP (ORDER BY time DESC)
FROM EVENTS
GROUP BY user_id

解决方案 #

只需在聚合函数内先将event_name和time按目标格式拼接，再进行分组聚合即可。不同数据库的字符串拼接语法略有差异，以下是常见实现方式：

1. PostgreSQL/Redshift #

使用CONCAT函数或||运算符拼接字符串：

SELECT 
    user_id, 
    ARRAYAGG(CONCAT(event_name, ':\'', time, '\'')) WITHIN GROUP (ORDER BY time DESC) AS event_array
FROM EVENTS
GROUP BY user_id;

2. MySQL #

如果需要生成示例中的逗号分隔格式，用GROUP_CONCAT；如果需要数组格式，使用JSON_ARRAYAGG：

逗号分隔字符串格式

SELECT 
    user_id, 
    GROUP_CONCAT(CONCAT(event_name, ':\'', time, '\'') ORDER BY time DESC SEPARATOR ',') AS event_array
FROM EVENTS
GROUP BY user_id;

JSON数组格式

SELECT 
    user_id, 
    JSON_ARRAYAGG(CONCAT(event_name, ':\'', time, '\'') ORDER BY time DESC) AS event_array
FROM EVENTS
GROUP BY user_id;

3. SQL Server #

使用STRING_AGG函数实现聚合：

SELECT 
    user_id, 
    STRING_AGG(CONCAT(event_name, ':\'', time, '\''), ',') WITHIN GROUP (ORDER BY time DESC) AS event_array
FROM EVENTS
GROUP BY user_id;

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