Aggregate Data by Specified Numbers of Rows with esProc

Problem source: http://bbs.csdn.net/topics/391014229

Below is data from SL field of table tb1:

SL

5

7

7

8

8

5

7

8

2

3

4

2

You need to calculate the sum of values every five rows. The expected aggregate result is as follows:

1-5  35

6-10 25

11-12      6

That is, the first column is the interval of rows and the second one is the sums of SL field values.

To do this in SQL, you need to use subqueries to generate sequence numbers. Whereas esProc uses sorted sets to make the computation intuitive. esProc code is as follows:

esProc_sql_aggrate_data_1

A1: Execute SQL to query SL data.

A2: Group and aggregate data according to member positions (sequence numbers). Result is as follows:

 

 

 

 

esProc_sql_aggrate_data_2

A3: Modify values in GN column that correspond to A2’ group numbers. Then you get the final computed result:

esProc_sql_aggrate_data_3

 

In a similar way any database is called, esProc can be called by the reporting tool or a JAVA program. The computed result in the form of ResultSet can be returned to JAVA main program via esProc JDBC. You can see related documents for detailed method.

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