用户行为分析系列实践 5 - 使用维表

目标任务

用户事件表T结构和部分数据示例如下:

Time

UserID

EventTypeID

ProductID

Quantity

2022/6/1 10:20

1072755

3

100001


2022/6/1 12:12

1078030

2

100002


2022/6/1 12:36

1005093

5

100003

3

2022/6/1 13:21

1048655

1



2022/6/1 14:46

1037824

6



2022/6/1 15:19

1049626

4

100004

4

2022/6/1 16:00

1009296

5

100005

6

2022/6/1 16:39

1070713

2

100006


2022/6/1 17:40

1090884

3

100007


T表字段说明:

字段名

数据类型

字段含义

Time

日期时间

事件发生的时间戳,精确到毫秒

UserID

字符串

用户ID

EventTypeID

整数

事件类型ID

ProductID

字符串

商品ID

Quantity

数值

数量

维表EventType:

EventTypeID

EventType

1

Login

2

Browse

3

Search

4

AddtoCart

5

Submit

6

Logout

维表Product

ProductID

ProductName

Unit

Price

ProductTypeID

100001

Apple

Pound

5.5

1

100002

Tissue

Packs

16

2

100003

Beef

Pound

35

3

100004

Wine

Bottles

120

4

100005

Pork

Pound

25

3

100006

Bread

Packs

10

5

100007

Juice

Bottles

6

4

维表Product字段说明:

字段名

数据类型

字段含义

ProductID

字符串

产品ID

ProductName

字符串

产品名称

Unit

字符串

销售单位

Price

数值

单价

ProductTypeID

整数

产品类别ID

维表ProductType:

ProductTypeID

ProductType

1

Fruits

2

Home&Personalcare

3

Meat

4

Beverage

5

Bakery

表间关系说明图:

计算任务:

统计指定时间段内每种产品类别下的销售额、下单次数、被搜索的次数、搜索和下单事件发生的去重用户数

实践技能

1 直接使用维表做关联,避免做宽表,减少数据存储,加快读数速度。

2 使用全程变量预先读入维表及建立关联以供复用

示例代码

1、 按照前面章节介绍的办法,把用户事件表T转储到T.ctx组表中,依旧按照Time字段排序;再按照前面章节介绍的办法,分别把维表转储到集文件EventType.btx, Product.btx, ProductType.btx

2、 先把维表读入内存,建立主键,再打开组表游标,和维表之间建立关联,最后分组汇总

设统计时间段为2022315日到2022616日:


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,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3))

3

>EventType=file("EventType.btx").import@b().keys@i(EventTypeID)

4

>ProductType=file("ProductType.btx").import@b().keys@i(ProductTypeID)

5

>Product=file("Product.btx").import@b().keys@i(ProductID)

6

>Product=Product.switch(ProductTypeID, ProductType:ProductTypeID)

7

=A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID)

8

=A7.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum)

A2 从组表文件中读取满足时间段的且事件类型为提交订单和搜索的数据,建立游标。

A3 从集文件EventType.btx中读取维表数据,设置主键并建立索引

A4 从集文件ProductType.btx中读取维表数据,设置主键为ProductTypeID

A5 从集文件Product.btx中读取维表数据,设置主键为ProductID

A6 ProductProductType建立关联

A7 A2游标关联内存中的维表ProductEventType

A8 对关联之后的游标A7,做小结果集分组计算

switch()关联维表时,需要事先在维表上设置主键,利用主键关联。关联后,相当于在原表关联字段中放了一个对维表记录的引用,之后可以用"原表字段.维表字段"的方式引用维表中的任意一个字段。

如果是多级维表,比如本例中,T-Product-ProductType表,则可以用.操作符逐级引用,比如" ProductID.ProductTypeID.ProductType "表示从T表的ProductID字段引用关联维表中的ProductTypeID字段再引用该字段关联维表中的ProductType

3、 维表经常被复用,而且通常维表不大,可以事先装入内存并建立关联,用全局变量保存,之后在统计时不用再读维表及建立关联了,直接使用全局变量即可。这样相当于把上述代码拆成两段,一段在服务器启动时把维表装载进全局变量,另一段则是统计代码:

第一段代码(服务器启动时执行一次即可)


A

1

=file("EventType.btx").import@b().keys@i(EventTypeID)

2

=file("ProductType.btx").import@b().keys@i(ProductTypeID)

3

=file("Product.btx").import@b().keys@i(ProductID)

4

>env(EventType,A1),env(ProductType,A2),env(Product,A3)

5

>Product.switch(ProductTypeID,ProductType:ProductTypeID)

A4 把内存中的维表存成全局变量,便于统计代码调用

第二段代码(统计部分)


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,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3))

3

=A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID)

4

=A3.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum)

运行结果:

EventTypeID

ProductTypeID

EventType

ProductType

Quantity

Num

iNum

3

1

Search

Fruits

0

499586

48735

3

2

Search

Home&Personalcare

0

508897

49872

3

3

Search

Meat

0

403213

39923

3

4

Search

Beverage

0

324567

29045

3

5

Search

Bakery

0

335498

30234

5

1

Submit

Fruits

206938

103469

13523

5

2

Submit

Home&Personalcare

463188

154396

14656

5

3

Submit

Meat

94378

93366

8754

5

4

Submit

Beverage

217504

54376

5233

5

5

Submit

Bakery

339480

67896

5844