sql计算某个日期区间用户连续登陆最大天数

例：测试数据如下表（user_login_table）

注：对于工作日（上周五和下周一也界定为连续），则方法二适用

实现思路一:

1、使用开窗函数row_number() 对每个user_id 的登陆日期进行排序，得到排名rn

2、然后用login_date减去排名rn，得到一个新的日期new_date字段，比如，‘2021-10-01’减去1得到‘2021-09-30’，‘2021-10-02’减去2得到‘2021-09-30’…，减完之后得到的日期相同，则说明登陆日期连续

3、之后按user_id和new_date，进行计数，得到login_days

4、最后按user_id分组，取每个user_id对应login_days的最大值，即为该user_id的最大登陆天数max_login_days

SQL:

select user_id,max(login_days) max_login_days

from (select user_id,new_date,count(*) login_days

from (select user_id,

login_date,

row_number() over(partition by user_id order by login_date) rn,

login_date - row_number() over(partition by user_id order by login_date) new_date

from user_login_table)a

group by user_id, new_date)b

group by user_id;

实现思路二：

1、自己创建一个日期表（包含分析期间的所有日期），表名(dic_date_table)

2、使用开窗函数row_number() 对每个user_id 的登陆日期进行排序，得到排名rn

3、使用开窗函数row_number()对日期表中的日期(dic_date)进行排序，得到rn_1

4、将步骤2和3得到的结果，通过日期字段关联，然后用rn_1 - rn得到 num

5、之后按user_id和num，进行计数，得到login_days

6、最后按user_id分组，取每个user_id对应login_days的最大值，即为该user_id的最大登陆天数max_login_days

　
　将上面两表关联（login_date = dic_date）得到以下结果：

SQL:

select T.user_id, max(login_days) max_login_days

from (select t.user_id, t.num, count(*) login_days

from (select a.user_id, b.rn_1 - a.rn num

from （select user_id,

login_date,

row_number() over(partition by user_id order by login_date) rn

from user_login_table)a

left join (select dic_date,

row_number() over(order by dic_date) rn_1

from dic_date_table)b

       　　  on a.login_date = b.dic_date)t

group by t.user_id, t.num)T

group by T.user_id;