描述
某产品在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;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*