SQL-计算连续签到天数

1、应用场景

现在有一份用户签到行为数据，记录了用户ID，签到日期。

在业务场景中，通常会要求用户连续签到，如果断签则要重新开始计数。

所以需要基于上述数据，计算筛选出连续签到天数大于n天的用户。

日期	用户ID
2022-01-01	A
2022-01-02	A
2022-01-02	B
2022-01-03	B
2022-01-05	B

2、实现思路

step1:根据用户的签到日期，利用窗口函数计算这是第几天签到。

如果同一天不能重复签到的话，那么三种排序函数都可用。但是如果同一天可以重复签到，则需要使用重复连续的dense_rank()over()函数。

三种排序函数在遇到重复值时的排序规则对比如下：

• dense_rank()，并列连续，1-2-2-3
• rank()，并列不连续，1-2-2-4
• row_number() ，不并列，1-2-3-4

窗口函数知识点回顾：

SQL-窗口函数知识点及案例学习_格勒王的博客-CSDN博客窗口函数的初步介绍，学习如何使用窗口函数计算排序、推移和累加求和等操作https://blog.csdn.net/weixin_47198715/article/details/130829177?spm=1001.2014.3001.5502

select 
  user_id,
  date,
  from_unixtime(unix_timestamp(date,'yyyymmdd'),'yyyy-mm-dd') as date_long,
  row_number()over(partition BY user_id order by date) as rn
  from table_name
  

得到如下结果：可以发现这里的日期排序完全没有考虑日期是否连续的问题

日期	用户ID	日期排序
2022-01-01	A	1
2022-01-02	A	2
2022-01-02	B	1
2022-01-03	B	2
2022-01-05	B	3

step2： 计算当前签到日期-日期排序序号

当前签到日期-日期排序序号之后得到的日期值的含义是：如果用户是连续签到的，那么当前日期对应的首次签到日期应该是几号。

这里注意：最好把日期提前转换为yyyy-mm-dd格式，便于时间加减运算

select
  user_id,
  date,
  rn,
  date_sub(date_long,rn) as date_first
from
  (select 
  user_id,
  date,
  from_unixtime(unix_timestamp(date,'yyyymmdd'),'yyyy-mm-dd') as date_long,
  row_number()over(partition BY user_id order by date) as rn
  from table_name)a
  

日期	用户ID	日期排序	date_first
2022-01-01	A	1	2021-12-31
2022-01-02	A	2	2021-12-31
2022-01-02	B	1	2022-01-01
2022-01-03	B	2	2022-01-01
2022-01-05	B	3	2022-01-02

根据结果可以理解，如果用户是连续签到，那么连续签到的几天对应的首次日期是一致的

B用户在20220105号进行了第3次签到，而如果是连续的，20220105日的签到排序应该是第4次，date_first是2022-01-01。但实际上，用户B在20220105日只进行了第3次签到，说明出现了断签情况。

step3：按照date_first（即假定连续签到下的首次签到时间）和用户ID分组，统计该分组下的日期数

select
 user_id,
 date_first,
 count(distinct date) as `连续签到天数`
from 
(select
  user_id,
  date,
  rn,
  date_sub(date_long,rn) as date_first
from
  (select 
  user_id,
  date,
  from_unixtime(unix_timestamp(date,'yyyymmdd'),'yyyy-mm-dd') as date_long,
  row_number()over(partition BY user_id order by date) as rn
  from table_name)a
 )a
group by user_id,date_first

date_first	用户ID	连续天数
2022-01-01	A	2
2022-01-01	B	2
2022-01-02	B	1

step4：取每个用户连续天数最大值

完整代码

select user_id,max(`连续签到天数`)
from 
(select
 user_id,
 date_first,
 count(distinct date) as `连续签到天数`
from 
(select
  user_id,
  date,
  rn,
  date_sub(date_long,rn) as date_first
from
  (select 
  user_id,
  date,
  from_unixtime(unix_timestamp(date,'yyyymmdd'),'yyyy-mm-dd') as date_long,
  row_number()over(partition BY user_id order by date) as rn
  from table_name)a
 )a
group by user_id,date_first)a
group by user_id