首页 > 试题广场 >

统计加班员工占比

[编程题]统计加班员工占比
  • 热度指数：34416 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
某公司员工信息数据及单日出勤信息数据如下：
员工信息表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%
算法知识视频讲解
添加回答
roll__头像 roll__
timestampdiff 中第一个参数如果用hour会导致小时被省略部分
发表于 2025-01-01 23:02:19 回复(3)
弓_长_张头像 弓_长_张 
select department, 
concat(round(sum(case when timestampdiff(second, first_clockin, last_clockin)/3600 >9.5 then 1 else 0 end)/count(staff_tb.staff_id) * 100,1),'%') ratio
from staff_tb
    right join attendent_tb on staff_tb.staff_id = attendent_tb.staff_id
group by department
order by ratio desc


思路：本题核心是：判断是否为加班。可以用case结合timestampdiff函数判断。
另外：求加班与非加班人数比。可以将加班的人数then1 else0 然后sum即可。总人数count即可
发表于 2025-01-06 11:00:26 回复(0)
ACM摸鱼侠头像 ACM摸鱼侠 
select 
    department,
    concat(round(sum(if(timestampdiff(minute, first_clockin, last_clockin)/60>9.5, 1, 0))/count(*)*100, 1), '%') as ratio
from    staff_tb s 
join    attendent_tb a  on  s.staff_id = a.staff_id
group by    department
order by    ratio desc;

发表于 2025-02-19 16:02:33 回复(0)
七分糖少冰a头像 七分糖少冰a 
select  
    department,
    concat(round(sum(case when ((timestampdiff(minute,first_clockin,last_clockin)/60)>9.5) then 1 else 0 end)/count(st.staff_id)*100,1),'%') ratio
from
    attendent_tb as at
    inner join staff_tb as st on at.staff_id=st.staff_id
group by
    department
order by
    ratio desc
又完美地完成一题
发表于 2025-11-05 16:33:38 回复(0)
4fbd81e头像 4fbd81e
timestampdiff例题。 

SELECT 
    s.department,
    CONCAT(ROUND(SUM(CASE 
                    WHEN TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) > 570 THEN 1
                    ELSE 0
                END) / COUNT(s.staff_id) * 100, 1), '%') AS ratio
FROM
    staff_tb s
JOIN
    attendent_tb a ON s.staff_id = a.staff_id
GROUP BY
    s.department
ORDER BY
    ratio DESC;


发表于 2025-04-26 21:14:57 回复(0)
牛客292332323号头像 牛客292332323号
select t2.department
,concat(round(count(distinct case when (unix_timestamp(last_clockin)-unix_timestamp(first_clockin))/(60*60)>9.5 then t1.staff_id else null end) /count(distinct t1.staff_id)*100,1),'%') ratio
from attendent_tb t1
left join staff_tb t2
on t1.staff_id=t2.staff_id
group by t2.department
order by ratio desc
发表于 2026-01-27 15:27:19 回复(0)
不知道哦_头像 不知道哦_ 
select
    a.department,
    concat(
        round(
            (
                sum(
                    case
                        when timestampdiff(minute, b.first_clockin, b.last_clockin) / 60 > 9.5 then 1
                        else 0
                    end
                ) / count(a.staff_id) * 100
            ),
            1
        ),
        '%'
    ) as ratio
from
    staff_tb a
    left join attendent_tb b on a.staff_id = b.staff_id
group by
    a.department
order by
    ratio desc;


发表于 2026-01-24 12:46:19 回复(0)
虫哇头像 虫哇
with t1 as (
    select sta.department,
    timestampdiff(minute, at.first_clockin, at.last_clockin) as duration
    from staff_tb sta
    right join attendent_tb at
    on sta.staff_id = at.staff_id
)

select department,
concat(round(sum(case when duration > 570 then 1 else 0 end) * 100/ count(*), 1), '%') as ratio
from t1
group by department
order by ratio desc
发表于 2026-01-23 20:53:11 回复(0)
牛客82180625号头像 牛客82180625号
select
department,
concat(round(count(if(timestampdiff(minute,first_clockin,last_clockin)/60 > 9.5,1,null))/count(*)*100,1),'%') as ratio
from staff_tb t1
join attendent_tb t2
on t1.staff_id = t2.staff_id
group by department
order by ratio desc
发表于 2026-01-22 13:59:09 回复(0)
有担当的黑眼圈在做毕设头像 有担当的黑眼圈在做毕设 
select st.department, concat(round(sum(case 
    when timestampdiff(minute,at.first_clockin,at.last_clockin) > 570 then 1
    else 0
end)/count(*)*100,1),'%') as ratio
from staff_tb as st join attendent_tb as at on st.staff_id = at.staff_id
group by st.department
order by ratio desc;

发表于 2026-01-20 01:59:34 回复(0)
77今天学习了没头像 77今天学习了没 
select department,
concat(round(sum(if(timestampdiff(minute,first_clockin,last_clockin)>9.5*60,1,0))*100/count(1),1),'%') as ratio
from attendent_tb as a
left join staff_tb as s using(staff_id)
group by 1


发表于 2026-01-19 16:50:31 回复(0)
风流倜傥三金老师头像 风流倜傥三金老师
WITH
t1 AS (
SELECT
    staff_id,
    TIMESTAMPDIFF(SECOND,first_clockin,last_clockin) / 3600 AS sj
FROM attendent_tb
)

SELECT
    b.department,
    CONCAT(
        ROUND(
            AVG(CASE WHEN t1.sj > 9.5 THEN 1 ELSE 0 END) * 100
        ,1)
    ,'%') AS ratio
FROM t1
INNER JOIN staff_tb AS b ON t1.staff_id = b.staff_id
GROUP BY b.department
ORDER BY ratio DESC;
发表于 2026-01-18 18:26:20 回复(0)
牛客603971499号头像 牛客603971499号
select department,concat(round(avg(if(timestampdiff(minute,first_clockin,last_clockin)/60>9.5,1,0))*100,1),'%') as ratio
from staff_tb s join attendent_tb a on s.staff_id=a.staff_id
group by department
order by ratio
发表于 2026-01-18 16:55:57 回复(0)
牛客192267230号头像 牛客192267230号
with t1 as (select department,timestampdiff(second,first_clockin,last_clockin)/3600 as 工作时长,
case when timestampdiff(second,first_clockin,last_clockin)/3600 >9.5 then 1 else 0 end as 分组
from attendent_tb t1
left join staff_tb t2 on t1.staff_id=t2.staff_id)
select department,concat(round(sum(分组)/count(*)*100,1),'%') as ratio
from t1
group by department
order by ratio desc
发表于 2026-01-15 19:12:35 回复(0)
jojozyh头像 jojozyh 
SELECT department, 
    CONCAT(ROUND(AVG(IFNULL(TIMESTAMPDIFF(SECOND, first_clockin, last_clockin) / 3600, 0) > 9.5) * 100, 1), '%') AS ratio
FROM attendent_tb RIGHT JOIN staff_tb USING (staff_id)
GROUP BY 1
ORDER BY 2 DESC

发表于 2026-01-11 05:40:29 回复(0)
大橘已定🍊头像 大橘已定🍊 
select 
    b.department as department,
    concat(round(avg(case 
        when timestampdiff(minute, a.first_clockin, a.last_clockin)/60 > 9.5 then 1 
        else 0 
    end) * 100, 1), '%') as ratio
from staff_tb b
left join attendent_tb a 
on b.staff_id = a.staff_id
group by b.department
order by 2 desc;

发表于 2026-01-07 18:43:28 回复(0)
可爱的王者天天摸鱼头像 可爱的王者天天摸鱼
select department, concat(round(sum(case when time >9.5 then 1 else 0 end)*100/count(*),1),'%') as ratio from (
select department, timestampdiff(minute,first_clockin,last_clockin)/60  as time
from attendent_tb a join  staff_tb s on a.staff_id = s.staff_id)t1 group by  department order by  ratio desc
发表于 2026-01-06 15:29:12 回复(0)
炎煬头像 炎煬
select department,concat(round(100*sum(if(timestampdiff(minute,first_clockin,last_clockin)/60>9.5,1,0))/count(a.staff_id),1),"%") as ratio
from staff_tb a
left join attendent_tb b
on a.staff_id=b.staff_id
group by 1
order by 2 desc
有时候要用sumif函数代替count函数
发表于 2026-01-03 14:57:04 回复(0)
爱吃肉的勇士说这不是bug头像 爱吃肉的勇士说这不是bug
要变成分钟，不然小时是向下取整，会导致错误
select
    department,
    concat(
        format(
            sum(
                if(
                    timestampdiff(minute, first_clockin, last_clockin) > 9.5*60,
                    1,
                    0
                )
            ) / count(1) * 100,
            1
        ),
        '%'
    ) ratio
from
    staff_tb s
    left join attendent_tb a on s.staff_id = a.staff_id
group by
    department
order by ratio desc
发表于 2025-12-29 14:30:05 回复(0)
牛客947671679号头像 牛客947671679号
-- 要求输出：部门（department）、加班员工占比（ratio，以百分数形式输出并保留1位小数），查询结果按照加班员工占比降序排序；
select department, concat(round((sum(if(a > 9.5, 1, 0)) / count(department)) * 100, 1), '%') ratio
from (select department, timestampdiff(second, first_clockin, last_clockin) / 3600 a
      from attendent_tb at,
           staff_tb st
      where at.staff_id = st.staff_id) t1
group by department
order by ratio desc;
发表于 2025-12-28 19:22:27 回复(0)
提交观点

问题信息

SQL
难度：
135条回答 858浏览

热门推荐

相关试题

统计加班员工占比

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号