Oracle SQL按部门百分比分配筛选符合条件参与者的实现咨询

需求明确 #

从标记flag_criteria为符合条件的参与者中，按60%部门A、20%部门B、20%部门C的比例选取。若某部门符合条件的人数达不到分配名额，剩余名额按A→B→C的优先级分配给其他还有富余符合人员的部门。

实现步骤 #

1. 统计基础数据 #

先圈出所有符合条件的用户，计算总人数，同时定义各部门的初始配额并统计实际符合人数：

WITH eligible_users AS (
    -- 筛选所有符合条件的用户
    SELECT user_id, department
    FROM your_table
    WHERE flag_criteria = 1 -- 假设1代表符合筛选条件
),
total_eligible AS (
    -- 计算符合条件的总人数
    SELECT COUNT(*) AS total FROM eligible_users
),
dept_quota_init AS (
    -- 定义各部门的初始配额和优先级
    SELECT
        'Dept A' AS dept,
        FLOOR((SELECT total FROM total_eligible) * 0.6) AS init_quota,
        1 AS priority -- 优先级：数字越小越先分配剩余名额
    UNION ALL
    SELECT
        'Dept B' AS dept,
        FLOOR((SELECT total FROM total_eligible) * 0.2) AS init_quota,
        2 AS priority
    UNION ALL
    SELECT
        'Dept C' AS dept,
        FLOOR((SELECT total FROM total_eligible) * 0.2) AS init_quota,
        3 AS priority
),
dept_actual_count AS (
    -- 统计各部门符合条件的实际人数
    SELECT department, COUNT(*) AS actual_num
    FROM eligible_users
    GROUP BY department
)

2. 调整配额并计算缺口 #

对比初始配额和实际人数，确定各部门能提供的名额，同时算出总缺口：

,quota_adjusted AS (
    SELECT
        dqi.dept,
        dqi.init_quota,
        COALESCE(dac.actual_num, 0) AS actual_num,
        -- 实际可分配名额：取初始配额和实际人数的较小值
        LEAST(dqi.init_quota, COALESCE(dac.actual_num, 0)) AS allocated,
        -- 计算该部门无法满足的缺口
        GREATEST(0, dqi.init_quota - COALESCE(dac.actual_num, 0)) AS deficit
    FROM dept_quota_init dqi
    LEFT JOIN dept_actual_count dac ON dqi.dept = dac.department
),
total_deficit AS (
    -- 计算所有部门的总缺口
    SELECT SUM(deficit) AS total_gap FROM quota_adjusted
),
available_depts AS (
    -- 筛选出还有富余名额的部门（实际人数 > 已分配名额）
    SELECT
        dept,
        actual_num - allocated AS available_slots,
        priority
    FROM quota_adjusted
    WHERE actual_num > allocated
    ORDER BY priority
)

3. 最终筛选用户 #

给每个部门的用户排序，先取初始分配名额，再按优先级分配剩余缺口名额：

,ranked_users AS (
    -- 给每个部门的符合用户排序，这里用随机排序，可替换为其他规则（如入职时间）
    SELECT
        user_id,
        department,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY RAND()) AS rn
    FROM eligible_users
)
-- 先取各部门初始分配的名额
SELECT user_id, department
FROM ranked_users ru
JOIN quota_adjusted qa ON ru.department = qa.dept
WHERE ru.rn <= qa.allocated

UNION ALL

-- 分配剩余缺口的名额
SELECT ru.user_id, ru.department
FROM ranked_users ru
JOIN (
    -- 按优先级生成需要补充的名额分配记录
    SELECT
        ad.dept,
        ROW_NUMBER() OVER (ORDER BY ad.priority) AS gap_rn
    FROM available_depts ad
    -- 生成与总缺口数对应的序列，不同数据库语法不同：PostgreSQL用generate_series，MySQL用递归CTE
    CROSS JOIN generate_series(1, (SELECT total_gap FROM total_deficit)) AS gap_seq(n)
    WHERE ad.available_slots >= gap_seq.n
) AS gap_alloc ON ru.department = gap_alloc.dept
WHERE ru.rn > (SELECT allocated FROM quota_adjusted WHERE dept = ru.department)
AND ru.rn <= (SELECT allocated + gap_alloc.gap_rn FROM quota_adjusted qa WHERE qa.dept = ru.department);

注意事项 #

• 排序规则：示例中用ORDER BY RAND()随机选用户，可根据业务需求替换为固定排序（如user_id、performance_score等）。
• 数据库适配：generate_series是PostgreSQL专属语法，MySQL可改用递归CTE生成序列，SQL Server可使用TOP结合递归实现。
• 优先级修改：如果需要调整剩余名额的分配顺序，修改dept_quota_init中的priority值即可。

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