PostgreSQL中为ARRAY_AGG结果添加LIMIT限制的技术问询

你的问题出在两个核心点上：

1. 直接在GROUP BY后加LIMIT 10是限制游戏的数量，不是每个游戏内的消息数量，完全达不到你要的防泛滥效果。
2. 错误方案里在WHERE子句中使用窗口函数生成的rn列——窗口函数是在SELECT阶段计算的，而WHERE的执行优先级高于SELECT，此时rn列还未生成，所以会报“列rn不存在”的语法错误。

正确的思路是：先对每个游戏的聊天消息单独取前10条，再进行聚合生成消息数组。以下是修正后的函数代码：

CREATE OR REPLACE FUNCTION words_get_user_chat( in_uid integer ) RETURNS jsonb AS $func$
SELECT COALESCE( JSONB_OBJECT_AGG(gid, ARRAY_TO_JSON(chat_messages)), '{}'::jsonb )
FROM (
    SELECT gid,
           ARRAY_AGG(
               JSON_BUILD_OBJECT(
                   'created', EXTRACT(EPOCH FROM created)::int,
                   'uid', uid,
                   'msg', msg
               ) ORDER BY created ASC
           ) AS chat_messages
    FROM (
        -- 第一步：给每个游戏的聊天消息按时间排序并编号，筛选前10条
        SELECT c.gid, c.created, c.uid, c.msg,
               ROW_NUMBER() OVER (PARTITION BY c.gid ORDER BY c.created ASC) AS rn
        FROM words_chat c
        LEFT JOIN words_games g USING (gid)
        WHERE in_uid IN (g.player1, g.player2)
          AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
    ) AS filtered_chats
    WHERE rn <= 10 -- 只保留每个游戏的前10条消息
    GROUP BY gid
) AS game_chats;
$func$ LANGUAGE sql STABLE;

代码逻辑说明： #

1. 最内层子查询filtered_chats：
   • 关联words_chat和words_games，筛选出用户参与的有效游戏（未结束或结束不超过1天）。
   • 用ROW_NUMBER() OVER (PARTITION BY c.gid ORDER BY c.created ASC)给每个游戏的聊天消息按创建时间升序编号，同一个游戏内的消息从1开始计数。
2. 中间层子查询game_chats：
   • 筛选出编号rn <=10的消息（即每个游戏的前10条）。
   • 按gid分组，用ARRAY_AGG将每个游戏的消息聚合为JSON数组，保持时间顺序。
3. 最外层查询：
   • 用JSONB_OBJECT_AGG将游戏ID作为键、消息数组作为值，生成最终的JSONB结果，COALESCE确保没有消息时返回空JSON对象{}。

这个方案完全兼容PostgreSQL 9.6.6，能精准实现每个游戏最多返回10条聊天消息的需求。

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