用户行为分析系列实践 8 – 变化的维表
目标任务
用户事件表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：
ProductID |
ProductName |
Unit |
Origin |
ProductType |
1 |
Apple |
Pound |
Shandong |
Fruits |
2 |
Tissue |
Packs |
Guangdong |
Home&Personalcare |
3 |
Beef |
Pound |
Qingdao |
Meat |
4 |
Wine |
Bottles |
Shanxi |
Beverage |
5 |
Pork |
Pound |
Xizang |
Meat |
6 |
Bread |
Packs |
Beijing |
Bakery |
7 |
Juice |
Bottles |
Xinjiang |
Beverage |
… |
… |
… |
… |
… |
维表Product字段说明：
字段名 |
数据类型 |
字段含义 |
ProductID |
字符串 |
产品ID |
ProductName |
字符串 |
产品名称 |
Unit |
字符串 |
销售单位 |
ProductTypeID |
字符串 |
产品类别 |
Origin |
数值 |
产地 |
计算任务：
统计指定时间段内每种产品类别下每个产地的销售数量。
需要考虑的问题是，产品的产地会不定期调整，统计销售数量时要使用销售事件发生时的产品产地。
实践技能
使用时间键维表，时间键相关知识可参考：
1. 根据生产系统的维表定期生成有时间键的维表
Product.btx中要增加时间键字段，结构如下：
字段名 |
数据类型 |
字段含义 |
eTime |
日期时间 |
当前记录生效时间 |
ProductID |
字符串 |
产品ID |
ProductName |
字符串 |
产品名称 |
Unit |
字符串 |
销售单位 |
ProductTypeID |
字符串 |
产品类别 |
Origin |
数值 |
产地 |
根据生产系统中Product表每天的变更信息补充到转储后的Product.btx中。
2. 使用有时间键的维表进行统计
指定eTime和ProductID共同作为产品维表的主键，并指定eTime为时间键，T和Product关联时，使用Time和ProductID两字段关联，SPL会自动处理和时间键有关的计算逻辑，使用者不必再关心内部实现，和普通的维表用法一样即可。
示例代码
1、 转储Product.btx维表
初始数据：直接增加时间键字段
A |
|
1 |
=connect("demo").query@x("select * from Product").derive(now():eTime) |
2 |
= file("Product.btx").export@b(A1) |
更新数据：维表有更新时，重新读出完整维表，并且从已转储过的btx中读出每种产品的时间最新的记录，和新读的维表进行对比，如果数据有变化，则追加新记录，标注新的生效时间。
A |
|
1 |
=connect("demo").cursor@x("select * from Product") |
2 |
= T("Product.btx").keys@it(ProductID,eTime) |
3 |
=A1.select(cmp(~.array(),A2.find(A1.ProductID).array().to(2,))!=0) |
4 |
= A3.fetch().derive(now():eTime) |
5 |
=file("Product.btx").export@ab(A4) |
A1 读新维表，设置主键索引
A2 从集文件中读出原Product维表，取每个产品的时间最新的记录，设置主键索引
@t表示后一个键是时间键
A3 选出新维表中和原维表中同主键中最新记录（eTime最大）有不同的记录，使用find时会自动选出同一主键下最新的记录，
A4 把A3中的记录添加生效时间字段
A5 把A4追加写出到集文件
2、 关联事实表和维表，并统计
假设T.ctx已经按照前面章节介绍的办法生成，并按Time排序：
A |
|
1 |
>Product=T("Product.btx").keys@ti(ProductID,eTime) |
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;Time>=start && Time<=end) |
4 |
=A3.switch(ProductID:Time,Product) |
5 |
=A4.groups(ProductID.ProductType,ProductID.Origin; sum(Quantity):Quantity) |
A1 读取维表，设置主键索引的同时指定时间键
A4 事实表和维表关联，和正常写法一样，关联字段多了一个时间字段将找出该时间之前的最新（eTime小于该时间且最大的）记录
运行结果：
ProductType |
Origin |
Quantity |
Fruits |
Shandong |
1241628 |
Fruits |
Xinjiang |
546357 |
Fruits |
Hainan |
24526 |
Home&Personalcare |
Guangdong |
7411008 |
Meat |
Qingdao |
3303230 |
Meat |
Neimeng |
657546 |
Meat |
Xizang |
2456235 |
Bakery |
Beijing |
247673 |
Beverage |
Xinjiang |
3526574 |
Beverage |
Shanxi |
6090112 |
… |
… |
… |
英文版