结构化数据上的循环运算
【摘要】
循环运算是指按照一定的次序对集合的成员进行计算。除了在循环中访问当前成员、对成员赋值等简单的计算，还有在循环中进行跨行计算、嵌套循环、迭代运算等复杂需求。如何简便快捷的处理循环运算，这里为你全程解析，并提供 esProc 示例代码。结构化数据上的循环运算
1. 循环比较每行记录判断文件是否相同
循环判断，每次在序列最后添加新的成员。
【例 1】 比较两个行数相同的文件中有多少行数据完全一致。文件部分数据如下：
ID |
Predicted_Y |
Original_Y |
10 |
0.012388464367608093 |
0.0 |
11 |
0.01519899123978988 |
0.0 |
13 |
0.0007920238885061248 |
0.0 |
19 |
0.0012656367468159102 |
0.0 |
21 |
0.009460545997473379 |
0.0 |
23 |
0.024176791871681664 |
0.0 |
… |
… |
… |
【SPL脚本】
A |
B |
C |
|
1 |
=file("p_old.csv").import@ct() |
/读取第一次输出的文件 |
|
2 |
=file("p_new.csv").import@ct() |
/读取第二次输出的文件 |
|
3 |
for A1.len() |
=cmp(A1(A3),A2(A3)) |
/循环比较两个文件同行记录的数据 |
4 |
=@|B3 |
/把每次比较的结果与 B4 格值合并 |
|
5 |
=B4.count(~==0) |
/统计有多少行数据相等 |
A5的执行结果如下：
Value |
11302 |
2. 循环赋值
对集合的成员进行循环计算并赋值。
【例 2】 在销售表中，将 2014 年前 10% 的销售员再给予 5% 的业绩奖励。销售表部分数据如下：
OrderID |
Customer |
SellerId |
OrderDate |
Amount |
10400 |
EASTC |
1 |
2014/01/01 |
3063.0 |
10401 |
HANAR |
1 |
2014/01/01 |
3868.6 |
10402 |
ERNSH |
8 |
2014/01/02 |
2713.5 |
10403 |
ERNSH |
4 |
2014/01/03 |
1005.9 |
10404 |
MAGAA |
2 |
2014/01/03 |
1675.0 |
… |
… |
… |
… |
… |
【SPL脚本】
A |
B |
|
1 |
=connect("db").query("select * from sales") |
/连接数据源，读取销售表 |
2 |
=A1.select(year(OrderDate)==2014) |
/选出 2014 年数据 |
3 |
=A2.groups(SellerId;sum(Amount):Amount) |
/按销售员分组汇总当年销售总额 |
4 |
=A3.sort@z(Amount).to(A3.len()*0.1) |
/按销售额降序排列，取前百分之十 |
5 |
=A4.run(Amount*=1.05) |
/使用函数 A.run()，对前百分之十循环，每人给予销售额 5% 的奖励 |
A5的执行结果如下：
SellerId |
Amount |
4 |
150433.185 |
3 |
127878.04 |
1 |
102756.759 |
8 |
87965.346 |
3. 复杂跨行计算
分组统计数据后，对每组分列统计不同的结果，同时跨行计算。
【例 3】根据用户支付数据明细表，统计各个用户 2014 年每月应付金额的汇总表。用户支付数据明细表部分数据如下：
ID |
customID |
name |
amount_payable |
due_date |
amount_paid |
pay_date |
112101 |
C013 |
CA |
12800 |
2014-02-21 |
12800 |
2014-12-19 |
112102 |
C013 |
CA |
3500 |
2014-06-15 |
3500 |
2014-12-15 |
112103 |
C013 |
CA |
2600 |
2015-03-21 |
6900 |
2015-10-17 |
要求根据指定年份（如2014），输出每月应付金额，若无当月数据，则当月应付金额为上月该值：
name |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
CA |
12800 |
12800 |
12800 |
12800 |
16300 |
16300 |
16300 |
16300 |
16300 |
16300 |
16300 |
|
… |
【SPL脚本】
A |
B |
C |
|
1 |
=file("Payment.txt").import@t().select(year(due_date)==2014) |
/从文件中导入 2014 年数据 |
|
2 |
=create(name,${12.().concat@c()}) |
=A1.group(customID) |
/A2：生成包含 12 个月的空表。A3：按客户 ID 分组。 |
3 |
for B2 |
=12.(null) |
/生成 12 个月的空数据 |
4 |
>A3.run(B3(month(due_date))= amount_payable) |
/设置相应月份的应付金额。 |
|
5 |
>B3.run(~+=~[-1]) |
/将空值置为前一个月的数值，新增应付款项时累加。 |
|
6 |
=A2.record(B2.name|B3) |
/将记录插入结果表中。 |
A2的执行结果如下：
name |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
CA |
12800 |
12800 |
12800 |
12800 |
16300 |
16300 |
16300 |
16300 |
16300 |
16300 |
16300 |
|
… |
4. 最大连续增长天数
在循环计算中，计算某列的连续增长次数。
【例 4】 根据上证指数记录，求 2019 年收盘价增长的最大连续天数。上证指数表部分数据如下：
Date |
Open |
Close |
Amount |
2019/12/31 |
3036.3858 |
3050.124 |
2.27E11 |
2019/12/30 |
2998.1689 |
3040.0239 |
2.67E11 |
2019/12/27 |
3006.8517 |
3005.0355 |
2.58E11 |
2019/12/26 |
2981.2485 |
3007.3546 |
1.96E11 |
2019/12/25 |
2980.4276 |
2981.8805 |
1.9E11 |
… |
… |
… |
… |
【SPL脚本】
A |
B |
|
1 |
=file("000001.csv").import@ct() |
/导入数据文件 |
2 |
=A1.select(year(Date)==2019).sort(Date) |
/选出 2019 年的记录并按日期排序 |
3 |
=n=0,A2.max(if(Close>Close[-1],n+=1,n=0)) |
/循环收盘价，比较每天的收盘价和前日收盘价，如果当日收盘价更高，则计数加 1，最后选出计数最大值。 |
A3的执行结果如下：
Value |
6 |
5. 嵌套循环
嵌套使用循环函数计算。
【例 5】 百鸡问题，鸡翁一值钱五，鸡母一值钱三，鸡雏三值钱一。百钱买百鸡，问鸡翁、母、雏各几？
【SPL 脚本】
A |
B |
|
1 |
=to(100/5) |
/可能购买的鸡翁数量 |
2 |
=to(100/3) |
/可能购买的鸡母数量 |
3 |
=33.(~*3) |
/可能购买的鸡雏数量 |
4 |
=create(Cock,Hen,Chick) |
/创建空表用于存放三种鸡的数量 |
5 |
>A1.run(A2.run(A3.run(if(A1.~+A2.~+A3.~==100 && A1.~*5+A2.~*3+A3.~/3==100,A4.insert(0,A1.~,A2.~,A3.~))))) |
/分别循环鸡翁、鸡母、鸡雏，当满足百钱买百鸡时，将结果插入到 A4 创建的集合中。其中用到了 ~ 符号代表集合循环的当前成员 |
A4的执行结果如下：
Cock |
Hen |
Chick |
4 |
18 |
78 |
8 |
11 |
81 |
12 |
4 |
84 |
6. 循环中调用次数
循环查找文本，并按需要生成结果，循环中需要使用循环次数。
【例 6】 根据文本 1 中的关键词，在文本 2 中查找，希望整理为输出结果：
【SPL 脚本】
A |
B |
|
1 |
=file("file1.txt").read@n() |
/读取文本 1 |
2 |
=file("file2.txt").read@n() |
/读取文本 2 |
3 |
=A1.conj(("Q"+string(#)+"."+~)|A2.select(pos(~,A1.~)).(~.words()(1))) |
/循环文本 1 中的字符串，在文本 2 中查找，并取第一个英文单词。A2.select 中用到了 ~ 代表 A2 当前成员，A1.~ 代表 A1 的当前成员。每组搜索结果前拼上 Q+A1 的序号 +A1 当前成员，其中序号通过 #取得。 |
A3的执行结果如下：
Member |
Q1. like parks |
I |
Shelly |
Harry |
Q2. went out |
Shelly |
Q3. go out |
I |
Ben |
Harry |
7. 循环时按位置统计临近数据
循环计算，计算时按位置计算偏移区间内均值。
【例 7】 根据招商银行的股市交易表，列出 2020 年 1 月 1 日至 10 日每天的 20 日收盘均价。股市交易表部分数据如下：
Date |
Open |
Close |
Amount |
2019/12/31 |
3036.3858 |
3050.124 |
2.27E11 |
2019/12/30 |
2998.1689 |
3040.0239 |
2.67E11 |
2019/12/27 |
3006.8517 |
3005.0355 |
2.58E11 |
2019/12/26 |
2981.2485 |
3007.3546 |
1.96E11 |
2019/12/25 |
2980.4276 |
2981.8805 |
1.9E11 |
… |
… |
… |
… |
【SPL脚本】
A |
B |
|
1 |
=connect("db") |
/连接数据源 |
2 |
=A1.query("select Date, Close from Stock where Code='600036'order by Date") |
/选出招商银行数据并按日期排序 |
3 |
=A2.pselect@a(Date>=date("2020/01/01") && Date<=date("2020/01/10")) |
/使用函数 A.pselect() 获取 2020 年 1 月 1 日到 10 日对应记录的序号 |
4 |
=A2(A3).derive(A2.calc(A3(#),avg(Close[-19:0])):ma20) |
/使用函数 calc() 循环计算前十日数据的 20 日均值并返回，计算时使用了 Close[-19:0 获取从过去 19 天到今天的收盘价。 |
A4的执行结果如下：
Date |
Close |
ma20 |
2020/01/02 |
38.88 |
37.35 |
2020/01/03 |
39.4 |
37.50 |
2020/01/06 |
39.24 |
37.64 |
2020/01/07 |
39.15 |
37.79 |
2020/01/08 |
38.41 |
37.90 |
2020/01/09 |
38.9 |
38.03 |
2020/01/10 |
39.04 |
38.16 |
8. 迭代累加
循环时迭代累加，根据累加值筛选。
【例 8】根据销售表，统计出 2014 年每个月达到 20 笔订单所需天数。销售表部分数据如下：
OrderID |
Customer |
SellerId |
OrderDate |
Amount |
10400 |
EASTC |
1 |
2014/01/01 |
3063.0 |
10401 |
HANAR |
1 |
2014/01/01 |
3868.6 |
10402 |
ERNSH |
8 |
2014/01/02 |
2713.5 |
10403 |
ERNSH |
4 |
2014/01/03 |
1005.9 |
10404 |
MAGAA |
2 |
2014/01/03 |
1675.0 |
… |
… |
… |
… |
… |
【SPL脚本】
A |
B |
|
1 |
=connect("db").query("select * from sales") |
/连接数据源，读取销售表 |
2 |
=A1.select(year(OrderDate)==2014) |
/选出 2014 年数据 |
3 |
=A2.sort(OrderDate) |
/按照订单日期排序 |
4 |
=A3.select(seq(month(OrderDate))==20) |
/使用函数 seq() 计算每个月份的订单序号，并选出每个月序号为 20 的记录 |
A4的执行结果如下：
Month |
Day |
1 |
20 |
2 |
20 |
3 |
20 |
4 |
18 |
… |
… |
9. 分组计算排名
循环中计算分组中的排名。
【例 9】根据员工收入表，求员工在本部门的收入排名。员工收入表部分数据如下：
ID |
NAME |
DEPT |
SALARY |
1 |
Rebecca |
R&D |
7000 |
2 |
Ashley |
Finance |
11000 |
3 |
Rachel |
Sales |
9000 |
4 |
Emily |
HR |
7000 |
5 |
Ashley |
R&D |
16000 |
… |
… |
… |
… |
【SPL 脚本】
A |
B |
|
1 |
=connect("db") .query("select * from Employee order by DEPT, SALARY DESC") |
/连接数据源，读取员工表并按部门和收入排序 |
2 |
=A1.derive(rank(SALARY;DEPT):DeptRank) |
/对有序的部门和收入利用函数 rank() 编号，计算出各部门排名 |
A2的执行结果如下：
ID |
NAME |
DEPT |
SALARY |
DeptRank |
2 |
Ashley |
Finance |
11000 |
1 |
32 |
Andrew |
Finance |
11000 |
1 |
230 |
Hannah |
Finance |
10000 |
3 |
24 |
Chloe |
Finance |
10000 |
3 |
… |
… |
… |
… |
… |
10. 分组计算紧凑排名
循环计算分组中的紧凑排名。
【例 10】根据成绩表，求出一班学生 ID 为 8 的学生的各科成绩在本班的排名。成绩表部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
Class one |
1 |
English |
84 |
Class one |
1 |
Math |
77 |
Class one |
1 |
PE |
69 |
Class one |
2 |
English |
81 |
Class one |
2 |
Math |
80 |
… |
… |
… |
… |
【SPL 脚本】
A |
B |
|
1 |
=connect("db") .query("select * from SCORES where CLASS='Class one'order by SUBJECT, SCORE DESC") |
/连接数据源，读取学生成绩表并按学科和成绩排序 |
2 |
=A1.derive(ranki(SCORE;SUBJECT):Rank) |
/对有序的学科和成绩利用函数 ranki() 计算各科成绩的紧凑排名 |
3 |
=A2.select(STUDENTID==8) |
/选出学生 ID 是 8 的学生信息 |
4 |
=create(${A3.(SUBJECT).concat@c()}).record(A3.(Rank)) |
/利用 A3 选出的结果，整理出各科紧凑排名 |
A4的执行结果如下：
English |
Math |
PE |
10 |
4 |
14 |
11. 迭代求和
循环计算迭代求和的结果。
【例 11】根据上证指数表，计算 2019 年每个交易日的全年累计成交金额。上证指数表部分数据如下：
Date |
Open |
Close |
Amount |
2019/12/31 |
3036.3858 |
3050.124 |
2.27E11 |
2019/12/30 |
2998.1689 |
3040.0239 |
2.67E11 |
2019/12/27 |
3006.8517 |
3005.0355 |
2.58E11 |
2019/12/26 |
2981.2485 |
3007.3546 |
1.96E11 |
2019/12/25 |
2980.4276 |
2981.8805 |
1.9E11 |
… |
… |
… |
… |
【SPL 脚本】
A |
B |
|
1 |
=file("000001.csv").import@ct() |
/导入数据文件 |
2 |
=A1.select(year(Date)==2019).sort(Date) |
/选出 2019 年的记录并按日期排序 |
3 |
=A2.derive(cum(Amount):CUM) |
/使用 cum() 函数计算累计成交金额 |
A3的执行结果如下：
Date |
Open |
Close |
Amount |
CUM |
2019/01/02 |
2497.8805 |
2465.291 |
9.759E10 |
9.759E10 |
2019/01/03 |
2461.7829 |
2464.3628 |
1.07E11 |
2.046E11 |
2019/01/04 |
2446.0193 |
2514.8682 |
1.39E11 |
3.436E11 |
2019/01/07 |
2528.6987 |
2533.0887 |
1.46E11 |
4.896E11 |
2019/01/08 |
2530.3001 |
2526.4622 |
1.23E11 |
6.126E11 |
… |
… |
… |
… |
… |
12. 自定义迭代计算
循环中使用迭代计算，自行设置迭代中的计算表达式和终止条件。
【例 12】根据销售表数据，统计 2014 年第一季度到哪一天完成了销售总额 15 万的季度目标。销售表部分数据如下：
OrderID |
Customer |
SellerId |
OrderDate |
Amount |
10400 |
EASTC |
1 |
2014/01/01 |
3063.0 |
10401 |
HANAR |
1 |
2014/01/01 |
3868.6 |
10402 |
ERNSH |
8 |
2014/01/02 |
2713.5 |
10403 |
ERNSH |
4 |
2014/01/03 |
1005.9 |
10404 |
MAGAA |
2 |
2014/01/03 |
1675.0 |
… |
… |
… |
… |
… |
【SPL脚本】
A |
B |
|
1 |
=connect("db").query("select * from sales") |
/连接数据源，读取销售表 |
2 |
=A1.select(year(OrderDate)==2014) |
/选出 2014 年数据 |
3 |
=A2.iterate((@+=Amount, ~~=OrderDate),0,@>150000) |
/使用函数 iterate() 迭代计算，初始值为 0。将销售额累加到当前格，直到超过 15 万终止。函数返回订单日期。 |
A3的执行结果如下：
Value |
2014/03/25 |
《SPL CookBook》中还有更多相关计算示例。
