已知用户id 和 用户表
select * from user_table where
思路方法1(别人的思想,毕竟懂得也是皮毛,先参考琢磨透)
1.因为每天用户登录的次数不止一次,需要将用户每天登录日期去重 distinct
2.再用row_number() over (partition by_order by_)函数将用户id分组,按照登录日期进行排序。
3.计算登录日期减去第二步得到的结果值,用户连续登录情况下,相减的结果都相同
4.按照id和日期分组并计数,筛选大于等于3即为连续登录3天用户
总结 登录日期去重 将登陆日期排序 desc
1 建表 create table user_login(
user_id int,
login_time datetime,
)
添加数据
insert into user_login values (1,' 2022-06-01 11:00:00.000 ' );
insert into user_login values (1,'2022-06-01 12:00:00.000');
insert into user_login values (1,'2022-06-01 12:00:00.000');
insert into user_login values (1,'2022-06-02 11:00:00.000');
insert into user_login values (1,'2022-06-03 11:00:00.000');
insert into user_login values (2,'2022-06-01 11:00:00.000');
insert into user_login values (2,'2022-06-02 11:00:00.000');
insert into user_login values (2,'2022-06-04 11:00:00.000');
insert into user_login values (3,'2022-06-01 11:00:00.000');
insert into user_login values (3,'2022-06-02 11:00:00.000');
insert into user_login values (3,'2022-06-04 11:00:00.000');
insert into user_login values (3,'2022-06-05 11:00:00.000');
insert into user_login values (3,'2022-06-06 11:00:00.000');
insert into user_login values (3,'2022-06-07 11:00:00.000');
insert into user_login values (3,'2022-06-08 11:00:00.000');
查询语句
select B.user_id from
( //两个嵌套select
select
A.user_id,
A.login_date,
datediff( day,A.login_date,A.rn) AS inteval_days
from
( //第一步先看这个查询
select
user_id,
CONVERT(varchar(100),login_time,23 ) login_date,
row_number() over (partition by user_id order by CONVERT (varchar(100),login_time,23)) as rn
from
user_login
) A
)B
GROUP BY B.USER_ID,B.intevel_days
having count(1) >=3;
整体看不懂 太复杂
到了12点就这样吧