如何按task_dispersal的to_disp值多次获取随机用户行？

核心思路 #

利用generate_series生成对应次数的虚拟序列，为每个序列项独立执行一次随机用户查询，既保证每个task_dispersal行能生成td.to_disp条结果，又避免了原OFFSET + LIMIT td.to_disp导致的结果块重叠问题。

具体实现SQL #

SELECT td.order_id, td.to_disp, u.*
FROM task_dispersal td
-- 生成td.to_disp个虚拟行，每个行对应一次随机查询
CROSS JOIN LATERAL generate_series(1, td.to_disp) AS gs(seq)
-- 每个虚拟行独立执行随机用户选取逻辑
CROSS JOIN LATERAL (
    SELECT u.*
    FROM users u
    -- 过滤掉已在当前order_id的task_pool中的用户
    WHERE NOT EXISTS (
        SELECT 1 
        FROM task_pool tp
        WHERE tp.order_id = td.order_id 
          AND tp.user_id = u.id
    )
    -- 基于符合条件的用户总数计算随机偏移量
    OFFSET floor(random() * (
        SELECT COUNT(*) 
        FROM users u_filter
        WHERE NOT EXISTS (
            SELECT 1 
            FROM task_pool tp
            WHERE tp.order_id = td.order_id 
              AND tp.user_id = u_filter.id
        )
    ))
    LIMIT 1
) AS u;

关键说明 #

1. generate_series的作用：为每一行task_dispersal生成td.to_disp个连续整数的虚拟行，相当于把原行复制了td.to_disp次，每次复制触发一次独立的随机查询。
2. 独立随机逻辑：每次查询都会重新计算符合条件的用户总数，并基于这个总数生成随机偏移量，确保每次选取的用户都是独立随机的，不会出现结果块重叠。
3. 性能优化提示：如果users表数据量很大，重复计算符合条件的用户总数会影响性能，可以提前用CTE预计算每个order_id对应的可用用户列表，再从中随机选取：

WITH available_users AS (
    SELECT td.order_id, u.id, u.name -- 替换为users表实际字段
    FROM task_dispersal td
    CROSS JOIN users u
    WHERE NOT EXISTS (
        SELECT 1 
        FROM task_pool tp
        WHERE tp.order_id = td.order_id 
          AND tp.user_id = u.id
    )
)
SELECT au.order_id, td.to_disp, au.*
FROM task_dispersal td
CROSS JOIN LATERAL generate_series(1, td.to_disp) AS gs(seq)
CROSS JOIN LATERAL (
    SELECT au.*
    FROM available_users au
    WHERE au.order_id = td.order_id
    ORDER BY random()
    LIMIT 1
) AS au;

这种方式通过CTE一次性计算所有order_id的可用用户，后续随机选取时直接基于预计算结果，性能更优。

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