Format, Parameters
GROUP BY divides the rows in the data stream into multiple groups, for each group keeps one row of data and discard the rest rows; it is always followed by an aggregation operation that generates an aggregated value for each group.
Syntax:
- Format: group by
<column1>as<alias1>,<column2>as<alias2>, ... - Parameters: see table below
- Affected columns：
<column>and all its homologous columns
| Name | Category | Specification | Description |
|---|---|---|---|
<column> |
NODE，EDGE，PATH，ATTR，ARRAY，TABLE | / | Grouping basis; more or more basises must be homologous columns and grouping is operated from left to right, from higher levels to lower levels |
<alias> |
string | Naming convention is the same as custom alias's | Alias for grouping basis, omittable |
Analysis
n(as n1).re().n(as n2) as path
group by n1.shape, n2.color
return path, count(path)
In the UQL statement above, Group By groups the one-step paths resulted from a template query; it groups results based on initial nodes' shapes, then based on colours of terminal nodes; then return results after counting the number of paths in each group.
ATTR
Example: Group all cards by card level, return the total number of cards at each level
find().nodes({@card}) as n
group by n.level as level
return level, count(n)
NODE
Example: find cards held by Customer CU001, CU002, CU003, and return the array of Card IDs and their owners
n({_id in ["CU001","CU002","CU003"]} as n1)
.re({@has}).n({@card} as n2)
group by n1
return n1{*}, collect(n2._id)
Multi-level Grouping
Example: find all-level customers'ownership of all-level cards, return customer levels, card levels, and the number of cards owned by each customer
n({@customer} as n1)
.re({@has}).n({@card} as n2)
group by n1.level as a, n2.level as b
return table(a, b, count(n2))