SQL求出最大连续登陆天数

求出最大连续登陆天数"/>

SQL求出最大连续登陆天数

一、题目描述

求解用户登陆信息表中，每个用户连续登陆平台的天数，连续登陆基础为汇总日期必须登陆，表中每天只有一条用户登陆数据（计算中不涉及天内去重）。

表描述：user_id：用户的id；

sigin_date：用户的登陆日期。

二、解法分析

注：求解过程有多种方式，下述求解解法为笔者思路，其他解法可在评论区交流。

思路：

该问题的突破的在于登陆时间，计算得到连续登陆标识，以标识分组为过滤条件，得到连续登陆的天数，最后以user_id分组，以count()函数求和得到每个用户的连续登陆天数。

连续登陆标识 =（当日登陆日期 - 用户的登陆日期）- 开窗排序的顺序号(倒序)

三、求解过程及结果展示

-- plsql
-- 1.建表语句
drop table if exists test_sigindate_cnt;
create table test_sigindate_cnt(user_id VARCHAR,sigin_date varchar
)
;-- 2.测试数据插入语句
insert INTO test_sigindate_cnt select 'uid_1' as user_id,'2021-08-03' as sigin_date        union allselect 'uid_1' as user_id,'2021-08-04' as sigin_date union allselect 'uid_1' as user_id,'2021-08-01' as sigin_date        union allselect 'uid_1' as user_id,'2021-08-02' as sigin_date        union allselect 'uid_1' as user_id,'2021-08-05' as sigin_date       union allselect 'uid_1' as user_id,'2021-08-06' as sigin_date        union allselect 'uid_2' as user_id,'2021-08-01' as sigin_date        union allselect 'uid_2' as user_id,'2021-08-05' as sigin_date        union allselect 'uid_2' as user_id,'2021-08-02' as sigin_date         union allselect 'uid_2' as user_id,'2021-08-06' as sigin_date        union allselect 'uid_3' as user_id,'2021-08-04' as sigin_date     union allselect 'uid_3' as user_id,'2021-08-06' as sigin_date        union allselect 'uid_4' as user_id,'2021-08-03' as sigin_date        union allselect 'uid_4' as user_id,'2021-08-02' as sigin_date              
;select  user_id,count(1) as sigin_cnt
from    (select  user_id,date_part('day',TIMESTAMP '2021-08-06'-sigin_date)  as data_diff,row_number() over (partition by user_id order by sigin_date desc) as row_numfrom    test_sigindate_cnt
) t
where   data_diff - row_num = -1
group by user_id
;

drop table if EXISTs user_login_table;
CREATE TABLE user_login_table
(user_id CHAR(10),login_date date
);INSERT INTO user_login_table  VALUES ('201','2017/1/1');
INSERT INTO user_login_table  VALUES ('201','2017/1/2');
INSERT INTO user_login_table  VALUES ('202','2017/1/2');
INSERT INTO user_login_table  VALUES ('202','2017/1/3');
INSERT INTO user_login_table  VALUES ('203','2017/1/3');
INSERT INTO user_login_table  VALUES ('201','2017/1/4');
INSERT INTO user_login_table  VALUES ('202','2017/1/4');
INSERT INTO user_login_table  VALUES ('201','2017/1/5');
INSERT INTO user_login_table  VALUES ('202','2017/1/5');
INSERT INTO user_login_table  VALUES ('201','2017/1/6');
INSERT INTO user_login_table  VALUES ('203','2017/1/6');
INSERT INTO user_login_table  VALUES ('203','2017/1/7');-- 思路1
SELECT
user_id,max(login_days)
froM(
SELECT
user_id,
new_date,
count(*) as login_days 
FROM
(
SELECT
user_id,
login_date,
row_number() over ( PARTITION BY user_id ORDER BY login_date ) rn,
login_date - row_number() over ( PARTITION BY user_id ORDER BY login_date ) new_date 
FROM
user_login_table 
) a 
GROUP BY
user_id,
new_date ) b　　 
GROUP BY user_id;-- 思路2drop table if EXISTs dic_date_table;
CREATE TABLE dic_date_table
(dic_date date
);INSERT INTO dic_date_table  VALUES ('2017/1/1');
INSERT INTO dic_date_table  VALUES ('2017/1/2');
INSERT INTO dic_date_table  VALUES ('2017/1/3');
INSERT INTO dic_date_table  VALUES ('2017/1/4');
INSERT INTO dic_date_table  VALUES ('2017/1/5');
INSERT INTO dic_date_table  VALUES ('2017/1/6');
INSERT INTO dic_date_table  VALUES ('2017/1/7');SELECT
T.user_id,
max( login_days ) max_login_days 
FROM
(
SELECT
t.user_id,
t.num,
count(*) login_days 
FROM
(
SELECT
a.user_id,
b.rn_1 - a.rn num 
FROM
( SELECT user_id, login_date, row_number() over ( PARTITION BY user_id ORDER BY login_date ) rn FROM user_login_table ) a
LEFT JOIN ( SELECT dic_date, row_number() over ( ORDER BY dic_date ) rn_1 FROM dic_date_table ) b ON a.login_date = b.dic_date 
) t 
GROUP BY
t.user_id,
t.num 
) T 
GROUP BY
T.user_id;

连续登陆问题的变型（mysql)

 测试数据及结果代码

drop table if EXISTs app_data;
CREATE TABLE app_data
(client_id CHAR(10),login_day date,result CHAR(10)
);INSERT INTO app_data  VALUES ('00001','2022-01-01','success');
INSERT INTO app_data  VALUES ('00002','2022-01-02','fail');
INSERT INTO app_data  VALUES ('00001','2022-01-03','timeout');
INSERT INTO app_data  VALUES ('00001','2022-01-15','success');
INSERT INTO app_data  VALUES ('00002','2022-01-16','timeout');
INSERT INTO app_data  VALUES ('00001','2022-01-17','success');
INSERT INTO app_data  VALUES ('00001','2022-01-18','success');
INSERT INTO app_data  VALUES ('00001','2022-01-19','timeout');
INSERT INTO app_data  VALUES ('00001','2022-01-20','timeout');
INSERT INTO app_data  VALUES ('00001','2022-01-21','success');
INSERT INTO app_data  VALUES ('00001','2022-01-22','success');
INSERT INTO app_data  VALUES ('00002','2022-01-18','success');
INSERT INTO app_data  VALUES ('00003','2022-01-18','success');SELECT
client_id,
max( max_day ) 
FROM
(
SELECT
client_id,
num3,
count(case when a=0 then null when a=1 then num3 end) AS max_day 
FROM
(
SELECT
*,
num2 - num1 AS num3 
FROM
(
SELECT
*,
sum( a ) over ( PARTITION BY client_id ORDER BY login_day ) num1,
count( a ) over ( PARTITION BY client_id ORDER BY login_day ) num2 
FROM
( SELECT *, CASE WHEN result = 'success' THEN 1 ELSE 0 END AS a FROM app_data t ) t1 
) t2 
) t3 
GROUP BY
client_id,
num3 
) t4 
GROUP BY
client_id