扫码加入数据分析学习群

mysql> create table user_login(user_id int,login_time date);

mysql> insert into user_login values
(1,'2019-06-01'),
(1,'2019-06-02'),
(1,'2019-06-03'),
(1,'2019-06-06'),
(1,'2019-06-07'),
(1,'2019-06-08'),
(1,'2019-06-11'),
(1,'2019-06-12'),
(2,'2019-06-01'),
(2,'2019-06-02'),
(2,'2019-06-04'),
(3,'2019-06-01'),
(3,'2019-06-02'),
(4,'2019-06-01'),
(5,'2019-06-01'),
(5,'2019-06-02');

mysql> select * from user_login;

| user_id | login_time |

| 1 | 2019-06-01 |
| 1 | 2019-06-02 |
| 1 | 2019-06-03 |
| 1 | 2019-06-06 |
| 1 | 2019-06-07 |
| 1 | 2019-06-08 |
| 1 | 2019-06-11 |
| 1 | 2019-06-12 |
| 2 | 2019-06-01 |
| 2 | 2019-06-02 |
| 2 | 2019-06-04 |
| 3 | 2019-06-01 |
| 3 | 2019-06-02 |
| 4 | 2019-06-01 |
| 5 | 2019-06-01 |
| 5 | 2019-06-02 |

-- 查询每位用户的累计登陆天数
mysql> select user_id,login_time,row_number() over(partition by user_id order by login_time) as rn from user_login;

| user_id | login_time | rn |

| 1 | 2019-06-01 | 1 |
| 1 | 2019-06-02 | 2 |
| 1 | 2019-06-03 | 3 |
| 1 | 2019-06-06 | 4 |
| 1 | 2019-06-07 | 5 |
| 1 | 2019-06-08 | 6 |
| 1 | 2019-06-11 | 7 |
| 1 | 2019-06-12 | 8 |
| 2 | 2019-06-01 | 1 |
| 2 | 2019-06-02 | 2 |
| 2 | 2019-06-04 | 3 |
| 3 | 2019-06-01 | 1 |
| 3 | 2019-06-02 | 2 |
| 4 | 2019-06-01 | 1 |
| 5 | 2019-06-01 | 1 |
| 5 | 2019-06-02 | 2 |

-- 登陆时间减去登陆天数得到用户连续登陆分组
mysql> select user_id,date_sub(login_time,interval rn day) as login_group,min(login_time) as start_login_time,max(login_time) as end_login_time,count(login_time) as continuous_days
from (select user_id,login_time,row_number() over(partition by user_id order by login_time) as rn from user_login) t
group by user_id,date_sub(login_time,interval rn day);

| user_id | login_group | start_login_time | end_login_time | continuous_days |

| 1 | 2019-05-31 | 2019-06-01 | 2019-06-03 | 3 |
| 1 | 2019-06-02 | 2019-06-06 | 2019-06-08 | 3 |
| 1 | 2019-06-04 | 2019-06-11 | 2019-06-12 | 2 |
| 2 | 2019-05-31 | 2019-06-01 | 2019-06-02 | 2 |
| 2 | 2019-06-01 | 2019-06-04 | 2019-06-04 | 1 |
| 3 | 2019-05-31 | 2019-06-01 | 2019-06-02 | 2 |
| 4 | 2019-05-31 | 2019-06-01 | 2019-06-01 | 1 |
| 5 | 2019-05-31 | 2019-06-01 | 2019-06-02 | 2 |