用户行为分析系列实践 13 – 双维有序
目标任务
用户事件表T结构和部分数据示例如下：
Time |
UserID |
EventType |
OS |
Browser |
ProductID |
… |
f1 |
f2 |
f3 |
f4 |
f5 |
… |
2022/6/1 10:20 |
1072755 |
Search |
Android |
IE |
100001 |
… |
true |
false |
false |
true |
false |
… |
2022/6/1 12:12 |
1078030 |
Browse |
IOS |
Safari |
100002 |
… |
false |
false |
true |
true |
true |
… |
2022/6/1 12:36 |
1005093 |
Submit |
Android |
Chrome |
100003 |
… |
true |
true |
true |
false |
false |
… |
2022/6/1 13:21 |
1048655 |
Login |
Windows |
Chrome |
… |
false |
false |
true |
true |
true |
… |
|
2022/6/1 14:46 |
1037824 |
Logout |
Android |
Edge |
… |
false |
false |
false |
true |
true |
… |
|
2022/6/1 15:19 |
1049626 |
AddtoCart |
Windows |
Edge |
100004 |
… |
true |
true |
false |
true |
false |
… |
2022/6/1 16:00 |
1009296 |
Submit |
IOS |
Firefox |
100005 |
… |
false |
true |
false |
false |
true |
… |
2022/6/1 16:39 |
1070713 |
Browse |
IOS |
Sogou |
100006 |
… |
true |
true |
true |
false |
false |
… |
2022/6/1 17:40 |
1090884 |
Search |
Windows |
IE |
100007 |
… |
true |
false |
true |
true |
false |
… |
T表字段说明：
字段名 |
数据类型 |
字段含义 |
Time |
日期时间 |
事件发生的时间戳，精确到毫秒 |
UserID |
字符串 |
用户ID |
EventType |
字符串 |
事件类型，取值为Login,Browse,Search,AddtoCart,Submit,Logout |
OS |
字符串 |
操作系统，取值为Android,IOS,Windows,Unknown |
Browser |
字符串 |
浏览器，取值为IE,Safari,Edge,Firefox,Chrome,Sogou,Unknown |
ProductID |
字符串 |
产品ID，取值为产品维表中的ProductID字段 |
… |
字符串 |
更多其它取值为枚举值的字段 |
f1 |
布尔值 |
是否异地发生，取值为真和假 |
f2 |
布尔值 |
是否惯用设备，取值为真和假 |
f3 |
布尔值 |
是否惯用浏览器，取值为真和假 |
f4 |
布尔值 |
是否是手机，取值为真和假 |
f5 |
布尔值 |
是否首次操作，取值为真和假 |
… |
布尔值 |
更多其它取值为真和假的字段 |
维表Product:
ProductName |
Unit |
Price |
ProductType |
|
100001 |
Apple |
Pound |
5.5 |
Fruits |
100002 |
Tissue |
Packs |
16 |
Home&Personalcare |
100003 |
Beef |
Pound |
35 |
Meat |
100004 |
Wine |
Bottles |
120 |
Beverage |
100005 |
Pork |
Pound |
25 |
Meat |
100006 |
Bread |
Packs |
10 |
Bakery |
100007 |
Juice |
Bottles |
6 |
Beverage |
… |
… |
… |
… |
… |
维表Product字段说明：
字段名 |
数据类型 |
字段含义 |
ProductID |
字符串 |
产品ID |
ProductName |
字符串 |
产品名称 |
Unit |
字符串 |
销售单位 |
Price |
数值 |
单价 |
ProductType |
整数 |
产品类别 |
计算任务：
统计指定时间段内，产品类别为Home&Personalcare，本地使用安卓/苹果手机，使用Safari/ Edge/ Chrome，且非首次操作的用户，在一个时间窗口期内依次发生过搜索、加购物车、提交订单这三个动作中前N个的用户数量，从而方便后续统计用户转化率和用户流失率，即漏斗转化分析。
需要注意的事项：总数据量很大，跨度时间很长，但每次选择的时间窗口比较短。其它事项和前述漏斗分析一致：
1、 上述三个事件必须按时间顺序依次发生，顺序不对的不算
2、 上述三个事件必须是同一个用户在一个时间窗口期内发生，超出时间窗口期则不算
3、 以第一个事件发生时间点开始计时，如果后续事件在时间窗口期内按顺序发生，则相应事件记为1次，否则为0次，一旦出现某个事件为0次，后续事件就不用继续扫描。
实践技能
关于双维有序的相关知识可参考：
使用双维有序结构，使数据整体上对Time有序（从而实现快速过滤），结果集还可以做到对UserID有序（从而方便地实施后续计算），看起来相当于实现了两个维度有序。
示例代码
1、 定义复组表，将数据按month@y()拆分表(假设数据为2010-2021年的)
A |
|
1 |
=to(2021,2022).conj((a=~*100,12.(~+a))) |
2 |
=file("T.ctx":A1).create@y(#UserID,#Time,Month,EventType,OS,Browser,ProductID,b1;month@y(Time)) |
3 |
=A2.close() |
A1 根据时间范围，定义年月序列，用作分表的分表号，即每月一个分表
A2 定义复组表，使用A1作为分表号，并定义month@y(Time)作为分表号的计算表达式
2、 在复组表的基础上定义虚表
A |
|
… |
/前面定义复组表的代码 |
4 |
=T("Product.btx").keys@i(ProductID) |
5 |
=[{file:"T.ctx", zone:A1, user:"UserID", date:"Time", column:[ {name:"Month",exp:"month@y(Time)"}, {name:"EventType",pseudo:"EventTypeName",enum:["Login","Browse","Search","AddtoCart","Submit","Logout"]}, {name:"OS",pseudo:"OSName",enum:["Android","IOS","Windows","Unknown"]}, {name:"Browser",pseudo:"BrowserName",enum:["IE","Safari","Edge","Firefox","Chrome","Sogou","Unknown"]}, {name:"b1",bits:["f1","f2","f3","f4","f5"]}, {name:"ProductID",dim:A4}] }] |
6 |
=pseudo(A5) |
A4 读入产品维表，并建立主键索引
A5 在复组表T.ctx的基础上定义虚表，zone为分表号列表，date为分列字段，这里是Time；user为账户字段，这里是UserID; 分列字段和帐户字段的意义可参考前述参考文章。
其它定义参照上一篇文章
A6 生成虚表
3、 把数据读出来，然后按UserID,Time排序，通过虚表追加到复组表中
A |
|
… |
/前面定义虚表的代码 |
7 |
=connect("demo").cursor@x("select * from T").sortx(UserID,Time) |
8 |
=A6.append@i(A7) |
A7 连接数据库，读取T表数据并产生游标, 对着游标按UserID,Time排序
A8 将游标数据读出通过虚表追加到复组表中
实际应用中，数据是不断追加的，我们这里假设Time字段是数据的发生时间，因此追加的数据始终是Time更大的数据，新数据要么写入最后一个分表，要么写入新的分表，不会插入中间的分表里，SPL会自动保证最后一个分表的数据始终按UserID有序。因此对于增量数据，依旧按上述代码追加即可。
4、 利用虚表进行数据统计
A |
B |
C |
D |
|
… |
/前面定义虚表的代码 |
|||
7 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-04-05","yyyy-MM-dd"),tw=7 |
|||
8 |
[Search,AddtoCart,Submit] |
=A8.(0) |
||
9 |
=A6.cursor(UserID, EventType,Time;Time>=start && Time<=end && A8.contain(EventType) && ProductID.ProductType=="Home&Personalcare"&& ["Safari","Edge","Chrome"].pos(BrowserName) && ["Android","IOS"].pos(OSName) && ! f1 && f4 && !f5) |
|||
10 |
for A9;UserID |
=first=A10.select@1(EventType==A8(1)) |
||
11 |
if(B10==null) |
next |
||
12 |
=t=null |
=A8.(null) |
||
13 |
for A8 |
if #B13==1 |
>C12(1)=t=t1=first.Time |
|
14 |
else |
>C12(#B13)=t=if(t,A10.select@1(EventType==B13 && Time>t && Time<elapse(t1,tw)).Time,null) |
||
15 |
=C12.(if(~,1,0)) |
|||
16 |
>D8=D8++B15 |
|||
17 |
return D8 |
|||
A7 定义时间段参数、时间窗口tw，实际使用中通过参数传入
A8 目标事件名称，其顺序是重点
D8 产生一个和A8等长的数组，用于存储每个事件的发生次数，此为最终返回结果
A9 用虚表产生游标，游标中对时间段、目标事件、产品类别、浏览器、设备等进行过滤。虚表会自动执行快速过滤，选出目标时间段的数据
A10 对游标循环，每次取出一个用户的所有数据，虚表会保证数据对UserId有序，可以使用类似前面有序游标一样的技术。
以下内容就和有序游标上的计算是一样了。
B10 读第一个事件第一次发生的记录，赋给first变量
B11 如果第一个事件没有发生，则跳转下一个用户，当前用户为无效用户，不需要继续统计
B12 定义变量t，用于存储后续循环中的当前事件发生的时间
C12 定义和A8等长的数组，用于存储后续循环中每一个事件对应的发生时间
B13 循环A8
C13-D13 如果B13的循环序号是1，表明当前为第一个事件，此时记录t为first的发生时间，同时把此时间赋给t1
C14-D14 如果不是第一个事件，则判断上一个t是否为空，如果为空则把当前t赋值null；如果不为空，则从满足>t且小于时间窗口t1+tw的数据中寻找最早发生的当前事件记录，把其发生时间赋给t
B15 循环C12，把时间为null的次数记为0，不为null的次数记为1
B16 把B15累加到D8
运行结果：
Member |
393400 |
257539 |
83375 |
英文版