MarkLogic 12 EA 1 Product Documentationop:group-by
op:group-by( $plan as map:map, $keys as item()*, [$aggregates as item()*] ) as map:map
Summary
This method collapses a group of rows into a single row.
If you want the results to include a column, specify the column either as a grouping key or as one of the aggregates. A group-by operation without a grouping key outputs a single group reflecting the entire row set.
The aggregates for a group by operation are specified as the second parameter instead of in a op:select operation (unlike SQL).
| Parameters | |
|---|---|
| $plan | The Optic Plan. You can either use the XQuery => chaining operator or specify the variable that captures the return value from the previous operation. |
| $keys | This parameter specifies the columns used to determine the groups. Rows with the same values in these columns are consolidated into a single group. The columns can be existing columns or new columns created by an expression specified with op:as. The rows produced by the group by operation include the key columns. Specify an empty sequence to create a single group for all of the rows in the row set. |
| $aggregates | This parameter specifies either new columns for aggregate functions over the rows in the group or columndefs that are constant for the group. The aggregate library functions are listed below. |
Usage Notes
The aggregate library functions for building the group-by parameters are as follows:
- op:array-aggregate
- op:sequence-aggregate
- op:avg
- op:count
- op:group-concat
- op:max
- op:min
- op:sample
- op:sum
- op:uda
Example
(: Calculate the total expenses for each employee and return results
in order of employee number. :)
xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic"
at "/MarkLogic/optic.xqy";
let $employees := op:from-view("main", "employees")
let $expenses := op:from-view("main", "expenses")
let $totalexpenses := op:col("totalexpenses")
return $employees
=> op:join-inner($expenses, op:on(
op:view-col("employees", "EmployeeID"),
op:view-col("expenses", "EmployeeID")))
=> op:group-by(op:view-col("employees", "EmployeeID"),
("FirstName", "LastName",
op:view-col("expenses", "Category"),
op:sum($totalexpenses,
op:view-col("expenses", "Amount"))))
=> op:order-by(op:view-col("employees", "EmployeeID"))
=> op:result()
Example
(: Calculate the average expense across all expense reports. :)
xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic"
at "/MarkLogic/optic.xqy";
op:from-view("main", "expenses")
=> op:group-by((), op:avg("Average Amount", "Amount"))
=> op:result()