用户行为分析系列实践 6 - 维表序号化

目标任务

用户事件表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、 使用序号定位方法关联维表

EventTypeProductType表的主键是自然数序号,可以直接用序号定位实现关联,不必建索引,避免计算和比对HASH值,性能更好

2、 把非序号主键维表的主键转换成自然数序号,从而可以使用序号定位以提高关联性能

Product表的主键不是自然数序号,可以事先转换成序号。同时把用户事件表T中的ProductID字段值也转换成相应的序号后,就可以也使用序号定位。

示例代码

分为5个部分:

1、 按照前面章节介绍的办法,分别把维表转储到集文件EventType.btx, Product.btx, ProductType.btx中,其中EventType表按EventTypeID排序,ProductType表按ProductTypeID排序

2、 用户事件表TEventType的关联、Product表和ProductType的关联直接改成序号关联

3、 Product表增加序号字段

4、 把用户事件表T转储到T.ctx组表时,除了依旧按照Time字段排序,还需要把ProductID的值变成Product表中的对应记录序号

5、 把维表读入内存,在打开组表游标,和维表之间建立关联时,和Product表的关联改用序号,Product维表可以不必建立主键和索引。

1. 转储维表数据


A

1

=connect("demo")

2

=A1.query("select * from Product")

3

=file("Product.btx").export@b(A1)

4

=A1.query("select * from EventType order by EventTypeID")

5

=file("EventType.btx").export@b(A4)

6

=A1.query@x("select * from ProductType order by ProductTypeID")

7

=file("ProductType.btx").export@b(A6)

A4 EventTypeID排序

A6 ProductTypeID排序

2. EventTypeProductType表与T表的关联方式改成序号方式关联。


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()

4

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

5

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

6

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

7

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

8

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

A3-A4 不必再建主键索引

A6 ProductType表的关联方式改成序号关联

A7 EventType表的关联方式改成序号关联

3. Product表增加序号字段

初始数据:直接增加序号字段


A

1

=connect("demo").query@x("select * from Product").derive(#:ProductNum)

2

= file("Product.btx").export@b(A1)

更新数据:维表有更新时,要重新读出完整维表,和已转储过的btx对比,使用已经已经转储过的记录序号,否则历史数据将对不上;新增的记录排在末尾;维表通常不会有删除,否则会发生事实表历史记录引用出错。


A

1

=connect("demo").query@x("select * from Product").derive(:ProductNum).keys@i(ProductID)

2

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

3

=A1.select(A2.find(A1.ProductID)==null)

4

=A2.(if(r=A1.find(A2.ProductID),r,~) )

5

=(A4|A3).run(ProductNum=#)

6

=file("Product.btx").export@b(A5)

A1 读新维表,添加ProductNum字段,设置主键索引

A2 从集文件中读出原Product维表,设置主键

A3 算出新维表中的新增记录

A4 原维表里的记录如果在新维表中存在,则用新的;如果不存在,则保留原维表的记录

A5 A4A3合并起来,设置序号,因为A4没有改变原维表的顺序,所以原维表的序号事实上没变

A6 A5写出到集文件

4. T.ctx文件准备代码,将ProductID改成序号

以存量数据为例:


A

1

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

2

=connect("demo").cursor@x("select * from T order by Time")

3

=A2.run(ProductID=Product.find(A2.ProductID).ProductNum)

4

=file("T.ctx").create@y(#Time,UserID,EventTypeID, ProductID, Quantity)

5

=A4.append(A3)

6

>A4.close()

A1 读取Product维表至内存,设置主键索引

A2 读取T表的数据时按时间排序

A3 T表的ProductID字段值变成其在维表中的序号

A4 创建组表

A5 T表的数据写入组表文件

增量数据类似

5. 针对转换过的数据再做一次完整的统计过程,全部都用序号

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


A

1

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

2

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

3

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

4

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

5

=file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3))

6

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

7

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

A1-A3 不用设置主键

A6 改用序号关联

运行结果:

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