用户行为分析系列实践 12 – 使用虚表

目标任务

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

Time

UserID

EventType

OS

Browser

ProductID

f1

f2

f3

f4

f5

2022/6/1 10:20

1072755

Search

Android

IE

100001

true

false

false

true

false

2022/6/1 12:12

1078030

Browse

IOS

Safari

100002

false

false

true

true

true

2022/6/1 12:36

1005093

Submit

Android

Chrome

100003

true

true

true

false

false

2022/6/1 13:21

1048655

Login

Windows

Chrome


false

false

true

true

true

2022/6/1 14:46

1037824

Logout

Android

Edge


false

false

false

true

true

2022/6/1 15:19

1049626

AddtoCart

Windows

Edge

100004

true

true

false

true

false

2022/6/1 16:00

1009296

Submit

IOS

Firefox

100005

false

true

false

false

true

2022/6/1 16:39

1070713

Browse

IOS

Sogou

100006

true

true

true

false

false

2022/6/1 17:40

1090884

Search

Windows

IE

100007

true

false

true

true

false

T表字段说明:

字段名

数据类型

字段含义

Time

日期时间

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

UserID

字符串

用户ID

EventType

字符串

事件类型,取值为Login,Browse,Search,AddtoCart,Submit,Logout

OS

字符串

操作系统,取值为Android,IOS,Windows, ,Unknown

Browser

字符串

浏览器,取值为IE,Safari,Edge,Firefox,Chrome,Sogou,Unknown

ProductID

字符串

产品ID,取值为产品维表中的ProductID字段

字符串

更多其它取值为枚举值的字段

f1

布尔值

是否异地发生,取值为真和假

f2

布尔值

是否惯用设备,取值为真和假

f3

布尔值

是否惯用浏览器,取值为真和假

f4

布尔值

是否是手机,取值为真和假

f5

布尔值

是否首次操作,取值为真和假

布尔值

更多其它取值为真和假的字段

根据前面的章节(https://c.raqsoft.com.cn/article/1657716176800)介绍,把枚举字段和标签字段转换为对应的序号和位,转换后的用户事件组表T.ctx结构和部分数据示例如下:

Time

UserID

EventType

OS

Browser

ProductID

b1

2022/6/1 10:20

1072755

3

1

1

100001

36864

2022/6/1 12:12

1078030

2

2

2

100002

14336

2022/6/1 12:36

1005093

5

1

5

100003

57344

2022/6/1 13:21

1048655

1

3

5


14336

2022/6/1 14:46

1037824

6

1

3


6144

2022/6/1 15:19

1049626

4

3

3

100004

53248

2022/6/1 16:00

1009296

5

2

4

100005

18432

2022/6/1 16:39

1070713

2

2

6

100006

57344

2022/6/1 17:40

1090884

3

3

1

100007

45056

转换后的T表字段说明:

字段名

数据类型

字段含义

Time

日期时间

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

UserID

字符串

用户ID

EventType

整数

事件类型,取值为枚举序列中的序号

OS

整数

操作系统,取值为枚举序列中的序号

Browser

整数

浏览器,取值为枚举序列中的序号

ProductID

字符串

商品ID,取值为维表中的ProductID字段

b1

整数

整数字段,用位存储二值字段,其中的前五位分别对应是否异地发生,是否惯用设备,是否惯用浏览器,是否是手机,是否首次操作

EventType枚举序列的序号和对应的文字说明:

1 Login

2 Browse

3 Search

4 AddtoCart

5 Submit

6 Logout

OS枚举序列的序号和对应的文字说明:

1 Android

2 IOS

3 Windows

4 Unknown

Browser枚举序列的序号和对应的文字说明:

1 IE

2 Safari

3 Edge

4 Firefox

5 Chrome

6 Sogou

7 Unknown

维表Product:

ProductID

ProductName

Unit

Price

ProductType

100001

Apple

Pound

5.5

Fruits

100002

Tissue

Packs

16

Home&Personalcare

100003

Beef

Pound

35

Meat

100004

Wine

Bottles

120

Beverage

100005

Pork

Pound

25

Meat

100006

Bread

Packs

10

Bakery

100007

Juice

Bottles

6

Beverage

维表Product字段说明:

字段名

数据类型

字段含义

ProductID

字符串

产品ID

ProductName

字符串

产品名称

Unit

字符串

销售单位

Price

数值

单价

ProductType

整数

产品类别

表间关系说明图:


计算任务:

统计指定时间段内,产品类别为Home&Personalcare,本地使用安卓/苹果手机,使用Safari/ Edge/ Chrome,且非首次操作的用户,每个月在每种事件类型下的发生次数,以及去重用户数。

实践技能

关于虚表的相关知识可参考:

SPL 虚表的数据类型优化

使用虚表实现计算列和外键的预定义,以及枚举维度和标签维度的简化书写:

1、 定义计算列Month,计算当前事件发生时间所在的月份

2、 定义EventTypeNameOSNameBrowserName伪字段,存储这些字段的序号和名称的对应关系

3、 ProductID字段上定义和对应维表的外键关联关系

4、 b1上定义每一位代表的字段名

定义完虚表后,可以利用虚表实现数据转储以及汇总统计,将极大地减少统计代码量。

示例代码

1、 定义虚表


A

1

=T("Product.btx").keys@i(ProductID)

2

=file("T.ctx").create(#Time,UserID,EventType,OS,Browser,ProductID,……,b1,……)

3

=A2.close()

4

=[{file:"T.ctx",

column:[

{name:"Month",exp:"month@y(Time)"},

{name:"EventType",pseudo:"EventTypeName",enum:["Login","Browse","Search","AddtoCart","Submit","Logout"]},

{name:"OS",pseudo:"OSName",enum:["Android","IOS","Windows","Unknown"]},

{name:"Browser",pseudo:"BrowserName",enum:["IE","Safari","Edge","Firefox","Chrome","Sogou","Unknown"]},

{name:"b1",bits:["f1","f2","f3","f4","f5",…….]},

{name:"ProductID",dim:A1}]

}]

5

=pseudo(A4)

A1 读入维表Product并设置主键和索引

A2 创建序号化及位维转化后的组表结构,写入空文件T.ctx(后续利用虚表直接将数据库的数据追加到此文件,不用再写序号和位的转换代码)

A3 关闭组表

A4 在组表T.ctx的基础上定义虚表:

{name:"Month",exp:"month@y(Time)"}定义计算列,列名Month,表达式month@y(Time)

{name:"EventType", pseudo:"EventTypeName", enum:[ "Login", "Browse", "Search", "AddtoCart", "Submit", "Logout"]}定义EventType字段值和枚举序列的对应关系,将对应的名称用伪字段EventTypeName表示

{name:"OS",pseudo:"OSName",enum:["Android","IOS","Windows","Unknown"]}定义OS字段值和枚举序列的对应关系,将对应的名称用伪字段OSName表示

{name:"Browser",pseudo:"BrowserName",enum:["IE","Safari","Edge","Firefox","Chrome","Sogou","Unknown"]}定义Browser字段值和枚举序列的对应关系,将对应的名称用伪字段BrowserName表示

{name:"b1",bits:["f1","f2","f3","f4","f5",…….]}定义位维度b1中每一位代表的字段名

{name:"ProductID",dim:A1}在字段ProductID上定义和维表A1的关联关系

A5 生成虚表

可以将虚表的定义串保存起来,后续每次使用时直接读取,进一步减少代码量。

2、 利用虚表进行数据的转储,SPL会自动根据虚表定义,将数据转换成对应的序号和位,存入T表中


A

/前面定义虚表的代码

6

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

7

=A5.append@i(A6)

A6 连接数据库,读取T表数据并产生游标

A7 将游标数据读出追加到虚表中

3、 利用虚表做统计


A

/前面定义虚表的代码

6

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

7

=A5.select(Time>=start && Time<=end && ProductID.ProductType=="Home&Personalcare"&& ["Safari","Edge","Chrome"].pos(BrowserName) && ["Android","IOS"].pos(OSName) && ! f1 && f4 && !f5).groups(Month,EventTypeName; count(1):Num, icount(UserID):iNum)

A7 利用虚表做汇总统计,其上定义的伪字段可以当成普通字段使用,不必再考虑存储和计算机制,将虚表当成简单的普通表即可

运行结果:

Month

EventTypeName

Num

iNum

202203

AddtoCart

307603

29252

202203

Browse

596492

58140

202203

Login

672163

65569

202203

Logout

672163

65569

202203

Search

491317

42919

202203

Submit

144552

13901

202204

AddtoCart

615222

58484

202204

Browse

1192970

116265

202204

Login

1344323

131123

202204

Logout

1344323

131123

202204

Search

982637

85843

202204

Submit

289112

27799

202205

AddtoCart

615214

58484

202205

Browse

1192976

116262

202205

Login

1344339

131133

202205

Logout

1344339

131133

202205

Search

982633

85848

202205

Submit

289108

27788

202206

AddtoCart

307635

29256

202206

Browse

596463

58124

202206

Login

672175

65575

202206

Logout

672175

65575

202206

Search

491344

42929

202206

Submit

144573

13887