这道题目要求我们统计公司各岗位员工的平均工作时长。下面是这个SQL查询的思路和实现步骤。

1. 确定总体问题

目标是计算每个岗位的员工平均工作时长，并按平均工作时长降序排序。工作时长是通过下班打卡时间减去上班打卡时间计算得出的。

2. 分析关键问题

  • 连接表：我们需要将staff_tb和attendent_tb表连接起来，以便获取每个员工的岗位信息和出勤记录。
  • 计算工作时长：对于每个员工，计算其工作时长。
  • 计算平均工作时长：对每个岗位的员工工作时长求平均值。
  • 格式化输出：将平均工作时长保留三位小数，并按降序排序。

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

步骤1：连接表

我们使用JOIN将staff_tb和attendent_tb表连接起来：

FROM
    staff_tb s
JOIN
    attendent_tb a ON s.staff_id = a.staff_id
  • ON s.staff_id = a.staff_id：通过员工ID进行连接，以便获取每个员工的岗位信息和出勤记录。
步骤2：计算工作时长

我们使用TIMESTAMPDIFF函数计算每个员工的工作时长（以分钟为单位），然后将其转换为小时：

TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60
  • TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin)：计算下班打卡时间与上班打卡时间之间的分钟差。
  • / 60：除以六十，将分钟转换为小时。
  • 注意这里直接计算员工工作的小时时长会不准确，所以先计算分钟再转化。
步骤3：计算平均工作时长

我们使用AVG函数对每个岗位的员工工作时长求平均值，并使用ROUND函数保留三位小数：

ROUND(AVG(TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60), 3) AS work_hours
  • AVG(...)：计算每个岗位的员工平均工作时长。
  • ROUND(..., 3)：将结果四舍五入保留三位小数。
步骤4：格式化输出

我们使用ORDER BY按平均工作时长降序排序输出结果：

ORDER BY
    work_hours DESC

完整代码

SELECT
    s.post,
    ROUND(AVG(TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60), 3) AS work_hours
FROM
    staff_tb s
JOIN
    attendent_tb a ON s.staff_id = a.staff_id
WHERE
    a.first_clockin IS NOT NULL
    AND a.last_clockin IS NOT NULL
GROUP BY
    s.post
ORDER BY
    work_hours DESC;