PostgreSQL：结合PARTITION与LIMIT实现带状态的队列任务选取

我来帮你搞定这个需求！根据你的描述，核心是要结合任务类型（type）和当前运行中的任务数量来筛选待处理任务，确保每种type的运行任务数不超过2个。目前type A和D各有1个running任务，所以这两个类型还能各再拿1个待处理任务，其他类型最多可以拿2个。

核心思路 #

先统计每种type当前已有的running任务数量，再基于这个统计结果，从待处理任务中按类型分配剩余的可运行名额：

• 没有running任务的type：最多选2个
• 已有1个running任务的type（比如A、D）：最多再选1个
• 已有2个running任务的type：直接跳过

完整SQL示例（以PostgreSQL为例） #

WITH running_tasks AS (
    -- 统计每种type当前运行中的任务数
    SELECT type, COUNT(*) AS running_count
    FROM queue
    WHERE status = 'running'
    GROUP BY type
)
SELECT q.*
FROM queue q
LEFT JOIN running_tasks rt ON q.type = rt.type
WHERE q.status = 'pending' -- 替换成你实际的待处理状态值
AND (
    -- 无运行任务的type，最多取2个
    (rt.running_count IS NULL AND ROW_NUMBER() OVER (PARTITION BY q.type ORDER BY q.id) <= 2)
    OR
    -- 已有1个运行任务的type，最多再取1个
    (rt.running_count = 1 AND ROW_NUMBER() OVER (PARTITION BY q.type ORDER BY q.id) <= 1)
)
-- 可以根据需求调整排序规则，比如按优先级
ORDER BY q.type, q.id;

关键细节解释 #

1. CTE统计运行任务数：running_tasks这个公共表达式帮我们快速算出每个type当前在跑的任务量，避免重复计算。
2. 窗口函数控制选取数量：ROW_NUMBER()按type分组给待处理任务编号，这样就能精准控制每个type能选的任务数。
3. 状态适配：这里假设待处理任务的状态是pending，如果你的业务里待处理状态是其他值（比如waiting），记得替换成实际值。

进阶优化：避免竞态条件 #

如果有多个进程同时执行这个查询，可能会出现同一任务被多个进程选中的情况。可以加上行锁机制来避免：

WITH running_tasks AS (
    SELECT type, COUNT(*) AS running_count
    FROM queue
    WHERE status = 'running'
    GROUP BY type
)
SELECT q.*
FROM queue q
LEFT JOIN running_tasks rt ON q.type = rt.type
WHERE q.status = 'pending'
AND (
    (rt.running_count IS NULL AND ROW_NUMBER() OVER (PARTITION BY q.type ORDER BY q.priority DESC, q.id) <= 2)
    OR
    (rt.running_count = 1 AND ROW_NUMBER() OVER (PARTITION BY q.type ORDER BY q.priority DESC, q.id) <= 1)
)
ORDER BY q.type, q.priority DESC, q.id
-- 锁定选中的任务，跳过已被锁定的任务
FOR UPDATE SKIP LOCKED;

FOR UPDATE SKIP LOCKED会锁定查询到的任务，同时跳过已经被其他进程锁定的任务，确保任务分配的唯一性。

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