esProc Program – Loop Computation

In esProc, loop computation works on each member of a sequence, or each record of a table sequence and a record sequence. Now the computation can be completed with simple loop functions, instead of the loop statements.

1. Loop computation

While performing loop computation on a sequence, you can reference sequence members in the parameters of a loop function. “~” represents current members of the sequence, or base members; “#” represents the sequence number of a current member. 

While performing loop computation on a table sequence or a record sequence, you can directly reference the field value of the current record using field name F. Note: In a record sequence, the prerequisite for referencing the value of a certain field F is that all records must have this field F. During referencing, ~.F, r.F or A.F can be used to indicate record r, or record sequence (or table sequence) A, to which the referenced field belongs. 

Special Note: If there is a variable having the same name as the field in the memory, the field cannot be accessed with its simplified form in the loop function. It must be written in its complete form as ~.F or A.F, otherwise an F alone could be interpreted as the namesake variable.

1.1. Create a sequence with results

Compute each member of sequence A by loop and return a new sequence composed of the results. The computation can be realized using function A.(x). For example:

Because there isn’t a loop expression in A2’s function, it will return the table sequence in A1 directly. A3 is to create a new sequence composed of square values of each sequence number. And A4 is to create a new sequence composed of powers of each member. The data in A1, A2, A3 and A4 are as follows: 

Among these results, the sequences in A1 and A2 are the same, while those in A3 and A4 are newly-created with the computed results.

Function A.(x) can also be used to perform computation on a table sequence or a record sequence. In the process, fields of records can be referenced in the expressions, as shown in the following figure:

What is in A1 is a table sequence retrieved from the database: 

A2 computes the state where each employee stays and A3 computes the age of each employee. Data in A2 and A3 are as follows:

Notice that, in a table sequence or a sequence, the result A.(x) returns is a sequence instead of a table sequence and the result contains no field names.

1.2. Return the original sequence after loop is done

You can also use function A.run(x) to perform loop computation on each member of sequence A. Meanwhile, the function will work on expression x in the same way, only to return the original sequence A. For example:

After the program is executed, you can get the same sequence in A1 and A2:

Since run function returns the original sequence rather than the resulting sequence, it is usually used to modify members of a sequence. In this case, for instance, members of the original sequence are doubled.

A.run(x) function is often used to modify records by loop in a table sequence or a sequence. For example:

In A2, run function is used to modify the value of GENDER field to Male and Female and change the format of BIRTHDAY field. After the code is executed, the table sequence in A1 becomes as follows:

1.3. Relative reference

In loop computation, A[i] or ~[i] can be used to reference the member that is i records after the current member in a sequence. i can be a negative number. For example:

A2 computes the sequence consisting of the differences of the current member and the following one in the original sequence in A1: 

That the expression in A3 starts with a > means a call to run function only modifies the original sequence but won’t return it. After execution the sequence in A1 becomes one made up of the accumulated values of the original members: 

Besides relatively referencing a record in a sequence or a record sequence using ~[i], you can also perform the same operation on field F of the record using F[i], which can be expressed as A[i].F. For example:

A2 sorts the table sequence data by BIRTHDAY:

The expressions in A3 and A4 are equal. Both compute the interval of days between the birth date of each employee and that of the employee who is ahead of him/her in age:

Instead, using BIRTHDAY[1] in A3’s expression represents the birth date of an employee who is behind the current employee in age.

Similarly, you can relatively reference multiple records, instead of only one record. In this case, A{a:b}, ~{a:b} can be used to reference a sequence or a record sequence composed of members from the ath member to the bth member after the current one. For example:

A2 counts the number of employees who were born in each year: 

A3 selects the statistic data according to a time range, which includes each year, its previous year and its following year:

In a similar way, you can also relatively reference the fields of multiple records with F{…}. For example, A4 counts the number of employees who were born in a time range of each year, its previous year and its following year:

2. Loop function

A loop function is used to perform a certain kind of computation on each member of a sequence. It is generally written as A.f(x). Actually the aggregate operation uses loop functions too. Which kind of computation a loop function will perform is determined by the function name, such as sum represents summing up and avg represents seeking average, etc.

2.1. Aggregate operation

The aggregate operation performs loop computation on each member of a sequence or a table sequence or a record sequence, and computes result according to what function it is. For example:

A2 sums up members of the sequence using A.sum() function. The result is as follows:

With A.sum() function, A4 computes the average age of all employees in the table sequence. A5 counts the number of the Californian employees. The results of A4 and A5 are as follows:

There are many other loop functions which are also used to perform the aggregate operation, like A.min(),A.max(),A.rank(),A.variance(), etc.

In particular, the aggregate operation in the form of [x₁, x₂,…x_n].f() can also be written as f([x₁, x₂,…x_n]) or f(x₁, x₂,…x_n) on the premise that no ambiguity will be caused about it.

The computed results of A2 and A3 are the same as that of A2 in the previous example. And the result of A5 is the same as that of A4 in the previous example. 

2.2. Integer loop

For operations that need to specify the number of times by loop, a sequence like [1,2,3,…,n] can be used to perform the loop computation. to(n).f(x) can be abbreviated to n.f(x), which is called the integer loop. For example:

A1 sums up the numbers from 1 to 10. The result is as follows:

A3 randomly selects 5 employees and lists their names, as shown in the following:

2.3. Nested Loop

A loop function can be used in a nested form, which means you can perform the loop computation again in the expression. The code is written in a format similar to A₁.f₁(A₂.f₂(x)). In a nested loop function, “~” and “#” represent respectively the current members and the sequence numbers of the inner sequence, while the referencing of outer sequence requires putting the sequence name before the signs, which are written as A.~ and A.#. For example:

A2 groups the data of employees by the year when they were born: 

A3 creates an empty table sequence for storing the computed results. A4 loops the grouped data of each year, computes the total number of male and female employees who were born in this year and insert the results into the resulting table sequence. After the code is executed, you can see the results in A3, as shown below:

2.4. Summarizing loop

In the example in 1.3 Relative reference, run function is used to compute the accumulated values of members in a sequence. But it will change the value of the original sequence. Alternatively, the summarizing loop function – loop – can be used to perform the loop computation with the previous result being referenced in the meantime and without changing the original value. For example:

In a loop function’s expression, ~~ represents the previous result and the original value is set after the semicolon. A2 computes the accumulated values of members in the sequence. A3 computes the result of multiplying the first member by the rest of ones in order. You may have noticed that the original value varies as the type of computation changes. The results in A2 and A3 are as follows:

If it is only the final result that you care about, not the data produced in the process of iteration computation, you can use loops function. For example:

A2 computes the final result of the accumulation. A3 computes the final result of the multiplication. Their results are as follows: