cs.group ( x:F,…;y:G,… )
Description:
Group records of a given cursor by comparing each with its neighbors, perform aggregation over each group and return the original cursor.
Syntax:
cs.group(x:F,...;y:G,…)
Note:
The function groups records in cursor cs according to expression x, by which cs is ordered and whose values are only compared with their neighbors while aggregate expression y is calculated. The resulting set won’t be sorted again. After grouping, records in the original cursor will have fields F,... G,…. Values of G field are the results of computing expression y over each group. The function supports a multicursor.
Parameters:
|
cs |
Cursor/cluster cursor |
|
x |
Grouping expression |
|
F |
Field name |
|
G |
Aggregation field name |
|
y |
Aggregate expression |
Options:
|
@b |
Enable returning a resut set containing aggregates only but not subgroups |
|
@s |
Cumulative aggregation |
|
@q(x:F,…;x’:F’,…;…) |
Used when parameter cs is ordered by x,… and only fields after it need to be sorted; support in-memory sorting |
|
@sq(x:F,…;x’:F’,…;…) |
Only sort without grouping when parameters y:G are absent, and perform cumulative aggregation when the parameters are present; @s works only when @q option is present |
Return value:
The original cursor/cluster cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SCORES where STUDENTID <5 order by STUDENTID ") |
Sort records by SCOREs field |
|
2 |
=A1.group(STUDENTID:StudentID; ~.sum(SCORE):TotalScore) |
Return the original cursor cs |
|
3 |
=A2.fetch() |
|
|
4 |
=demo.cursor("select * from SCORES ") |
|
|
5 |
=A4.group@b(STUDENTID:StudentID; ~.sum(SCORE):TotalScore) |
|
|
6 |
=A5.fetch() |
|
|
7 |
=demo.cursor("select * from SCORES where STUDENTID <5 order by STUDENTID ") |
|
|
8 |
=A7.group@s(STUDENTID:StudentID;sum(SCORE):TotalScore) |
Cumulative aggregation |
|
9 |
=A8.fetch() |
|
|
10 |
=demo.cursor("select * from EMPLOYEE order by DEPT ") |
Query EMPLOYEE table, sort it by DEPT, and return result as a cursor |
|
11 |
=A10.group@q(DEPT;GENDER).fetch() |
GENDER排序Sort only by GENER as A10’s cursor is already ordered by DEPT |
|
12 |
=demo.cursor("select * from EMPLOYEE order by DEPT ") |
|
|
13 |
=A12.group@qs(DEPT:DEPT;GENDER:GENDER).fetch() |
Only sort without grouping
|
|
14 |
=demo.cursor("select * from EMPLOYEE order by DEPT ") |
|
|
15 |
=A14.group@qs(DEPT:DEPT;GENDER:GENDER;count(GENDER):count).fetch() |
Cumulative aggregation |
|
|
A |
|
|
1 |
=file("employees.ctx","192.168.0.111:8281") |
Below is employees.ctx ordered by DEPT field:
|
|
2 |
=A1.open () |
Create a cluster composite table |
|
3 |
=A2.cursor() |
Return a cluster cursor |
|
4 |
=A3.group(DEPT:dept;count(NAME):count) |
Group the cluster cursor according to DEPT by comparing each record with its next neighbor, perform aggregation and return the grouped cluster cursor |
|
5 |
=A4.fetch() |
|
