一、题目

有用户登录日志表，包含日期、用户ID，当天是否登录，请给每条记录添加用户截止到当前连续登录天数（如果中断需要从1开始，当天未登录数据为0）。

+-------------+----------+-----------+
| login_date  | user_id  | is_login  |
+-------------+----------+-----------+
| 2023-08-01  | 1        | 1         |
| 2023-08-02  | 1        | 0         |
| 2023-08-03  | 1        | 1         |
| 2023-08-04  | 1        | 1         |
| 2023-08-05  | 1        | 0         |
| 2023-08-06  | 1        | 0         |
| 2023-08-07  | 1        | 0         |
| 2023-08-08  | 1        | 1         |
| 2023-08-09  | 1        | 1         |
| 2023-08-01  | 2        | 1         |
| 2023-08-02  | 2        | 0         |
| 2023-08-03  | 2        | 0         |
| 2023-08-04  | 2        | 0         |
| 2023-08-05  | 2        | 1         |
| 2023-08-06  | 2        | 1         |
| 2023-08-07  | 2        | 1         |
| 2023-08-08  | 2        | 0         |
| 2023-08-09  | 2        | 1         |
+-------------+----------+-----------+

二、分析

1.首先该题目中的原始数据已经经过了处理，每个用户每天都会有且只有一条记录，标识了用户的登录状态；

2.该题目并不是一个最大连续登录或者满足连续n天登录的分析题目，而是一个数据的清洗加工，对每行数据进行连续登录天数的计算；

3.完成该清洗之后，对于所有连续问题，都会变的超级简单，属于基建工作；

处理思路

1.要给每一行添加连续登录天数，是一种累积求和需求，所以最后逻辑应该是一个sum()over(partition by order by )的逻辑；

2.接下来就是按照什么进行分组？把用户每次连续登录的日期段进行分组，然后按照这个组别进行分组；

3.如何能实现按照连续登录进行分组呢？需要判断用户是否连续登录，连续登录为0，非连续登录为1，然后对数据进行累积求和；

以上是反向思路，逐步求解，下面SQL给出正序的，逐步增加字段的的逻辑处理。

三、SQL

1）增加一列标识用户是否连续登录，连续登录为0，非连续登录为1；连续登录：昨天登录今天也登录则今天属于连续登录，昨天未登录今天登录，则今天不算连续登录；

select
user_id,
login_date,
is_login,
case when lag(is_login)over(partition by user_id order by login_date asc) =1 and is_login=1 then 0 else 1 end as is_continue_login
from t_login_log_099

查询结果

2)根据是否连续字段，进行累积计数,获得分组字段

select
user_id,
login_date,
is_login,
is_continue_login,
sum(is_continue_login) over(partition by user_id order by login_date asc) as col_group
from 
(
-- 增加是否连续字段，连续0，不连续1
select
user_id,
login_date,
is_login,
case when lag(is_login)over(partition by user_id order by login_date asc) =1 and is_login=1 then 0 else 1 end as is_continue_login
from t_login_log_099
)t1

查询结果

3）上一步产出结果可以看出，每次非连续登录均获得了一个独立分组，连续登录的记录在同一个记录里面。我们按照用户和分组列进行分组，累积求和

select
user_id,
login_date,
is_login,
is_continue_login,
col_group,
count(1)over(partition by user_id,col_group order by login_date) as count_order
from
(
--根据是否连续，进行累积求和，获得分组列
select
user_id,
login_date,
is_login,
is_continue_login,
sum(is_continue_login) over(partition by user_id order by login_date asc) as col_group
from 
(
-- 增加是否连续字段，连续0，不连续1
select
user_id,
login_date,
is_login,
case when lag(is_login)over(partition by user_id order by login_date asc) =1 and is_login=1 then 0 else 1 end as is_continue_login
from t_login_log_099
)t1
)t2

查询结果

4)我们可以看到，上面结果中，红框内用户未登录，连续登录天数应该是0。非红框的数据是正确的。我们根据is_login字段进行判断，如果是登录，则取count_order的值，否则为0，得到最后结果

select
user_id,
login_date,
is_login,
is_continue_login,
col_group,
case when is_login = 1 then count(1)over(partition by user_id,col_group order by login_date) else 0 end as continue_login_days
from
(
--根据是否连续，进行累积求和，获得分组列
select
user_id,
login_date,
is_login,
is_continue_login,
sum(is_continue_login) over(partition by user_id order by login_date asc) as col_group
from 
(
-- 增加是否连续字段，连续0，不连续1
select
user_id,
login_date,
is_login,
case when lag(is_login)over(partition by user_id order by login_date asc) =1 and is_login=1 then 0 else 1 end as is_continue_login
from t_login_log_099
)t1
)t2

查询结果

5）去掉中间字段，得到最终结果

select
user_id,
login_date,
is_login,
case when is_login = 1 then count(1)over(partition by user_id,col_group order by login_date) else 0 end as continue_login_days
from
(
--根据是否连续，进行累积求和，获得分组列
select
user_id,
login_date,
is_login,
is_continue_login,
sum(is_continue_login) over(partition by user_id order by login_date asc) as col_group
from 
(
-- 增加是否连续字段，连续0，不连续1
select
user_id,
login_date,
is_login,
case when lag(is_login)over(partition by user_id order by login_date asc) =1 and is_login=1 then 0 else 1 end as is_continue_login
from t_login_log_099
)t1
)t2

查询结果

四、建表语句和数据插入

--建表语句
CREATE TABLE t_login_log_099 (
  login_date string COMMENT '日期',
  user_id bigint COMMENT '用户ID',
  is_login bigint COMMENT '是否登录'
) COMMENT '用户签到记录表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
;
--插入数据
insert into t_login_log_099 (login_date,user_id,is_login)
values
('2023-08-01',1,1),
('2023-08-02',1,0),
('2023-08-03',1,1),
('2023-08-04',1,1),
('2023-08-05',1,0),
('2023-08-06',1,0),
('2023-08-07',1,0),
('2023-08-08',1,1),
('2023-08-09',1,1),
('2023-08-01',2,1),
('2023-08-02',2,0),
('2023-08-03',2,0),
('2023-08-04',2,0),
('2023-08-05',2,1),
('2023-08-06',2,1),
('2023-08-07',2,1),
('2023-08-08',2,0),
('2023-08-09',2,1)