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

Simplifies SQL-style Computations ΓΆ€“ Records Corresponding to Max Value

Published on 07 November 14
531
0
0

In developing database applications, usually it is the records corresponding to the max/min value that we need to retrieve, instead of the value itself. For example, the occasion in which each employee gets his/her biggest pay raise; the three lowest scores ever got in golf; the five days in each month when each product gets its highest sales amount; and so on. As the max function of SQL can only retrieve the max value, instead of the records to which the max value corresponds, it is quite complicated to handle the computation in SQL with the aid of some advanced techniques, such as window functions or the nested sub-queries or keep/top/row number. If multi-layered grouping or relations are involved, the computation will become even more complicated.

With the top function in esProc, the records corresponding to the max value can be retrieved and the computation becomes much easier. The following is such an example.

The database table golf contains the scores of members in a golf club. Please select the best three scores each member has ever got. Part of the data is as follows:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 1
The code written in esProc:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 2
A1ï¼ Retrieve data from the database. If the data come from a structured text file, the following equivalent code can be used: =file("\\golf").import@t(). Click the cell and we can check the retrieving result:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 3
A2ï¼=A1.group(User_ID), i.e., group the result of A1. The result is as follows:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 4
As shown in the above figure, the data have been separated into multiple groups by User_ID and each row is a group. Click the blue hyperlink and members of the group will be shown as follows:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 5
A3ï¼=A2.(~.top(-Score;3)). The code is to compute the records of each group of data whose field Score is in the top three. Here ~ represents each group of data. ~.top() represents that top function will work on every group of data in turn. The top function can retrieve the N biggest/smallest records from a data set. For example, top(Score;3) represents sorting by Score in ascending order and fetching the first three records (i.e. min values); top(-Score;3) represents sorting in descending order and fetching the first three records (i.e. max values). The result of this step is as follows:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 6
A4ï¼=A3.union(), which means union data of every group. The result is as follows:
Simplifies SQL-style Computations â Records Corresponding to Max Value - Image 7

In the above, the computation is performed step by step. But the steps can be integrated into one for the convenience of maintenance and debugging: db.query("select * from golf").group(User_ID). (~.top(-Score;3)).union().

The method with which a Java program calls esProc is similar to that with which it calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents
In developing database applications, usually it is the records corresponding to the max/min value that we need to retrieve, instead of the value itself. For example, the occasion in which each employee gets his/her biggest pay raise; the three lowest scores ever got in golf; the five days in each month when each product gets its highest sales amount; and so on. As the max function of SQL can only retrieve the max value, instead of the records to which the max value corresponds, it is quite complicated to handle the computation in SQL with the aid of some advanced techniques, such as window functions or the nested sub-queries or keep/top/row number. If multi-layered grouping or relations are involved, the computation will become even more complicated.

With the top function in esProc, the records corresponding to the max value can be retrieved and the computation becomes much easier. The following is such an example.

The database table golf contains the scores of members in a golf club. Please select the best three scores each member has ever got. Part of the data is as follows:

Simplifies SQL-style Computations â

The code written in esProc:

Simplifies SQL-style Computations â

A1ï¼ Retrieve data from the database. If the data come from a structured text file, the following equivalent code can be used: =file("\\golf").import@t(). Click the cell and we can check the retrieving result:

Simplifies SQL-style Computations â

A2ï¼=A1.group(User_ID), i.e., group the result of A1. The result is as follows:

Simplifies SQL-style Computations â

As shown in the above figure, the data have been separated into multiple groups by User_ID and each row is a group. Click the blue hyperlink and members of the group will be shown as follows:

Simplifies SQL-style Computations â

A3ï¼=A2.(~.top(-Score;3)). The code is to compute the records of each group of data whose field Score is in the top three. Here ~ represents each group of data. ~.top() represents that top function will work on every group of data in turn. The top function can retrieve the N biggest/smallest records from a data set. For example, top(Score;3) represents sorting by Score in ascending order and fetching the first three records (i.e. min values); top(-Score;3) represents sorting in descending order and fetching the first three records (i.e. max values). The result of this step is as follows:

Simplifies SQL-style Computations â

A4ï¼=A3.union(), which means union data of every group. The result is as follows:

Simplifies SQL-style Computations â

In the above, the computation is performed step by step. But the steps can be integrated into one for the convenience of maintenance and debugging: db.query("select * from golf").group(User_ID). (~.top(-Score;3)).union().

The method with which a Java program calls esProc is similar to that with which it calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents

This blog is listed under Data & Information Management Community

Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top