ModifyPlan.prototype.groupByUnion
ModifyPlan.prototype.groupByUnion( groups as groupdef[], [aggregates as aggregatedef[]] ) as ModifyPlan
Summary
This method performs the union of multiple group-by operations on a row set.
The aggregates for the operation are specified as the second parameter instead of in a prototype.select operation (unlike SQL).
The group-by-union operation provides a powerful, low-level capability. For many purposes, the higher-level prototype.groupToArrays provides a convenient alternative.
| Parameters | |
|---|---|
| groups |
The sets of grouping keys. The keys for each group are specified with the
op.group, op.rollup, or
op.cube functions. Each group must have a unique set of keys
but multiple groups can have the same key.
As a convenience, a group with a single key can specify the name of the key column with a string or a column parameter function such as op.col or constructed from an expression with op.as. The rows produced by the group-by-union operation include the key columns from each group. A group can be empty to group over all of the rows in the row set. |
| aggregates | This parameter specifies either columns to sample or aggregate functions to apply to a column for all of the rows in the group. Sampled columns can be existing columns or new columns created by an expression specified with op.as. Often a sampled column might have a constant value within the group such as a title or label closely associated with a numeric identifier used as the grouping key. |
Usage Notes
The groupByUnion operation differs from the groupBy operation as follows:
- Instead of taking a single set of grouping keys, the groupByUnion operation takes multiple sets of grouping keys.
- For each combination of the values of the grouping keys in each set, the groupByUnion operation produces an output row with the values in the columns for the grouping keys and the aggregates. Columns for grouping keys belonging to other groups but not the current group have null values.
- The output row set consists of unordered union of these rows.
groupByUnion is a method of the following classes:
See Also
-
- op.group function
- op.rollup function
- op.cube function
- prototype.groupToArrays function
Example
// Calculate the total expenses separately for each category and for each location.
const op = require('/MarkLogic/optic');
op.fromView('main', 'expenses')
.groupByUnion(
[op.group('Category'), op.group('Location'), op.group()],
op.sum("TotalAmount", "Amount")
)
.orderBy(['Category', 'Location'])
.result();
Stack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.