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()