作为人力资源员工管理人员,公司经常会对员工考勤进行统计,有些领导会根据员工连续考勤最大天数数据作为考核基础。下面就来用代码完成这个需求,在我之前的博客中有关怎么统计连续天数(难点就是怎么将周末此处后算连续打卡),博客链接:hive中统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数
一、 准备模拟数据
cat temp_fuyun_attendance_tb.txt #分隔符为制表符:\t
2019-11-01 YG-00001 上班
2019-11-02 YG-00001 上班
2019-11-03 YG-00001 缺勤
2019-11-04 YG-00001 上班
2019-11-05 YG-00001 上班
2019-11-06 YG-00001 上班
2019-11-07 YG-00001 上班
2019-11-08 YG-00001 上班
2019-11-09 YG-00001 缺勤
2019-11-10 YG-00001 缺勤
2019-11-11 YG-00001 上班
2019-11-12 YG-00001 上班
2019-11-13 YG-00001 上班
2019-11-14 YG-00001 上班
2019-11-15 YG-00001 缺勤
2019-11-16 YG-00001 缺勤
2019-11-17 YG-00001 缺勤
2019-11-18 YG-00001 上班
2019-11-19 YG-00001 上班
2019-11-20 YG-00001 上班
2019-11-01 YG-00002 上班
2019-11-02 YG-00002 缺勤
2019-11-03 YG-00002 缺勤
2019-11-04 YG-00002 上班
2019-11-05 YG-00002 上班
2019-11-06 YG-00002 上班
2019-11-07 YG-00002 上班
2019-11-08 YG-00002 上班
2019-11-09 YG-00002 缺勤
2019-11-10 YG-00002 缺勤
2019-11-11 YG-00002 上班
2019-11-12 YG-00002 上班
2019-11-13 YG-00002 上班
2019-11-14 YG-00002 上班
2019-11-15 YG-00002 上班
2019-11-16 YG-00002 缺勤
2019-11-17 YG-00002 缺勤
2019-11-18 YG-00002 上班
2019-11-19 YG-00002 上班
2019-11-20 YG-00002 上班
二、 在Hive中创建对应的表
create table temp.temp_fuyun_attendance_tb (
date_col string,
employee_no string,
is_punch string
)
row format delimited fields terminated by '\t';
三、 将模拟数据上传到Hive表对应的hdfs目录下
hdfs dfs -put temp_fuyun_attendance_tb.txt /user/hive/warehouse/temp.db/temp_fuyun_attendance_tb/
四、 统计员工连续打卡最大天数(剔除周末)
with t1_tb as
(
select date_col, employee_no, week_which_day,
date_sub(date_col, (row_number() over(partition by employee_no order by date_col) - 1)) from_day --连续打卡的开始日期
from
(
select date_col, employee_no, is_punch,
pmod(datediff(date_col, '2012-01-01'), 7) week_which_day --标记周几,周日为0
from temp.temp_fuyun_attendance_tb
group by date_col, employee_no, is_punch
) t
--将缺勤的数据过滤,但保留周末的数据(如果过滤周末数据,则每个员工最大连续打卡天数为5)
where is_punch = '上班' or week_which_day in (6, 0)
)
select employee_no, start_date, end_date, continuous_days
from
(
select employee_no, start_date, end_date, continuous_days,
row_number() over(partition by employee_no order by continuous_days desc) rn
from
(
select employee_no,
min(date_col) start_date, --连续打卡的开始日期
max(date_col) end_date, --连续打卡的结束日期
count(1) continuous_days --连续打卡天数
from t1_tb
where week_which_day not in (6, 0)
group by from_day, employee_no
) t
) t1
where rn = 1 --每个员工只取连续打卡最大天数的记录