RaqForum 25 No.
13 View  • 
     ...

5.3 Iteration in groups

 
example codes for comparing spl sql and python(19)

5.2 Early terminated iterative operation

1. Calculate the cumulative sales of each salesperson.

2. Number the orders of each salesperson from 1 and replace ORDERID with corresponding number.

SPL

A B
1 =file(“SALES.csv”).import@tc()
2 =A1.sort(SELLERID,ORDERDATE) /Sorting
3 =A2.(iterate(~~+AMOUNT,0;SELLERID)) /Cumulative sales
4 =A2.derive(iterate(~~+AMOUNT,0;SELLERID):CUM_AMOUNT) /Cumulative sales
5 =A2.derive(cum(AMOUNT;SELLERID):CUM_AMOUNT) /Cumulative sales
6 =A2.(iterate(~~+#,0;SELLERID)) /Number the orders of salesperson
7 =A2.run(ORDERID=iterate(~~+#,0;SELLERID)) /Number the orders of salesperson
8 =A2.run(ORDERID=seq(SELLERID)) /Number the orders of salesperson

A3: The iterate(x,a;G) is to calculate the cumulative sales. In this function, G is the name of field. When G changes, it will record the current ~~ and then start a new iteration again, which is equivalent to A2.group@o(SELLERID).conj(~.(iterate(~~+AMOUNT,0))).

A4: Take the result of iterate() as a new column in A2.

A5: The cum(x;G) is to calculate the cumulative sales. When G changes, it will start to calculate again and produce the same result as A4.

A6: The iterate() is to number by SELLERID from 1. When SELLERID changes, it will start to number from 1 again.

A7: Change the ORDERID of A2 to the value of A6.

A8: The seq()has the same role as the iterate() in A6, and will produce the same result as A7.

SQL

SQL doesn’t provide a similar method.

Python

Python doesn’t provide a similar method.

Example codes for comparing SPL, SQL, and Python

example codes for comparing spl sql and python(19)
 
 •  13 View  •