The GROUP function provides important functionality when processing very large datasets. The basic concept is that the GROUP function will break the dataset up into a number of smaller subsets, but the GROUPed dataset is still treated as a single entity in your ECL code.
Operations on a GROUPed dataset are automatically performed on each subset, separately. Therefore, an operation on a GROUPed dataset will appear in the ECL code as a single operation, but will in fact internally be accomplished by serially performing the same operation against each subset in turn. The advantage this approach has is that each individual operation is much smaller, and more likely to be able to be accomplished without spilling to disk, which means the total time to perform all the separate operations will typically be less than performing the same operation against the entire dataset (sometimes dramatically so).
The GROUP function does not automatically sort the records it's operating on--it will GROUP based on the order of the records it is given. Therefore, SORTing the records first by the field(s) on which you want to GROUP is usually done (except in circumstances where the GROUP field(s) are used only to break a single large operation up into a number of much smaller operations).
For the set of operations that use TRANSFORM functions (such as ITERATE, PROJECT, ROLLUP, etc), operating on a GROUPed dataset where the operation is performed on each fragment (group) in the recordset, independently, implies that testing for boundary conditions will be different than if you were working with a SORTed dataset. For example, the following code (contained in GROUPfunc.ECL) uses the GROUP function to rank people's accounts, based on the open date and balance. The account with the newest open date is ranked highest (if there are multiple accounts opened the same day the one with the highest balance is used). There is no boundary check needed in the TRANSFORM function because the ITERATE starts over again with each person, so the L.Ranking field value for each new person group is zero (0).
IMPORT $; accounts := $.DeclareData.Accounts; rec := RECORD accounts.PersonID; accounts.Account; accounts.opendate; accounts.balance; UNSIGNED1 Ranking := 0; END; tbl := TABLE(accounts,rec); rec RankGrpAccts(rec L, rec R) := TRANSFORM SELF.Ranking := L.Ranking + 1; SELF := R; END; GrpRecs := SORT(GROUP(SORT(tbl,PersonID),PersonID),-Opendate,-Balance); i1 := ITERATE(GrpRecs,RankGrpAccts(LEFT,RIGHT)); OUTPUT(i1);
The following code just uses SORT to achieve the same record order as in the previous code. Notice the boundary check code in the TRANSFORM function. This is required, since the ITERATE will perform a single operation against the entire dataset.:
rec RankSrtAccts(rec L, rec R) := TRANSFORM SELF.Ranking := IF(L.PersonID = R.PersonID,L.Ranking + 1, 1); SELF := R; END; SortRecs := SORT(tbl,PersonID,-Opendate,-Balance); i2 := ITERATE(SortRecs,RankSrtAccts(LEFT,RIGHT)); OUTPUT(i2);
The different bounds checking in each is required by the fragmenting created by the GROUP function. The ITERATE operates separately on each fragment in the first example, and operates on the entire record set in the second.