Loop Computations

1.     Compare tables row by row to find the equals

Perform judgements by loop, adding a new member to an existing sequence each time.

【Example 1】Compare two files that have same number of rows to count the rows with same data. Below is part of the source data:

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 script】

	A	B	C
1	=file("p_old.csv").import@ct()		/ Read in the file first imported
2	=file("p_new.csv").import@ct()		/ Read in the second file imported
3	for A1.len()	=cmp(A1(A3),A2(A3))	/ Compare the two files row by row by loop
4		=@|B3	/ Union each comparison result with B4’s value
5	=B4.count(~==0)		/ Count the rows having same data

   Below is A5’s result:

Value

11302

2.     Assign values by loop

Loop through members of a set to compute each and assign value to it.

【Example 2】According to the following sales table, give a  reward of sales amount’s 5% to each salesperson whose performance in 2014 rank the top 10%. Below is part of the source table:

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 script】

	A	B
1	=connect("db").query("select * from sales")	/ Connect to the data source to read in sales table
2	=A1.select(year(OrderDate)==2014)	/ Get data of 2014
3	=A2.groups(SellerId;sum(Amount):Amount)	/ Group A2 by seller and calculate the total sales amount in 2014
4	=A3.sort@z(Amount).to(A3.len()*0.1)	/ Sort A3 by sales amount in descending order and get records where the amount ranks top 10%
5	=A4.run(Amount*=1.05)	/ Use A.run() function to loop through A4’s selected records and give a reward of the sale’s amount’s 5% to each seller

   Below is A5’ s result:

SellerId	Amount
4	150433.185
3	127878.04
1	102756.759
8	87965.346

3.     Complex inter-row computation

After data is grouped, summarize specific columns to get different aggregates while performing inter-row computations.

【Example 3】According to the user payment details table below, calculate the monthly sum payable in 2014 for each user. Below is part of the source table:

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

The target is to output the payable sum per month in the year of 2014. If no data exists for the current month, the payable sum will be the one in the previous month:

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
…

	A	B	C
1	=file("Payment.txt").import@t().select(year(due_date)==2014)		/ Import data of 2014 from the file
2	=create(name,${12.().concat@c()})	=A1.group(customID)	/A2: Generate an empty table of 12 months. A3: Group A1 by customer ID
3	for B2	=12.(null)	/ Generate null values for the 12 months
4		>A3.run(B3(month(due_date))= amount_payable)	/ Set the payable sum for the corresponding month
5		>B3.run(~+=~[-1])	/ Set null as payable sum of the previous month and calculate the cumulative sum month by month
6		=A2.record(B2.name|B3)	/ Insert records to the result table

【SPL script】

   Below is A2’s result:

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.     Get the largest number of consecutively rising days

Count the frequency of continuous rising of a specific column value during the loop.

【Example 4】According to the SSE Composite Index records below, get the closing price’s largest number of consecutive rising days in 2019. Below is the part of the source table:

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 script】

	A	B
1	=file("000001.csv").import@ct()	/ Import the source data file
2	=A1.select(year(Date)==2019).sort(Date)	/ Get records of 2019 and sort them by date
3	=n=0,A2.max(if(Close>Close[-1],n+=1,n=0))	/ Loop through closing prices to compare each one with the previous one and add 1 if the current price is higher, and then get the largest count

    Below is A3’s result:

Value

6

5.     Nested loop

Use loop functions in a nested way.

【Example 5】The hundred fowls problem: If a rooster is worth five coins, a hen three coins, and three chicks together are worth one coin, how many roosters, hens, and chicks totaling 100 can be bought for 100 coins?

【SPL script】

	A	B
1	=to(100/5)	/ Number of roosters that can be bought
2	=to(100/3)	/ Number of hens that can be bought
3	=33.(~*3)	/ Number of chicks that can be bought
4	=create(Cock,Hen,Chick)	/ Create an empty table to store the numbers of roosters, hens, and chicks
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.~)))))	/ Loop through the number of roosters, hens, and chicks respectively and insert the result records to A4’s table if the specific requirement is satisfied. The tilde sign is used to represent the current member

    Below is A4’s result:

Cock	Hen	Chick
4	18	78
8	11	81
12	4	84

6.     Call loop number during the loop

Query a certain file by loop to generate desired information during which the loop number is displayed.

【Example 6】Query file 2 to output specific information according to key words in file 1.

【SPL script】

	A	B
1	=file("file1.txt").read@n()	/ Read in file 1
2	=file("file2.txt").read@n()	/ Read in file 2
3	=A1.conj(("Q"+string(#)+"."+~)|A2.select(pos(~,A1.~)).(~.words()(1)))	/ Loop through file1’s strings to find them in each string file 2 and get the first word if it is matched. A2.select uses “~” to represent the current member of file 2; A1.~ is the current member of A1. Precede each query result with “Q + sequence number of the current member of A1”; the sequence number is obtained through “#”

Below is A3’s result:

Member

Q1. like parks

I

Shelly

Harry

Q2. went out

Shelly

Q3. go out

I

Ben

Harry

7.     Summarize data in skewness intervals starting from specific positions by loop

Calculate the average within skewness intervals starting from specific positions by loop.

【Example 7】According to the stock exchange table below, list the average closing price within 20 days for each transaction date during 1.1 – 1.10 in the year of 2020. Below is part of the source table:

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 script】

	A	B
1	=connect("db")	/Connect to the data source
2	=A1.query("select Date, Close from Stock where Code='600036'order by Date")	/ Select records of the targeted stock and sort them by date
3	=A2.pselect@a(Date>=date("2020/01/01") && Date<=date("2020/01/10"))	/ Use A.pselect() function to get the sequence numbers of records from 1.1-1.10 in 2020
4	=A2(A3).derive(A2.calc(A3(#),avg(Close[-19:0])):ma20)	/ Use calc()function to calculate and return the average closing price within 20 days for each of the specified ten days. Close[-19:0] gets closing prices from 19 days ago to the current date

Below is A4’s result:

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.     Iterative accumulation

Perform iterative accumulation during the loop and then filtering according to the cumulative total.

【Example 8】According to the sales table below, count the days needed to bag 20 orders per month in the year of 2014. Below is part of the source table:

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 script】

	A	B
1	=connect("db").query("select * from sales")	/ Connect to the data source to read in the sales table
2	=A1.select(year(OrderDate)==2014)	/ Get records of 2014
3	=A2.sort(OrderDate)	/ Sort the selected records by order date
4	=A3.select(seq(month(OrderDate))==20)	/ Use seq() function to get sequence numbers of orders in each month, and then select the record whose sequence number is 20

Below is A4’s result:

Month	Day
1	20
2	20
3	20
4	18
…	…

9.     Post-grouping ranking

Get the rank in each group by loop.

【Example 9】According to the employee table below, get the rank of each employee’s salary in their department. Below is part of the source table:

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 script】

	A	B
1	=connect("db") .query("select * from Employee order by DEPT, SALARY DESC")	/ Connect to the data source, read in the employee table, and sort it by department and salary
2	=A1.derive(rank(SALARY;DEPT):DeptRank)	/ Use rank() function to number the ordered departments and salaries, and calculate department ranks

Below is A2’s result:

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.   Post-grouping tight ranking

Get the tight ranks for specific fields in each group by loop.

【Example 10】According to the scores table below, get the ranks of all subject scores for the student whose ID is 8 in class one. Below is part of the source table:

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 script】

	A	B
1	=connect("db") .query("select * from SCORES where CLASS='Class one'order by SUBJECT, SCORE DESC")	/ Connect to the data source, read in the scores table, and sort it by subject and score
2	=A1.derive(ranki(SCORE;SUBJECT):Rank)	/ Use ranki() function to number the ordered departments and salaries, and calculate the tight rank for each score
3	=A2.select(STUDENTID==8)	/ Get information of student whose ID is 8
4	=create(${A3.(SUBJECT).concat@c()}).record(A3.(Rank))	/ Join up tight ranks of scores of all subjects according to A3’ information

Below is A4’s result:

English	Math	PE
10	4	14

11.   Iterative sum

Calculate iterative sums by loop.

【Example 11】According to the SSE Composite Index records below, calculate the cumulative amount total for each transaction date in 2019. Below is part of the source table:

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 script】

	A	B
1	=file("000001.csv").import@ct()	/ Import the source file
2	=A1.select(year(Date)==2019).sort(Date)	/ Select records of 2019 and sort them by date
3	=A2.derive(cum(Amount):CUM)	/ Use cum() function to calculate the cumulative amount total

Below is A3’s result:

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.   User-defined iterative computation

Users define the computing expression and the desired termination condition for the iteration computation performed within the loop.

【Example 12】According to the sales table below, get the date when the sales target of 150,000 is achieved for the first quarter. Below is part of the source table:

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 script】

	A	B
1	=connect("db").query("select * from sales")	/ Connect to the data source and read in the sales table
2	=A1.select(year(OrderDate)==2014)	/ Select data of 2014
3	=A2.iterate((@+=Amount, ~~=OrderDate),0,@>150000)	/ iterate() function performs the iterative computation with the initial value as 0 and returns the desired target date. Sum sales amounts to the current cell and until the total reaches 150,000.

Below is A3’s result:

Value

2014/03/25

There are more examples in SPL CookBook.