Oracle SQL实现工作负载分配：匹配5名员工的技术需求

Got it, let's tackle this workload allocation problem step by step. First, aligning with your requirements: we need to assign exactly 5 employees, only allocate to workstations that truly need staff (like skipping Station5), and don't need to worry about follow-up scheduling.

Approach Breakdown #

1. Filter out unnecessary workstations: Exclude stations with negligible workload (like Station5 with workload=2) since they don't require employees.
2. Calculate proportional allocation: Base initial staff counts on each workstation's share of the total workload from eligible stations.
3. Adjust to exact headcount: Fix any rounding discrepancies to ensure the total assigned employees equal exactly 5.

Complete SQL Code #

WITH workstations AS (
    -- Your raw workstation data (replace with your actual table)
    SELECT 'Station1' AS work_station, 500 AS workload FROM dual
    UNION ALL SELECT 'Station2', 450 FROM dual
    UNION ALL SELECT 'Station3', 50 FROM dual
    UNION ALL SELECT 'Station4', 600 FROM dual
    UNION ALL SELECT 'Station5', 2 FROM dual
    UNION ALL SELECT 'Station6', 350 FROM dual
),
filtered_workstations AS (
    -- Keep only workstations that need potential staffing
    SELECT work_station, workload
    FROM workstations
    WHERE workload > 10 -- Adjust threshold as needed; excludes Station5 here
),
total_workload AS (
    -- Calculate total workload for eligible stations
    SELECT SUM(workload) AS total_wl
    FROM filtered_workstations
),
initial_allocation AS (
    -- Initial proportional allocation (rounded to whole numbers)
    SELECT 
        work_station,
        workload,
        ROUND((workload / total_wl) * 5, 0) AS allocated_staff
    FROM filtered_workstations, total_workload
),
allocation_sum AS (
    -- Check if initial total matches 5 employees
    SELECT SUM(allocated_staff) AS sum_staff
    FROM initial_allocation
),
final_allocation AS (
    -- Adjust allocation to hit exactly 5 employees
    SELECT 
        work_station,
        workload,
        CASE 
            -- If we allocated too many, subtract from highest-priority stations first
            WHEN sum_staff > 5 THEN 
                allocated_staff - CASE 
                    WHEN ROW_NUMBER() OVER (ORDER BY (workload / total_wl) DESC) <= (sum_staff - 5) THEN 1 
                    ELSE 0 
                END
            -- If we allocated too few, add to highest-priority stations first
            WHEN sum_staff < 5 THEN
                allocated_staff + CASE 
                    WHEN ROW_NUMBER() OVER (ORDER BY (workload / total_wl) DESC) <= (5 - sum_staff) THEN 1 
                    ELSE 0 
                END
            -- If already perfect, keep initial allocation
            ELSE allocated_staff
        END AS final_staff
    FROM initial_allocation, allocation_sum, total_workload
)
-- Final result: only stations with assigned employees
SELECT 
    work_station,
    workload,
    final_staff AS allocated_employees
FROM final_allocation
WHERE final_staff > 0
ORDER BY final_staff DESC;

Expected Output #

Key Notes #

• Threshold adjustment: The workload > 10 filter can be tweaked based on your definition of "necessary" (e.g., if a station with workload=50 doesn't need staff, raise the threshold).
• Priority logic: When adjusting headcount, we prioritize stations with higher workload ratios—so the busiest stations get extra staff first, or lose staff last if over-allocated.
• Flexibility: Replace the workstations CTE with your actual table name to use this in your database.

内容的提问来源于stack exchange，提问作者Quentin T.