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。

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