统计加班员工占比_

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

员工信息表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

问题：请统计该公司各部门加班员工所占比例？

注：工作时长大于9.5小时定义为加班

要求输出：部门（department）、加班员工占比（ratio，以百分数形式输出并保留1位小数），查询结果按照加班员工占比降序排序；
示例数据结果如下：

department	ratio
dep1	25.0%
dep2	0.0%

解释：dep1部门共有4名员工，其中仅有员工6(Ben)在该日加班，所以结果为1/4=25.0%

其他结果同理.....

输入

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');

统计加班员工占比

输入

输出

问题信息

热门推荐

相关试题