PostgreSQL中使用array_agg限制聚合结果数量的问题

你可以通过先给每个用户的任务按时间排序编号，再筛选前10条后聚合的方式实现需求，具体修改后的查询语句如下：

SELECT 
  *, 
  COUNT(*) OVER () as count 
FROM 
  (
    SELECT 
      u.*,
      ARRAY_AGG(
        JSON_BUILD_OBJECT(
          'id', th.id, 'task', th.task, 
          'taskname', t.name, 'project', th.project, 
          'projectname', p.name, 'started_at', th.started_at, 
          'stopped_at', th.stopped_at
        )
      ) as tasks
    FROM users u
    LEFT JOIN (
      -- 给每个用户的任务按时间倒序编号，筛选前10条
      SELECT 
        th_inner.*,
        ROW_NUMBER() OVER (PARTITION BY th_inner.user ORDER BY th_inner.started_at DESC) as row_num
      FROM taskhistories th_inner
    ) th ON u.id = th.user AND th.row_num <= 10
    LEFT JOIN tasks t ON th.task = t.id
    LEFT JOIN projects p ON th.project = p.id
    WHERE u.isadmin IS NOT TRUE
    GROUP BY u.id, u.isadmin -- 需包含users表所有非聚合字段，可根据实际schema调整
  ) as users 
LIMIT 10 OFFSET 0

关键逻辑说明： #

1. 任务编号与筛选：用ROW_NUMBER() OVER (PARTITION BY user ORDER BY started_at DESC)按用户分组、任务按启动时间倒序生成行号，再通过row_num <=10过滤每个用户的最新10条任务——解决了直接用LIMIT会全局限制而非按用户限制的问题。
2. 聚合数组：将筛选后的任务用ARRAY_AGG聚合为指定结构的JSON数组，保留你需要的字段。
3. 用户分组：外层按用户表主键（及其他非聚合字段）分组，确保每个用户仅返回一行，同时对应聚合后的任务数组。

简化写法（PostgreSQL 13+）： #

如果你的PostgreSQL版本在13及以上，可用FETCH FIRST ROWS PER GROUP替代行号筛选，代码更简洁：

-- 替换上述查询中的th子查询部分
SELECT th_inner.*
FROM taskhistories th_inner
ORDER BY started_at DESC
FETCH FIRST 10 ROWS PER GROUP th_inner.user

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