直接来看实战,现在有一张表t,这张表存储了每个员工每天的打卡情况,现在需要统计截止目前每个员工的连续打卡天数,表t如下表所示:
uid tdate is_flag
1 2020/2/1 1
1 2020/2/2 0
1 2020/2/3 1
1 2020/2/4 1
1 2020/2/5 0
1 2020/2/6 1
1 2020/2/7 1
1 2020/2/8 1
2 2020/2/1 1
2 2020/2/2 0
2 2020/2/3 0
2 2020/2/4 1
2 2020/2/5 1
2 2020/2/6 1
2 2020/2/7 1
2 2020/2/8 1
上表中uid是用户id,tdate是日期,is_flag是记录用户当天是否打卡,1为打卡,0为未打卡。
create table dk(
uid int,tdate string,is_flag int
)
row format delimited fields terminated by '\t'
stored as textfile
;
load data local inpath '/opt/module/data/dk.txt' into table dk;
我们希望得到的结果为:
uid flag_days
1 3
2 5
这个逻辑还是挺难想的,第一个想法就是通过前后数据偏移来实现,就是将is_flag向前移动一行或者向后移动一行,然后和原来的is_flag标签做差,如果结果为0,说明前后两天的值是相同的,要么都是0,要么都是1。但是还是不能够得出我们想要的结果。
再换一种思路:如果是连续打卡,那么打卡日期与一个递增的数字依次做差的结果值应该是相等的,不理解这句话没关系,看具体结果你就明白了。
我们先获取每个用户在这一段时间内所有打卡的排名,是所有打卡的排名哦,利用的是窗口函数的row_number(),代码如下:
select
uid
,tdate
,row_number() over(partition by uid order by tdate) date_rank
from
dk
where is_flag=1;
运行上面的代码,可以得到如下结果:
uid tdate date_rank
1 2020/2/1 1
1 2020/2/3 2
1 2020/2/4 3
1 2020/2/6 4
1 2020/2/7 5
1 2020/2/8 6
2 2020/2/1 1
2 2020/2/4 2
2 2020/2/5 3
2 2020/2/6 4
2 2020/2/7 5
2 2020/2/8 6
接着再获取每个打卡日期(tdate)中的日与其打卡日期排名(date_rank)之间的差,比如uid=1的2020/2/3的打卡日期中的3号与其排名(date_rank)2做差等于1,实现代码如下:
select
uid
,tdate
,date_rank
,(day(date_format(regexp_replace(tdate,'/','-'),"yyyy-MM-dd")) - date_rank) as day_cha
from
(
select
uid
,tdate
,row_number() over(partition by uid order by tdate) date_rank
from
dk
where is_flag=1
)t1;
运行上面的代码,最后可以得到如下结果:
uid tdate date_rank day_cha
1 2020/2/1 1 0
1 2020/2/3 2 1
1 2020/2/4 3 1
1 2020/2/6 4 2
1 2020/2/7 5 2
1 2020/2/8 6 2
2 2020/2/1 1 0
2 2020/2/4 2 2
2 2020/2/5 3 2
2 2020/2/6 4 2
2 2020/2/7 5 2
2 2020/2/8 6 2
看上面的结果表,有没有看出点意思来,连续打卡日期的day_cha都是相等的,比如uid=1的2020/2/3和2020/2/4是连续的,他们的day_cha都是1。到这里,如果我们要获取连续打卡天数是不是就很容易了。
不过这里面还有一个问题,就是连续打卡天数是截止目前最近的一个 连续打卡天数还是历史坚持最长的打卡天数,这就是传说中的口径问题哈。虽然在我们这个例子里面,这两种打卡天数的出来的结果是一样的,但是有的时候会是不一样的,比如下面这样的例子:
uid tdate is_flag
1 2020/2/1 1
1 2020/2/2 0
1 2020/2/3 1
1 2020/2/4 1
1 2020/2/5 1
1 2020/2/6 0
1 2020/2/7 1
1 2020/2/8 1
上面这个例子中,最近连续打卡天数是2,历史最长的连续打卡天数却是3。
好了,我们继续回到解题上,我们先获取每个用户历史所有连续过得的打卡情况,实现代码如下:
select
uid
,day_cha
,count(tdate) flag_days
from
(select
uid
,tdate
,date_rank
,(day(date_format(regexp_replace(tdate,'/','-'),"yyyy-MM-dd")) - date_rank) as day_cha
from
(
select
uid
,tdate
,row_number() over(partition by uid order by tdate) date_rank
from
dk
where is_flag=1
)t1
)t2
group by
uid
,day_cha;
运行上面的代码,得到如下结果:
uid day_cha flag_days
1 0 1
1 1 2
1 2 3
2 0 1
2 2 5
要获取最近的连续打卡天数,我们只需要把上表中day_cha这一列最大的值对应的flag_days取出来就可以;要获取历史最久的连续打卡天数,我们只需要把上表中flag_days的最大值取出来就可以。直接再来个子查询就好了。
类似的需求可能还有获取过去连续打卡天数大于某个值得人,只需要筛选上表中的flag_days即可达到目的。只要能够生成上面这样每个人历史所有连续打卡的情况表,那么大部分连续打卡相关的需求都可以通过上表来获得。