sql高频面试题-连续完成两个指定动作的用户统计

 
 

用户行为分析

业务背景

某购物APP最近上线了一个新功能，用户签到后可以跳转到大转盘抽奖，抽奖获得的奖金可以抵消购物的费用，以此来培养用户使用app的习惯。

数据表介绍

现有一张用户行为表action_log，主要字段如下，记录了用户在app上的所有行为日志，即何人userid在何时action_time进行了什么操作action_name。

select 10001 userid ,'2023-08-01 00:32:33'  action_time , 'sign' action_name 
into #action_log
union all   
select 10001 userid ,'2023-08-01 00:32:38'  action_time , 'lottery' action_name 
union all   
select 10001 userid ,'2023-08-01 00:32:10'  action_time , 'login' action_name 
union all  
select 10001 userid ,'2023-08-01 01:20:12'  action_time , 'logout' action_name 
union all  
select 10002 userid ,'2023-08-01 15:32:33'  action_time , 'sign' action_name 
union all 
select 10002 userid ,'2023-08-01 15:32:38'  action_time , 'lottery' action_name 
union all   
select 10002 userid ,'2023-08-01 15:32:10'  action_time , 'login' action_name 
union all  
select 10002 userid ,'2023-08-01 15:20:12'  action_time , 'logout' action_name 
union all 
select 10002 userid ,'2023-08-01 15:32:35'  action_time , 'gift' action_name 
  
 select * from  #action_log

需求：

统计每天签到之后并进行抽奖的用户数，注意签到和抽奖行为必须相邻（签到和抽奖行为对应的event_id分别为'sign','lottery'）。

思路：

统计用户数时添加了限制：签到之后要大转盘抽奖，两个行为一前一后必须相邻才可以。这个时候我们可以用窗口函数的位移函数lead() over()实现，lead可以取当前记录的下一条记录，如果我们对每个用户userid分组，按照行为时间action_time升序排列，就可以得到一个用户的连续的行为记录，再用lead() 就可以得到下一条记录，从而在当前记录中得到下一条记录，对两个连续行为进行筛选，就可以计算满足这个条件的用户数。

代码：
 select  convert(varchar(10),action_time,120) day, count(distinct userid)  users
 from (
      select *,lead (action_name,1) over(partition by userid order by action_time ) next_aciton  
      from #action_log
)t
 where action_name ='sign' and next_aciton = 'lottery' 
 group by convert(varchar(10),action_time,120)

细节点注意：这种查询方式可查询出两个连续动作跨天的用户，用户被统计在了第一个动作（即签到 “sign”）所属的日期中

lead() over 必须在sqlserver 2012版及以上执行