如何基于潜在客户量优先级分配客户至Offer并实现均衡分布？

背景与现有数据 #

现有表 hh_potential_offers 存储可接收潜在Offer的客户ID（customer_id），执行以下查询得到各Offer的潜在客户量：

select offer, count(customer_id) as customers from hh_potential_offers group by 1;

查询结果：

核心需求 #

• 为每个customer_id分配唯一的Offer；
• 单个客户可能拥有1至10个潜在Offer可选；
• 重叠客户（同一客户可被多个Offer覆盖）优先分配给潜在客户量更少的Offer（例：优先分配给offer1，其次是offer3，最后是offer2）；
• 最终实现各Offer的分配客户量尽可能均衡；
• 将分配结果插入至assigned_offers表。

示例数据 #

输入数据（hh_potential_offers 前7条） #

select * from hh_potential_offers limit 7;

结果：

预期分配结果 #

最优实现方案 #

思路解析 #

1. Offer优先级排序：按各Offer的潜在客户量从小到大排序，确定分配优先级（offer1 > offer3 > offer2）；
2. 客户Offer排序：为每个客户的可选Offer按优先级排序，确保优先选择低潜在量的Offer；
3. 去重分配：每个客户仅分配优先级最高的Offer，避免重复分配；
4. 均衡性保障：优先满足低潜在量Offer的分配需求，自然缩小各Offer间的客户量差距，实现近似均衡；若需更严格均衡，可后续对高分配量Offer的剩余客户进行二次调整。

SQL实现（以PostgreSQL为例） #

WITH offer_priority AS (
    -- 计算各Offer潜在客户量并确定优先级
    SELECT offer, 
           count(customer_id) AS total_potential,
           ROW_NUMBER() OVER (ORDER BY count(customer_id) ASC) AS priority
    FROM hh_potential_offers
    GROUP BY offer
),
customer_offers AS (
    -- 关联客户与可选Offer的优先级
    SELECT po.customer_id,
           po.offer,
           op.priority
    FROM hh_potential_offers po
    JOIN offer_priority op ON po.offer = op.offer
),
ranked_customer_offers AS (
    -- 为每个客户的可选Offer按优先级排序
    SELECT customer_id,
           offer,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY priority ASC) AS offer_rank
    FROM customer_offers
),
final_assignment AS (
    -- 每个客户取优先级最高的Offer作为最终分配结果
    SELECT customer_id, offer
    FROM ranked_customer_offers
    WHERE offer_rank = 1
)
-- 将结果插入目标表
INSERT INTO assigned_offers (customer_id, offer)
SELECT customer_id, offer
FROM final_assignment;

方案说明 #

• 优先级排序逻辑确保低潜在量Offer优先获得重叠客户，从根源上平衡各Offer的分配量；
• 通过ROW_NUMBER()实现客户的唯一分配，避免重复；
• 若需要进一步优化均衡性，可在final_assignment前添加调整逻辑，将高分配量Offer中原本有其他低优先级Offer可选的客户，调整到仍有剩余容量的低分配量Offer中。

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