?
?
用戶行為分析
業務背景
某購物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版及以上執行
