用户行为分析系列实践 7 - 维表过滤
目标任务
用户事件表T结构和部分数据示例如下：
Time |
UserID |
ProductID |
Quantity |
… |
2022/6/1 10:20 |
1072755 |
1 |
7 |
… |
2022/6/1 12:12 |
1078030 |
2 |
8 |
… |
2022/6/1 12:36 |
1005093 |
3 |
3 |
… |
2022/6/1 13:21 |
1048655 |
4 |
9 |
… |
2022/6/1 14:46 |
1037824 |
5 |
5 |
… |
2022/6/1 15:19 |
1049626 |
6 |
4 |
… |
2022/6/1 16:00 |
1009296 |
7 |
6 |
… |
2022/6/1 16:39 |
1070713 |
8 |
7 |
… |
2022/6/1 17:40 |
1090884 |
9 |
4 |
… |
T表字段说明：
字段名 |
数据类型 |
字段含义 |
Time |
日期时间 |
事件发生的时间戳，精确到毫秒 |
UserID |
字符串 |
用户ID |
ProductID |
整数 |
用户购买的产品ID |
Quantity |
数值 |
用户购买的产品数量 |
维表Product:
ProductName |
Unit |
Price |
ProductTypeID |
|
1 |
Apple |
Pound |
5.5 |
1 |
2 |
Tissue |
Packs |
16 |
2 |
3 |
Beef |
Pound |
35 |
3 |
4 |
Wine |
Bottles |
120 |
4 |
5 |
Pork |
Pound |
25 |
3 |
6 |
Bread |
Packs |
10 |
5 |
7 |
Juice |
Bottles |
6 |
4 |
… |
… |
… |
… |
… |
维表Product字段说明：
字段名 |
数据类型 |
字段含义 |
ProductID |
字符串 |
产品ID |
ProductName |
字符串 |
产品名称 |
Unit |
字符串 |
销售单位 |
Price |
数值 |
单价 |
ProductTypeID |
整数 |
产品类别ID |
计算任务：
统计指定时间段内产品类别ID为1、2、3的每种产品的销售数量
实践技能
1、 维表过滤后再关联
先对维表过滤，然后再和事实表关联，关联不上的事实表记录不必再执行对维表的条件判断，判断次数会减少很多。
2、 游标中关联
在游标中和过滤后的维表关联，关联不上的事实表记录不再生成，进一步减少生成记录的时间。
3、 复用索引
对维表过滤时复用原维表索引。如果过滤后的结果集还较大，即过滤掉的记录较少，可以减少重建索引的时间。
4、 对位序列
对序号化的维表过滤时，可以使用更高效的对位序列。生成一个同长度的序表，其成员值为对应维表记录是否满足过滤条件。和事实表关联时，直接用序号定位取出匹配与否的结果，减少关联和比对的时间。
示例代码
假设T.ctx和Product.btx已经按照前面章节介绍的办法生成，其中T.ctx按Time排序，Product.btx按照ProductID排序：
1、 对维表Product过滤，然后再和T关联，关联时删除关联不上的T表记录
A |
|
1 |
>Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID) |
2 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
3 |
=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end) |
4 |
=A3.switch@i(ProductID,Product:ProductID) |
5 |
=A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A1 读取维表并过滤，然后设置主键索引
A4 事实表和维表关联，@i选项表示关联不上的记录将被删除
2、 把1中的关联移到产生游标的语句中，关联不上的记录不生成
A |
|
1 |
>Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID) |
2 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
3 |
=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end,ProductID:Product) |
4 |
=A3.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A3 把和维表的关联移到产生游标的语句中，关联不上的记录不生成
3、 对维表Product过滤时复用索引
A |
|
1 |
>Product=file("Product.btx").import@b().keys@i(ProductID) |
2 |
=Product.select@i([1,2,3].pos(ProductTypeID)!=null) |
3 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
4 |
=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end,ProductID:A2) |
5 |
=A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A1 读取维表并设置主键索引
A2 对维表过滤时使用@i选项将复用索引
索引复用并不会总是更快，因为要把过滤掉的记录从索引表删除，如果过滤掉的记录数很多（剩下的较少），这个动作也不会很快。而剩下记录较少时，重建索引很可能更快。具体采用哪种方式，要根据实际情况决定。
4、 Product维表的主键是序号，可以使用对位序列
A |
|
1 |
>Product=file("Product.btx").import@b() |
2 |
=Product.([1,2,3].pos(ProductTypeID)) |
3 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
4 |
=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end) |
5 |
=A4.select(A2(ProductID)) |
6 |
=A5.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity) |
A1 读出Product维表，不再设置主键索引了
A2 用Product表产生一个对位序列，值为当前Product记录是否满足过滤条件
A5 直接用T表的ProductID作为序号定位A2，即可得到当前记录是否满足过滤条件
5、 把利用对位序列过滤的操作移到游标产生的语句中
A |
|
1 |
>Product=file("Product.btx").import@b() |
2 |
=Product.([1,2,3].pos(ProductTypeID)) |
3 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
4 |
=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end && A2(ProductID)) |
5 |
=A4.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity) |
A4 把利用对位序列的过滤移到游标产生中，不满足过滤条件的记录不再生成
运行结果：
ProductID |
ProductName |
Quantity |
1 |
Apple |
206938 |
2 |
Tissue |
463188 |
3 |
Beef |
94378 |
5 |
Pork |
217504 |
… |
… |
… |
英文版