1.4 常规数据表运算
1.4.1 T 函数和 E 函数
使用 import()/xlsimport 函数需要先定义文件对象,而读写结构化文件的操作很常见,SPL 提供了更简单的 T() 函数,可以自动根据文件扩展名采用不同的动作。
A | |
---|---|
1 | =T(“data.txt”) |
2 | =T(“data.csv”) |
3 | =T(“data.txt”;“|”) |
4 | =T@b(“data.csv”) |
5 | =T(“data.xls”) |
6 | =T(“data.xlsx”) |
7 | =T(“data.xlsx”;“sheet2”) |
8 | =T@b(“data.xlsx”) |
A1 有标题,TAB 分隔列
A2 有标题,逗号分隔列
A3 有标题,用 | 分隔列
A4 无标题,逗号分隔
A5 有标题
A6 有标题
A7 有标题,指定 Sheet
A8 无标题
T() 函数还有其它参数,可以选择读入部分列,并支持写入。因为不太常见,这里不再举例。
Excel 表格中的数据经常以二维序列的形式出现,而处理时转换成序表会更方便。SPL 提供了简短的 E 函数负责处理二维序列和序表之间的转换:
A | |
---|---|
1 | =file(“data.xlsx”).xlsimport@w() |
2 | =E(A1) |
3 | =E@b(A1) |
4 | =E(A2) |
5 | =E@b(A2) |
A1 将 Excel 读成二维序列
A2 将二维序列转换成序表,第一行是标题
A3 没有标题
A4 将序表转换成二维序列,第一行为标题
A5 忽略标题
E() 函数还有其它选项,还可将序表与 TAB/ 回车分隔的串进行互相转换,可以参考文档自行实验。
1.4.2 过滤
从数据表中筛选出满足条件的行。
示例:从学生成绩表 Students_scores.txt 中筛选出 10 班的学生成绩,文件中第一行是列名,第二行开始是数据,如下图所示。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt").select(CLASS==10) |
A1 读取文件中数据,然后选出班级为 10 的行。T 函数会自动根据文件扩展名选用适合的分隔符。
1.4.3 汇总
对数据表中的数据进行汇总。
示例:计算学生成绩表中全体学生的语文平均分、数学最高分、英语总分。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt") |
2 | =A1.avg(Chinese) |
3 | =A1.max(Math) |
4 | =A1.sum(English) |
A1 读取文件中数据。
A2 计算语文平均分
A3 计算数学最高分
A4 计算英语总分
1.4.4 跨列计算
对数据表中的数据进行跨列计算。
示例:计算学生成绩表中每位学生的总分。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt") |
2 | =A1.derive(English+Chinese+Math:total_score) |
A1 读取文件中数据。
A2 在 A1 中新增一列 total_score,其值为英语、语文、数学 3 列之和
A2 中结果如下:
1.4.5 排序
对数据表中的数据进行升 / 降序排序。
示例:将学生成绩表按照班号升序、总分降序的顺序排列。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt") |
2 | =A1.sort(CLASS) |
3 | =A1.sort(CLASS,-Math) |
A1 读取文件中数据。
A2 按班级号升序排列
A3 先按班级号升序排列,班级内再按数学成绩降序排列
1.4.6 分组汇总
对数据表中的数据进行分组汇总。
示例:查询各班的英语最低分、语文最高分、数学总分。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt") |
2 | =A1.groups(CLASS;min(English),max(Chinese),sum(Math)) |
A1 读取文件中数据。
A2 按班级分组,计算各班英语最低分、语文最高分、数学总分
1.4.7 分组后过滤
对数据表中的数据分组汇总后再过滤。
示例:找出英语平均分低于 70 分的班级。
A | |
---|---|
1 | =T(“E:/txt/Students_scores.txt") |
2 | =A1.groups(CLASS;avg(English):avg_En) |
3 | =A2.select(avg_En<70) |
A1 读取文件中数据。
A2 按班级分组,计算各班英语平均分命名新列名为 avg_En
A3 从 A2 中选出英语平均分低于 70 的
A3 中查询结果如下:
1.4.8 关联
- 对两个数据表中的数据进行关联计算
示例:销售订单信息和产品信息分别存储在两个 Excel 文件中,计算各订单的销售额。两个文件数据结构如下图:
A | |
---|---|
1 | =T(“e:/orders/sales.xlsx") |
2 | =T(“e:/orders/product.xlsx").keys(ID) |
3 | =A1.join(ProductID,A2,Name,Price) |
4 | =A3.derive(Quantity*Price:amount) |
A1 读取销售订单数据
A2 读取产品信息数据,设置 ID 为主键
A3 将 A1 按照 ProductID 与 A2 中的主键进行关联,同时引入 Name、Price 列数据
A4 A3 中新增一列 amount,其值为销售数量 Quantity 与产品价格 Price 的积
- 对两个数据表中的数据进行关联查询
示例:仍用上节中的 2 个文件,查询产品价格大于 20 元的销售订单。
A | |
---|---|
1 | =T(“e:/orders/sales.xlsx") |
2 | =T(“e:/orders/product.xlsx").select(Price>20).keys(ID) |
3 | =A1.switch@i(ProductID,A2) |
A1 读取销售订单数据
A2 读取产品信息数据,选出价格大于 20 的产品信息,然后设置 ID 为主键
A3 将 A1 按照 ProductID 与 A2 中的主键进行关联,选项 @i 表示在 A2 中找不到与 ProductID 匹配的产品 ID 时,则删掉此行
- 对主表与明细表数据进行关联查询
示例:有员工信息表 employee.xlsx 与员工家庭成员表 family.xlsx 部分数据如下,请查询家中有 70 岁以上老人的员工信息。
A | |
---|---|
1 | =T(“e:/work/employee.xlsx”) |
2 | =T(“e:/work/family.xlsx”).select(age(Birthday)>=70) |
3 | =join(A1:employee,Eid;A2:family,Eid) |
4 | =A3.conj(employee) |
A1 读取员工信息数据
A2 读取员工家庭成员数据,选出年龄 70 以上的成员
A3 将 A1 与 A2 按照 Eid 进行关联过滤,删除不匹配的行,将 A1 命名为 employee,A2 命名为 family
A4 取出 A3 中的 employee 列,连接为序表