用户行为分析系列实践 6 - 维表序号化
目标任务
用户事件表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、 使用序号定位方法关联维表
EventType和ProductType表的主键是自然数序号，可以直接用序号定位实现关联，不必建索引，避免计算和比对HASH值，性能更好
2、 把非序号主键维表的主键转换成自然数序号，从而可以使用序号定位以提高关联性能
Product表的主键不是自然数序号，可以事先转换成序号。同时把用户事件表T中的ProductID字段值也转换成相应的序号后，就可以也使用序号定位。
示例代码
分为5个部分：
1、 按照前面章节介绍的办法，分别把维表转储到集文件EventType.btx, Product.btx, ProductType.btx中，其中EventType表按EventTypeID排序，ProductType表按ProductTypeID排序
2、 用户事件表T和EventType的关联、Product表和ProductType的关联直接改成序号关联
3、 Product表增加序号字段
4、 把用户事件表T转储到T.ctx组表时，除了依旧按照Time字段排序，还需要把ProductID的值变成Product表中的对应记录序号
5、 把维表读入内存，在打开组表游标，和维表之间建立关联时，和Product表的关联改用序号，Product维表可以不必建立主键和索引。
1. 转储维表数据
A |
|
1 |
=connect("demo") |
2 |
=A1.query("select * from Product") |
3 |
=file("Product.btx").export@b(A1) |
4 |
=A1.query("select * from EventType order by EventTypeID") |
5 |
=file("EventType.btx").export@b(A4) |
6 |
=A1.query@x("select * from ProductType order by ProductTypeID") |
7 |
=file("ProductType.btx").export@b(A6) |
A4 按EventTypeID排序
A6 按ProductTypeID排序
2. 把EventType和ProductType表与T表的关联方式改成序号方式关联。
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() |
4 |
>ProductType=file("ProductType.btx").import@b() |
5 |
>Product=file("Product.btx").import@b().keys@i(ProductID) |
6 |
>Product=Product.switch(ProductTypeID, ProductType:#) |
7 |
=A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:#) |
8 |
=A7.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
A3-A4 不必再建主键索引
A6 和ProductType表的关联方式改成序号关联
A7 和EventType表的关联方式改成序号关联
3. Product表增加序号字段
初始数据：直接增加序号字段
A |
|
1 |
=connect("demo").query@x("select * from Product").derive(#:ProductNum) |
2 |
= file("Product.btx").export@b(A1) |
更新数据：维表有更新时，要重新读出完整维表，和已转储过的btx对比，使用已经已经转储过的记录序号，否则历史数据将对不上；新增的记录排在末尾；维表通常不会有删除，否则会发生事实表历史记录引用出错。
A |
|
1 |
=connect("demo").query@x("select * from Product").derive(:ProductNum).keys@i(ProductID) |
2 |
= file("Product.btx").import@b().keys@i(ProductID) |
3 |
=A1.select(A2.find(A1.ProductID)==null) |
4 |
=A2.(if(r=A1.find(A2.ProductID),r,~) ) |
5 |
=(A4|A3).run(ProductNum=#) |
6 |
=file("Product.btx").export@b(A5) |
A1 读新维表，添加ProductNum字段，设置主键索引
A2 从集文件中读出原Product维表，设置主键
A3 算出新维表中的新增记录
A4 原维表里的记录如果在新维表中存在，则用新的；如果不存在，则保留原维表的记录
A5 把A4和A3合并起来，设置序号，因为A4没有改变原维表的顺序，所以原维表的序号事实上没变
A6 把A5写出到集文件
4. T.ctx文件准备代码，将ProductID改成序号
以存量数据为例：
A |
|
1 |
>Product=file("Product.btx").import@b().keys@i(ProductID) |
2 |
=connect("demo").cursor@x("select * from T order by Time") |
3 |
=A2.run(ProductID=Product.find(A2.ProductID).ProductNum) |
4 |
=file("T.ctx").create@y(#Time,UserID,EventTypeID, ProductID, Quantity) |
5 |
=A4.append(A3) |
6 |
>A4.close() |
A1 读取Product维表至内存，设置主键索引
A2 读取T表的数据时按时间排序
A3 把T表的ProductID字段值变成其在维表中的序号
A4 创建组表
A5 把T表的数据写入组表文件
增量数据类似
5. 针对转换过的数据再做一次完整的统计过程，全部都用序号
设统计时间段为2022年3月15日到2022年6月16日：
A |
|
1 |
>EventType=file("EventType.btx").import@b() |
2 |
>ProductType=file("ProductType.btx").import@b() |
3 |
>Product=file("Product.btx").import@b() |
4 |
>Product=Product.switch(ProductTypeID, ProductType:#) |
5 |
=file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3)) |
6 |
=A5.switch(ProductID,Product:#; EventTypeID,EventType:#) |
7 |
=A6.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
A1-A3 不用设置主键
A6 改用序号关联
运行结果：
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 |
… |
… |
… |
… |
… |
… |
… |
