1.7 每个用户连续登陆的最大天数?
uid,date
1,2019-08-01
1,2019-08-02
1,2019-08-03
2,2019-08-01
2,2019-08-02
3,2019-08-01
3,2019-08-03
4,2019-07-28
4,2019-07-29
4,2019-08-01
4,2019-08-02
4,2019-08-03
结果如下:
uid cnt_days
1 3
2 2
3 1
4 3
create table if not exists login1(
uid string,
date1 string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile
;
load data local inpath '/root/hivedata/login1.txt' into table login1;
select uid,max(cn)
from
(select
uid,
date1,
count(1) cn
from
(select
t1.uid uid,
date_sub(t1.date1,t1.r) date1
from
(select
uid,
date1,
row_number() over(distribute by uid sort by date1) r
from login1) t1) t2
group by uid,date1) t3
group by uid
;