Complete Set-lizing

esProc realizes complete set-lizing, which differs from the simple and rough SQL sets. esProc sets are ubiquitous, and the members can be of any data type or another set.

Complete set-lizing transforms the cumbersome and difficult lower-level calculations into high-level set operations, for example, the intersection, union, complement enable the business staffs to solve analysis problems in a more simple and intuitive way from business perspective. You will find that esProc can greatly simplify the computing complexity on a set related data analysis and solve problems even difficult to SQL agilely.

Omnipresent sets

  • Ubiquity of sets

The set is a widely-applied basic data type. For example, the order paid in this quarter, the list of premium clients (set A1), and the Top 500 enterprise (set B1).

  • Provides the perfect set operations

esProc provides the set operation (intersection, union, compliment, and other operations) that can materialize the abstract basic data, convert them to the business understandable language, and carry out the stepwise computations according to the business requirements. In this way, the complexity is greatly reduced. For example, you can directly use A1^B1 to select out the Top 500 clients among those premium clients.
       By compassion, the stepwise computation is not favored and thus the set is also unsupported by SQL. The high level language cannot support the set operation directly, requiring programmers to implement a great many lines of codes

A
1 =db.query(“select * from Employee”)
2
=A1.select(Gender==”Male”)
 Male employees
3
=A1.select(Birthday>=date(“1970-01-01”))
 Employees born in and after 1970
4
=A2^A3
 Intersection operation to count male employees born in and after 1970
5
=A2&A3
 Union operation to count employees born in and after 1970,and male employees
6
=A2\A3
 Subtraction operation to count male employees who are born before 1970
7 =A4.sum(Salary) Total salary of male employees born in and after 1970
8 =A5.avg(Age) Average age of male employees and employees born in and after 1970
9
=A6.sort(Birthday)
 Sorting by ages of male employees born before1970
Sets reference in field value

  • The esProc fields can also be used to store several records or sets. The field of the primary table in database usually corresponds to several records in the sub tables. Therefore, with the object reference, esProc can easily represent the correlations between the primary table and the sub table.
  • As illustrated below, the primary table Department table has an Employee field. The Employee field of every record point to the several records of sub table Employee table.
  • The cell A4 retrieved a record from Department table. Then, the statements followed can use A4.Employee to access to the set of records in the corresponding sub table of this record.
  • A4.Employee.avg(Age) represents the summarization of Age field of this set.
A
1 =db.query(“select * from Employee”)
2
=db.query(“select * from Department”)
3
>A2.derive(A1.select(Dept==A2.Dept):Employee)
 The new fields is assigned with the reference to the subset from other tables
4
=A2.select@1(Dept==”Sales”)
5 =A4.Employee.avg(Age) To sum up and average the sets of referenced sets,and get 40.75
6
=A2.new(Dept,Employee.count():Quantity)