用户行为分析系列实践 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:
| ProductID | 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 |
英文版