扫码加入数据分析学习群

现在数据库dd中有一个表，如下，如何根据这个表求每个用户的连续登录天数：

查询代码及结果解释如下：

CREATE TABLE `user_login1` (

`user_id` varchar(10) DEFAULT NULL,

`login_time` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into user_login1 (user_id,login_time) values ('1','2019-06-01 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-02 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-03 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-06 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-07 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-08 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-11 00:00:00');

insert into user_login1 (user_id,login_time) values ('1','2019-06-12 00:00:00');

insert into user_login1 (user_id,login_time) values ('2','2019-06-01 00:00:00');

insert into user_login1 (user_id,login_time) values ('2','2019-06-02 00:00:00');

insert into user_login1 (user_id,login_time) values ('2','2019-06-04 00:00:00');

insert into user_login1 (user_id,login_time) values ('3','2019-06-01 00:00:00');

insert into user_login1 (user_id,login_time) values ('3','2019-06-02 00:00:00');

insert into user_login1 (user_id,login_time) values ('4','2019-06-01 00:00:00');

insert into user_login1 (user_id,login_time) values ('5','2019-06-01 00:00:00');

insert into user_login1 (user_id,login_time) values ('5','2019-06-02 00:00:00');

select user_id,min(login_date) start_time, max(login_date) end_date, count(login_date) counts

/*根据分组变量user_id,adm进行汇总，也就是每个用户的每次连续登录进行汇总*/

/*计算每次连续登录的首次登录日期start_time*/

/*计算每次连续登录的最后登录日期end_time*/

/*计算每次连续登录的天数 counts*/

from (

select *,date_sub(login_date, interval ranks day) adm

/*用interval方法将ranks变量生成一个以day为单位时间差变量，比如ranks为3的话就是3天*/

/*subtract:减法。DATE_SUB(date,INTERVAL expr unit) Subtract a time value (interval) from a date，

也就是让日期减去一个时间差变量,得到一个日期变量。也就是adm变量。

这个adm变量的结果是一个比较难懂的地方，如果一个用户有两次连续登录，那么这个用户的adm变量就有两个数值，

从而以此变量来判断那一条数据数据属于哪一次的连续登录,adm变量只是起一个分组的作用*/

/*把查询的临时表记作c*/

from (

select *,rank() over(partition by user_id order by login_date) ranks

/*通过开窗函数over 根据user_id分组，根据login_date排序，生成组内秩变量ranks*/

/*把查询的临时表记作b*/

from (

select distinct user_id, date(login_time) login_date

/*用date函数将原有变量login_time变成日期变量后重新保存为login_date。

然后根据id和login_date进行去重,

然后把查询结果记作a*/

from user_login1

) as a /*把查询的临时表记作a*/

) as b/*把查询的临时表记作b*/

) as c

group by user_id,adm;

分解：

a结果

b结果

c结果

最终结果：

sql查询代码见附件

new 3.txt

注意上面这个文本文件中因为有中文注释，所以大家在网页浏览或者下载查看的时候会有乱码，

因此再给大家发一个没有中文注释的代码文件

new 3.txt

数据文件如下

user_login1.csv