ROLLUP(recordset, condition, transform [, LOCAL] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )
ROLLUP(recordset, transform, fieldlist [, LOCAL] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )
ROLLUP(recordset, GROUP, transform [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )
| recordset | The set of records to process, typically sorted in the same order that the condition or fieldlist will test. |
| condition | An expression that defines "duplicate" records. The keywords LEFT and RIGHT may be used as dataset qualifiers for fields in the recordset. |
| transform | The TRANSFORM function to call for each pair of duplicate records found. |
| LOCAL | Optional. Specifies the operation is performed on each node independently, without requiring interaction with all other nodes to acquire data; the operation maintains the distribution of any previous DISTRIBUTE. |
| fieldlist | A comma-delimited list of expressions or fields in the recordset that defines "duplicate" records. You may use the keywords WHOLE RECORD (or just RECORD) to indicate all fields in that structure, and/or you may use the keyword EXCEPT to list fields to exclude. |
| GROUP | Specifies the recordset is GROUPed and the ROLLUP operation will produce a single output record for each group. If this is not the case, an error occurs. |
| UNORDERED | Optional. Specifies the output record order is not significant. |
| ORDERED | Specifies the significance of the output record order. |
| bool | When False, specifies the output record order is not significant. When True, specifies the default output record order. |
| STABLE | Optional. Specifies the input record order is significant. |
| UNSTABLE | Optional. Specifies the input record order is not significant. |
| PARALLEL | Optional. Try to evaluate this activity in parallel. |
| numthreads | Optional. Try to evaluate this activity using numthreads threads. |
| ALGORITHM | Optional. Override the algorithm used for this activity. |
| name | The algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options. |
| Return: | ROLLUP returns a record set. |
The ROLLUP function is similar to the DEDUP function with the addition of a call to the transform function to process each duplicate record pair. This allows you to retrieve valuable information from the "duplicate" record before it's thrown away. Depending on how you code the transform function, ROLLUP can keep the LEFT or RIGHT record, or any mixture of data from both.
The first form of ROLLUP tests a condition using values from the records that would be passed as LEFT and RIGHT to the transform. The records are combined if the condition is true. The second form of ROLLUP compares values from adjacent records in the input recordset, and combines them if they are the same. These two forms will behave differently if the transform modifies some of the fields used in the matching condition (see example below).
For the first pair of candidate records, the LEFT record passed to the transform is the first record of the pair, and the RIGHT record is the second. For subsequent matches of the same values, the LEFT record passed is the result record from the previous call to the transform and the RIGHT record is the next record in the recordset, as in this example:
ds := DATASET([{1,10},{1,20},{1,30},{3,40},{4,50}],
{UNSIGNED r, UNSIGNED n});
d t(ds L, ds R) := TRANSFORM
SELF.r := L.r + R.r;
SELF.n := L.n + R.n;
END;
ROLLUP(ds, t(LEFT, RIGHT), r);
/* results in:
3 60
3 40
4 50
*/
ROLLUP(ds, LEFT.r = RIGHT.r,t(LEFT, RIGHT));
/* results in:
2 30
1 30
3 40
4 50
the third record is not combined because the transform modified the value.
*/For forms 1 and 2 of ROLLUP, the transform function must take at least two parameters: a LEFT record and a RIGHT record, which must both be in the same format as the recordset. The format of the resulting record set must also be the same as the inputs.
For form 3 of ROLLUP, the transform function must take at least two parameters: a LEFT record which must be in the same format as the recordset, and a ROWS(LEFT) whose format must be a DATASET(RECORDOF(recordset)) parameter. The format of the resulting record set may be different from the inputs.