题解 | #统计加班员工占比#

这道题目要求我们统计公司各部门加班员工所占比例。加班定义为工作时长大于9.5小时。我们需要从员工信息和出勤信息中提取数据，计算每个部门的加班比例，并按比例降序排序。下面我将详细讲解这个SQL查询的思路和实现步骤。

题目描述

我们有两个表：

• staff_tb：包含员工信息，包括员工ID、姓名、性别、岗位类别和所在部门。
• attendent_tb：包含员工的出勤信息，包括信息ID、员工ID、上班打卡时间和下班打卡时间。

目标是统计各部门加班员工所占比例，并按比例降序排序。

拆解题目

1. 确定总体问题

我们需要计算各部门的加班员工比例。加班定义为工作时长大于9.5小时。

2. 分析关键问题

• 计算工作时长：计算每个员工的工作时长。
• 识别加班员工：识别出工作时长大于9.5小时的员工。
• 计算加班比例：计算每个部门的加班员工比例。
• 格式化输出：将加班比例以百分数形式输出，并保留一位小数。

3. 解决每个关键问题的代码及讲解

步骤1：计算工作时长

我们使用WITH子句创建一个临时表work_hours，计算每个员工的工作时长：

WITH work_hours AS (
    SELECT 
        s.department,
        a.staff_id,
        TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin)  / 60.0 AS hours_worked
    FROM 
        staff_tb s
    JOIN 
        attendent_tb a ON s.staff_id = a.staff_id
)

• TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin)/ 60.0: 计算工作时长的分钟部分并转换为小时。

步骤2：识别加班员工

我们使用CASE WHEN语句识别出工作时长大于9.5小时的员工：

overtime_counts AS (
    SELECT 
        department,
        COUNT(CASE WHEN hours_worked > 9.5 THEN 1 END) AS overtime_count,
        COUNT(*) AS total_count
    FROM 
        work_hours
    GROUP BY 
        department
)

• COUNT(CASE WHEN hours_worked > 9.5 THEN 1 END): 计算加班员工的数量，若加工时长大于9.5就记为1，没有指定 ELSE 部分时，CASE WHEN 表达式在不满足条件时会默认返回NULL。
• COUNT(*): 计算每个部门的总员工数量。

步骤3：计算加班比例

我们计算每个部门的加班员工比例，并使用ROUND和CONCAT函数格式化输出：

SELECT 
    department,
    CONCAT(ROUND(overtime_count / total_count * 100, 1), '%') AS ratio
FROM 
    overtime_counts
ORDER BY 
    ratio DESC;

• ROUND(overtime_count / total_count * 100, 1): 计算加班比例并保留一位小数。
• CONCAT(..., '%'): 将结果格式化为百分数。

完整代码

WITH work_hours AS (
    SELECT 
        s.department,
        a.staff_id,
        TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60.0 AS hours_worked
    FROM 
        staff_tb s
    JOIN 
        attendent_tb a ON s.staff_id = a.staff_id
),
overtime_counts AS (
    SELECT 
        department,
        COUNT(CASE WHEN hours_worked > 9.5 THEN 1 END) AS overtime_count,
        COUNT(*) AS total_count
    FROM 
        work_hours
    GROUP BY 
        department
)
SELECT 
    department,
    CONCAT(ROUND(overtime_count / total_count * 100, 1), '%') AS ratio
FROM 
    overtime_counts
ORDER BY 
    ratio DESC;