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


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾