本文交代了一种求上班总时长的特殊的业务场景,并造了一个玩具数据 ,分别用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记录一致。

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


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

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
;

得到结果如下:

mysql工作日期计算工龄 sql计算工作时长_计算逻辑_02


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

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
;