This grouping is based on the comparison of the field values in the previous row, and the generation of a new group when a certain change occurs, such as a change larger than 10. SQL only supports equivalent grouping. In order to realize this kind of ordered conditional grouping, we have to transform the data into equivalent grouping after several times. Take the new MySQL that supports window functions as an example. It goes through three steps:

1. Get the change flag field. The field value of the previous row is obtained through the window function lag, and the flag is set to 1 if the change condition is met (such as current row - previous row > 10), otherwise it is 0;

2. Add up the flag field to get the ACC field. Add from the first row to the current row, and it also needs to be achieved by using window function: sum(flag)…BETWEEN ROWS UNBOUNDED AND CURRENT ROW；

3. According to the field of ACC, the general equivalent grouping can be done.

 For MySQL without window functions in the early days, it could be realized theoretically, which is more complex, so I won't go into details.

This kind of orderly grouping would be very simple if written in SPL language. With the @i option of group operation, you can finish it in one line:

=connect(”mysqlDB”).query(“select * from t”).group@i(f>~[-1].f+10)

After the grouping operation, the sequence table B of two-tier structure is obtained. For the second layer of grouped subsets, it is easy to do any calculation:

=B.max(~.len())   Get the number of rows in the maximum group；

=B.(~.new(f,avg(f2))).conj()    Get the average value of f2 of each group；

=B.(~.sort@z(f2).m(to(3))).conj()  Take out the three largest rows of f2 of each group；

In addition to ordered conditional grouping, there are also ordered equivalent grouping, nested grouping and many other grouping methods that are difficult to implement by SQL. For details, please refer to Grouped Subsets.

When the data is not in the database, it is still convenient for SPL to perform complex calculations:

=file(“d:/t.csv”).import(;,",").group...

It's also easy to embed esProc into Java applications，please refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc