According to the contract table in database, compute in a Java application: Monthly top N quality customers by sales amount in a specified year.
1. Implementing the data-computing layer in esProc
A1: Execute SQL to retrieve necessary columns from contract table.
A2: filter out the data of specified year based on A1.
A3: Group data by month. Each row on the right represents a group, of which the blue indicates that you can view the group members.
For example, click row 2 (February):
A4：For data within each group, group it by customer again, and sum the sales amount. Each member on the right represents a month.
A5: Sort data in descending order by sales amount within each group, and click row 2 to view the detailed data.
A6：Filter out the Top N records from each group (Assuming that N=10). Check row 2.
A7：Fetch the Client field from each group. Check row 2.
A8: Find the intersection of the groups to get the top N customers of each month.
Up to now, target is done! Next is an additional step for returning the result set to Java code.
A10: The result can be exported to the main application via JDBC by result statements. There can be multiple result statements to return multiple result sets.
Java applications can invoke esProc to retrieve the results via JDBC, as shown below:
PreparedStatement st=con. prepareStatement(“call p2(?,?)”);
Now we introduce the syntax features of codes mentioned above, including:
a. Grid-style coding
b. Smart scripts for database computing
c. Complete set orientation
d. Real grouping
e. Powerful loop functions
2. Feature a: Grid-style coding
Different from traditional text-based scripts, esProc scripts are written in the grids. Cell names are the natural variable names without being defined and intuitive references of the previous calculation results are allowed.
Traditional scripts require a cumbersome “variable naming conventions”, and they are not intuitive to find and call.
Grid-style coding matches well with step-by-step computing mode. You can view the current result intuitively, then decide the next step of algorithm according to the current result and go on with the script by referencing the previous results. In this way, a complex object can be divided into simple steps, as indicated by the pic below:
SQL does not advocate step-by-step computing mode. There’s no choice but to produce all the code together. The code is, therefore, lengthy and error-prone.
3. Feature b: Smart scripts for database computing
esProc can run SQL directly( as A1 in the below pic indicates ).It supports filter operation(A2), group(A4) and sort(A5) on data from SQL, Excel, Txt etc..
4. Feature c: Complete set orientation
esProc produces more convenient script for computing structured data than SQL does. In addition to the step-by-step operation for complex targets, esProc realizes through set orientation, which is not available in SQL.
In the pic below, you can find that A7 is a set with 12 members. Each member is also a set, e.g. the first three months are as follows:
So, A7 is a set of subsets, which easily expresses “Monthly top N customers.”
But as SQL is not completely set-oriented, it is hard to express such an concept in it.
To compute “top N customers with highest sales amount in each month”, just find the intersection of these sets, as shown in the pic below:
Since SQL lacks the explicit set data type, you can’t compute intersection directly like this.
You can also find the difference between A3 and A7. Members of A3 are structured records. The following is the first member:
Members of a set can be of simple data type, sets or even structured records. The complete set orientation enables easy solutions to complex computations related with sets.
5. Feature d：Real grouping
Grouping does not necessarily involve summarizing. For example, A3 simply group data without making a monthly summary.
The intention of data grouping is to divide data into multiple data sets. But SQL will summarize whenever there is a group operation, because it hasn’t the set data type. So, it is not the grouping in the pure sense.
esProc, however, can realize the true grouping.
The real grouping can express “a second grouping on grouped data”, for example, A4 groups data in each group by Client.
Note: ”~” stands for the current member, that is, every month. “~” is often used in loop functions.
Real grouping is also reflected in computations after a group operation, for example, the sort operation for each group in A5 and the data querying for each group in A6.
SQL does not provide the most basic grouping operation, making the processing of grouped data much more difficult.
6. Feature e: Powerful loop functions
A function that enables handling records one by one is called a Loop function. In this sense, most esProc functions can be considered as loop functions when performing database queries.
Let’s see A2, “select” is the basic loop function to perform filtering through each member of A1 (each record).
Sometimes, you need to operate on “members of a member”, for example, in A5 you sort the records for each month in A4. See the pic below.
Then you can use “~” to represent every month. The “~” Can be nested, such as the statement in A4 in the pic below. Here data is grouped by Client for each member in A3 and then aggregated.
Let’s make it clearer. The expression “A3.(~.group(Client; ~.sum(Quantity*Amount)) ) ” is computed as below. You see that “~ “enables easy access to each member of a set, and thus easy solutions to many complex issues.
In addition, database computations are often related with sequences, as with A6 that filters away records whose serial numbers are greater than N.
Similar to “~”, “#” indicates the serial number of each member within the set. With an ordered set, you can easily solve problems such as top N, comparison on year-on-year basis, and link relative ratio etc. With “~”, “#” and loop functions, you can avoid most of the complex Loop / cursor statements, making them very suitable for database computing.
Through a policy of thorough set orientation and data ordering, esProc is capable of handling most of the database computations.