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:
| A | |
| 1 | $select SL from tb1 |
| 2 | =A1.groups((#-1)\5:GN;sum(SL):SL) |
| 3 | =A2.run(GN=string(1+GN*5)+”-“+string(min(A1.len(),5+GN*5))) |
A1: Execute SQL to query SL data.
A2: Group and aggregate data according to member positions (sequence numbers). Result is as follows:
A3: Modify values in GN column that correspond to A2’ group numbers. Then you get the final computed result:
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.