2021-03-22

sqlboy之-关于连续登陆那一些事儿 

个人感觉即考察对sql语法的掌握程度，又比较考验经验和技巧，出题的老司机比较坏 ！

注： 下述代码在mysql 8.0.23环境下执行通过

1、普通版本

有用户登陆日志表login_tab(user_id bigint,login_date date) ,求用户连续登陆最大天数

select
    *
from
(
    select
        *
        ,row_number() over(partition by user_id order by cnt desc  )  as rn
    from
    (
        select
            user_id,
            diffdate,
            count(1) cnt
        from
        (
            select
                 user_id
                ,login_date
                ,date_sub(login_date,interval row_number() over(partition by user_id order by login_date ) day ) as diffdate  
            from yws.login_tab
        ) t01
        group by
        user_id
        ,diffdate
    ) t02
)t03 where rn=1

 

2、升级版本

有用户登陆表 login_tab(user_id,login_date) 求用户连续最大登陆天数 （如果用户登陆中断不超过3天，则视为连续登陆  如 3，（4，5，6），7 如果（4，5，6）三天未登陆，因未登陆天数未超过3天则仍视为连续，连续天数为5

insert into  login_tab values(100,'2020-02-02');

insert into  login_tab values(100,'2020-02-06');

insert into  login_tab values(100,'2020-02-10');

insert into  login_tab values(100,'2020-02-18');

insert into  login_tab values(100,'2020-02-19');

insert into  login_tab values(100,'2020-02-23');

insert into  login_tab values(100,'2020-02-25');

select
   *
from
(
    select
       *
       ,row_number() over(partition by user_id order by days desc ) as rn
    from
    (
        select
             user_id
            ,subdate
            ,max(login_date) as max_date
            ,min(login_date) as min_date
            ,datediff(max(login_date),min(login_date)) +1  as days
        from
        (
            select
                 user_id
                ,login_date
                ,lag_date
                ,date_sub(login_date,interval sum(days_diff) over(partition by user_id order by login_date)  day )as subdate
            from
            (
                select
                    user_id
                    ,login_date
                    ,lag_date  
                    ,case when lag_date is null then 0
                          when datediff(login_date,lag_date)>4 then 0
                          else datediff(login_date,lag_date)
                     end as days_diff
                from
                (
                    select
                         user_id
                        ,login_date
                        ,lag(login_date,1,null) over(partition by user_id order by login_date) as lag_date
                    from yws.login_tab
                ) t01
            ) t02
        ) t03
        group by user_id,subdate
    ) t04
) t05 where rn=1 ;