SPL 文件上的常规运算

SPL 有集文件组表两种文件格式,后缀分别是 btx 和 ctx。本文介绍 SPL 在这两种文件上的常规计算,类似于 SQL 可以表达的简单计算(不包含子查询)。

这两种文件相关的基本概念和生成方法参见:新手如何使用 SPL 存储

样例数据

这里采用 TPCH 中的部分数据表和字段。

orders 订单表(ctx),用到字段 O_ORDERKEY 订单号,主键;O_CUSTKEY 客户号;O_TOTALPRICE 订单金额;O_ORDERDATE 订单日期。

lineitem 在线商品表(订单明细表)(ctx),用到的字段 L_ORDERKEY 订单号,主键;L_LINENUMBER 在线商品号,主键;L_QUANTITY 数量;L_DISCOUNT 折扣;L_EXTENDEDPRICE 扩展价格。

customer 客户表(ctx),字段有 C_CUSTKEY 客户号,主键;C_NAME 姓名;C_NATIONKEY 国家号;C_ACCTBAL 客户数值。

nation 国家表(btx),字段有 N_NATIONKEY 国家号,主键;N_NAME 国家名称;N_REGIONKEY 区域号;N_COMMENT 备注。

表间关系说明:

订单表通过订单号关联在线商品表,订单表通过客户号关联客户表。

客户表通过国家号关联国家表。

1. 过滤

从文件中筛选出满足条件的数据。

示例:从国家、客户、在线商品、订单中按照条件过滤数据。


A

1

=T("nation.btx").select(N_NAME=="CHINA" && like(N_COMMENT,"*express*"))

2

=T("customer.ctx").select(C_ACCTBAL>=1000)

3

=file("lineitem.ctx").open().cursor@m(L_ORDERKEY,L_EXTENDEDPRICE,L_DISCOUNT).select(L_EXTENDEDPRICE*L_DISCOUNT>=10000)

4

=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;between(O_ORDERDATE,date(1996,1,1):date(1996,1,31)))

A1 将集文件数据读取到内存中,然后按照条件过滤。

A2将组表数据读取到内存中,然后按照条件过滤。

A3假设组表 lineitem.ctx 数据量较大,不能全部装入内存,用游标方式分批读入内存,读取一批,按照条件过滤一批。

由于是先读取后过滤,所以过滤条件的字段也要出现在字段列表中,比如这里的L_EXTENDEDPRICE 和 L_DISCOUNT。

游标加上 @m 是指多线程并行读取、计算,称为多路游标。下面的例子中,如无特殊说明,游标都可以直接加 @m 变成多路游标。去掉 @m 代表普通游标,进行单线程读取计算。

A4假设组表 orders.ctx 数据量较大,不能全部装入内存。和 A3 不同,这里用带过滤条件的游标分批读取,边读取边过滤计算,性能更好。这种方法称为游标前过滤

由于是边读取边过滤,所以过滤条件可以不出现在字段列表中,比如这里的O_ORDERDAT。

A3 和 A4 返回的是游标,并不是结果集,一般还要进行后续计算才有意义。这种情况在 SPL 中很常见。

说明:

1、一般情况下推荐 A4 这种游标前过滤。A3 的方式用于分组后过滤、遍历复用等不能用游标前过滤的场景。

2、从例子可以看到,无论是集文件还是组表,数据全读入内存后形成了序表,后续代码写法相同。后面全内存的情况,就不再分别给出两种文件的例子。

2. 汇总

对文件中的数据进行汇总。

示例:汇总国家总数、客户总数和平均值、订单总数、在线商品折后价和总价格。


A

1

=T("nation.btx").count(1)

2

=T("customer.ctx").groups(;count(1),avg(C_ACCTBAL))

3

=file("orders.ctx").open().cursor(O_ORDERKEY).total(count(1))

4

=file("lineitem.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT).total(sum(L_EXTENDEDPRICE*L_DISCOUNT),max(L_EXTENDEDPRICE))

A1:将文件读入内存,然后汇总记录数。

A2:将文件读入内存,然后汇总记录数和平均值。groups 可以计算多个汇总值,简化书写。

A3:用游标方式汇总组表记录数。只用一个主键字段就可以了,尽量减少取出的字段,有利于性能。

A4:用游标方式汇总组表,求平均值和最大值。同样只取出必须用到的字段。

过滤和汇总可以一起使用,实现过滤后汇总。

示例:

客户先按照客户数值过滤,再计算客户数值平均值。

在线商品先过滤,再汇总求和、求最大值。


A

1

=T("customer.ctx").select(C_ACCTBAL>=1000)

2

=A1.avg(C_ACCTBAL)

3

=file("lineitem.ctx").open().cursor(L_ORDERKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_EXTENDEDPRICE*L_DISCOUNT>=10000)

4

=A3.total(sum(L_EXTENDEDPRICE*L_DISCOUNT),max(L_EXTENDEDPRICE))

A1:客户数据读入内存,然后按照条件过滤。

A2:过滤后求平均值。

A3:建立在线商品表,并定义游标前过滤。

A4:游标计算过滤、汇总求和以及最大值。

3. 跨列计算

用文件数据中的多列计算出新的列,分全内存和游标两种方式。

示例:

国家表将编号和名称连成一个字符串。

在线商品表计算出折后价格。


A

1

=T("nation.btx").derive(N_NATIONKEY/"-"/N_NAME:key&name)

2

=file("lineitem.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT).derive(L_EXTENDEDPRICE*L_DISCOUNT:newPrice)

A1:将文件数据读入内存,再计算出新的列 key&name。

A2:用游标方式计算出新列 newPrice。这里返回的是游标,不会立即计算,需要后续计算才能得到结果。

4. 分组汇总

对文件中的数据按照一些字段分组,每组汇总。

示例:

国家按照区域分组,汇总每个区域的国家数。

订单按照日期分组,汇总每个日期的订单金额、最大金额。

分组汇总分为两种情况:

1、分组汇总后的结果集不大,内存可以装下


A

1

=T("nation.btx").groups(N_REGIONKEY;count(1):nationCount)

2

=file("orders.ctx").open().cursor(O_ORDERDATE,O_TOTALPRICE).groups(O_ORDERDATE;sum(O_TOTALPRICE):all,max(sum(O_TOTALPRICE)):max)

A1:把文件内容装入内存,然后分组汇总。

A2:用游标方式计算分组汇总,groups 结果是序表。

2、分组汇总后的结果很大,内存装不下


A

1

=file("orders.ctx").open().cursor(O_ORDERDATE,O_TOTALPRICE).groupx(O_ORDERDATE;sum(O_TOTALPRICE):all,max(sum(O_TOTALPRICE)):max)

A1:用游标方式计算分组汇总,groupx 结果是游标。groupx 需要利用外存缓存,时间较长。一般用在离线的数据准备上,比如将订单明细数据按照订单号汇总成订单表,另存一份共后续查询。

5. 去重

对文件中的数据去掉重复,相同的值仅保留一个。

示例:

先过滤,再查询国家表所有区域编号,去掉重复。

先过滤出金额在指定区间的订单,再查询订单表所有客户编号,去掉重复。


A

1

=T("nation.btx").select(like(N_NAME,"C*")).id(N_REGIONKEY)

2

=file("orders.ctx").open().cursor(O_CUSTKEY;between(O_TOTALPRICE,1000:5000)).id(O_CUSTKEY)

A1:把文件内容装入内存,然后先过滤再去重。

A2:用游标方式先游标前过滤,再去重。当去重之后的结果集还很大时,用 groupx 函数。写法是:

=file("orders.ctx").open().cursor(O_CUSTKEY;between(O_TOTALPRICE,1000:5000)).groupx(O_CUSTKEY)

序表和游标的 id 函数都允许多字段去重,多个字段用逗号分开。

6. 去重计数

对文件的数据去掉重复,计算不同的值有多少个。

示例:

先过滤出符合条件的国家,再计算国家不重复的区域编号个数。

先过滤出客户号为 2、3、5、8 的订单,再计算订单不重复的客户编号个数。


A

1

=T("nation.btx").select(N_NATIONKEY>10).icount(N_REGIONKEY)

2

=file("orders.ctx").open().cursor(O_CUSTKEY;[2,3,5,8].contain(O_CUSTKEY)).total(icount(O_CUSTKEY))

A1:把文件内容装入内存,然后先过滤再去重计数。

A2:用游标方式,先过滤再去重计数。

7. 组内去重计数

对文件中的数据进行分组后,每组去重计数。

示例:

客户按照国家分组后,求每一组不重复的客户名称个数。

订单按照日期分组后,求每一组不重复的客户个数。


A

1

=T("customer.ctx").groups(C_NATIONKEY;icount(C_NAME))

2

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_ORDERDATE).groups(O_ORDERDATE;icount(O_CUSTKEY))

A1:文件数据装入内存,然后计算分组去重计数。

A2:文件数据用游标方式计算分组去重计数。如果数据量很大,要用 groupx 函数。

8. 排序

对文件中的数据进行升/降序排序。

示例:

将国家数据按照区域升序、名称降序排序。

将订单数据按照日期升序排序。


A

1

=T("nation.btx").sort(N_REGIONKEY,N_NAME:-1)

2

=file("orders.ctx").open().cursor(O_ORDERDATE,O_ORDERKEY).sortx(O_ORDERDATE)

A1:文件数据读入内存后排序,N_NAME:-1 代表降序。

A2:文件数据用游标方式排序,仅支持升序。这种情况一般数据量较大,排序需要利用硬盘缓存,时间较长。一般用在离线的数据准备上,比如将订单数据按照日期有序另存一个组表,做日期查询时性能会更好。

9. 有序分组汇总

如果数据按照分组字段有序,分组时可以只和相邻的记录比较,可以提高分组性能。

示例:

国家数据按照区域排序后,有序分组汇总。

预先将订单按照日期排好序存成组表 orders_order_date.ctx,这里就可以按照日期有序分组汇总。


A

1

=T("nation.btx").sort(N_REGIONKEY)

2

=A1.groups@o(N_REGIONKEY;count(1):c)

3

=file("orders_order_date.ctx").open().cursor(O_ORDERDATE,O_TOTALPRICE)

4

=A3.group@s(O_ORDERDATE;count(1):c,sum(O_TOTALPRICE):s)

A1:国家数据读入内存,按照区域排序。排序计算可以预先做好,另存一个文件。

A2:用有序的数据,使用 groups@o 做有序分组汇总。groups@o 要求数据必须对分组字段有序。

A3:用预先按照日期排好序的订单文件,定义游标。

A4:使用 group 函数对游标做有序分组,返回的仍然是游标。group 要求数据必须对分组字段有序。

10. 有序去重

数据按照去重字段有序,去重时可以只和相邻的记录比较,可以提高去重性能。

示例:

先过滤,再查询国家表所有区域编号,去掉重复。假设国家数据按照区域号有序。

先过滤,再查询订单表所有客户编号,去掉重复。假设订单数据按照客户号有序。


A

1

=T("nation_order_regionkey.btx").select(like(N_NAME,"C*")).id@o(N_REGIONKEY)

2

=file("orders_order_custkey.ctx").open().cursor(O_CUSTKEY;between(O_TOTALPRICE,1000:5000)).id@o(O_CUSTKEY)

A1:把文件内容装入内存,然后先过滤再去重。

A2:用游标方式先游标前过滤,再去重。如果去重后的结果仍然很大,要采用 group@1 的写法:

=file("orders_order_custkey.ctx").open().cursor(O_CUSTKEY;between(O_TOTALPRICE,1000:5000)).group@1(O_CUSTKEY)

返回的仍是游标。group 函数,要求游标数据对去重字段有序。

id@o 函数要求数据按照去重字段有序。

11. 有序去重计数

数据按照去重字段有序,去重时可以只和相邻的记录比较,可以提高去重计数性能。

示例:

计算国家不重复的区域编号个数。假设国家数据按照区域号有序。

计算订单不重复的客户编号个数。假设订单数据按照客户号有序。


A

1

=T("nation.btx").icount@o(N_REGIONKEY)

2

=file("orders.ctx").open().cursor(O_CUSTKEY).total(icount@o(O_CUSTKEY)

A1:把文件内容装入内存,然后先过滤再去重计数。

A2:用游标方式,先过滤再去重计数。

icount@o 函数要求数据按照去重字段有序。

12. 组内有序去重计数

数据按照去重字段有序,去重时可以只和相邻的记录比较,可以提高组内去重计数性能。

示例:

将客户按照国家分组,再计算每组不重复的客户名个数。假设客户数据按照客户名有序。

将订单按照日期分组,再计算每组不重复的客户编号个数。假设订单数据按照客户号有序。


A

1

=T("customer.ctx").groups(C_NATIONKEY;icount@o(C_NAME))

2

=file("orders.ctx").open().cursor(O_CUSTKEY,O_ORDERDATE).groups(O_ORDERDATE;icount@o(O_CUSTKEY))

A1:文件数据装入内存,然后计算分组去重计数。

A2:文件数据用游标方式计算分组去重计数。

如果数据按照分组字段、去重字段有序,比如订单数据对 O_ORDERDATE ,O_CUSTKEY 有序,可以组合使用有序分组和组内有序去重计数。

13. TOP-N

对文件中的数据求前几名、后几名。

示例:

先按照枚举类型条件过滤,再计算客户数值前 3 名。

先按照枚举类型排除部分客户(20,31,55,86),再计算订单金额倒数 10 名。


A

1

=T("customer.ctx").select([2,3,5,8].contain(C_NATIONKEY)).top(3;C_ACCTBAL)

2

=file("orders.ctx").open().cursor(O_ORDERKEY,O_TOTALPRICE;![20,31,55,86].contain(O_CUSTKEY)).total(top(-10;O_TOTALPRICE))

A1:把文件内容装入内存,然后计算过滤后的前三名。

A2:用游标方式计算过滤后的后 10 名,top 和 sum、count 一样是汇总函数。

14. 组内 TOP-N

对文件中的数据分组后求各组TOP-N

示例:

先按照客户数值是 10 的整数倍过滤出客户,再按照国家分组,求每个国家中客户数值前 3 名的客户数值。

先过滤出年份为 2023 年的订单,再按照客户分组,每组求金额倒数 10 名的金额。


A

1

=T("customer.ctx").select(C_ACCTBAL%10==0).groups(C_NATIONKEY;top(3,C_ACCTBAL))

2

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE;year(O_ORDERDATE)==2023).groups(O_CUSTKEY;top(-10,O_TOTALPRICE))

A1:把文件内容装入内存,然后过滤、分组计算前三名。

A2:用游标方式过滤、分组计算后 10 名。当分组结果集不大时,用 groups 函数;当结果集很大时,用 groupx 函数。

这里的 top 函数与前例的参数分隔符不同。这里用逗号分隔,返回前 n 个排序表达式的值组成的序列;前面用分号分隔,返回前 n 个记录组成的序列。

15. 外键关联查询

为了描述方便,我们也可以把集文件和组表看成数据表。

两个数据表,表 A 中的非主键字段与表 B 的主键关联,B 称为 A 的外键表,称此关联为外键关联。

示例:

客户表和国家表通过国家号关联,将客户名和国家名用“-”连接起来。

订单表和客户表通过客户号关联,计算各订单的金额+客户数值。

对于这种单外键关联有两种方法实现,方法一:


A

B

1

=T("nation.btx").keys(N_NATIONKEY)

=T("customer.ctx")

2

=B1.switch(C_NATIONKEY,A1:N_NATIONKEY)

3

=A2.derive(C_NAME/"-"/C_NATIONKEY.N_NAME)

4

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE)

5

=A4.switch(O_CUSTKEY,B1)


6

=A5.derive(O_CUSTKEY.C_ACCTBAL+O_TOTALPRICE:newValue)

A1:国家数据读入内存,并设置主键。

B1:客户数据读入内存,组表生成时已经定义了主键,这里不需要再设置。

A2:客户关联国家,由于 A1 中定义了主键,所以这里:N_NATIONKEY 可以省略。

A3:关联好的客户表,用自身的字段和国家表字段做计算。

A4:定义订单的游标,只取需要的字段。

A5:订单游标关联内存中的客户表。

A6:用订单游标的字段加上客户的字段,返回的仍然是游标。

方法二:


A

B

1

=T("nation.btx").keys(N_NATIONKEY)

=T("customer.ctx")

2

=B1.join(C_NATIONKEY,A1:N_NATIONKEY,N_NAME)

3

=A2.derive(C_NAME/"-"/N_NAME)

4

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE)

5

=A4.join(O_CUSTKEY,B1,C_ACCTBAL)


6

=A5.derive(C_ACCTBAL+O_TOTALPRICE:newValue)

A1:国家数据读入内存,并设置主键。

B1:客户数据读入内存,组表生成时已经定义了主键,这里不需要再设置。

A2:用 join 函数将 B1 中国家号与 A1 中的国家号进行关联,N_NATIONKEY 为主键时也可省略不写,同时引入 A1 中 N_NAME 列数据,形成新的序表。

A3:对新序表做计算。

A5:用 join 函数将 A4 的游标与 B1 通过客户号关联,同时引入 B1 的 C_ACCTBAL 字段,返回游标。

A6:用游标两个字段相加得到新字段。

对于多个外键字段关联的情况,就不能用switch函数了,只能用join函数来关联。假设上面例子的主键、外键都变成两个,写法是这样的:


A

B

1

=T("nation.btx").keys(N_NATIONKEY1, N_NATIONKEY2)

=T("customer.ctx")

2

=B1.join(C_NATIONKEY1:N_NATIONKEY2,A1:N_NATIONKEY1: N_NATIONKEY2,N_NAME)

3

=A2.derive(C_NAME/"-"/N_NAME)

4

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE)

5

=A4.join(O_CUSTKEY1: O_CUSTKEY2,B1,C_ACCTBAL)


6

=A5.derive(C_ACCTBAL+O_TOTALPRICE:newValue)

A2、A5 中多个主键、外键要用冒号分开。

16. 主键关联查询

表 A 的主键与表 B 的主键关联,A 和 B 相互称为同维表。同维表是一对一的关系,逻辑上可以简单地看成一个表来对待。同维表都是按主键关联,相应记录是唯一对应的。

假设国家信息表 nation_info 和国家表的主键都是国家编号

客户和客户信息表 customer_info 的主键都是客户编号。

示例 1:

将国家中的名称字段和国家信息中的人口数量 POPULATION 用“-”连接在一起。

将客户中的客户数值和客户信息表中的客户资金 FUND 相加。


A

B

1

=T("nation.btx").keys(N_NATIONKEY)

=T("nation_info.btx").keys(NI_NATIONKEY)

2

=join(A1:n,N_NATIONKEY;B1:ni,POPULATION)

3

=A2.new(n.N_NAME/"-"/ni.POPULATION)

4

=file("customer.ctx").open().cursor(C_CUSTKEY,C_ACCTBAL)

5

=file("customer_info.ctx").open().cursor(CI_CUSTKEY,FUND)

6

=joinx(A4:c,C_CUSTKEY;A5:ci,CI_CUSTKEY)

7

=A6.new(c.C_ACCTBAL+ci.FUND:newValue)

A1、B1 将国家和国家信息读入内存。

A2:国家和国家信息按照主键连接。

A3:新产生一个序表,用国家名称和人口连成一个字符串字段。

A4、A5:定义客户和客户信息的游标。

A6:将两个游标按照主键关联。使用 joinx时,参与关联的各游标数据必须按关联字段有序。

A7:取出 A6 中 c 的字段和 ci 的字段,相加构成一个新的游标。

A4、A5 如果要用多路游标,A4 加 @m,A5 不能直接加上 @m,要写成:

=file("customer_info.ctx").open().cursor(CI_CUSTKEY,FUND;;A4)

这是由于并行分段时,要保证两个文件相同的主键分到对应的段中。

还有一种主子表形式的主键关联。表 A 的主键与表 B 的部分主键关联,A 称为主表,B 称为子表。主子表是一对多的关系。

订单表和在线商品表是主子表,订单表是主表。

假设客户表和客户联系人表 customer_contacts 是主子表,后者主键是客户号和联系人号。

示例:

查询客户联系人有女性的客户。

查询在线商品表中有折扣大于 0.05 的订单信息。


A

1

=T("customer.ctx")

2

=T("customer_contacts.ctx").select(GENDER==1)

3

=join(A1:c,A1.C_CUSTKEY;A2:cc,CC_CUSTKEY)

4

=A3.conj(c)

5

=file("orders.ctx").open().cursor(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE)

6

=file("lineitem.ctx").open().cursor(L_ORDERKEY;L_DISCOUNT>0.05)

7

=joinx(A5:o,O_ORDERKEY;A6:l,L_ORDERKEY)

8

=A7.conj(o)

A1:将客户读入内存。

A2:将客户联系人读入内存,并按照性别为女(1)过滤。

A3:按照主键连接 A1 和 A2。

A4:用 c 列合并得到客户数据。

A5:定义订单的游标。

A6:定义在线商品表游标,采用游标前过滤。

A7:将两个游标按照主键关联。使用 joinx时,参与关联的各游标数据必须按关联字段有序。

A8:取出 A7 中的 o,合并得到结果游标。

A5、A6 如果要用多路游标,A5 加 @m,A6 不能直接加上 @m,要写成:

=file("lineitem.ctx").open().cursor(L_ORDERKEY;L_DISCOUNT>0.05;A5)

而且,主子表的顺序不能改变。主表 A5 要加 @m 写在前面,子表 A6 不加 @m,最后一个“;”后要写明主表 A5。

这是由于并行分段时,要保证子表按照主表的方式分段,同样的主键分到对应的段中。