连续登陆天数+最大登陆天数

转载：https://www.cnblogs.com/jiaxinwei/p/13936273.html

SQL计算最长登录天数

计算最长登陆天数主要用两个函数：1.窗口函数row_number()over() 2.date_sub()

1.使用row_number()窗口函数

1 select 
2     UID,
3     loadtime,
4     row_number()over(partition by UID order by loadtime) sort
5 from 
6     user_login

2.使用date_sub()函数

--取每个用户的连续登陆天数
 1 select 
 2     UID,
 3     date_sub(loadtime,sort) as date_group, --拿登陆时间和排序相减，如果差值是定值，可以分成一组，说明用户是连续登陆的
 4     min(loadtime) as start, 
 5     max(loadtime) as end ,
 6     count(1) as continue_days   --连续登陆天数
 7 from(
 8     select 
 9         UID,
10         loadtime,
11         row_number()over(partition by UID order by loadtime) sort
12     from 
13         user_login
14 ) a
15 group by 
16     UID,
17     date_sub(loadtime,sort)

3.以UID分组，取max(continue_days)

 1 --- 用户最大登陆天数
 2 SELECT
 3     UID,
 4     max(continue_days) as maxday
 5 FROM(
 6     select 
 7         UID,
 8         date_sub(loadtime,sort) as date_group,
 9         min(loadtime) as start, 
10         max(loadtime) as end ,
11         count(1) as continue_days
12     from(
13         select 
14             UID,
15             loadtime,
16             row_number()over(partition by UID order by loadtime) sort
17         from 
18             user_login
19         ) a
20     group by 
21         UID,
22         date_sub(loadtime,sort)
23 )t1
24 group by UID