1、sql建表语句
DROP TABLE IF EXISTS `app_login_record`;
CREATE TABLE `app_login_record` (
`user_id` int(0) NULL DEFAULT NULL,
`enter_time` datetime(0) NULL DEFAULT NULL,
`leave_time` datetime(0) NULL DEFAULT NULL
);
INSERT INTO `app_login_record` VALUES (789012, '2023-05-03 17:52:00', '2023-05-03 17:55:00');
INSERT INTO `app_login_record` VALUES (789013, '2023-05-04 12:02:00', '2023-05-04 12:03:00');
INSERT INTO `app_login_record` VALUES (789012, '2023-05-02 13:36:00', '2023-05-02 13:45:00');
INSERT INTO `app_login_record` VALUES (789001, '2023-05-08 14:25:00', '2023-05-08 14:32:00');
INSERT INTO `app_login_record` VALUES (789003, '2023-05-10 10:46:00', '2023-05-10 10:47:00');
INSERT INTO `app_login_record` VALUES (789001, '2023-05-07 08:12:00', '2023-05-07 08:14:00');
INSERT INTO `app_login_record` VALUES (789012, '2023-05-04 16:32:00', '2023-05-04 16:35:00');
INSERT INTO `app_login_record` VALUES (789012, '2023-05-08 11:58:00', '2023-05-08 12:01:00');
INSERT INTO `app_login_record` VALUES (789001, '2023-05-09 19:35:00', '2023-05-09 19:38:00');
INSERT INTO `app_login_record` VALUES (789003, '2023-05-11 20:35:00', '2023-05-11 20:38:00');
INSERT INTO `app_login_record` VALUES (789009, '2023-05-01 22:58:00', '2023-05-01 23:03:00');
2、请找出连续3天登录小程序且浏览时长大于2分钟的用户
(这里考虑了,一天登录两次的情况,需要去重)
select DISTINCT tw.user_id from(
select *,
if(tc.tg=1 and tc.t_user_id=1,@size:=@size+1,@size:=1) as t_num
from (
select user_id,enter_time_s,
lead(enter_time_s, 1) over () =
DATE_ADD(enter_time_s,INTERVAL 1 day) as tg ,
lead(user_id, 1) over () = user_id as t_user_id
from(
select DISTINCT user_id as user_id , left(enter_time,10) as enter_time_s
from app_login_record t , (SELECT @size:=1)r
where TIMESTAMPDIFF(MINUTE, enter_time, leave_time) > 2
ORDER BY t.user_id,enter_time_s)ta)tc)tw
where tw.t_num = 3
-- 语法解释1:
-- 别名不能直接做where和group by后的查询条件,但order by 可以用别名
-- 原因是where在select之前执行,所以别名不能直接做where后的查询条件
-- group by 同理。
-- 但order by是最后执行,所以可以用别名。
-- 语法解释2
-- 怎么定义多个变量,如下
-- (SELECT @old:=null,@size:=1,@name:=null)r
5、
6、
7、
8、
9、
10、
11、