推荐阅读文章列表

大数据开发面经汇总【持续更新...】

我的大数据学习之路

大数据开发面试笔记V6.0

SQL题目

有一张员工扫脸进出园区的天表dwd_emp_face_di，包含emp_id（员工id）、time（扫脸时间）、in_flag（是否进入园区）

问题：计算2024年12月13日打卡时长不满8小时的员工名单

-- 举例如下：
-- 输入
emp_id     time          in_flag
001   2024-12-13 9:00       1
001   2024-12-13 19:00      0
002   2024-12-13 8:00       1
002   2024-12-13 9:00       0
002   2024-12-13 12:00      1
002   2024-12-13 14:00      0
002   2024-12-13 18:00      1
002   2024-12-13 20:00      0

-- 输出
emp_id
002

答案解析

模拟数据

create table dwd_emp_face_di (
emp_id varchar(20),
time varchar(20),
in_flag bigint
);
INSERT INTO dwd_emp_face_di VALUES 
('001','2024-12-13 9:00',1),
('001','2024-12-13 19:00',0),
('002','2024-12-13 8:00',1),
('002','2024-12-13 9:00',0),
('002','2024-12-13 12:00',1),
('002','2024-12-13 14:00',0),
('002','2024-12-13 18:00',1),
('002','2024-12-13 20:00',0)
;

思路分析

如果一个员工每天只有一次进入和出去的记录，那么员工时长就很容易计算了；但是员工可以进进出出很多次，如何计算总时长呢？

• 我们先试想一下如何计算总的员工时长，是不是就累计每次员工进出的出去时间-进入时间就可以了？
• 所以关键点在于把员工的每次进入和出去建立mapping关系
• 只需要将两条记录放入同一个分组即可，如何构建分组是多数SQL题的难点
• 我们可以通过sum(in_flag) over(partition by emp_id order by time)即可完成分组

具体代码

select 
  emp_id
from (
  select 
    emp_id,
    time_to_sec(timediff(max(ds), min(ds))) diff_sec
  from (
    select 
      emp_id,
      str_to_date(time, '%Y-%m-%d %H:%i') as ds,
      sum(in_flag) over(partition by emp_id order by str_to_date(time, '%Y-%m-%d %H:%i')) gid
    from dwd_emp_face_di
    where date(time) = "2024-12-13"
  ) t
  group by emp_id, gid
) t
group by emp_id
having sum(diff_sec) <= 8 * 60 * 60
;