Simplifies SQL-style Computations - In-group Computation

During developing the database applications, we often need to perform computations on the grouped data in each group. For example, list the names of the students who have published papers in each of the past three years; make statistics of the employees who have taken part in all previous training; select the top three days when each client gets the highest scores in a golf game; and the like. To perform these computations, SQL needs multi-layered nests, which will make the code difficult to understand and maintain. By contrast, esProc is better at handling this kind of in-group computation, as well as easy to integrate with Java and the reporting tool. We�ll illustrate this through an example.

To complete the task, first select the sales data of the year of 2013, and then group the data by the month and, in each group, select the clients whose monthly sales amount is in the top 20. Finally, compute the intersection of these groups.

Note: The code for filtering in A2 can also be written in SQL.

In order to compute every client�s sales amount of each month, we need to group the data a second time by clients. In esProc, we just need to perform this step by looping the data of each month and group it respectively. A.(x) can be used to execute the loop on members of a certain group, with no necessity for loop code.

A4ï¼ï¿½=A3.(~group(Client))

It can be seen that each group of data in March contains the sales data of a certain client.

Please note ~ in the above code represents each member of the group, and the code written with ~ is called in-group computation code, like the above-mentioned ~.group(Client).

Next, select the clients whose rankings of each month are in the top 20 through the in-group computation:

A5ï¼ï¿½=A4.(~.top(-sum(Amount);20))

A6ï¼ï¿½=A5.(~.new(Client,sum(Amount):MonthAmount))

Finally, list the field Client of each subgroup and compute the intersection of the subgroups:

A7ï¼ï¿½=A6.(~.(Client))

A8ï¼ï¿½=A7.isect()

As can be seen from this problem, esProc can easily realize the in-group computation, including the second group and sort, on the structured data, make the solving way more visually, and display a clear and smooth data processing in each step. Moreover, the operations, like looping members of a group or computing intersection, become easier in esProc, which will reduce the amount of code significantly.