用户行为分析系列实践 4 - 使用列存
目标任务
用户事件表T结构和部分数据示例如下：
Time |
UserID |
EventTypeID |
EventType |
Product |
Quantity |
Unit |
Price |
… |
2022/6/1 10:20 |
1072755 |
3 |
Search |
Apple |
5 |
Pound |
5.5 |
… |
2022/6/1 12:12 |
1078030 |
2 |
Browse |
Tissue |
4 |
Packs |
16 |
… |
2022/6/1 12:36 |
1005093 |
5 |
Submit |
Beef |
3 |
Pound |
35 |
… |
2022/6/1 13:21 |
1048655 |
1 |
Login |
… |
||||
2022/6/1 14:46 |
1037824 |
6 |
Logout |
… |
||||
2022/6/1 15:19 |
1049626 |
4 |
AddtoCart |
Wine |
4 |
Bottles |
120 |
… |
2022/6/1 16:00 |
1009296 |
5 |
Submit |
Pork |
6 |
Pound |
25 |
… |
2022/6/1 16:39 |
1070713 |
2 |
Browse |
Bread |
3 |
Packs |
10 |
… |
2022/6/1 17:40 |
1090884 |
3 |
Search |
Juice |
6 |
Bottles |
6 |
… |
T表字段说明：
字段名 |
数据类型 |
字段含义 |
Time |
日期时间 |
事件发生的时间戳，精确到毫秒 |
UserID |
整数 |
用户ID |
EventTypeID |
整数 |
事件类型ID |
EventType |
字符串 |
事件类型名称 |
Product |
字符串 |
商品 |
Quantity |
数值 |
数量 |
Unit |
字符串 |
单位 |
Price |
数值 |
价格 |
…… |
…… |
…… |
计算任务：
统计指定时间段内每种事件类型下的发生次数和发生过该事件的去重用户数。
需要先从很大的数据量里筛选出指定时间段内的数据。
实践技能
1．列式存储
由于用户事件表的字段数比较多，而本例统计需要用到的字段数比较少，所以如果采用列存组表，读数时只读需要用到的字段，可以极大地加快读数速度，减少内存占用。
列式存储通常还会较高的压缩，以减少外存访问量。
2. 有序存储
本例需要过滤出某个时间段内的数据进行统计，如果让组表数据以时间作为维字段有序存储，可提升过滤速度。
组表按块存储，每一块会保存维字段的最大最小值（minmax索引）。对于针对维字段的过滤条件，可以迅速跳过没有符合条件的块。
有序存储还能进一步提升压缩率和减少外存访问量。
3. 游标前过滤
生成数据对象需要消耗时间，可以在游标读出数据之前就判断过滤条件以减少没必要的数据对象生成。
示例代码
1、 使用按时间有序的列存组表文件转储数据库的数据
存量数据：将数据读出时按时间排序，然后写入列存组表文件
A |
|
1 |
=connect("demo").cursor@x("select * from T order by Time") |
2 |
=file("T.ctx").create@y(#Time,UserID,EventTypeID,EventType, Product, Quantity, Unit, Price, ……) |
3 |
=A2.append(A1) |
4 |
>A2.close() |
A1 读取T表的数据时按时间排序
A2 产生组表文件T.ctx，create()产生组表文件的结构，其中#Time表示以Time作为维字段有序存储。创建组表时需要写清数据结构，这一点和集文件不同。
A3 把A1中的数据读出来追加到组表T.ctx中，要保证写入的数据已经有序（即在A1中做过排序），组表写入时并不检查。
A4 关闭组表，对组表写入时需要有关闭动作。
增量数据：用时间戳来识别增量数据，每天0点以后，把前一天的增量数据追加到组表文件里：
A |
|
1 |
=connect("demo").cursor@x("select * from T where Time>=? && Time<? order by Time",date(now()-1), date(now())) |
2 |
=file("T.ctx").open() |
3 |
=A2.append(A1) |
4 |
>A2.close() |
A1 通过过滤条件筛选出前一天的数据，产生游标，需要先按时间戳排序
A2 打开组表文件
A3 将A1中的数据通过游标读出追加到T.ctx组表文件中。由于本例采用时间戳来识别增量数据，增量部分天然是后序时间发生的事件，所以不需要考虑和原文件排序归并的问题，直接追加即可。
A4 关闭组表。
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,EventType;Time>=start && Time<=end).groups(EventTypeID; EventType, count(1):Num, icount(UserID):iNum) |
A2 从组表文件中读取满足时间段的数据，并进行分组汇总。由于只读数不写数，所以不必对组表进行close()
本例统计只需要用到少数字段，所以在写cursor的时候需要把用到的字段列出来，这样不会读取所有字段。特别注意的是，如果仅仅在过滤中用到，后续分组汇总计算都用不上的字段，不必选出。
过滤时要把条件写到cursor()里面，这样会先判断过滤条件再决定是否生成结果集记录（此时会自动处理维字段实施前述的跳块动作），不满足条件的数据会被直接跳过。如果写成cursor().select(…)，则将先生成结果集记录再做过滤，不满足条件的数据也会被生成。
3、 并行计算，进一步提高性能
A |
|
1 |
>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
2 |
=file("T.ctx").open().cursor@m(UserID,EventTypeID,EventType;Time>=start && Time<=end;2).groups(EventTypeID; EventType, count(1):Num, icount(UserID):iNum) |
A2 并行计算只需要使用cursor@m(...;...;n)，@m选项表示生成多路游标实现并行计算，n是游标的路数，一般不超过CPU的核数。n省略则使用系统配置的并行数
运行结果：
EventTypeID |
EventType |
Num |
iNum |
1 |
Login |
4033000 |
393400 |
2 |
Browse |
3578901 |
348791 |
3 |
Search |
2947931 |
257539 |
4 |
AddtoCart |
1845674 |
175476 |
5 |
Submit |
867345 |
83375 |
6 |
Logout |
4033000 |
393400 |
