连续登录天数

表结构如图

1.每个ID最大的连续登录天数

先找到连续的可以和日期参照的数字

select id,
       login_date,
       row_number() over(partition by id order by login_date) as rn
  from login

使用该连续的数字与日期做差

select id,
       login_date,
       row_number() over(partition by id order by login_date) as rn,
       to_char(login_date, 'ddd') - row_number() over(partition by id order by login_date) as date_sub
  from login

 

 看到连续登录的话，相减的值是相同的，根据ID和该值 Group By即可获得每次连续登录的天数 

select id, date_sub, count(1)
  from (select id,
               login_date,
               row_number() over(partition by id order by login_date) as rn,
               to_char(login_date, 'ddd') - row_number() over(partition by id order by login_date) as date_sub
          from login)
 group by id, date_sub

 

再套一层max即可获得连续登录最大天数

select id, max(ct)
  from (select id, date_sub, count(1) as ct
          from (select id,
                       login_date,
                       row_number() over(partition by id order by login_date) as rn,
                       to_char(login_date, 'ddd') - row_number() over(partition by id order by login_date) as date_sub
                  from login)
         group by id, date_sub)
 group by id

 

不过  to_char(login_date, 'ddd')  返回的是当年天数，如果跨年的话，就不能算出连续登录

插入一条跨年数据，以上解法失效

 改为 login_date 与 连续的rn相减，如果 login_date 也是连续的，则做出的差值是相同的。

select id, max(ct)
  from (select id, date_sub, count(1) as ct
          from (select id,
                       login_date,
                       row_number() over(partition by id order by login_date) as rn,
                       login_date - row_number() over(partition by id order by login_date) as date_sub
                  from login)
         group by id, date_sub)
 group by id

2.每个ID的最大连续登录天数，开始时间和结束时间。

在刚刚最里面的子查询外，加一个 max,min，即可获得每次连续登录的开始时间和结束时间 

 取到每个id 对应的 CT 最大的记录即可 

select *
  from (select id,
               start_date,
               end_date,
               ct,
               max(ct) over(partition by id) as max_ct
          from (select id,
                       date_sub,
                       min(login_date) as start_date,
                       max(login_date) as end_date,
                       count(1) as ct
                  from (select id,
                               login_date,
                               row_number() over(partition by id order by login_date) as rn,
                               login_date - row_number() over(partition by id order by login_date) as date_sub
                          from login)
                 group by id, date_sub))
 where ct = max_ct