回忆是一座桥
却是通往寂寞的牢

SQL案例 - 统计各岗位员工平均工作时长

描述

某公司员工信息数据及单日出勤信息数据如下:

员工信息表staff_tb(staff_id-员工id,staff_name-员工姓名,staff_gender-员工性别,post-员工岗位类别,department-员工所在部门),如下所示:

staff_id staff_name staff_gender post department
1 Angus male Financial dep1
2 Cathy female Director dep1
3 Aldis female Director dep2
4 Lawson male Engineer dep1
5 Carl male Engineer dep2
6 Ben male Engineer dep1
7 Rose female Financial dep2

出勤信息表attendent_tb(info_id-信息id,staff_id-员工id,first_clockin-上班打卡时间,last_clockin-下班打卡时间),如下所示:

info_id staff_id first_clockin last_clockin
101 1 2022-03-22 08:00:00 2022-03-22 17:00:00
102 2 2022-03-22 08:30:00 2022-03-22 18:00:00
103 3 2022-03-22 08:45:00 2022-03-22 17:00:00
104 4 2022-03-22 09:00:00 2022-03-22 18:30:00
105 5 2022-03-22 09:00:00 2022-03-22 18:10:00
106 6 2022-03-22 09:15:00 2022-03-22 19:30:00
107 7 2022-03-22 09:30:00 2022-03-22 18:29:00

问题:请统计该公司各岗位员工平均工作时长?要求输出:员工岗位类别、平均工作时长(以小时为单位输出并保留三位小数),按照平均工作时长降序排序。

注:如员工未打卡该字段数据会存储为NULL,那么不计入在内。

示例数据结果如下:

post work_hours
Engineer 9.639
Financial 8.992
Director 8.875

解释:Engineer类岗位有4、5、6共计3名员工,工作时长分别为:9.500、9.167、10.250,则平均工作时长为 (9.500+9.167+10.250)/3=9.639小时。

其他结果同理…..

示例1

输入:

drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `attendent_tb` ;   
CREATE TABLE `attendent_tb` (
`info_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`first_clockin` datetime NULL,
`last_clockin` datetime NULL,
PRIMARY KEY (`info_id`));
INSERT INTO attendent_tb VALUES(101,1,'2022-03-22 08:00:00','2022-03-22 17:00:00');
INSERT INTO attendent_tb VALUES(102,2,'2022-03-22 08:30:00','2022-03-22 18:00:00');
INSERT INTO attendent_tb VALUES(103,3,'2022-03-22 08:45:00','2022-03-22 17:00:00');
INSERT INTO attendent_tb VALUES(104,4,'2022-03-22 09:00:00','2022-03-22 18:30:00');
INSERT INTO attendent_tb VALUES(105,5,'2022-03-22 09:00:00','2022-03-22 18:10:00');
INSERT INTO attendent_tb VALUES(106,6,'2022-03-22 09:15:00','2022-03-22 19:30:00');
INSERT INTO attendent_tb VALUES(107,7,'2022-03-22 09:30:00','2022-03-22 18:29:00');

输出:

post|work_hours
Engineer|9.639
Financial|8.992
Director|8.875

答案

解法1:

select
    s.post,
    round(
        avg(
            timestampdiff (MINUTE, a.first_clockin, a.last_clockin)
        ) / 60,
        3
    ) work_hours
from
    attendent_tb a
    join staff_tb s on a.staff_id = s.staff_id
group by
    s.post
order by
    work_hours desc;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 统计各岗位员工平均工作时长
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论