Oracle PL/SQL按条件累计行数据：保单分配员工逻辑开发求助

需求说明 #

根据Employees表中员工的max_amount限额，按field分组，优先给限额高的员工分配Amounts表中的保单；当员工累计保费超过限额后，切换到下一位员工，最终给Amounts表填充assign_to_employee字段，并生成统计员工分配情况的Assign Stats Table。

给定表结构 #

Employees表 #

Amounts表（待填充字段：assign_to_employee） #

核心分配逻辑 #

1. 按field分组处理所有保单和员工
2. 每组内：
   • 员工按max_amount降序排序（限额相同的员工可按employee_id辅助排序）
   • 按保单顺序依次分配，给当前员工累加保费，直到加上当前保单保费会超过限额时，切换到下一位员工
   • 重复操作直到所有保单分配完毕

SQL实现方案（以PostgreSQL为例） #

以下代码完成保单分配、填充assign_to_employee字段并生成统计表格：

WITH ranked_employees AS (
    -- 给每个field分组的员工按限额降序排序
    SELECT 
        employee_id,
        field,
        max_amount,
        ROW_NUMBER() OVER (PARTITION BY field ORDER BY max_amount DESC, employee_id DESC) AS emp_rank
    FROM Employees
),
ranked_policies AS (
    -- 给每个field分组的保单按pol排序（保留原顺序）
    SELECT 
        pol,
        premia,
        field,
        ROW_NUMBER() OVER (PARTITION BY field ORDER BY pol) AS pol_rank
    FROM Amounts
),
policy_assignments AS (
    -- 递归初始：分配第一个保单给组内第一位员工
    SELECT 
        rp.pol,
        rp.premia,
        rp.field,
        re.employee_id AS assign_to_employee,
        re.max_amount,
        rp.premia AS accumulated,
        re.emp_rank,
        rp.pol_rank + 1 AS next_pol_rank
    FROM ranked_policies rp
    JOIN ranked_employees re ON rp.field = re.field AND re.emp_rank = 1
    WHERE rp.pol_rank = 1

    UNION ALL

    -- 递归处理后续保单
    SELECT 
        rp.pol,
        rp.premia,
        rp.field,
        CASE 
            WHEN pa.accumulated + rp.premia <= pa.max_amount THEN pa.assign_to_employee
            ELSE (SELECT employee_id FROM ranked_employees WHERE field = rp.field AND emp_rank = pa.emp_rank + 1)
        END AS assign_to_employee,
        CASE 
            WHEN pa.accumulated + rp.premia <= pa.max_amount THEN pa.max_amount
            ELSE (SELECT max_amount FROM ranked_employees WHERE field = rp.field AND emp_rank = pa.emp_rank + 1)
        END AS max_amount,
        CASE 
            WHEN pa.accumulated + rp.premia <= pa.max_amount THEN pa.accumulated + rp.premia
            ELSE rp.premia
        END AS accumulated,
        CASE 
            WHEN pa.accumulated + rp.premia <= pa.max_amount THEN pa.emp_rank
            ELSE pa.emp_rank + 1
        END AS emp_rank,
        rp.pol_rank + 1 AS next_pol_rank
    FROM policy_assignments pa
    JOIN ranked_policies rp ON pa.field = rp.field AND rp.pol_rank = pa.next_pol_rank
)

-- 1. 输出填充好assign_to_employee的Amounts表
SELECT 
    pol,
    premia,
    field,
    assign_to_employee
FROM policy_assignments
ORDER BY field, pol;

-- 2. 输出Assign Stats Table
SELECT 
    employee_id,
    field,
    max_amount,
    SUM(premia) AS true_amount,
    max_amount - SUM(premia) AS remain
FROM policy_assignments
JOIN Employees e ON e.employee_id = policy_assignments.assign_to_employee AND e.field = policy_assignments.field
GROUP BY employee_id, field, max_amount
UNION ALL
-- 补充未分配到保单的员工记录
SELECT 
    employee_id,
    field,
    max_amount,
    0 AS true_amount,
    max_amount AS remain
FROM Employees
WHERE (employee_id, field) NOT IN (
    SELECT DISTINCT assign_to_employee, field FROM policy_assignments
)
ORDER BY field, max_amount DESC, employee_id;

结果验证 #

执行上述代码后，得到的Amounts表会与示例中的填充结果一致，Assign Stats Table也会匹配示例中的统计数据。

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