1、sql建表语句(mysql数据库)
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、下面的求连续登录3天的用户，建表语句及sql执行语句(mysql数据库)(先清洗重复基础数据)
思路：连续三天的逻辑变更为按照user_id分组排序之后，前后两个日期相减为1，再相加等于3
来源：https://www.cnblogs.com/wangyu233/p/16795825.html
https://www.cnblogs.com/wangyu233/p/16795825.html
-- create table test_user_login_3days(
-- user_id int,
-- login_date VARCHAR(1000)
-- );
select * from test_user_login_3days;
-- insert into test_user_login_3days values (123,'2018-08-02');
-- insert into test_user_login_3days values (123,'2018-08-03');
-- insert into test_user_login_3days values (123,'2018-08-04');
-- insert into test_user_login_3days values (456,'2018-11-02');
-- insert into test_user_login_3days values (456,'2018-12-09');
-- insert into test_user_login_3days values (789,'2018-01-01');
-- insert into test_user_login_3days values (789,'2018-04-23');
-- insert into test_user_login_3days values (789,'2018-09-10');
-- insert into test_user_login_3days values (789,'2018-09-11');
-- insert into test_user_login_3days values (789,'2018-09-12');
-- 注意细节第一步要去重（一个用户一天登录多次的情况）
select B.user_id from(
select A.user_id, A.login_date, A.rn, date_sub(A.login_date, INTERVAL A.rn DAY ) AS inteval_days
from
(
select
user_id,
login_date,
row_number() over (partition by user_id order by login_date) as rn
from
test_user_login_3days)A)B
GROUP BY B.user_id,B.inteval_days
HAVING count(1)=3
6、查询逻辑二(mysql数据库)：
第三天减去第一天的差为2，只要存在差为2的都是满足连续三天登录的情况(先清洗重复基础数据)
SELECT
A.user_id,A.login_date,A.lag_2days
FROM (SELECT
user_id,
login_date,
LEAD(login_date,2,'9999-12-31') OVER(PARTITION BY user_id ORDER BY user_id ,login_date) AS lag_2days
FROM test_user_login_3days)A
WHERE DATEDIFF(A.lag_2days,A.login_date) = 2;
同理，这种情况可以继续推到满足连续N天登录的情况。7、sql学习网站
8、
9、
10、
11、