用户行为分析系列实践 5 - 使用维表
目标任务
用户事件表T结构和部分数据示例如下：
Time |
UserID |
EventTypeID |
ProductID |
Quantity |
2022/6/1 10:20 |
1072755 |
3 |
100001 |
|
2022/6/1 12:12 |
1078030 |
2 |
100002 |
|
2022/6/1 12:36 |
1005093 |
5 |
100003 |
3 |
2022/6/1 13:21 |
1048655 |
1 |
||
2022/6/1 14:46 |
1037824 |
6 |
||
2022/6/1 15:19 |
1049626 |
4 |
100004 |
4 |
2022/6/1 16:00 |
1009296 |
5 |
100005 |
6 |
2022/6/1 16:39 |
1070713 |
2 |
100006 |
|
2022/6/1 17:40 |
1090884 |
3 |
100007 |
T表字段说明：
字段名 |
数据类型 |
字段含义 |
Time |
日期时间 |
事件发生的时间戳，精确到毫秒 |
UserID |
字符串 |
用户ID |
EventTypeID |
整数 |
事件类型ID |
ProductID |
字符串 |
商品ID |
Quantity |
数值 |
数量 |
维表EventType:
EventTypeID |
EventType |
1 |
Login |
2 |
Browse |
3 |
Search |
4 |
AddtoCart |
5 |
Submit |
6 |
Logout |
维表Product：
ProductName |
Unit |
Price |
ProductTypeID |
|
100001 |
Apple |
Pound |
5.5 |
1 |
100002 |
Tissue |
Packs |
16 |
2 |
100003 |
Beef |
Pound |
35 |
3 |
100004 |
Wine |
Bottles |
120 |
4 |
100005 |
Pork |
Pound |
25 |
3 |
100006 |
Bread |
Packs |
10 |
5 |
100007 |
Juice |
Bottles |
6 |
4 |
… |
… |
… |
… |
… |
维表Product字段说明：
字段名 |
数据类型 |
字段含义 |
ProductID |
字符串 |
产品ID |
ProductName |
字符串 |
产品名称 |
Unit |
字符串 |
销售单位 |
Price |
数值 |
单价 |
ProductTypeID |
整数 |
产品类别ID |
维表ProductType:
ProductTypeID |
ProductType |
1 |
Fruits |
2 |
Home&Personalcare |
3 |
Meat |
4 |
Beverage |
5 |
Bakery |
… |
… |
表间关系说明图：
计算任务：
统计指定时间段内每种产品类别下的销售额、下单次数、被搜索的次数、搜索和下单事件发生的去重用户数
实践技能
1 直接使用维表做关联，避免做宽表，减少数据存储，加快读数速度。
2 使用全程变量预先读入维表及建立关联以供复用
示例代码
1、 按照前面章节介绍的办法，把用户事件表T转储到T.ctx组表中，依旧按照Time字段排序；再按照前面章节介绍的办法，分别把维表转储到集文件EventType.btx, Product.btx, ProductType.btx中
2、 先把维表读入内存，建立主键，再打开组表游标，和维表之间建立关联，最后分组汇总
设统计时间段为2022年3月15日到2022年6月16日：
A |
|
1 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
2 |
=file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3)) |
3 |
>EventType=file("EventType.btx").import@b().keys@i(EventTypeID) |
4 |
>ProductType=file("ProductType.btx").import@b().keys@i(ProductTypeID) |
5 |
>Product=file("Product.btx").import@b().keys@i(ProductID) |
6 |
>Product=Product.switch(ProductTypeID, ProductType:ProductTypeID) |
7 |
=A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID) |
8 |
=A7.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
A2 从组表文件中读取满足时间段的且事件类型为提交订单和搜索的数据，建立游标。
A3 从集文件EventType.btx中读取维表数据，设置主键并建立索引
A4 从集文件ProductType.btx中读取维表数据，设置主键为ProductTypeID
A5 从集文件Product.btx中读取维表数据，设置主键为ProductID
A6 将Product和ProductType建立关联
A7 将A2游标关联内存中的维表Product和EventType
A8 对关联之后的游标A7，做小结果集分组计算
用switch()关联维表时，需要事先在维表上设置主键，利用主键关联。关联后，相当于在原表关联字段中放了一个对维表记录的引用，之后可以用"原表字段.维表字段"的方式引用维表中的任意一个字段。
如果是多级维表，比如本例中，T表-Product表-ProductType表，则可以用.操作符逐级引用，比如" ProductID.ProductTypeID.ProductType "表示从T表的ProductID字段引用关联维表中的ProductTypeID字段再引用该字段关联维表中的ProductType
3、 维表经常被复用，而且通常维表不大，可以事先装入内存并建立关联，用全局变量保存，之后在统计时不用再读维表及建立关联了，直接使用全局变量即可。这样相当于把上述代码拆成两段，一段在服务器启动时把维表装载进全局变量，另一段则是统计代码：
第一段代码(服务器启动时执行一次即可)：
A |
|
1 |
=file("EventType.btx").import@b().keys@i(EventTypeID) |
2 |
=file("ProductType.btx").import@b().keys@i(ProductTypeID) |
3 |
=file("Product.btx").import@b().keys@i(ProductID) |
4 |
>env(EventType,A1),env(ProductType,A2),env(Product,A3) |
5 |
>Product.switch(ProductTypeID,ProductType:ProductTypeID) |
A4 把内存中的维表存成全局变量，便于统计代码调用
第二段代码(统计部分)：
A |
|
1 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
2 |
=file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3)) |
3 |
=A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID) |
4 |
=A3.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
运行结果：
EventTypeID |
ProductTypeID |
EventType |
ProductType |
Quantity |
Num |
iNum |
3 |
1 |
Search |
Fruits |
0 |
499586 |
48735 |
3 |
2 |
Search |
Home&Personalcare |
0 |
508897 |
49872 |
3 |
3 |
Search |
Meat |
0 |
403213 |
39923 |
3 |
4 |
Search |
Beverage |
0 |
324567 |
29045 |
3 |
5 |
Search |
Bakery |
0 |
335498 |
30234 |
… |
… |
… |
… |
… |
… |
… |
5 |
1 |
Submit |
Fruits |
206938 |
103469 |
13523 |
5 |
2 |
Submit |
Home&Personalcare |
463188 |
154396 |
14656 |
5 |
3 |
Submit |
Meat |
94378 |
93366 |
8754 |
5 |
4 |
Submit |
Beverage |
217504 |
54376 |
5233 |
5 |
5 |
Submit |
Bakery |
339480 |
67896 |
5844 |
… |
… |
… |
… |
… |
… |
… |
