PostgreSQL按指定数量为用户分配未访问地点的可行性问询

PostgreSQL完全可以实现你的需求，以下是具体的SQL方案，同时会说明相关限制：

实现步骤与SQL代码 #

完整分配SQL #

WITH candidate_pairs AS (
    -- 获取目标用户近4个月未到访的地点列表
    SELECT 
        p.place_name, 
        u.user_id, 
        u.user_name,
        p.place_id
    FROM code.place p
    JOIN code.user u 
        ON u.user_id IN ('1', '2', '3') -- 仅筛选需要分配的用户
    LEFT JOIN code.visit v 
        ON v.user_id = u.user_id 
        AND v.place_id = p.place_id
        AND v.date >= CURRENT_DATE - INTERVAL '4 months' -- 近4个月有到访记录
    WHERE v.date IS NULL -- 筛选该用户未到访的地点
),
user_quotas AS (
    -- 定义每个用户需要分配的地点数量
    SELECT '1' AS user_id, 2 AS quota UNION ALL
    SELECT '2' AS user_id, 1 AS quota UNION ALL
    SELECT '3' AS user_id, 3 AS quota
),
quota_summary AS (
    -- 计算累计配额，用于后续全局分配时的区间划分
    SELECT 
        user_id,
        quota,
        SUM(quota) OVER (ORDER BY user_id) AS cumulative_quota,
        SUM(quota) OVER (ORDER BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_cumulative
    FROM user_quotas
),
ranked_places AS (
    -- 给每个用户的候选地点排序，同时生成全局唯一排名
    SELECT 
        cp.place_name,
        cp.user_id,
        cp.user_name,
        cp.place_id,
        -- 每个用户内部的地点排序（按place_id保证稳定）
        ROW_NUMBER() OVER (PARTITION BY cp.user_id ORDER BY cp.place_id) AS user_rank,
        -- 全局排名，用于确保地点不重复分配
        ROW_NUMBER() OVER (ORDER BY cp.place_id) AS global_rank
    FROM candidate_pairs cp
),
assigned_places AS (
    -- 筛选符合配额且地点不重复的结果
    SELECT 
        rp.place_name,
        rp.user_id,
        rp.user_name
    FROM ranked_places rp
    JOIN quota_summary qs 
        ON rp.user_id = qs.user_id
    WHERE 
        rp.user_rank <= qs.quota -- 不超过用户的配额
        AND rp.global_rank > COALESCE(qs.prev_cumulative, 0)
        AND rp.global_rank <= qs.cumulative_quota
)
SELECT * FROM assigned_places ORDER BY user_id, place_name;

如果需要随机分配地点，只需将ORDER BY cp.place_id替换为ORDER BY random()即可。

相关限制 #

• 配额不足限制：如果某个用户的未到访地点数量小于需要分配的配额，SQL会返回该用户所有可用的未到访地点，无法满足配额要求。建议提前通过查询验证每个用户的候选地点数量：

  SELECT u.user_id, COUNT(*) AS available_places
  FROM candidate_pairs cp
  JOIN code.user u ON cp.user_id = u.user_id
  GROUP BY u.user_id;
  

• 性能限制：如果place或user表数据量极大，JOIN操作会产生大量中间数据，需通过过滤目标用户、添加索引（比如visit(user_id, place_id, date)的复合索引）来优化性能。
• 排序依赖：地点分配的顺序完全依赖排序规则（比如place_id或随机），如果需要特定的分配优先级（比如优先分配热门地点），需调整ORDER BY的字段。
• 表结构修正：你提供的visit表创建语句存在两个笔误，需修正后才能正常使用：
  • 外键约束中REFERENCES SCHEMA.user应改为REFERENCES code.user
  • EXCLUDE约束中的data应改为date

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