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

这篇具有很好参考价值的文章主要介绍了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

sql高频面试题-连续完成两个指定动作的用户统计,Sql,sql,数据库,sqlserver

需求：

统计每天签到之后并进行抽奖的用户数，注意签到和抽奖行为必须相邻（签到和抽奖行为对应的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)

sql高频面试题-连续完成两个指定动作的用户统计,Sql,sql,数据库,sqlserver