esProc Simplifies SQL-style Computations – Records Corresponding to Max Value

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:

esProc_sql_maxvalue_1

The code written in esProc:

esProc_sql_maxvalue_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:

esProc_sql_maxvalue_3

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

esProc_sql_maxvalue_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:

esProc_sql_maxvalue_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:

esProc_sql_maxvalue_6

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

esProc_sql_maxvalue_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

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in SQL-related Puzzle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s