cs.g roupx()
Description:
Group the ordered records in a cursor and return result as a cursor.
Syntax:
cs.groupx(x:F,…;y:G…;n)
Note:
The function groups records in a cursor by grouping expression x to create a new cursor consisting of fields F,...G,… sorted by the grouping field x. The G field gets values by computing y – the aggregate function with which records of cs is aggregated – on each group. The cursor the function returns is irreversible.
Option:
|
@n |
x gets assigned with group numbers which can be used to define the groups. |
|
@u |
Won’t sort the result set by parameter x; mutually exclusive with @n. |
|
@g |
Treat parameter n as the segmentation expression by which records are first segmented and then grouped and sorted. |
Parameter:
|
cs |
A cursor; when it is a multicursor, the function retrieves and groups records through multithreaded processing, and returns a unicursor. |
|
x |
Grouping expression. |
|
F |
Result field name. |
|
y |
Aggregate function on cs, which only supports sum/count/max/min/top/ avg/iterate. When the function works with the iterate(x,a;Gi,…) function, the latter’s parameter Gi should be omitted. |
|
G |
Aggregate field name. |
|
n |
Number of buffer rows; if the number of groups reaches n, write the grouping result to a temporary file; its value is n times of the default, which is automatically calculated, if n<1. |
Return value:
Cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SCORES where CLASS='Class one'") |
|
|
2 |
=A1.groupx(STUDENTID:ID;sum(SCORE):Scores).fetch() |
|
|
3 |
=demo .cursor("select * from FAMILY") |
|
|
4 |
=A3.groupx@n(if(GENDER=="Male",1,2):ID;sum(AGE):TotalAge).fetch() |
The value of grouping expression is a group number. Put records whose GENDER is "Male" to the first group, and others to the second group. Meanwhile, aggregate every group of records. |
|
5 |
=demo.cursor("select * from EMPLOYEE") |
|
|
6 |
=A5.groupx@u(STATE:State;count(STATE):TotalScore).fetch() |
The result set isn’t sorted. |
|
7 |
=demo.cursor("select * from EMPLOYEE where EID <=20") |
|
|
8 |
=A7.groupx@g(GENDER;min(SALARY);EID>=10).fetch() |
Segment records by the segmentation condition EID>=10 and then calcualte min(SALARY); the last two records are the result of segmentation by EID>=10. |
|
9 |
=demo.cursor("select * from SCORES").groupx(STUDENTID:ID;sum(SCORE):Scores;3) |
Since the number of groups reaches 3, write the grouping result to a temporary file. |
Related function: