某公司员工信息数据及单日出勤信息数据如下： 员工信息表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)在该日加班，所以结果为14=25.0% 其他结果同理.....
示例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');

输出

department|ratio
dep1|25.0%
dep2|0.0%
加载中...