用户行为分析系列实践 7 - 维表过滤

目标任务

用户事件表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

Price

ProductTypeID

1

Apple

Pound

5.5

1

2

Tissue

Packs

16

2

3

Beef

Pound

35

3

4

Wine

Bottles

120

4

5

Pork

Pound

25

3

6

Bread

Packs

10

5

7

Juice

Bottles

6

4

维表Product字段说明:

字段名

数据类型

字段含义

ProductID

字符串

产品ID

ProductName

字符串

产品名称

Unit

字符串

销售单位

Price

数值

单价

ProductTypeID

整数

产品类别ID

计算任务:

统计指定时间段内产品类别ID123的每种产品的销售数量

实践技能

1、 维表过滤后再关联

先对维表过滤,然后再和事实表关联,关联不上的事实表记录不必再执行对维表的条件判断,判断次数会减少很多。

2、 游标中关联

在游标中和过滤后的维表关联,关联不上的事实表记录不再生成,进一步减少生成记录的时间。

3、 复用索引

对维表过滤时复用原维表索引。如果过滤后的结果集还较大,即过滤掉的记录较少,可以减少重建索引的时间。

4、 对位序列

对序号化的维表过滤时,可以使用更高效的对位序列。生成一个同长度的序表,其成员值为对应维表记录是否满足过滤条件。和事实表关联时,直接用序号定位取出匹配与否的结果,减少关联和比对的时间。

示例代码

假设T.ctxProduct.btx已经按照前面章节介绍的办法生成,其中T.ctxTime排序,Product.btx按照ProductID排序:

1、 对维表Product过滤,然后再和T关联,关联时删除关联不上的T表记录


A

1

>Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID)

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>=start && Time<=end)

4

=A3.switch@i(ProductID,Product:ProductID)

5

=A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity)

A1 读取维表并过滤,然后设置主键索引

A4 事实表和维表关联,@i选项表示关联不上的记录将被删除

2、 1中的关联移到产生游标的语句中,关联不上的记录不生成


A

1

>Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID)

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>=start && Time<=end,ProductID:Product)

4

=A3.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity)

A3 把和维表的关联移到产生游标的语句中,关联不上的记录不生成

3、 对维表Product过滤时复用索引


A

1

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

2

=Product.select@i([1,2,3].pos(ProductTypeID)!=null)

3

>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd")

4

=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end,ProductID:A2)

5

=A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity)

A1 读取维表并设置主键索引

A2 对维表过滤时使用@i选项将复用索引

索引复用并不会总是更快,因为要把过滤掉的记录从索引表删除,如果过滤掉的记录数很多(剩下的较少),这个动作也不会很快。而剩下记录较少时,重建索引很可能更快。具体采用哪种方式,要根据实际情况决定。

4、 Product维表的主键是序号,可以使用对位序列


A

1

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

2

=Product.([1,2,3].pos(ProductTypeID))

3

>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd")

4

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

5

=A4.select(A2(ProductID))

6

=A5.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity)

A1 读出Product维表,不再设置主键索引了

A2 Product表产生一个对位序列,值为当前Product记录是否满足过滤条件

A5 直接用T表的ProductID作为序号定位A2,即可得到当前记录是否满足过滤条件

5、 把利用对位序列过滤的操作移到游标产生的语句中


A

1

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

2

=Product.([1,2,3].pos(ProductTypeID))

3

>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd")

4

=file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end && A2(ProductID))

5

=A4.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity)

A4 把利用对位序列的过滤移到游标产生中,不满足过滤条件的记录不再生成

运行结果:

ProductID

ProductName

Quantity

1

Apple

206938

2

Tissue

463188

3

Beef

94378

5

Pork

217504