用户行为分析系列实践 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

数值

产地

计算任务:

统计指定时间段内每种产品类别下每个产地的销售数量。

需要考虑的问题是,产品的产地会不定期调整,统计销售数量时要使用销售事件发生时的产品产地。

实践技能

使用时间键维表,时间键相关知识可参考:

BI 系统中为什么会有很多快照表

1. 根据生产系统的维表定期生成有时间键的维表

Product.btx中要增加时间键字段,结构如下:

字段名

数据类型

字段含义

eTime

日期时间

当前记录生效时间

ProductID

字符串

产品ID

ProductName

字符串

产品名称

Unit

字符串

销售单位

ProductTypeID

字符串

产品类别

Origin

数值

产地

根据生产系统中Product表每天的变更信息补充到转储后的Product.btx中。

2. 使用有时间键的维表进行统计

指定eTimeProductID共同作为产品维表的主键,并指定eTime为时间键,TProduct关联时,使用TimeProductID两字段关联,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