首先膜拜下二楼的大神,这个问题我困扰了很久,终于看到答案,我把 @世界第一辣鸡 的过程用HQL给写一遍。
大概思路如下:
1.根据uid进行分组,将所有签到记录依次按照时间排序并标记序号
2.根据时间有序的特点,可以将所有时间减去序号,获取连续签到日期唯一的时间点,记为 data_rank。
如 2017-02-09 序号1
2017-02-10 序号2
2017-02-11 序号 3
减去序号可得唯一时间 2017-02-08
3.后根据id和date_rank进行分组,并计算连续签到次数
-- 建表语句create table sigin(
uid int
sigindate date
) --建表参数自行填写-- 数据插入
insert into sigin values(1,'2017-01-01');
insert into sigin values(1,'2017-01-02');
insert into sigin values(1,'2017-01-03');
insert into sigin values(1,'2017-01-04');
insert into sigin values(2,'2017-01-01');
insert into sigin values(2,'2017-01-02');
insert into sigin values(2,'2017-01-03');
根据开始提供的思路可以写出如下语句
select
uid,
date_sub(c.sigindate,rank) as date_rank,
count(1) as sigincount
from
(
-- 子查询 用作组内序号
select
uid,
sigindate,
-- row_number() over() 相同的uid分到一起,并按照时间排序
row_number() over(partition by uid order by sigindate) as rank
from sigin
) c group by uid ,date_sub(c.sigindate,rank)
上述sql可以找到所用用户连续签到数,但用户有中断时程序就无法满足,如在插入下面两条数据
insert into sigin values(1,'2016-12-30');
insert into sigin values(2,'2016-12-30');
以上结果就回出现偏差,查出来的结果如下:
12016-12-291
12016-12-304
22016-12-291
22016-12-303
一个用户出现了多条记录,分别为多次连续签到产生,若想求出最近连续签到数只获取每个用户最大日期的记录即可,如下sql可以获得
select f.uid,g.sigincount,f.max_rank from (
-- 获取分组后每个用户的最大时间
select d.uid, Max(d.date_rank) as max_rank from (
select
uid,
date_sub(c.sigindate,rank) as date_rank,
count(1) as sigincount
from
(
select
uid,sigindate,row_number() over(partition by uid order by sigindate) as rank
from sigin
) c group by uid ,date_sub(c.sigindate,rank)
) d group by d.uid
) f inner join (
-- 与每个用户连续签到数据进行 join 条件为 uid 和 时间
select
uid,
date_sub(c.sigindate,rank) as date_rank,
count(1) as sigincount
from
(
select
uid,sigindate,row_number() over(partition by uid order by sigindate) as rank
from sigin
) c group by uid ,date_sub(c.sigindate,rank)
) g on f.uid = g.uid and f.max_rank = g.date_rank