请求协助基于test1、test2数据源计算员工任务完成率

Got it, let's work through calculating that task completion rate using your test1 (assigned tasks) and test2 (completed tasks) datasets. I'll cover the most common tools folks use for this—SQL and Python/Pandas—since you didn't specify which one you're working with. The core logic stays the same across tools: (Number of Completed Tasks / Total Assigned Tasks) × 100.

Scenario 1: Using SQL #

First, confirm both tables share a common identifier (like employee_id for per-employee rates, or task_id to match specific tasks). Here's how to compute it:

Per Employee Completion Rate #

This gives you the completion rate for each individual employee:

SELECT
    t1.employee_id,
    COUNT(DISTINCT t1.task_id) AS total_assigned_tasks,
    COUNT(DISTINCT t2.task_id) AS completed_tasks,
    ROUND((COUNT(DISTINCT t2.task_id)::FLOAT / COUNT(DISTINCT t1.task_id)) * 100, 2) AS completion_rate_percent
FROM test1 t1
LEFT JOIN test2 t2 
    ON t1.employee_id = t2.employee_id 
    AND t1.task_id = t2.task_id  -- Match the exact task assigned to the employee
GROUP BY t1.employee_id;

• LEFT JOIN ensures we include employees who have no completed tasks (their completion rate will show as 0).
• DISTINCT handles duplicate entries in either table (e.g., a task listed twice for the same employee).
• Casting to FLOAT prevents integer division (which would truncate results to 0 if completed tasks are fewer than assigned).

Overall Completion Rate (All Employees) #

If you want a single completion rate across all tasks:

SELECT
    COUNT(DISTINCT t1.task_id) AS total_assigned_tasks,
    COUNT(DISTINCT t2.task_id) AS completed_tasks,
    ROUND((COUNT(DISTINCT t2.task_id)::FLOAT / COUNT(DISTINCT t1.task_id)) * 100, 2) AS overall_completion_rate_percent
FROM test1 t1
LEFT JOIN test2 t2 
    ON t1.task_id = t2.task_id;

Scenario 2: Using Python (Pandas) #

Assuming you've loaded your datasets into Pandas DataFrames df_test1 (assigned tasks) and df_test2 (completed tasks):

Per Employee Completion Rate #

import pandas as pd

# Merge datasets to match assigned tasks with completed ones
merged_df = pd.merge(
    df_test1, 
    df_test2, 
    on=['employee_id', 'task_id'], 
    how='left', 
    indicator=True
)

# Calculate task counts per employee
completion_stats = merged_df.groupby('employee_id').agg(
    total_assigned_tasks=('task_id', 'nunique'),
    completed_tasks=('_merge', lambda x: (x == 'both').sum())
)

# Compute the completion rate
completion_stats['completion_rate_percent'] = round(
    (completion_stats['completed_tasks'] / completion_stats['total_assigned_tasks']) * 100,
    2
)

# View results
print(completion_stats)

• The _merge column tells us if a task exists in both tables (both = completed) or only test1 (uncompleted).
• nunique ensures we don't count duplicate task entries for the same employee.

Overall Completion Rate #

# Count unique tasks in each dataset
total_assigned = df_test1['task_id'].nunique()
completed = df_test2['task_id'].nunique()

# Calculate and print overall rate
overall_rate = round((completed / total_assigned) * 100, 2)
print(f"Overall Task Completion Rate: {overall_rate}%")

Quick Notes to Avoid Issues #

• Double-check that task_id values are consistent across both datasets (no typos, mismatched formats like strings vs integers).
• If tasks can be assigned to multiple employees, adjust the logic to count unique employee-task pairs instead of just task IDs.
• For tools like Excel or Tableau, the core workflow is identical: count total assigned tasks, count matching completed tasks, divide, multiply by 100.

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