当前活跃用户连续活跃天数

有用户登录日志表，包含日期、用户ID，当天是否登录，请查询出当天活跃的用户当前连续活跃天数；

需求:只需要当前登录用户的最近连续登录的数据，而不是求历史所有的最大连续登录

CREATE TABLE t4_login_log (
  login_date date COMMENT '日期',
  user_id bigint COMMENT '用户ID',
  is_login bigint COMMENT '是否登录'
) COMMENT '用户签到记录表';
 

insert into t4_login_log (login_date,user_id,is_login)
values
('2023-08-01',1,1),
('2023-08-01',2,1),
('2023-08-01',3,1),
('2023-08-01',4,0),
('2023-08-02',1,1),
('2023-08-02',2,0),
('2023-08-02',3,1),
('2023-08-02',4,1),
('2023-08-03',1,1),
('2023-08-03',2,1),
('2023-08-03',3,0),
('2023-08-03',4,1);

1.找到用户的最后未登录日期

select user_id,
       max(login_date) as latest_unlogin_date
from t4_login_log
where is_login = 0
group by user_id;

 

2.筛选出最后登录日期之后的所有记录，如果没有则取默认较小值（用户1）。用户3最后一天是未登录状态，所以结果记录中不存在用户3的登录记录。

COALESCE 函数在 SQL 中用于返回其参数列表中第一个非 NULL 值的表达式。如果所有的参数都是 NULL，那么 COALESCE 函数返回 NULL。这个函数非常有用，尤其是在处理可能包含 NULL 值的数据时，可以提供一个默认值或备选值。

select t1.user_id,
       t1.is_login,
       t1.login_date
from (select user_id,
             is_login,
             login_date
      from t4_login_log) t1
         left join
     (select user_id,
             max(login_date) as latest_unlogin_date
      from t4_login_log
      where is_login = 0
      group by user_id) t2
     on t1.user_id = t2.user_id
where t1.login_date > coalesce(t2.latest_unlogin_date, '1970-01-01')
order by user_id, login_date asc

3.统计完成最终结果

select t1.user_id,
       count(1) as login_days
from (select user_id,
             is_login,
             login_date
      from t4_login_log) t1
         left join
     (select user_id,
             max(login_date) as latest_unlogin_date
      from t4_login_log
      where is_login = 0
      group by user_id) t2
     on t1.user_id = t2.user_id
where t1.login_date > coalesce(t2.latest_unlogin_date, '1970-01-01')
group by t1.user_id