基于Oracle数据库求最大连续登陆天数

01 题目

登陆表中有 id（user_id）、login_time求出用户连续登录天数

02 建表插入数据

CREATE TABLE "FINANCE"."USER_LOGIN"
 (
 id VARCHAR2 ( 255 BYTE ),
	login_time DATE
	)


INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-01 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-01 03:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-02 04:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-03 06:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-07 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-08 22:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-09 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-10 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('A', TO_DATE('2023-06-11 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-02 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-03 00:50:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-04 00:50:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-05 10:20:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-06 20:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-10 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-18 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "FINANCE"."USER_LOGIN" VALUES ('B', TO_DATE('2023-06-20 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

2.1数据展示

03 具体操作

3.1 id，日期去重

SELECT 
ul.id,
TRUNC(ul.login_time) time
FROM USER_LOGIN ul
GROUP BY ul.id ,TRUNC(ul.login_time)

结果：

3.2  对用户的登录天数进行排序

SELECT
	ul1.id,
	ul1.time,
	ROW_NUMBER() over ( partition BY ul1.id ORDER BY ul1.time ) rn
FROM
	( SELECT ul.id, TRUNC( ul.login_time ) time FROM USER_LOGIN ul GROUP BY ul.id, TRUNC( ul.login_time )) ul1

结果：

3.3 每条记录的登录时间减去排序的数字

得到一个组 date_group，对用户及date_group 做聚合，continue_days 即为连续登录天数

解析【核心思路 很重要】：

因为如果用户是连续登录，比如1月2、3、4号都进行了登录，排序就是1、2、3，1月2号减对应序号1就得到1月1号，1月3号减对应序号2也得到1月1号，所以如果用户连续登录，那登录日期减去序号作为的天数，得到的日期是一样的。对减过序号后一样的日期进行计数，这个数就是连续登录的天数。 
 

WITH user_log_rank   AS(SELECT
	ul1.id,
	ul1.time,
	ROW_NUMBER() over ( partition BY ul1.id ORDER BY ul1.time ) rn
FROM
	( SELECT ul.id, TRUNC( ul.login_time ) time FROM USER_LOGIN ul GROUP BY ul.id, TRUNC( ul.login_time )) ul1)
	
SELECT
ulr.id，
(ulr.time-rn) date_group,
MIN(ulr.time),
MAX(ulr.time),
COUNT(ulr.id) continue_days

FROM user_log_rank ulr
GROUP BY ulr.id，
(ulr.time-rn)

结果：

3.4 取用户最大值 去重 即得用户最大连续登陆天数

WITH user_log_rank   AS(SELECT
	ul1.id,
	ul1.time,
	ROW_NUMBER() over ( partition BY ul1.id ORDER BY ul1.time ) rn
FROM
	( SELECT ul.id, TRUNC( ul.login_time ) time FROM USER_LOGIN ul GROUP BY ul.id, TRUNC( ul.login_time )) ul1)
	

SELECT 
ulr1.id,
MAX(ulr1.continue_days) max_continue_days

FROM(SELECT
ulr.id，
(ulr.time-rn) date_group,
MIN(ulr.time),
MAX(ulr.time),
COUNT(ulr.id) continue_days

FROM user_log_rank ulr
GROUP BY ulr.id，
(ulr.time-rn))ulr1
GROUP BY ulr1.id

结果：

查看数据源，查验结果正确。