题解 | 最长连续登录天数

获取判断每行是否为连续中的日期

1、利用date_sub和date_add函数分别获取每行前一天和后一天日期

SELECT 
A.*
,CASE WHEN LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_sub(fdate, interval 1 day) THEN 1 ELSE 0 END LAG1
,CASE WHEN LEAD(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_add(fdate, interval 1 day) THEN 1 ELSE 0 END LEAD1
FROM tb_dau A

2、排除前一行和后一行都为连续日期的数据，只获取每人每段登录时间的开始日期和结束日期

SELECT A.*
#,LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate) lag2
FROM(
SELECT 
A.*
,CASE WHEN LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_sub(fdate, interval 1 day) THEN 1 ELSE 0 END LAG1
,CASE WHEN LEAD(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_add(fdate, interval 1 day) THEN 1 ELSE 0 END LEAD1
FROM tb_dau A
)A 
WHERE LAG1=0  OR LEAD1=0

获取每人每段登录的开始日期和结束日期

1、根据每人每段登录时间的开始日期和结束日期，利用lag函数获取每行的前一行数据，使结束日期和开始日期在一行中，where中只取fdate为结束日期

SELECT A.*,LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate) lag2
FROM(
SELECT 
A.*
,CASE WHEN LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_sub(fdate, interval 1 day) THEN 1 ELSE 0 END LAG1
,CASE WHEN LEAD(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_add(fdate, interval 1 day) THEN 1 ELSE 0 END LEAD1
FROM tb_dau A
)A 
WHERE LAG1=0  OR LEAD1=0

计算最长登录天数

利用DATEDIFF函数计算登录天数，max函数计算每人最长登录天数

SELECT user_id,MAX(DATEDIFF(fdate,lag2))+1 max_consec_days
FROM(
SELECT A.*,LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate) lag2
FROM(
SELECT 
A.*
,CASE WHEN LAG(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_sub(fdate, interval 1 day) THEN 1 ELSE 0 END LAG1
,CASE WHEN LEAD(fdate,1)OVER(PARTITION BY user_id ORDER BY fdate)=date_add(fdate, interval 1 day) THEN 1 ELSE 0 END LEAD1
FROM tb_dau A
)A 
WHERE LAG1=0  OR LEAD1=0
)A 
where  LAG1=1 AND LEAD1=0
GROUP BY user_id