如何将事件按用户分组为数组并同时展示最新事件？

场景说明 #

现有EVENTS表结构及数据如下：

需要按user_id分组后得到如下结果：

优化方案 #

无需分组后再左连接，可通过一次分组查询同时完成两个需求，避免额外的表扫描开销，逻辑更简洁。以下是适配主流数据库的实现方式：

方案1：利用聚合函数直接获取结果（PostgreSQL/Oracle） #

通过STRING_AGG拼接事件与时间的字符串，同时利用ARRAY_AGG按时间降序排列后取第一个元素作为最新事件：

SELECT
    user_id,
    STRING_AGG(CONCAT(event_name, ':', time), ',') WITHIN GROUP (ORDER BY time ASC) AS event_array,
    ARRAY_AGG(event_name) WITHIN GROUP (ORDER BY time DESC)[1] AS "most recent event"
FROM EVENTS
GROUP BY user_id;

方案2：结合窗口函数标记最新事件（通用型） #

先通过窗口函数ROW_NUMBER()标记每个用户的最新事件，再聚合拼接：

WITH ranked_events AS (
    SELECT
        user_id,
        event_name,
        time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time DESC) AS rn
    FROM EVENTS
)
SELECT
    user_id,
    STRING_AGG(CONCAT(event_name, ':', time), ',') WITHIN GROUP (ORDER BY time ASC) AS event_array,
    MAX(event_name) FILTER (WHERE rn = 1) AS "most recent event"
FROM ranked_events
GROUP BY user_id;

方案优势 #

• 仅需一次表扫描，相比分组后左连接的方式减少了IO开销，性能更优
• 逻辑集中在单个查询中，代码更简洁易维护

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