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

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

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

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

department	ratio
dep1	25.0%
dep2	0.0%

解释：dep1部门共有4名员工，其中仅有员工6(Ben)在该日加班，所以结果为1/4=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%

答案

解法1：

select
    s.department,
    concat (
        round(
            sum(
                case
                    when timestampdiff (minute, a.first_clockin, a.last_clockin) > 9.5 * 60 then 1
                    else 0
                end
            ) / count(s.staff_id) * 100,
            1
        ),
        '%'
    ) ratio
from
    staff_tb s
    left join attendent_tb a on s.staff_id = a.staff_id
group by
    s.department
order by
    ratio desc;

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