mysql工作日期计算工龄 sql计算工作时长

本文交代了一种求上班总时长的特殊的业务场景，并造了一个玩具数据 ，分别用MySQL和Hive SQL给出了其计算逻辑。

---

一、业务背景及口径说明

指标 上班总时长 = SUM(下班时间-上班时间) ，但在特殊的业务场景下并没有那么容易得到，如：

• 某员工一天的操作为：1→1→1→2→2（上班打卡为1，下班打卡为2）。
• 因为可以重复打卡，该员工打完卡后不确定是否打卡成功，又进行多次操作。

此时，定义指标 上班总时长 口径：

1. 员工每天第一次的上班打卡时间为上班时间，第一次下班打卡时间为下班时间（即 1→1→1→2→2 取加粗的情况）；
2. 上班总时长 = SUM(下班时间-上班时间)。

对于这个口径，该怎么写sql的计算逻辑呢？

---

二、计算逻辑

2.1 MySQL

MySQL 8.0之前的版本不支持窗口函数，此处不使用窗口函数。

在此，先造一个玩具数据用于说明：

-- 直接粘贴进查询语句里执行即可，会创建一个test数据库及test数据库里的work_time1表
-- create test database
create database if not exists test character set utf8;
-- DDL
drop table if exists test.work_time1;
create table test.work_time1 (
    id int not null auto_increment comment '主键，表示记录数'
  , user_id int not null comment '用户编号'
  , user_type int not null comment '用户类型：1表示上班，2表示下班'
  , create_time datetime not null comment '创建时间'
  , primary key(id)
) engine=innodb default charset=utf8
;
-- insert data
insert into test.work_time1(
    user_id
  , user_type
  , create_time
)
values(101, 1, '2020-01-05 09:00:00')
    , (101, 1, '2020-01-05 09:05:30')
    , (102, 1, '2020-01-05 09:15:02')
    , (101, 2, '2020-01-05 18:06:31')
    , (102, 2, '2020-01-05 18:30:31')
    , (102, 2, '2020-01-05 18:31:21')
    , (101, 1, '2020-01-06 08:45:22')
    , (101, 2, '2020-01-06 18:07:28')
    , (101, 1, '2020-01-07 09:30:02')
    , (101, 2, '2020-01-07 18:10:30')
    , (101, 2, '2020-01-07 18:15:21')
;

表的结果如下所示：

id	user_id	user_type	create_time
1	101	1	2020-01-05 09:00:00
2	101	1	2020-01-05 09:05:30
3	102	1	2020-01-05 09:15:02
4	101	2	2020-01-05 18:06:31
5	102	2	2020-01-05 18:30:31
6	102	2	2020-01-05 18:31:21
7	101	1	2020-01-06 08:45:22
8	101	2	2020-01-06 18:07:28
9	101	1	2020-01-07 09:30:02
10	101	2	2020-01-07 18:10:30
11	101	2	2020-01-07 18:15:21

其中：

• id: 主键，仅表示记录数；
• user_id: 员工id；
• user_type: 员工操作类型，1表示上班，2表示下班；
• create_time: 创建时间。

由此可知，上班时间的计算逻辑（if user_type = 1 then create_time），下班时间的计算逻辑（if user_type = 2 then create_time）。
因为数据较少，可以先手工计算，最后与sql输出的结果做对比。只保留每天第一次的上下班记录的话，最后保留的记录只有1、3、4、5、7、8、9、10这10条记录。

-- 员工101的上班总时长：27.1514小时
select ((unix_timestamp('2020-01-05 18:06:31') - unix_timestamp('2020-01-05 09:00:00'))
	+ (unix_timestamp('2020-01-06 18:07:28') - unix_timestamp('2020-01-06 08:45:22'))
	+ (unix_timestamp('2020-01-07 18:10:30') - unix_timestamp('2020-01-07 09:30:02'))) / 3600
-- 员工102的上班总时长：9.2581小时
select (unix_timestamp('2020-01-05 18:30:31') - unix_timestamp('2020-01-05 09:15:02')) / 3600

如上所示，手工计算最后员工101的上班总时长为27.1514小时，员工102的上班总时长为9.2581小时。
接下来用MySQL写计算逻辑：

step 1: 数据清洗，仅保留需要的记录
可以将该问题理解为求组内 TopN 问题，在这个场景下就是求每个员工在每天每个打卡行为（上班或下班）时的最早的时间：
思路1：
因为是 Top1 问题，只要满足在组（user_id, date(create_time), user_type）中，create_time取最小值即可。

select id
	, user_id
	, user_type
	, create_time
from test.work_time1
where (user_id, date(create_time), user_type, create_time) in
    (
	  select user_id, date(create_time), user_type, min(create_time)
	  from test.work_time1
	  group by user_id, date(create_time), user_type
	)
;

思路2：
可以将组内最早的时间理解为：组内比该时间还早的时间数 < 1。

select id 
	, a.user_id
	, a.user_type
	, a.create_time
from test.work_time1 a
where 1 > (select count(*)
					 from test.work_time1 b
					 where b.user_id = a.user_id
							 and date(b.create_time) = date(a.create_time)
							 and b.user_type = a.user_type
							 and b.create_time < a.create_time)
;

思路1和2得到的结果都是下图所示结果，与肉眼判断的id记录一致。

现在得到的数据就很干净啦，每个员工每天就只有两条记录，即分别是上班打卡和下班打卡。

step 2: 计算总时长

只需要分别计算每个员工每天的上班时长，然后加起来即可。

但是sql里的字段计算操作都是对于行的，怎么把每个员工每天的下班时间与上班时间合并到一行里呢？

这里用一个关联，将step 1的sql结果分别作为临时表t1和t2关联起来，关联条件是同一个员工、同一天、t2.user_type - t1.user_type = 1（2-1=1)

• Q：有没有更简单的通过step 1来得到每个员工的上班总时长的方法呢？
• 备注：

1. 这里用了step 1中的思路2的计算逻辑，因为它效率略微高一些；
2. setp 1里可以将t1作为员工的上班时间表与t2作为下班时间表，再在step 2关联起来，更直观。

-- 计算每个员工的上班总时长
select t1.user_id
	, sum(unix_timestamp(t2.create_time) - unix_timestamp(t1.create_time)) / 3600 work_time
from 
	(
		select a.user_id
			, a.user_type
			, a.create_time
		from test.work_time1 a
		where 1 > (select count(*)
							 from test.work_time1 b
							 where b.user_id = a.user_id
									 and date(b.create_time) = date(a.create_time)
									 and b.user_type = a.user_type
									 and b.create_time < a.create_time)
	) t1
	left join 
	(
		select a.user_id
			, a.user_type
			, a.create_time
		from test.work_time1 a
		where 1 > (select count(*)
							 from test.work_time1 b
							 where b.user_id = a.user_id
									 and date(b.create_time) = date(a.create_time)
									 and b.user_type = a.user_type
									 and b.create_time < a.create_time)
	) t2 on t1.user_id = t2.user_id and date(t1.create_time) = date(t2.create_time)
				and 1 = t2.user_type - t1.user_type
where t1.user_type = 1
group by t1.user_id
;

得到结果如下：

结果与手工计算一致，代码无误。

2.2 Hive SQL

在 hive 中使用 lag 分析窗口函数可以更高效的得到我们想要的结果。
先造个数据，数据同上。

create database if not exists test comment '测试数据库';
drop table if exists test.work_time1;
create table test.work_time1 (
    user_id int comment '用户编号'
  , user_type int comment '用户类型：1表示上班，2表示下班'
  , create_time string comment '创建时间'
)
;
-- insert data
insert into test.work_time1(
    user_id
  , user_type
  , create_time
)
values(101, 1, '2020-01-05 09:00:00')
    , (101, 1, '2020-01-05 09:05:30')
    , (102, 1, '2020-01-05 09:15:02')
    , (101, 2, '2020-01-05 18:06:31')
    , (102, 2, '2020-01-05 18:30:31')
    , (102, 2, '2020-01-05 18:31:21')
    , (101, 1, '2020-01-06 08:45:22')
    , (101, 2, '2020-01-06 18:07:28')
    , (101, 1, '2020-01-07 09:30:02')
    , (101, 2, '2020-01-07 18:10:30')
    , (101, 2, '2020-01-07 18:15:21')
;

简要介绍一个 lag 函数，它的主要作用是将数据向后偏移：

lag(col, n, default) over(partition by … order by …)

• lag中的内容：col表示字段名称，即对哪个字段使用lag函数；n表示滞后阶数；default表示滞后后没有值的位置用什么填充；
• over中的内容：partition by 后接需要作为分区的字段（可多个，也可以不使用这个关键字，即所有数据为一个分区），order by 即在每个分区中排序

总而言之，对某个字段使用 lag 函数，按照指定分区或排序规则（可以不指定分区或排序规则），生成一个新的列。
可以理解为，lag后字段看到的是原字段前一行中的值。
假如一个员工在一段时间（几天）的上下班打卡操作为：12112的试想一下，我们要取的1之前是不是只有没有值（即1是该员工这几天第一个操作）或2，我们要取的2前一定是1。

with a as (
    select user_id
        , user_type
        , create_time
        , lag(user_type, 1, null) over(partition by user_id order by create_time) type_lag
    from xn_test.work_time1
),
b as (
    select a.user_id
        , a.user_type
        , a.create_time
        , row_number() over(partition by a.user_id order by a.create_time) rn
    from a
    where (a.user_type = 1 and (a.type_lag is null or a.type_lag = 2))
        or (a.user_type = 2 and a.type_lag = 1)
),
c as (
    select b.user_id
        , case when b.rn % 2 = 0 then b.rn - 1 else b.rn end
        , (unix_timestamp(max(b.create_time)) - unix_timestamp(min(b.create_time))) / 3600 work_time
    from b
    group by b.user_id
        , case when b.rn % 2 = 0 then b.rn - 1 else b.rn end
)
select c.user_id
    , sum(work_time) work_time
from c
;