SQL：连续登录类型问题的解题思路

前言

连续登录、连续上场问题是SQL笔试面试中高频问题之一，这里提供解决此类问题的3个思路。
思路来源：SQL连续登录问题-托尼老师

1.数据准备阶段

使用以下语句进行数据的建表和数据插入

# 创建初始表
drop table if exists  `register_tb` ;
CREATE TABLE `register_tb` (
`user_id` int(11) NOT NULL,
`reg_time` datetime NOT NULL,
`reg_port` varchar(8) NOT NULL,
PRIMARY KEY (`user_id`));
INSERT INTO register_tb VALUES(1101,'2022-02-08 07:23:15','pc');
INSERT INTO register_tb VALUES(1102,'2022-02-08 09:12:22','app');
INSERT INTO register_tb VALUES(1103,'2022-02-08 09:35:45','m');
INSERT INTO register_tb VALUES(1104,'2022-02-08 09:41:01','app');
INSERT INTO register_tb VALUES(1105,'2022-02-08 12:01:01','app');
INSERT INTO register_tb VALUES(1106,'2022-02-08 17:22:13','app');
INSERT INTO register_tb VALUES(1107,'2022-02-08 18:26:21','pc');
INSERT INTO register_tb VALUES(1108,'2022-02-08 19:16:21','pc');
INSERT INTO register_tb VALUES(1109,'2022-02-08 19:56:21','pc');

drop table if exists  `login_tb` ;
CREATE TABLE `login_tb` (
`log_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`log_time` datetime NOT NULL,
`log_port` varchar(8) NOT NULL,
PRIMARY KEY (`log_id`));
INSERT INTO login_tb VALUES(101,1101,'2022-02-09 07:24:15','pc');
INSERT INTO login_tb VALUES(102,1102,'2022-02-09 09:12:57','app');
INSERT INTO login_tb VALUES(103,1003,'2022-02-09 09:36:11','m');
INSERT INTO login_tb VALUES(104,1102,'2022-02-10 09:37:01','app');
INSERT INTO login_tb VALUES(105,1104,'2022-02-10 12:01:46','app');
INSERT INTO login_tb VALUES(106,1106,'2022-02-10 10:23:01','app');
INSERT INTO login_tb VALUES(107,1003,'2022-02-10 10:43:01','m');
INSERT INTO login_tb VALUES(108,1102,'2022-02-11 11:56:47','app');
INSERT INTO login_tb VALUES(109,1104,'2022-02-11 14:52:37','app');
INSERT INTO login_tb VALUES(1010,1106,'2022-02-11 16:56:27','app');
INSERT INTO login_tb VALUES(1011,1003,'2022-02-11 17:43:01','m');
INSERT INTO login_tb VALUES(1012,1106,'2022-02-12 10:56:17','app');

register_tb表，即用户注册表：

login_tb表：即用户登录表：

2.查看数据

# 查询登录信息
select
    *
from login_tb;

数据如下：

查询新用户注册信息

select
    *
from login_tb;

数据如下：

问题

查询连续登录不小于3天的新用户的user_id。

数据处理

因为一个用户一天可能登录多次，所以需要先对登录表进行去重。

select
    distinct
    user_id,
    date(log_time)
from login_tb
order by user_id;

方法一：适用于短期(3天)的方法

datediff(day1,day2)   -- 或者使用timestampdiff(day,day1,day2) 计算两个日期的相差天数   

由于只需要查出连续登录三天的用户ID，所以可以连续进行连接，且连接的时候限制第二次连接的时间比原时间加1天，第三次连接比第二次连接的时间加1天。

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
)
select
    t1.user_id,
    t1.ymd,
    t2.ymd,
    t3.ymd
from t1
join t1 t2
on t1.user_id=t2.user_id
    and datediff(t2.ymd,t1.ymd)=1  # 连续登录2天
join t1 t3
on t2.user_id=t3.user_id
    and datediff(t3.ymd,t2.ymd)=1

所以在登录表中一共有3个用户连续登录3天。但是题干要求是新用户，所以还需要进行过滤。

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
)
select
    t1.user_id
from t1
join t1 t2
on t1.user_id=t2.user_id
    and datediff(t2.ymd,t1.ymd)=1  # 连续登录2天
join t1 t3
on t2.user_id=t3.user_id
    and datediff(t3.ymd,t2.ymd)=1 # 连续登录3天
where t1.user_id in (
    select
        user_id
    from register_tb
    )

结果如下，所以只有1102和1106两位新用户连续登录次数超过三天

方法二：适用于求最长连续登录天数类型

前面的思路适用于较少的天数登录，如果求连续登录15天的用户，那么上面的表需要join 15次，太繁琐。
可以使用窗口函数row_number()进行解决。思路如下：
先对处理的数据，按照用户进行分组排序，这里使用row_number即可

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
)
select
    *,
    row_number() over (partition by user_id order by ymd) as num
from t1

接下来可以使用ymd减去对应的时间，然后统计相同的日期，例如对于1003用户，2022-02-09减去1天为2022-02-08，然后2022-02-10 减去2天为2022-02-08，2022-02-11减去3天为2022-02-08，三个结果均为2022-02-08，所以再对这个结果进行聚合即可。如果1003还有一个数据，是2022-02-14，num为4，那2022-02-14减去4天，为2022-02-10，和前面不同，这个时候就不会进行计入。

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
),
t2 as(
    select
        *,
        row_number() over (partition by user_id order by ymd) as num
    from t1
)
select
    *,
    date_sub(ymd,interval num day) as init_day
from t2

接下来只需要按照user_id和init_day进行聚合，查询天数不小于3天且在新人注册表内的用户ID即可。

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
),
t2 as(
    select
        *,
        row_number() over (partition by user_id order by ymd) as num
    from t1
),
t3 as(
    select
        *,
        date_sub(ymd,interval num day) as init_day
    from t2
)
select
    user_id
from t3
where user_id in (
        select
            distinct
            user_id
        from register_tb
    )
group by user_id,init_day
having count(*)>=3  

方法三：适用于知道指定天数

使用窗口函数lag()进行处理，lag(字段,n)可以把对应的字段向下延展n行

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
)
select
    *,
    lag(ymd,2) over(partition by user_id order by ymd) llday
from t1

然后只需要筛选出来新用户内，上上次登录日期间隔两天即可。

with t1 as(
    select
        distinct
        user_id,
        date(log_time) ymd
    from login_tb
    order by user_id
),
t2 as(
    select
        *,
        lag(ymd,2) over(partition by user_id order by ymd) llday
    from t1
)
select
    user_id
from t2
where datediff(ymd,llday) = 2
    and user_id in (
        select
            distinct
            user_id
        from register_tb
    );

总结

本文一共介绍三种方法求连续登录问题，下面对其使用场景进行说明