虚表

阅读(476) 标签: 组表, 虚表, 实表,

组表中的数据除了直接访问来使用之外,还可以通过定义虚表来使用,虚表并非事实表,而是通过定义的情况从组表中获取数据的。

虚表的基本使用

为了说明虚表的使用,下面先生成三个组表,一个是员工表,包括员工ID,部门ID,性别,是否已婚,以及姓名,其中性别与是否已婚再合并用一个字段Bools存储;另两个是订单表,包括销售员ID,订单编码,签单时间以及订单金额,生成数据时使用的代码如下:

 

A

B

1

=demo.query("select NAME, SURNAME, GENDER, STATE from employee")

 

2

=A1.select(GENDER=="M")

=A1\A2

3

=A2.(NAME).id()

=A3.len()

4

=B2.(NAME).id()

=A4.len()

5

=A1.(SURNAME).id()

=A5.len()

6

=A1.(STATE).id()

=A6.len()

7

[Sales,Technology,R&D,Financial,Admin]

[0,0.5,0.75,0.9,0.97,1]

8

=to(1000).new(#:EID, B7.pseg(rand()):DeptID, if(rand()<0.5,0,1):Gender, if(rand()>0.8,1,0):Married, if(Gender==0,A3(rand(B3)+1), A4(rand(B4)+1))/" "/A5(rand(B5)+1):Name, bits(Gender, Married):Bools )

 

9

=A8.select(DeptID==1)

=A9.len()

10

2020-01-01

2021-01-01

11

=periods@x(A10,B10)

=periods@x(B10,elapse@y(B10,1))

12

=A11.((a=string(~,"yyMMdd"), to(rand(100)+ 10).new(A9(if(rand()>0.9, rand(B9)+1, rand(B9-20)+ 21)).EID:SID, a/string(#, "0000"):OID, datetime(A11.~, time(rand(8)+8, rand(60), 0)):OTime, rand(100)*10+200:Amount))).conj().sort(SID, OID)

 

13

=B11.((a=string(~,"yyMMdd"), to(rand(100)+ 10).new(A9(if(rand()>0.9, rand(B9)+1, rand(B9-20)+ 21)).EID:SID, a/string(#, "0000"):OID, datetime(B11.~, time(rand(8)+8, rand(60), 0)):OTime, rand(100)*10+200:Amount))).conj().sort(SID, OID)

 

14

=file("D:/file/pseudo/emps.ctx")

=A14.create(#EID,DeptID,Gender, Married, Name, Bools)

15

 

>B14.append@i(A8.cursor())

16

=file("D:/file/pseudo/1.orders.ctx")

=file("D:/file/pseudo/2.orders.ctx")

17

=A16.create(SID, #OID, OTime, Amount)

>A17.append(A12.cursor())

18

=B16.create(SID, #OID, OTime, Amount)

>A18.append(A13.cursor())

19

>B14.close()

 

20

>A17.close()

>A18.close()

这里准备的数据量并不太大,在Gender字段中,0表示男性,1表示女性;Married字段中,0表示未婚,1表示已婚。A8中得到的员工表数据如下:

A12A13中构建的20202021年度的订单表数据如下:

上面的3个表数据分别存储在pseudo路径下的emps.ctx, 1.orders.ctx2.orders.ctx3个组表文件中,其中后两个构成复组表。将以它们为例讲解如何定义及使用虚表。在使用前,可以将它们备份,以防止在后续的测试使用中数据被修改。

 

 

A

B

C

1

=create(file).record(["pseudo/emps.ctx"])

=pseudo(A1)

=B1.cursor().fetch@x(100)

2

=create(file, zone).record(["pseudo/orders.ctx", [1,2]])

=pseudo(A2)

=B2.import()

虚表的定义记录是一个有指定结构的序表记录,最简单的虚表定义记录必须有file字段来定义虚表的数据来源。如上面例子中,A1中的虚表定义记录如下:

虚表的数据来源可以是组表文件,也可以是集文件。

使用这个记录,可以用pseudo(pd)来产生虚表定义,其中pd即为虚表定义记录。B1中即为产生的虚表定义:

虚表定义的使用和组表类似,可以用T.cursor()产生游标来使用,或者用T.import()直接读取全部数据。C1中用游标读取前100条数据,结果如下:

除了使用单一的数据文件构成虚表,也可以使用复组表文件构成虚表。如A2中的虚表定义记录如下:

C2从虚表中获取到的数据如下:

可以看到,使用复组表文件时,数据会按照各个分表的顺序,顺次获取。

 

使用虚表时,可以用delete/update/append等函数删除/更新/添加表中的记录,数据变动时会直接修改对应的组表文件,如:

 

A

B

1

=create(file).record(["pseudo/emps.ctx"])

=pseudo(A1)

2

=B1.select(DeptID==2 && EID<10)

=A2.import()

3

>B2.run(DeptID=0)

=B1.update(B2)

4

=B1.import()

=A2.import()

5

>B1.delete(B2)

=B1.import()

6

>B2.run(DeptID=2, EID=EID+1000)

=B1.append(B2.cursor())

7

=B1.import()

 

虚表可以类似组表一样,用T.select()函数筛选其中的数据,A2中选出DeptID2EID小于10的员工数据,B2中用T.import()读取如下:

A3中把这些员工记录的DeptID字段改为0,并在B3中用T.update(P)函数将修改后的排列P中的记录更新到虚表中。使用delete/update/append等函数对虚表进行维护操作时,虚表对应的组表必须定义维,对虚表的维护操作都是针对组表的维执行的。更新后,A4B4中得到的数据如下:

可以看到,对应的记录已经被修改了。修改后的数据不满足B2中的筛选条件,因此B4中获取的排列中没有记录。

A5中用T.delete(P)函数,在虚表T中将排列P中的记录删除,删除时也是根据组表的维执行的,执行后,在B5中从虚表中获取数据如下:

经过对比可以看到,B2中的2条和第8条记录已经被删除了。

A6中将B2中结果中两位员工的DeptID改回原值,同时把EID增加1000,并用T.append(cs)函数用虚表构建游标添加到虚表中,A7中结果如下:

可以看到,记录会添加在组表的最后面。如果虚表中的数据来源于多个组表文件,添加记录时只会添加在最后一个文件中。如果未修改新增记录中EID的值,则无法保持数据所在组表中的维有序,会使得虚表无法再次更新。

 

除了组表,也可以用集文件生成虚表访问,使用集文件生成虚表时,使用方法和用组表生成虚表时完全一致,如:

 

A

B

1

=create(file).record(["D:/files/txt/PersonnelInfo.btx "])

=pseudo(A1)

2

=B1.cursor().fetch@x(100)

 

3

=B1.select(City=="Columbus" && ID<500)

=A3.import()

A2中用游标访问虚表,结果如下:

A3中从虚表中选择出Columbu市且编号在500以内的员工,B3结果如下:

 

虚表的归并

数据在复组表文件中,常常是基于不同用户去存储和使用的,如上面的销售记录是分为不同销售员存储的,在这种情况下,在分组处理时会按照首字段处理,如:

 

A

1

=create(file,zone).record(["pseudo/orders.ctx", [1,2]])

2

=pseudo(A1)

3

=A2.import()

4

=A2.group(year(OTime)).import()

5

=A4.new(SID,year(OTime):Year,sum(Amount):Total)

6

=A2.groups(SID, year(OTime):Year;sum(Amount):Total)

A1中的虚表定义记录如下:

数据文件中取数时会按照各个分表依次获取,A3中获取到数据如下,这个其实在上一节中已经展示过了:

 

A4中按照订单年份执行分组,结果如下:

可以看到,分组时会按指定字段或者表达式执行处理。为了更直观地理解,根据分组后情况在A5中统计销售总额,结果如下:

也可以用T.groups()函数执行分组汇总计算,注意使用了SID和订单年份两层分组, A6中结果如下:

 

在复组表中,数据会分别存储到各分表中,而存储的依据往往是根据某个时间类型的字段,各个分表分别存储某个时间段的数据。这样的时间字段称为分列字段,可以用date字段设置,这个字段要求在使用复组表文件时,对各个分表有序,但并不要求在单个数据文件中有序,在建立虚表时会记录每个文件的date字段区间,查询或者过滤时自动定位到所需文件中处理。如:

 

A

1

=create(file,zone,user,date).record(["pseudo/orders.ctx", [1,2],"SID","OTime"])

2

=pseudo(A1)

3

=A2.import()

4

=A2.group(year(OTime)).import()

5

=A4.new(SID,year(OTime):Year,sum(Amount):Total)

6

=A2.select(OTime>date(2020,12,20) && OTime<date(2021,1,10))

7

=A6.import()

A1中虚表定义记录如下:

定义了date字段OTime后,虚表会明确知道数据根据这个分列字段存储在各个分表中,此时再从虚表中获取数据时,数据将会自动按照首字段执行归并,将各个分表中的数据合并在一起返回。此时A3从虚表中查询到的结果如下:

从结果中可以看到,同一个销售员不同年份的销售顺序归并到了一起,即使它们本来存储在复组表的不同分表中。

在虚表存储的数据中,特别是使用复组表时,首字段常常是用来表示销售员、客户、账户编码等等,这样的字段称为用户字段,表中数据也会是按不同的用户字段存储的信息。虚表在处理复组表数据时,在定义了分列字段的情况下,会将首字段设定为默认的用户字段,并在查询数据时按照首字段执行归并处理。

A4中按照订单年份执行分组,结果如下:

定义了分列字段后,虚表再执行分组时,情况也产生了变化,同样会先根据首字段分组,再按照指定字段或者表达式分组。根据分组后情况在A5中统计销售总额,结果如下:

 

A6中,查询日期在20201220日起至2021110日前的订单,A7中获取数据如下:

数据从各个分表中获取后,如果根据分列字段执行了筛选,同样会按照首字段归并。在通过分列字段执行筛选的情况下,程序可以智能判断需要从哪些分表中获取数据并按照首字段执行归并。

 

从上面的例子可以看到,在复组表中获取数据时,会根据首字段来在各个分表中分段取数,再执行归并后得到结果。但是,按首字段分段时,有些情况下数据的分布可能不平均,或者每个分组的数据量较少。如上面的例子中,前20个销售员的订单就会比较少,其实其他各位销售人员的数据也不是很多,在取数时就可能由于分段数据较少或者分段数太多,在归并时频繁在各个分表中切换获取数据,造成在各个分表中取数的效率降低。为此,可以重新构造数据,将首字段设为更的分组,使取数时效率更高,如:

 

A

B

1

=demo.query("select distinct(NAME) from CITIES")

=A1.(NAME)

2

=create(file,zone).record(["pseudo/orders.ctx", [1]])

=pseudo(A2).import()

3

=create(file,zone).record(["pseudo/orders.ctx", [2]])

=pseudo(A3).import()

4

=B1.len()

=1000.(B1(rand(A4)+1))

5

=B2.new(B4(SID):City, SID, OID, OTime, Amount)

=B3.new(B4(SID):City, SID, OID, OTime, Amount)

6

=A5.sort(City)

=B5.sort(City)

7

=file("pseudo/1.orders2.ctx")

=file("pseudo/2.orders2.ctx")

8

=A7.create(City, SID, #OID, OTime, Amount)

>A8.append(A6.cursor())

9

=B7.create(City, SID, #OID, OTime, Amount)

>A9.append(B6.cursor())

原有的订单数据中是没有城市数据(City)的,上面的测试数据中,为销售人员增加了City数据,并将订单数据按照City, SIDOID的顺序依次做了排序,然后将数据存入了复组表orders2.ctx中。在实际使用中,这样用来初步分组的首字段可能是人员所在城市,所在部门,或者是交易的类型编码,订单的支付银行等等之类信息。整理数据时,记录需要按照首字段、用户字段的顺序排序。

下面看一下使用orders2.ctx作为虚表的查询情况:

 

A

B

1

=create(file,zone, date).record(["pseudo/orders2.ctx", [1,2], "OTime"])

=pseudo(A1).import()

2

=create(file,zone, user, date).record(["pseudo/orders2.ctx",  [1,2] , "SID", "OTime"])

=pseudo(A2).import()

A1中的虚表从复组表中取数,数据将按首字段归并,B1中结果如下:

此时取出的数据,会按照复组表orders2.ctx中的首字段City归并,同样会把两年的数据归并在一起,但是并不会按照销售员执行归并处理了。

在这种情况下,可以另外指定用户字段user,如A2中定义虚表时,指定了SID作为用户字段user,此时B2中得到的结果如下:

从结果中可以看到,在定义了用户字段的情况下,数据会首先按照首字段归并,而首字段相同的记录会按照指定的用户字段再完成排序。

用户定义字段

除了使用原始数据表中的字段,还可以在虚表定义记录中,添加column字段,定义一些用户定义字段,如:

 

A

1

[Sales,Technology,R&D,Financial,Admin]

2

=create(name,pseudo,enum).record(["DeptID","Dept",A1])

3

=create(name,bits).record(["Bools",["IfMarried","IfLady"]])

4

=create(file,column).record(["pseudo/emps.ctx",A2|A3])

5

=pseudo(A4)

6

=A5.select(Dept=="Sales").import()

7

=A5.import(EID, Name, DeptID, Dept)

8

=A5.select(!IfLady && IfMarried).import()

9

=A5.import(EID, Name, Gender, IfLady,Married,IfMarried)

这个例子中定义了两个用户定义字段,A2中是Dept字段:

column定义记录中,name字段是字段的名称,如果是原始数据表中的字段,称为真字段,除此之外还可能是其它列生成的伪字段。如果需要把真字段DeptID转换为对应的部门名称,这样的伪字段是枚举伪字段,在column记录中用pseudo字段定义枚举伪字段的名称,部门转换时需要把对应的需要转换为序列中的值,转换时对应的取值序列需要在enum列中设置,转换时将把对应的编号转换为对应的值,位置1开始

A3中定义了另一个用户定义字段,原始数据表中,Bools字段存储了2个二值型字段的数据,性别和是否已婚。A3中字段定义记录如下:

定义时需要从中读出各个位对应的二值字段,这里Bools对应的伪字段称为二值维度伪字段,二值维度伪字段的名称在column记录中用bits字段定义。伪字段的名称不能和原始字段重名,这里使用IfMarriedIfLady,定义时按照Bools从低位到高位对应的字段来设置,各个字段的值都是布尔值true或者false,一个二值维度伪字段最多可以存储16个二值字段。

A4中定义虚表,设置了数据文件file和字段定义column,如下:

A5中使用这个定义记录生成虚表,在这个虚表中,就可以使用定义的column了。如A6中使用枚举伪字段Dept来筛选出销售部员工如下:

使用T.import()查看虚表中的数据时,并不能看到伪字段中的数据,只能看到初始字段,但是可以很明显看到根据伪字段执行的筛选成功执行了,这些员工对应的DeptID都是1

如果想查看伪字段的数据,需要在import中直接指明字段,如A7中查询结果如下:

从这个结果中能够更明确的看到枚举伪字段与对应真字段数据间的对应关系。

A8中使用二值维度伪字段执行筛选,选出已婚男员工的数据,结果如下:

同样,二值维度伪字段查询时也必须指明列名,如A9中结果如下:

虚表使用用户定义字段时,同样可以用delete/update/append等函数对虚表进行维护操作,如:

 

A

1

[Sales,Technology,R&D,Financial,Admin]

2

=create(name,pseudo,enum).record(["DeptID","Dept",A1])

3

=create(name,bits).record(["Bools",["IfMarried","IfLady"]])

4

=create(file,column).record(["pseudo/emps.ctx",A2|A3])

5

=pseudo(A4)

6

=A5.select(EID<5)

7

=A6.select(Dept=="Sales").import(EID,Name,Dept,IfMarried,IfLady)

8

>A7.run(Dept="Technology", IfLady=!IfLady)

9

>A5.update(A7)

10

=A5.import(EID,Name,Dept,IfMarried,IfLady)

11

=A5.import()

虚表中使用了枚举伪字段和二值维度伪字段,在A7中筛选出EID小于5的员工资料如下:

A8中修改这些员工的部门和性别,并在A8中执行对虚表的更新。更新执行后,在A10中读取更新后的虚表数据,注意前3条记录中Dept字段和IfLady字段都被修改了:

A11中,可以看到虚表使用的组表文件中,数据实际被修改的情况:

可以看到,在修改虚表中伪字段的值时,会自动根据它们的值,倒算出实际对应的数据,更新到组表中。由于更新时使用的字段是Bools,而并没有作为参考的Gender/Married等字段,因此这两个字段在更新后变为了空值。

 

除了枚举伪字段,原始数据表中的字段还可能对应其它表中的记录,即外键列。如:

 

A

1

=create(file).record(["pseudo/emps.ctx"])

2

=pseudo(A1).select(DeptID==1).memory().keys(EID)

3

=create(name,dim,fkey).record(["SID",A2,["SID"]])

4

=create(name,exp).record(["OYear","year(OTime)"])

5

=create(file,zone,column).record(["pseudo/orders.ctx",[1],A3|A4])

6

=pseudo(A5)

7

=A6.select(left(SID.Name, 4)=="Jack").import(SID, OTime, OYear, Amount)

8

=A7.new(SID.EID:SID, SID.Name:Name, OTime, OYear, Amount)

9

=A6.import(SID.EID:SID, SID.Name:Name, OTime, OYear, Amount)

A2中从员工虚表中获取销售部的人员数据,并用T.memory()将其加载为内表,并设主键为员工编号EID

A3中添加用户定义字段:

如果伪字段是外键字段,需要设置维表dim和外键字段fkey,本例中真字段就是外键字段,可以省略fkey。这里的维表字段就是A2中的销售人员表,维表除了是虚表加载的内表,也可以是其它内表、序表或者集群内表。此时的name是外键字段的字段名,本例中使用原字段名SID,也可以根据需要定义为新的字段名,如Seller

A4中添加用户定义字段:

这里定义的字段名OYear并不存在与复组表文件中,它是一个普通的伪字段,可以在column记录中用exp字段定义表达式,由真字段计算获得。

A5中的虚表定义记录如下:

A7中从虚表中,获取名为Jack的员工的销售记录如下:

特别的,可以看到结果中OYear的值,同样查询伪字段时需要指明字段名。另外,结果中的SID已经被转换为了维表中对应的记录,可以双击查看对应记录,为了便于查看,在A8中用查询结果生成了简要信息表如下:

虚表中的外键字段及伪字段除了用来筛选,可以在import函数中指明查询,如A9中从维表中获取了销售员编码和姓名,而订单时间和金额来源于主表,结果如下:

 

在虚表的外键列与维表做关联时,维表也可以使用时间键,如下面的例子:

 

A

B

1

=demo.query("select distinct(NAME) from CITIES")

=A1.(NAME)

2

=create(City, FactoryID, PTime)

=8.(elapse@m(date(2020,1,1), (~-1)*3)+rand(3))

3

>B1.run(B2.(if(#<2 || rand()>0.5, A2.insert(0, B1.~, rand(5)+1, B2.~))))

>file("pseudo/FacSupply.btx").export@b(A2)

4

=create(name,dim,fkey,tkey).record(["Product",A2, ["City"], "OTime"])

>A2.keys@t(City;PTime)

5

=create(file,zone,user,date,column).record(["pseudo/orders2.ctx",[1,2],"SID","OTime", A4])

=pseudo(A5)

6

=A5.select(Amount>1100)

=A6.import()

7

=A6. import(City, SID, OTime, Amount, Product.FactoryID:Factory, Product.PTime:PTime)

 

B1中生成各个城市名构成的序列,A2中新建产品供应表,在A3中准备测试数据,由5个工厂随机供货,在每季度初可能向某个城市供货,而202011日所有城市都需要完成初始供货。A3中数据填入后,A2中供货记录如下:

为了便于使用这个文件,在B3中将其存储为集文件FacSupply.btx。另外,在B4中设置了序表A2的主键,其中City为基本键,PTime为时间键。

A4中添加用户定义字段:

这里设置外键字段Product,使用的维表为A2中的序表,由于这个序表使用了时间键,因此除了用fkey设置维表中普通键对应的虚表字段,还需要用tkey设置维表的时间键对应的虚表字段。

A5中的虚表定义记录如下:

B5中使用这个虚表定义建立虚表后,在A6中从中查询出Amount大于1100的销售记录,B6取出数据如下:

在取出的数据中,Product是每批产品对应的供货出厂记录。

为了更明确外键字段的关联情况,在A7中指定字段取出了每笔交易中,产品的工厂序号和供货时间,如下:

从表中能够看到,通过时间键的关联,这里选出的各笔订单能够获得对应的产品来源。

 

除了使用文件构成虚表,还可以用内存中的序表/内表/集群内表等构成虚表,此时定义虚表结构时不使用file指定文件名,而是用var字段来指定内存表存储的变量名,如:

 

A

1

>tab=demo.query("select * from CITIES")

2

=create(var).record(["tab"])

3

=pseudo(A2)

4

=A3.select(STATEID==3)

5

=A4.import()

A1中从数据库中获取CITIES表并将序表存储到变量tabA2使用内存表来定义虚表。A4中从虚表中获取STATEID3的城市资料,A5中得到的结果如下:

使用内存表定义虚表时,虚表的使用与其它虚表是没有区别的。