SQL统计连续登陆3天用户的实现示例_MsSqlserver

1. 数据准备

-- 数据准备
with user_active_info as (
select * from (
    values ('10001' , '2023-02-01'),('10001' , '2023-02-03')
          ,('10001' , '2023-02-04'),('10001' , '2023-02-05')
          ,('10002' , '2023-02-02'),('10002' , '2023-02-03')
          ,('10002' , '2023-02-04'),('10002' , '2023-02-05')
          ,('10002' , '2023-02-07'),('10003' , '2023-02-02')
          ,('10003' , '2023-02-03'),('10003' , '2023-02-04')
          ,('10003' , '2023-02-05'),('10003' , '2023-02-06')
          ,('10003' , '2023-02-07'),('10003' , '2023-02-08')
          ,('10004' , '2023-02-03'),('10004' , '2023-02-04')
          ,('10004' , '2023-02-06'),('10004' , '2023-02-07')
          ,('10004' , '2023-02-08'),('10004' , '2023-02-08') 
    	  ,('10005' , '2023-02-02'),('10005' , '2023-02-05') 
) as user_active_info(user_id, active_date) 
)

2. 方法一: 差值计算

-- 1. 对用户数据进行分组,按照活跃日期进行排序(去重:防止有一天有多次活跃记录)
select 
      user_id
    , active_date
    , row_number() over(partition by user_id order by active_date) as rn 
from user_active_info
group by user_id , active_date
;

user_id	active_date	rn
10001	2023-02-01	1
10001	2023-02-03	2
10001	2023-02-04	3
10001	2023-02-05	4
10002	2023-02-02	1
10002	2023-02-03	2
10002	2023-02-04	3
10002	2023-02-05	4
10002	2023-02-07	5
…	…	…

-- 2. 使用活跃日期和排序rn进行差值计算,得到的日期如果是相等的,就说明活跃日期是连续的
select 
      user_id
    , active_date
    , rn 
    , date_sub(active_date,rn) as sub_date
from (
    select 
          user_id
        , active_date
        , row_number() over(partition by user_id order by active_date) as rn 
    from user_active_info 
    group by user_id , active_date
    ) a
;

user_id	active_date	rn	sub_date
10001	2023-02-01	1	2023-01-31
10001	2023-02-03	2	2023-02-01
10001	2023-02-04	3	2023-02-01
10001	2023-02-05	4	2023-02-01
10002	2023-02-02	1	2023-02-01
10002	2023-02-03	2	2023-02-01
10002	2023-02-04	3	2023-02-01
10002	2023-02-05	4	2023-02-01
10002	2023-02-07	5	2023-02-02
…	…	…	…

-- 3. 按照user_id和sub_date 进行分组求和,筛选出连续登陆天数大于3天的用户
select 
      user_id
    , min(active_date) as begin_date
    , max(active_date) as end_date
    , count (1) as login_duration
from (
    select 
          user_id
        , active_date
        , rn 
        , date_sub(active_date,rn) as sub_date
    from (
        select 
              user_id
            , active_date
            , row_number() over(partition by user_id order by active_date) as rn 
        from user_active_info 
        group by user_id , active_date
    ) a
) b
group by user_id , sub_date
having login_duration >= 3
;

user_id	begin_date	end_date	login_duration
10001	2023-02-03	2023-02-05	3
10002	2023-02-02	2023-02-05	4
10003	2023-02-02	2023-02-08	7
10004	2023-02-06	2023-02-08	3

3. 方法二: lead或lag函数

-- 1. 将active_date 上抬2行,不存在默认为'0'(计算连续活跃3天以上的, 上抬2行,n天上抬n-1行)(去重:防止有一天有多次活跃记录)
select 
      user_id
    , active_date
    , lead(active_date , 2 , 0) over(partition by user_id order by active_date) as lead_active_date 
from user_active_info
group by user_id , active_date

user_id	active_date	lead_active_date
10001	2023-02-01	2023-02-04
10001	2023-02-03	2023-02-05
10001	2023-02-04	0
10001	2023-02-05	0
10002	2023-02-02	2023-02-04
10002	2023-02-03	2023-02-05
10002	2023-02-04	2023-02-07
10002	2023-02-05	0
10002	2023-02-07	0
…	…	…

-- 2. 过滤筛选出, lead_active_date 与 active_date 差值为2的, 差值2 -> 连续活跃了3天
select 
      user_id , active_date , lead_active_date
from (
    select 
          user_id
        , active_date
        , lead(active_date , 2 , 0) over(partition by user_id order by active_date) as lead_active_date
    from user_active_info
    group by user_id , active_date
) a 
where  lead_active_date != '0'
    and datediff(lead_active_date , active_date) = 2

user_id	active_date	lead_active_date
10001	2023-02-03	2023-02-05
10002	2023-02-02	2023-02-04
10002	2023-02-03	2023-02-05
…	…	…

-- 3. user_id 去重, 得到连续活跃天数>=3天的用户
select 
      user_id
from (
    select 
          user_id , active_date , lead_active_date
    from (
        select 
              user_id
            , active_date
            , lead(active_date , 2 , 0) over(partition by user_id order by active_date) as lead_active_date 
        from user_active_info
        group by user_id , active_date
    ) a 
    where  lead_active_date != '0'
        and datediff(lead_active_date , active_date) = 2
) b
group by user_id