The esProc table sequence is a structured two-dimensional table, having concepts of field, record, primary key and reference. These concepts originate from the data table of relational database. A table sequence is also an explicit set of genericity and orderliness, which can perform structured data computing more flexibly. A record sequence is the reference of table sequences. They are closely related and their usages are basically the same. The article will explain their basic computations from aspects of access, loop function, aggregate function and sets operations.
1. Access
1.1 Creation
In thecellset below, read two-dimensional structured data from a file, create the table sequence object and store it in cell A1. Create the record sequence object by referring to A1 and store it in B1:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
The following is the table sequence in A1 after computing. Only part of the data is displayed in the window. You can drag the scrollbar on the right to check the complete records:
The record sequence in B1 is as follows:
Note: The table sequence object can be created based on a database or a file, or be created by inserting records into an empty object. A record sequence originates from a table sequence but it doesn’t store physical records. It only stores the references of some records in the table sequence.
1.2 Access field values
In the cellset below, get the field PName of the twentieth recordin table sequence object A1 and store it in cell A2, andget the field PName of the second record in record sequence object B1 and store it in cell B2:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =A1(20).PName | =B1(2).PName |
The results in A2 and B2 are as follows:
It can be seen that, as the second record in B1 is identical to the twentieth record in A1, so A2 and B2 have the same computed results. As can also be seen from the expressions of A2and B2, both table sequence and record sequence have completely the same syntax for accessing fields.
A field name can be replaced by the field’s sequence number and the result won’t change. For instance:
=A1(20).#3. Because this kind of replacement is employed universally in esProc, we won’t go into details about it.
1.3 Access column data
In the cellset below, fetch column PName according to the column name from table sequence A1 and store it in A2, and again from table sequence A1, fetch column PName and column Amount according to the column names and store them in B2. The record sequence and table sequence have the same expression when accessing the column data, so only the latter is selected for our illustration:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =A1.(PName) | =A1.new(PName,Amount) |
The results in A3 and B3 are as follows:
Using the syntax of T.(x), you can only fetch one column of data, and the computed result is a sequence without structured column name. With T.new() function, however, you can fetch one or more columns of data and the computed result is a table sequence with structured column names.
Whether the computing object is a table sequence or a sequence, both T.new() function and A.new() function will create a new table sequence. This means the computed result of B1.new(PName, Amount) is also a table sequence.
1.4 Access row data
In the following, fetch the first two records from table sequence A1 according to row number and store them in A2, and fetch the first two records from table sequence B1 according to row number and store them in B2. Both the record sequence and table sequence in this example have the same expression for accessing row data:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =A1([1,2]) | =B1([1,2]) |
The result of A2 is as follows:
The result of B2 is as follows:
2.Loop functions
Loop functions can compute each record of a table sequence/record sequence, express complex loop statements with simple function. For instance, select is used to make query, sort to sort, id to merge repeated records, pselect to fetch sequence numbers of eligible records and maxif to read the maximum value from eligible records. Here the most basic ones – select function and sort function- will be illustrated. For more information about loop functions, please refer to esProc Program: Loop Operation.
2.1 Query
In the following cellset, query out records whose Amount field is greater than or equal to 20,000 and whose Date is the month of March. As both record sequence and table sequence have the same expression for querying data, only the latter is selected for our illustration:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>=20000 && month(Date)==3) |
The result of B1 is as follows:
Whether the computing object is a table sequence or a record sequence, the computed result of select function will always be a record sequence, that is, the references of records instead of the physical records.
2.2 Sort
In the following cellset, sort records in ascending order according to SalesID field, and for the recordshaving the same SalesID, sort them in descending order according to Date field. The record sequence and table sequence have the same expression for sorting. Here we’ll take the record sequence in B1 as an example:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =B1.sort(SalesID,Date:-1) |
The computed result is:
Whether the computing object is a table sequence or a record sequence, the computed result of sort function will always be a record sequence. In fact, most of the functions for table sequences and record sequences can be employed universally unless the records are modified.
3. Aggregate function
3.1 Seek maximum value
Seek the maximum value of Amount field. The record sequence and table sequence in this example have the same expression, so we only take table sequence A1 as an example:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =A1.max(Amount) |
The computed result of A2 is as follows:
Similar functions include min(minimum value), sum(summation), avg(average value), count(count), etc.
3.2 Sum by grouping
In the following cellset, sum Amount in each group of data according to SalesIDand the month, and count orders of each group. The record sequence and table sequence in this example have the same expression, so we only take the table sequence A1 as an example:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>20000) |
| 2 | =A1.groups(SalesID, month(Date); sum(Amount), count(~)) |
The computed result of A2 is as follows:
Note: groups function will create a new table sequence.“~” in expression count(~) represents the current group. count(~) can also be written as count(ID). Besides, we don’t designate field names of computed results in writing code, so default field names like month(Date)will appear. A colon could be used in designating field names, such as =A1.groups(SalesID, month(Date):Month;sum(Amount),count(ID)).
4. Operations between sets
Operations between sets include intersection “^”, union “&”, complement “\” and concatenate “|”, etc.
4.1 Intersection and union operations
In the following cellset, store orders whose Amount is greater than and equal to 20,000 in the month of March in A2, and store those whose SalesID is equal to 540 or 992 in B2. Now seek the intersection and complement of A2 and A3, and store results respectively in A4 and A5:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | |
| 2 | =A1.select(Amount>=20000 && month(Date)==3) | =A1.select(SalesID==540 || SalesID==992) |
| 3 | =A2^B2 | =A2\B2 |
The record sequence in A2 is as follows:
The record sequence in B2 is as follows:
A3 computes the intersection and gets a record sequence as the result:
B3 computes the complement. The result is still a record sequence after members of B2 have been removed from A2:
Let’s move on to the intersection and complement operations between table sequences. First create new table sequences from sequences or record sequences using dup@t() function, and then compute the intersection and the complement:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | |
| 2 | =A1.select(Amount>=20000 && month(Date)==3) | =A1.select(SalesID==540 || SalesID==992) |
| 3 | =A2.dup@t() | =B2.dup@t() |
| 4 | =A3^B3 | =A3\B3 |
The table sequence data in A3 and B3 are the same as the datain A2 and B2 in the previous example.
A4 computes the intersection of A3 and B3. The result is an empty set:
This means, as the set of physical members, different table sequences have different members and the result of intersection operation between them will always be empty. Thus the operation has no practical significance in business.
B4 computes the complement of A3 and B3. As members of two table sequences are always different, the computed result of complement operation is still A3:
Only sets operations between record sequences originating from the same table sequence have practical significance in business. Usually, the intersection and complement operations between different table sequences or record sequences originating from different table sequences make no sense in business.
4.2 Union and concatenation operations
In the following cellset, select some records from the record sequence in B1 to create another record sequence, store orders in which SalesID equals 540 and 992 in A2, and store those in which SalesID equals 540 and 668 in B2. Now seek the union and concatenation of A2 and B2and store the results respectively in A3 and B3:
| A | B | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>=20000 && month(Date)==3) |
| 2 | =B1.select(SalesID==540 || SalesID==992) | =B1.select(SalesID==540 || SalesID==668) |
| 3 | =A2&B2 | =A2|B2 |
The record sequence in A2 is as follows:
The record sequence in B2 is as follows:
A3 computes the union of A2 and B2. Members of A2 and B2 will combine in order and the repeated members will be removed.
B3 computes the concatenation of A2 and B2. Members of A2 and B2 will combine in order and repeated members won’t be removed.
Next let’s look at the the corresponding operations between table sequences:
| A | A | |
| 1 | =file(“Order_Books.txt”).import@t() | =A1.select(Amount>=20000 && month(Date)==3) |
| 2 | =B1.select(SalesID==540 || SalesID==992) | =B1.select(SalesID==540 || SalesID==668) |
| 3 | =A2.dup@t() | =B2.dup@t() |
| 4 | =A3&B3 | =A3|B3 |
The table sequence data in A3 and B3 are the same as the data in A2 and B2 in the previous example.
A4 computes the union of A3 and B3. As members of the two table sequences are totally different, the union operation means a simple union-all of the two, with the same result as that of the concatenation operation:
B4 computes the concatenation of A3 and B3. The result is completely the same as that in A4, which computes the union:
