回忆是一座桥
却是通往寂寞的牢

SQL案例 - 查询连续登录的用户

描述

某产品在2022年2月8日各端口用户注册信息及后几日登录信息如下:

用户注册信息表register_tb(user_id-用户id, reg_time-注册时间, reg_port-注册端口)

user_id reg_time reg_port
1101 2022-02-08 07:23:15 pc
1102 2022-02-08 09:12:22 app
1103 2022-02-08 09:35:45 m
1104 2022-02-08 09:41:01 app
1105 2022-02-08 12:01:01 app
1106 2022-02-08 17:22:13 app
1107 2022-02-08 18:26:21 pc
1108 2022-02-08 19:16:21 pc
1109 2022-02-08 19:56:21 pc

用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间, log_port-登录端口)

log_id user_id log_time log_port
101 1101 2022-02-09 07:24:15 pc
102 1102 2022-02-09 09:12:57 app
103 1003 2022-02-09 09:36:11 m
104 1102 2022-02-10 09:37:01 app
105 1104 2022-02-10 12:01:46 app
106 1106 2022-02-10 10:23:01 app
107 1003 2022-02-10 10:43:01 m
108 1102 2022-02-11 11:56:47 app
109 1104 2022-02-11 14:52:37 app
1010 1106 2022-02-11 16:56:27 app
1011 1003 2022-02-11 17:43:01 m
1012 1106 2022-02-12 10:56:17 app

问题:请查询连续登陆不少于3天的新注册用户?要求:输出user_id并升序排序。

注:登录表为单日随机一次登录数据,该题忽略单日多次登录情况。

示例输出如下:

user_id
1102
1106

解释:1102在9日、10日、11日,登陆了系统满足查询条件;1106在10日、11日、12日登录了系统满足查询条件。

示例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');

输出:

1102
1106

答案

解法1:

with
    tb1 as (
        select
            t.*,
            to_days (t.log_time) - rank() over (partition by t.user_id order by t.log_time) grp
        from
            login_tb t
            join register_tb r on t.user_id=r.user_id
    ),
    tb2 as (
        select
            t.user_id,
            max(t.nbr) max_nbr
        from
            (select user_id,count(*) nbr from tb1 group by user_id,grp) t
        group by
            t.user_id
    )
select user_id from tb2 where max_nbr >= 3 order by user_id;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 查询连续登录的用户
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论