cs.g roupx()

Read(948) Label: cursor, records, group,

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

Records in a cursor

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:

A.group(xi,…)

A.group(x:F,…;y:G,…)

A.groups()

cs.groups()