描述
某公司员工信息数据及单日出勤信息数据如下:
员工信息表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;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*