hive sql 怎么实现循环_Hive_sql如何计算连续签到天数？

首先膜拜下二楼的大神，这个问题我困扰了很久，终于看到答案，我把 @世界第一辣鸡 的过程用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