用户行为分析系列实践 12 – 使用虚表
目标任务
用户事件表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 |
布尔值 |
是否首次操作，取值为真和假 |
… |
布尔值 |
更多其它取值为真和假的字段 |
根据前面的章节(https://c.raqsoft.com.cn/article/1657716176800)介绍，把枚举字段和标签字段转换为对应的序号和位，转换后的用户事件组表T.ctx结构和部分数据示例如下：
Time |
UserID |
EventType |
OS |
Browser |
ProductID |
… |
b1 |
… |
2022/6/1 10:20 |
1072755 |
3 |
1 |
1 |
100001 |
… |
36864 |
… |
2022/6/1 12:12 |
1078030 |
2 |
2 |
2 |
100002 |
… |
14336 |
… |
2022/6/1 12:36 |
1005093 |
5 |
1 |
5 |
100003 |
… |
57344 |
… |
2022/6/1 13:21 |
1048655 |
1 |
3 |
5 |
… |
14336 |
… |
|
2022/6/1 14:46 |
1037824 |
6 |
1 |
3 |
… |
6144 |
… |
|
2022/6/1 15:19 |
1049626 |
4 |
3 |
3 |
100004 |
… |
53248 |
… |
2022/6/1 16:00 |
1009296 |
5 |
2 |
4 |
100005 |
… |
18432 |
… |
2022/6/1 16:39 |
1070713 |
2 |
2 |
6 |
100006 |
… |
57344 |
… |
2022/6/1 17:40 |
1090884 |
3 |
3 |
1 |
100007 |
… |
45056 |
… |
转换后的T表字段说明：
字段名 |
数据类型 |
字段含义 |
Time |
日期时间 |
事件发生的时间戳，精确到毫秒 |
UserID |
字符串 |
用户ID |
EventType |
整数 |
事件类型，取值为枚举序列中的序号 |
OS |
整数 |
操作系统，取值为枚举序列中的序号 |
Browser |
整数 |
浏览器，取值为枚举序列中的序号 |
ProductID |
字符串 |
商品ID，取值为维表中的ProductID字段 |
… |
… |
… |
b1 |
整数 |
整数字段，用位存储二值字段，其中的前五位分别对应是否异地发生，是否惯用设备，是否惯用浏览器，是否是手机，是否首次操作 |
… |
… |
… |
EventType枚举序列的序号和对应的文字说明：
1 Login
2 Browse
3 Search
4 AddtoCart
5 Submit
6 Logout
OS枚举序列的序号和对应的文字说明：
1 Android
2 IOS
3 Windows
4 Unknown
Browser枚举序列的序号和对应的文字说明：
1 IE
2 Safari
3 Edge
4 Firefox
5 Chrome
6 Sogou
7 Unknown
维表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，且非首次操作的用户，每个月在每种事件类型下的发生次数，以及去重用户数。
实践技能
关于虚表的相关知识可参考：
使用虚表实现计算列和外键的预定义，以及枚举维度和标签维度的简化书写：
1、 定义计算列Month，计算当前事件发生时间所在的月份
2、 定义EventTypeName、OSName、BrowserName伪字段，存储这些字段的序号和名称的对应关系
3、 在ProductID字段上定义和对应维表的外键关联关系
4、 在b1上定义每一位代表的字段名
定义完虚表后，可以利用虚表实现数据转储以及汇总统计，将极大地减少统计代码量。
示例代码
1、 定义虚表
A |
|
1 |
=T("Product.btx").keys@i(ProductID) |
2 |
=file("T.ctx").create(#Time,UserID,EventType,OS,Browser,ProductID,……,b1,……) |
3 |
=A2.close() |
4 |
=[{file:"T.ctx", 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:A1}] }] |
5 |
=pseudo(A4) |
A1 读入维表Product并设置主键和索引
A2 创建序号化及位维转化后的组表结构，写入空文件T.ctx(后续利用虚表直接将数据库的数据追加到此文件，不用再写序号和位的转换代码)
A3 关闭组表
A4 在组表T.ctx的基础上定义虚表：
{name:"Month",exp:"month@y(Time)"}定义计算列，列名Month，表达式month@y(Time)
{name:"EventType", pseudo:"EventTypeName", enum:[ "Login", "Browse", "Search", "AddtoCart", "Submit", "Logout"]}定义EventType字段值和枚举序列的对应关系，将对应的名称用伪字段EventTypeName表示
{name:"OS",pseudo:"OSName",enum:["Android","IOS","Windows","Unknown"]}定义OS字段值和枚举序列的对应关系，将对应的名称用伪字段OSName表示
{name:"Browser",pseudo:"BrowserName",enum:["IE","Safari","Edge","Firefox","Chrome","Sogou","Unknown"]}定义Browser字段值和枚举序列的对应关系，将对应的名称用伪字段BrowserName表示
{name:"b1",bits:["f1","f2","f3","f4","f5",…….]}定义位维度b1中每一位代表的字段名
{name:"ProductID",dim:A1}在字段ProductID上定义和维表A1的关联关系
A5 生成虚表
可以将虚表的定义串保存起来，后续每次使用时直接读取，进一步减少代码量。
2、 利用虚表进行数据的转储，SPL会自动根据虚表定义，将数据转换成对应的序号和位，存入T表中
A |
|
… |
/前面定义虚表的代码 |
6 |
=connect("demo").cursor@x("select * from T order by Time") |
7 |
=A5.append@i(A6) |
A6 连接数据库，读取T表数据并产生游标
A7 将游标数据读出追加到虚表中
3、 利用虚表做统计
A |
|
… |
/前面定义虚表的代码 |
6 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
7 |
=A5.select(Time>=start && Time<=end && ProductID.ProductType=="Home&Personalcare"&& ["Safari","Edge","Chrome"].pos(BrowserName) && ["Android","IOS"].pos(OSName) && ! f1 && f4 && !f5).groups(Month,EventTypeName; count(1):Num, icount(UserID):iNum) |
A7 利用虚表做汇总统计，其上定义的伪字段可以当成普通字段使用，不必再考虑存储和计算机制，将虚表当成简单的普通表即可
运行结果：
Month |
EventTypeName |
Num |
iNum |
202203 |
AddtoCart |
307603 |
29252 |
202203 |
Browse |
596492 |
58140 |
202203 |
Login |
672163 |
65569 |
202203 |
Logout |
672163 |
65569 |
202203 |
Search |
491317 |
42919 |
202203 |
Submit |
144552 |
13901 |
202204 |
AddtoCart |
615222 |
58484 |
202204 |
Browse |
1192970 |
116265 |
202204 |
Login |
1344323 |
131123 |
202204 |
Logout |
1344323 |
131123 |
202204 |
Search |
982637 |
85843 |
202204 |
Submit |
289112 |
27799 |
202205 |
AddtoCart |
615214 |
58484 |
202205 |
Browse |
1192976 |
116262 |
202205 |
Login |
1344339 |
131133 |
202205 |
Logout |
1344339 |
131133 |
202205 |
Search |
982633 |
85848 |
202205 |
Submit |
289108 |
27788 |
202206 |
AddtoCart |
307635 |
29256 |
202206 |
Browse |
596463 |
58124 |
202206 |
Login |
672175 |
65575 |
202206 |
Logout |
672175 |
65575 |
202206 |
Search |
491344 |
42929 |
202206 |
Submit |
144573 |
13887 |
英文版