support

Monthly Top N Customers by Sales Amount

Target:

According to the contract table in database, compute in a Java application: Monthly top N quality customers by sales amount in a specified year.

esProc_demo_monthly_topn_1

1. Implementing the data-computing layer in esProc

A1: Execute SQL to retrieve necessary columns from contract table.

esProc_demo_monthly_topn_2

A2: filter out the data of specified year based on A1.

esProc_demo_monthly_topn_1

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.

esProc_demo_monthly_topn_4

For example, click row 2 (February):

esProc_demo_monthly_topn_5

A4:For data within each group, group it by customer again, and sum the sales amount. Each member on the right represents a month.

esProc_demo_monthly_topn_6

A5: Sort data in descending order by sales amount within each group, and click row 2 to view the detailed data.

esProc_demo_monthly_topn_7

A6:Filter out the Top N records from each group (Assuming that N=10). Check row 2.

esProc_demo_monthly_topn_8

A7:Fetch the Client field from each group. Check row 2.

esProc_demo_monthly_topn_9

A8: Find the intersection of the groups to get the top N customers of each month.

esProc_demo_monthly_topn_10

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.

esProc_demo_monthly_topn_11

Java applications can invoke esProc to retrieve the results via JDBC, as shown below:

Class.forName(“com.esproc.jdbc.InternlDriver”);
Connection con=DriverManager.getConnection(“jdbc:esproc:loca://”);
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.

esProc_demo_monthly_topn_12

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:

esProc_demo_monthly_topn_13

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..

esProc_demo_monthly_topn_14

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:

esProc_demo_monthly_topn_15

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:

esProc_demo_monthly_topn_16

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:

esProc_demo_monthly_topn_17

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.

esProc_demo_monthly_topn_18

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.

esProc_demo_monthly_topn_19

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.

esProc_demo_monthly_topn_20

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.

esProc_demo_monthly_topn_21

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.

esProc_demo_monthly_topn_22

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.

esProc_demo_monthly_topn_23

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.

esProc_demo_monthly_topn_24

In addition, database computations are often related with sequences, as with A6 that filters away records whose serial numbers are greater than N.

esProc_demo_monthly_topn_25

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.