基于Evaluation与Employees表的SQL分析：验证评估系统对员工发展的作用

Let's break down how to answer your three key questions with SQL, joining the Evaluation and Employees tables effectively. I'll assume both tables share a common identifier like employee_id to link records—adjust this to match your actual schema if needed!

Combined Efficient Query (Recommended) #

Instead of running three separate queries, we can get all the metrics you need in one go, which is faster and avoids redundant table scans:

SELECT
    -- Define the 4 evaluation score intervals (adjust ranges to fit your system!)
    CASE
        WHEN ev.evaluation_score BETWEEN 0 AND 25 THEN '0-25'
        WHEN ev.evaluation_score BETWEEN 26 AND 50 THEN '26-50'
        WHEN ev.evaluation_score BETWEEN 51 AND 75 THEN '51-75'
        WHEN ev.evaluation_score BETWEEN 76 AND 100 THEN '76-100'
        ELSE 'Out of Range'
    END AS evaluation_bucket,
    -- Total employees in each interval
    COUNT(DISTINCT emp.employee_id) AS total_employees,
    -- Average satisfaction for the interval (rounded for readability)
    ROUND(AVG(emp.satisfaction), 2) AS avg_satisfaction,
    -- Number of promoted employees (works because promotion_last_5years is 0/1)
    SUM(emp.promotion_last_5years) AS promoted_employee_count
FROM
    Evaluation ev
JOIN
    Employees emp ON ev.employee_id = emp.employee_id
GROUP BY
    evaluation_bucket
ORDER BY
    evaluation_bucket;

Breakdown of Each Metric #

Let's walk through what each part of the query does:

• Evaluation Score Intervals: The CASE statement groups employees into your 4 defined score ranges. If your company uses a different scale (e.g., 1-5 instead of 0-100), just update the numeric ranges here.
• Total Employees per Bucket: COUNT(DISTINCT emp.employee_id) ensures we don't count duplicate records (in case an employee has multiple evaluations—swap to COUNT(*) if each employee has exactly one evaluation entry).
• Average Satisfaction: AVG(emp.satisfaction) calculates the mean satisfaction score for each group, rounded to 2 decimal places for clarity.
• Promoted Employee Count: Since promotion_last_5years is a binary (0/1) field, summing these values directly gives the total number of employees who received a promotion in the last 5 years for each interval.

If You Need Separate Queries #

If you prefer to run each metric as a standalone query, here's how to structure them:

1. Count Employees by Evaluation Score Interval #

SELECT
    CASE
        WHEN ev.evaluation_score BETWEEN 0 AND 25 THEN '0-25'
        WHEN ev.evaluation_score BETWEEN 26 AND 50 THEN '26-50'
        WHEN ev.evaluation_score BETWEEN 51 AND 75 THEN '51-75'
        WHEN ev.evaluation_score BETWEEN 76 AND 100 THEN '76-100'
        ELSE 'Out of Range'
    END AS evaluation_bucket,
    COUNT(DISTINCT emp.employee_id) AS total_employees
FROM
    Evaluation ev
JOIN
    Employees emp ON ev.employee_id = emp.employee_id
GROUP BY
    evaluation_bucket
ORDER BY
    evaluation_bucket;

2. Average Satisfaction by Evaluation Interval #

SELECT
    CASE
        WHEN ev.evaluation_score BETWEEN 0 AND 25 THEN '0-25'
        WHEN ev.evaluation_score BETWEEN 26 AND 50 THEN '26-50'
        WHEN ev.evaluation_score BETWEEN 51 AND 75 THEN '51-75'
        WHEN ev.evaluation_score BETWEEN 76 AND 100 THEN '76-100'
        ELSE 'Out of Range'
    END AS evaluation_bucket,
    ROUND(AVG(emp.satisfaction), 2) AS avg_satisfaction
FROM
    Evaluation ev
JOIN
    Employees emp ON ev.employee_id = emp.employee_id
GROUP BY
    evaluation_bucket
ORDER BY
    evaluation_bucket;

3. Promoted Employees by Evaluation Interval #

SELECT
    CASE
        WHEN ev.evaluation_score BETWEEN 0 AND 25 THEN '0-25'
        WHEN ev.evaluation_score BETWEEN 26 AND 50 THEN '26-50'
        WHEN ev.evaluation_score BETWEEN 51 AND 75 THEN '51-75'
        WHEN ev.evaluation_score BETWEEN 76 AND 100 THEN '76-100'
        ELSE 'Out of Range'
    END AS evaluation_bucket,
    SUM(emp.promotion_last_5years) AS promoted_employee_count
FROM
    Evaluation ev
JOIN
    Employees emp ON ev.employee_id = emp.employee_id
GROUP BY
    evaluation_bucket
ORDER BY
    evaluation_bucket;

Once you have this data, you can analyze trends like:

• Do employees in higher evaluation buckets have higher satisfaction?
• Is there a correlation between higher evaluation scores and promotion rates?
  These insights will help you determine if the evaluation system supports employee development.

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