连续登录天数
思路:
- 将数据去重,日期变为yyyy-mm-dd 格式(函数名date())
- 对数据进行按照日期进行排序(函数名row_number())
- 排序后日期和原始日期相减。
建表
create table user_login(
user_id varchar(100),
login_time datetime);
insert into user_login values
(1,'2016-11-25 13:30:45'),
(1,'2016-11-24 13:30:45'),
(1,'2016-11-24 10:30:45'),
(1,'2016-11-24 09:30:45'),
(1,'2016-11-23 09:30:45'),
(1,'2016-11-10 09:30:45'),
(1,'2016-11-09 09:30:45'),
(1,'2016-11-01 09:30:45'),
(1,'2016-10-31 09:30:45'),
(2,'2016-11-25 13:30:45'),
(2,'2016-11-24 13:30:45'),
(2,'2016-11-23 10:30:45'),
(2,'2016-11-22 09:30:45'),
(2,'2016-11-21 09:30:45'),
(2,'2016-11-20 09:30:45'),
(2,'2016-11-19 09:30:45'),
(2,'2016-11-02 09:30:45'),
(2,'2016-11-01 09:30:45'),
(2,'2016-10-31 09:30:45'),
(2,