MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.

I'm not curious

# Basic Computation of esProc Table Sequence and Record Sequence

Published on 09 September 14
579
0
0
esProc table sequence is a structured two-dimensional table, boasting concepts like field, record, primary key and reference. These concepts originate from data table of relational database. A table sequence is also an explicit set of genericity and orderliness, which can make computation of structured data more flexibly. Record sequence is the reference of table sequence. They are closely related and their usages are almost the same. The article will explain their basic computation from aspects of accessing, maintenance, loop function, aggregate function and sets operations.
Accessing
1. Create objects
Description: Read two-dimensional structured data from a file, create table sequence objects and store them in cell A1. Create record sequence objects by referring to A1 and store them in B1.

Codeï¼
A1=file("e:/sales.txt").import@t()
B1=A1.select(Amount>2000)

Results are: the first ten records in A1(table sequence):
The first ten records in B1(record sequence):
Note: table sequence can be created according to a database or a file, or be created by inserting records within an empty object. A record sequence originates from a table sequence but it doesnât store physical records. It only stores references of some records in the table sequence.

2. Access field values
Description: Get Client field of the fifth record in from A1 - the table sequence objects - and store it in cell A2. Get Client field of the first record from B1 - the record sequence objects - and store it in cell B2.

Codeï¼
A2=A1(5).(Client) /computed resultï¼DSG
B2=B1(1).(Client) /computed resultï¼DSG
Noteï¼
1. Since the first record in B1 correspond to the fifth record in A1, both of them have the same computed results.
2. Both table sequence and record sequence have completely same syntax for accessing field.
3. A field name can be replaced by the fieldâs sequence number and the result wonât change. For instance: A1(5).#2. Because this kind of replacement is employed universally in esProc, we wonât go into details about it.

4. Access column data
Description: Fetch column Client according to column name and store it in A3. Fetch column Client and column Amount according to column names and store them in A4. The record sequence and table sequence in this example have the same expression, and only the latter is selected for our illustration.

Codeï¼
A3=A1.(Client)
A4=A1.new(Client,Amount)
Results areï¼
A3ï¼
A4ï¼
Noteï¼
1. With the syntax âtable sequence.(field name)â , only a column of data can be fetched, and the result is sequence without structured column name. With new function, however, a column or more columns of data can be fetched, and the result is table sequence with structured column name.
2. Whether the computing object is a table sequence or a sequence, new function will create a new table sequence, that is, the computed result of B1.new(Clientï¼Amount) is also a table sequence.

4. Access row data
Description: Fetch the first two records according to row number. The record sequence and table sequence in this example have the same expression.

Codeï¼ =A1([1,2])

Results areï¼
Maintenance
Description: Append a record r1 to table sequence A1, insert r2, of which only the OrderID field has value and the other fields are null, into the second row. Execute the same operation in record sequence B1.
Table sequenceï¼
A6=A1.insert(0,152:OrderID,"CA":Client,5:SellerId,2961.40:Amount,"2010-12-5 0:00:00":OrderDate)
A7=A1.insert(2,153:OrderID)

Record sequenceï¼
B6=create(OrderID,Client,SellerId,Amount,OrderDate) /empty table sequence B6
B7=B6.record([152,"CA",5,2961.40,"2010-12-5 00:00:00"]) /insert the first record r1 into B6
B9=B6.record([153,,,,]) /insert the second record r2 into B6
B10=B1.insert(2,B6(2)) /insert r2 into the second row of B1

Noteï¼
1. The syntax of table sequence and record sequence has a lot of difference when new records are added to them. What is added to table sequence is physical records, and insert function can be directly used in table sequence A1. While for record sequence, only recordsâ references can be added to it, so the physical records must be there before making any references. In the example, the physical records are stored in B6(or A1 and B1).
2. After those computations are done, the records in B1 originate from two table sequences: A1 and B6.
3. If insert functionâs first parameter is zero, add records at the end; if not, insert records into designated places. The rule applies in both table sequence and record sequence.

6. Delete records
Description: delete the record in the second row.
Table sequenceï¼=A1.delete(2)
Record sequenceï¼=B1.delete(2)

Noteï¼What is deleted in table sequence is physical records; while those deleted in record sequence are references of records and the original table sequence wonât be affected with this operation.

7. Modify records
Description: Change the Amount field in the second record to 2000, and the OderDate field to 2009-07-01 00:00:00.

Table sequenceï¼=A1(2).modify(2000:Amount,datetime("2009-07-01 00:00:00"):OrderDate)
Record sequenceï¼Record modification is forbidden in a sequence, it can only be executed in the original table sequence.

Noteï¼In the example, modify function executes modification of a single record. But, it can do modification in batches in a table sequence.

Description: Add two new fields - type and orderYear, in which type is null and orderYear is derived from the year in original OderDate field. The record sequence and table sequence in this example have the same expression.

Codeï¼=A1.derive(:type, year(OrderDate):orderYear)
Noteï¼Whether the computing object is a table sequence or a sequence, derive function will create a new table sequence.

Loop function

Loop function can compute each record of table sequence/record sequence, express complex loop statement with simple function. For instance, select is used to make query, sort to sequence, id to merge repeated records, pselect to fetch sequence number of eligible records and max if to read the maximum value from eligible records. Here the most basic ones - select function and sort function- will be illustrated.

9. Query
Description: Query out records whose Amount field is greater than or equal to 2000 and whose OrderDate is the year 2009. The record sequence and table sequence in this example have the same expression.
Table sequenceï¼=A1.select(Amount>=2000 && year(OrderDate)==2009)
Results areï¼
Noteï¼Whether the computing object is table sequence or record sequence, the computed result of select function will always be a record sequence, that is, the references of records instead of physical records.

10. Sort
Description: Sort records in an ascending order according to SellerID fields. If the results of SellerID are the same, sort records in a descending order according to OrderDate fields. The record sequence and table sequence in this example have the same expression.
Codeï¼=A1.sort(SellerId,OrderDate:-1)
Results areï¼
Noteï¼Whether the computing object is table sequence or record sequence, the computed result of sort function will always be a record sequence. In fact, most of the functions for table sequence and record sequence can be employed universally unless the records are modified.

Aggregate function
11. Seek maximum value
Descriptionï¼Seek the maximum value of Amount field. The record sequence and table sequence in this example have the same expression.
Codeï¼A1.max(Amount)

Computed resultï¼A2=29600.0

Noteï¼Similar functions include min(minimum value), sum(summation), avg(average value), count(count), etc.

12. Sum by grouping
Descriptionï¼Sum Amount in each group of data according to SellerID and the year, and count orders of each group. The record sequence and table sequence in this example have the same expression.

Codeï¼A1.groups(SellerId,year(OrderDate);sum(Amount),count(~))

Results areï¼
Noteï¼
1. groups function will create a new table sequence.
2. â~â in expression count(~) represents the current group. count(~) can also be written as count(OrderID). Besides, we donât designate field names of computed results in writing code, so default field names like year(OrderDate) will appear. A colon could be used in designated field names, such as =A1.groups(SellerId,year(OrderDate):OrderYear;sum(Amount),count(OrderID)) .
Operations between sets
Operations between sets include intersection â^â, union â&â, complement â\â and concatenate â|â, etc.

13. Intersection and union operations
Descriptionï¼Store orders whose Amount is greater than and equal to 2000 in the year 2009 in A2, and store those whose SellerID is equal to 1 or 2 in A3. Now seek intersection and complement of A2 and A3, then store results respectively in A4 and A5.

Record sequenceï¼
A2=A1.select(Amount>=2000 && year(OrderDate)==2009) /A2 is record sequence
A3=A1.select(SellerId==1 || SellerId==2) /A3 is record sequence
A4=A2^A3 /intersection, the result is record sequence
A5=A2\A3 /complement, remove members of A3 from A2
Table sequenceï¼
A6=A2.new(OrderID,Client,SellerId,Amount,OrderDate)/table sequence created from A2
A7=A3.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A3
A8=A6^A7 /Intersection, result is empty. A table sequence is a collection of physical members. Different table sequences always have different members, so the intersection operation of two table sequences is definitely empty. This has no practical significance in business.
A9=A6\A7 /Complement. Because members of two table sequences are always different, the computed result of complement is still A6.

Noteï¼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.

14. Union and concatenate operations
Descriptionï¼Store orders of which SellerID equals 2 and 10 in A2, and store those of which SellerID equals 3 and 10 in A3. Now seek the union and concatenate of A2 and A3 and store results respectively in A4 and A5.

Record sequenceï¼
A2=A1.select(SellerId==2 || SellerId==10)
A3=A1.select(SellerId==3 || SellerId==10)
A4=A2&A3 /Union. Members of A2 and A3 will combine in order and repeated records will be removed.
A5=A2|A3 /concatenate. Members of A2 and A3 will combine in order and repeated records wonât be removed.

Table sequence:

A6=A2.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A2

A7=A3.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A2

A8=A6&A7 /Union. Members of two table sequences are completely different, so the union operation means a simple union-all of the two.
A9=A6|A7 /concatenate

esProc table sequence is a structured two-dimensional table, boasting concepts like field, record, primary key and reference. These concepts originate from data table of relational database. A table sequence is also an explicit set of genericity and orderliness, which can make computation of structured data more flexibly. Record sequence is the reference of table sequence. They are closely related and their usages are almost the same. The article will explain their basic computation from aspects of accessing, maintenance, loop function, aggregate function and sets operations.

Accessing

1. Create objects

Description: Read two-dimensional structured data from a file, create table sequence objects and store them in cell A1. Create record sequence objects by referring to A1 and store them in B1.

Codeï¼

A1=file("e:/sales.txt").import@t()

B1=A1.select(Amount>2000)

Results are: the first ten records in A1(table sequence):

The first ten records in B1(record sequence):

Note: table sequence can be created according to a database or a file, or be created by inserting records within an empty object. A record sequence originates from a table sequence but it doesnât store physical records. It only stores references of some records in the table sequence.

2. Access field values

Description: Get Client field of the fifth record in from A1 - the table sequence objects - and store it in cell A2. Get Client field of the first record from B1 - the record sequence objects - and store it in cell B2.

Codeï¼

A2=A1(5).(Client) /computed resultï¼DSG

B2=B1(1).(Client) /computed resultï¼DSG

Noteï¼

1. Since the first record in B1 correspond to the fifth record in A1, both of them have the same computed results.

2. Both table sequence and record sequence have completely same syntax for accessing field.

3. A field name can be replaced by the fieldâs sequence number and the result wonât change. For instance: A1(5).#2. Because this kind of replacement is employed universally in esProc, we wonât go into details about it.

4. Access column data

Description: Fetch column Client according to column name and store it in A3. Fetch column Client and column Amount according to column names and store them in A4. The record sequence and table sequence in this example have the same expression, and only the latter is selected for our illustration.

Codeï¼

A3=A1.(Client)

A4=A1.new(Client,Amount)

Results areï¼

A3ï¼

A4ï¼

Noteï¼

1. With the syntax âtable sequence.(field name)â , only a column of data can be fetched, and the result is sequence without structured column name. With new function, however, a column or more columns of data can be fetched, and the result is table sequence with structured column name.

2. Whether the computing object is a table sequence or a sequence, new function will create a new table sequence, that is, the computed result of B1.new(Clientï¼Amount) is also a table sequence.

4. Access row data

Description: Fetch the first two records according to row number. The record sequence and table sequence in this example have the same expression.

Codeï¼ =A1([1,2])

Results areï¼

Maintenance

Description: Append a record r1 to table sequence A1, insert r2, of which only the OrderID field has value and the other fields are null, into the second row. Execute the same operation in record sequence B1.

Table sequenceï¼

A6=A1.insert(0,152:OrderID,"CA":Client,5:SellerId,2961.40:Amount,"2010-12-5 0:00:00":OrderDate)

A7=A1.insert(2,153:OrderID)

Record sequenceï¼

B6=create(OrderID,Client,SellerId,Amount,OrderDate) /empty table sequence B6

B7=B6.record([152,"CA",5,2961.40,"2010-12-5 00:00:00"]) /insert the first record r1 into B6

B9=B6.record([153,,,,]) /insert the second record r2 into B6

B10=B1.insert(2,B6(2)) /insert r2 into the second row of B1

Noteï¼

1. The syntax of table sequence and record sequence has a lot of difference when new records are added to them. What is added to table sequence is physical records, and insert function can be directly used in table sequence A1. While for record sequence, only recordsâ references can be added to it, so the physical records must be there before making any references. In the example, the physical records are stored in B6(or A1 and B1).

2. After those computations are done, the records in B1 originate from two table sequences: A1 and B6.

3. If insert functionâs first parameter is zero, add records at the end; if not, insert records into designated places. The rule applies in both table sequence and record sequence.

6. Delete records

Description: delete the record in the second row.

Table sequenceï¼=A1.delete(2)

Record sequenceï¼=B1.delete(2)

Noteï¼What is deleted in table sequence is physical records; while those deleted in record sequence are references of records and the original table sequence wonât be affected with this operation.

7. Modify records

Description: Change the Amount field in the second record to 2000, and the OderDate field to 2009-07-01 00:00:00.

Table sequenceï¼=A1(2).modify(2000:Amount,datetime("2009-07-01 00:00:00"):OrderDate)

Record sequenceï¼Record modification is forbidden in a sequence, it can only be executed in the original table sequence.

Noteï¼In the example, modify function executes modification of a single record. But, it can do modification in batches in a table sequence.

Description: Add two new fields - type and orderYear, in which type is null and orderYear is derived from the year in original OderDate field. The record sequence and table sequence in this example have the same expression.

Codeï¼=A1.derive(:type, year(OrderDate):orderYear)

Noteï¼Whether the computing object is a table sequence or a sequence, derive function will create a new table sequence.

Loop function

Loop function can compute each record of table sequence/record sequence, express complex loop statement with simple function. For instance, select is used to make query, sort to sequence, id to merge repeated records, pselect to fetch sequence number of eligible records and max if to read the maximum value from eligible records. Here the most basic ones - select function and sort function- will be illustrated.

9. Query

Description: Query out records whose Amount field is greater than or equal to 2000 and whose OrderDate is the year 2009. The record sequence and table sequence in this example have the same expression.

Table sequenceï¼=A1.select(Amount>=2000 && year(OrderDate)==2009)

Results areï¼

Noteï¼Whether the computing object is table sequence or record sequence, the computed result of select function will always be a record sequence, that is, the references of records instead of physical records.

10. Sort

Description: Sort records in an ascending order according to SellerID fields. If the results of SellerID are the same, sort records in a descending order according to OrderDate fields. The record sequence and table sequence in this example have the same expression.

Codeï¼=A1.sort(SellerId,OrderDate:-1)

Results areï¼

Noteï¼Whether the computing object is table sequence or record sequence, the computed result of sort function will always be a record sequence. In fact, most of the functions for table sequence and record sequence can be employed universally unless the records are modified.

Aggregate function

11. Seek maximum value

Descriptionï¼Seek the maximum value of Amount field. The record sequence and table sequence in this example have the same expression.

Codeï¼A1.max(Amount)

Computed resultï¼A2=29600.0

Noteï¼Similar functions include min(minimum value), sum(summation), avg(average value), count(count), etc.

12. Sum by grouping

Descriptionï¼Sum Amount in each group of data according to SellerID and the year, and count orders of each group. The record sequence and table sequence in this example have the same expression.

Codeï¼A1.groups(SellerId,year(OrderDate);sum(Amount),count(~))

Results areï¼

Noteï¼

1. groups function will create a new table sequence.

2. â~â in expression count(~) represents the current group. count(~) can also be written as count(OrderID). Besides, we donât designate field names of computed results in writing code, so default field names like year(OrderDate) will appear. A colon could be used in designated field names, such as =A1.groups(SellerId,year(OrderDate):OrderYear;sum(Amount),count(OrderID)) .

Operations between sets

Operations between sets include intersection â^â, union â&â, complement â\â and concatenate â|â, etc.

13. Intersection and union operations

Descriptionï¼Store orders whose Amount is greater than and equal to 2000 in the year 2009 in A2, and store those whose SellerID is equal to 1 or 2 in A3. Now seek intersection and complement of A2 and A3, then store results respectively in A4 and A5.

Record sequenceï¼

A2=A1.select(Amount>=2000 && year(OrderDate)==2009) /A2 is record sequence

A3=A1.select(SellerId==1 || SellerId==2) /A3 is record sequence

A4=A2^A3 /intersection, the result is record sequence

A5=A2\A3 /complement, remove members of A3 from A2

Table sequenceï¼

A6=A2.new(OrderID,Client,SellerId,Amount,OrderDate)/table sequence created from A2

A7=A3.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A3

A8=A6^A7 /Intersection, result is empty. A table sequence is a collection of physical members. Different table sequences always have different members, so the intersection operation of two table sequences is definitely empty. This has no practical significance in business.

A9=A6\A7 /Complement. Because members of two table sequences are always different, the computed result of complement is still A6.

Noteï¼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.

14. Union and concatenate operations

Descriptionï¼Store orders of which SellerID equals 2 and 10 in A2, and store those of which SellerID equals 3 and 10 in A3. Now seek the union and concatenate of A2 and A3 and store results respectively in A4 and A5.

Record sequenceï¼

A2=A1.select(SellerId==2 || SellerId==10)

A3=A1.select(SellerId==3 || SellerId==10)

A4=A2&A3 /Union. Members of A2 and A3 will combine in order and repeated records will be removed.

A5=A2|A3 /concatenate. Members of A2 and A3 will combine in order and repeated records wonât be removed.

Table sequence:

A6=A2.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A2

A7=A3.new(OrderID,Client,SellerId,Amount,OrderDate) /table sequence created from A2

A8=A6&A7 /Union. Members of two table sequences are completely different, so the union operation means a simple union-all of the two.

A9=A6|A7 /concatenate

This blog is listed under Development & Implementations and Data & Information Management Community

Related Posts:

## RSeq

You may also be interested in
Diana

on 17 March 17
Feriha

on 12 October 16
David

on 16 October 15
David

on 16 October 15
Machine

on 23 December 14