groups()

@o

Group records by comparing adjacent ones, which is equal to the merging operation, and the result set won’t be sorted.

@n

x gets assigned with group numbers which can be used to define the groups. @n and @o are mutually exclusive.

@u

Do not sort the result set by x. It doesn’t work with @o/@n.

@i

x is a Boolean expression. If the result of x is true, then start a new group. There is only one x.

@m

Use parallel algorithm to handle data-intensive or computation-intensive tasks; no definite order for the records in the result set; can’t be used with @o and @i options.

@0

Discard the group over which the result of grouping expression x is null.

@h

Used over a grouped table with each group ordered to speed up grouping.

@t

Return an empty table sequence with data structure if the grouping and aggregate operation over the sequence returns null.

@z(…;…)

Split the sequence according to groups during parallel computation, and the multiple threads share a same result set.

@e

Return a table sequence consisting of results of computing function y; expression x is a field of sequence A and y is a function on A; the result of y must be one record of A and y only supports maxp, minp and top@1 when it is an aggregate function.

A

A sequence.

x

Grouping expression

F

Field name of the result table sequence.

G

Summary field name in the result table sequence.

y

y is the function with which A is traversed. When y is an aggregate function, it only supports sum, count, max, min, top, avg, iterat, icount, median, maxp, minp, concat, var, conj and union. When the function work with iterate(x,a;G_i,…) function, the latter’s parameter Gi should be omitted. When y isn’t an aggregate function, perform calculation over only the first record in each group.

A

1

=demo.query("select * from SCORES where CLASS = 'Class one'")

2

=A1.groups(STUDENTID:StudentID;sum(SCORE):TotalScore)

Group by a single field.

3

=demo.query("select * from SCORES")

4

=A3.groups(CLASS:Class,STUDENTID:StudentID;sum(SCORE):TotalScore)

group by multiple fields.

5

=A3.groups@m(STUDENTID:StudentID;sum(SCORE):TotalScore)

Use @m option to increase performance of big data handling.

6

=A3.groups@o(STUDENTID:StudentID;sum(SCORE):TotalScore)

Only compare and merge with the neighboring element, and the result set is not sorted.

7

=demo.query("select * from STOCKRECORDS where STOCKID<'002242'")

8

=A7.groups@n(if(STOCKID=="000062",1,2):StockID;sum(CLOSING):TotalPrice)

The value of x is the group number.

9

=demo.query("select * from EMPLOYEE")

10

=A9.groups@u(STATE:State;count(STATE):TotalScore)

Do not sort result set by the sorting field.

11

=A9.groups@i(STATE=="California":IsCalifornia;count(STATE):count)

Start a new group when STATE=="California".

12

=A3.groups(CLASS:Class,STUDENTID:StudentID;iterate(~~*2,10): Score1)

Perform iterate operation within each group.

13

=file("D:\\Salesman.txt").import@t()

14

=A13.groups@0(Gender:Gender;sum(Age):Total)

Discard groups where Gender values are nulls.

15

=file("D:/emp10.txt").import@t()

For data file emp10.txt, every 10 records are ordered by DEPT.

16

=A15.groups@h(DEPT:dept;sum(SALARY):bouns)

A15 is grouped and ordered by DEPT, for which @h option is used to speed up grouping.

17

=A1.groups(STUDENTID:StudentID;SUBJECT,sum(SCORE):SUMSCORE)

Parameter y isn’t an aggregate function, so the function performs operation over the first record.

18

=demo.query("select * from SCORES where CLASS = 'Class three'")

Return an empty table sequence.

19

=A18.groups@t(STUDENTID:StudentID;sum(SCORE):TotalScore)

Return an empty table sequence with the data structure.

A

1

=demo.query("select * from SCORES")

2

=A1.groups@z(STUDENTID:StudentID;sum(SCORE):TotalScore)

Split A1’s sequence according to groups during parallel computation.

A

1

=demo.query("select EID,NAME,GENDER,DEPT,SALARY from employee")

2

=A1.groups(DEPT;minp(SALARY))

Execute aggregate function minp() and return A3’s records.

3

=A1.groups@e(DEPT;minp(SALARY))

Return a table sequence consisting of result records of computing minp(SALARY).

A

1

=connect("demo").query("select * from employee")

2

=A1.groups(DEPT;conj(NAME))

Group A1’s table sequence by DEPT and for each group concatnate NAME  values:

3

=A1.groups(DEPT;union(STATE))

Group A1’s table sequence by DEPT and for each group compute union of STATE values:

@n

With the option the value of expression x is a group number, which points to the desired group.

@u

Won’t sort the resulting set by expression x; the option and @n are mutually exclusive.

ch

Channel.

x

Grouping expression, by which an aggregation over the whole grouped set is performed if x:F is omitted. In that case the semicolon “;” should not be omitted.

F

Field names of the resulting table sequence.

y

An aggregate function on channel ch, which only supports sum, count, max, min, top, avg, iterate, concat, var, conj and union; the parameter Gi should be given up if function iterate(x,a;G_i,…) is used.

G

The aggregate fields in the resulting table sequence.

A

1

=demo.cursor("select * from EMPLOYEE ")

2

=channel()

Create a channel.

3

=channel()

Create a channel.

4

=channel()

Create a channel.

5

=channel()

Create a channel.

6

=A1.push(A2,A3,A4,A5)

Be ready to push the data in A1’s cursor into channel A2, A3 and A5, but the action needs to wait.

7

=A2.groups(;sum(SALARY):TotalSalary)

As x:F is omitted, calculate the sum of salaries of all employees.

8

=A3.groups(DEPT:dept;sum(SALARY):TotalSalary)

Group and sort records by DEPT field.

9

=A4.groups@n(if(GENDER=="F",1,2):SubGroups;sum(SALARY):TotalSalary)

The value of x is group number; put records where GENDER is “F” into the first group and others into the second group, and then aggregate each group.

10

=A5.groups@u(STATE:State;count(STATE):count)

Won’t sort the resulting set by grouping field

11

A1.select(month(BIRTHDAY)==2)

12

A11.fetch()

Attach a fetch operation to A11’s cursor.

13

=A2.result()

14

=A3.result()

15

=A4.result()

16

=A5.result()

@c

Perform the group operation over data in every node and compose the result sets into a cluster in-memory table in the segmentation way of the cursor; support a cluster dimension table.

cs

Records in a cluster cursor.

x

Grouping expression; if omitting parameters x:F, aggregate the whole set; in this case, the semicolon “;” must not be omitted.

F

Field name in the result table sequence.

y

An aggregate function on cs, which only supports sum, count, max, min, top, avg, iterate, concat, var, conj and union; when the function works with iterate(x,a;G_i,…) function, the latter’s parameter Gi should be omitted.

G

Aggregate field name in the result table sequence.

n

The specified maximum number of groups; stop executing the function when the number of data groups is bigger than n to prevent memory overflow; the parameter is used in scenarios when it is predicted that data will be divided into a large number of groups that are greater than n.

A

1

=file("emp1.ctx","192.168.0.111:8281")

Below is emp1.ctx:

2

=A1.open()

Open a cluster composite table.

3

=A2.cursor()

Return a cluster cursor.

4

=A3.groups(Dept:dept;count(Name):count)

Group data by DEPT and perform aggregation.

A

1

[192.168.0.110:8281,192.168.18.143:8281]

2

=file("emp.ctx":[1,2], A1)

3

=A2. open ()

Open a cluster composite table.

4

=A3.cursor()

Create a cluster cursor.

5

=A4.groups(GENDER:gender;sum(SALARY):totalSalary)

Group data by GENDER and perform aggregation and return result as a table sequence.

6

=A3.cursor()

7

=A6.groups@c(GENDER:gender;sum(SALARY):totalSalary).dup()

Retain the way of segmentation of the distributed cursor and return a cluster in-memory table.

@n

The value of grouping expression is group number used to locate the group; you can use n to specify the number of groups and generate corresponding number of zones first.

@u

Do not sort the result set by the grouping expression; it doesn’t work with @n.

@o

Compare each record only with its neighboring record to group, which is equivalent to the merge operation, and won’t sort the result set.

@i

With this option, the function only has one parameter x that is a Boolean expression; start a new group if its result is true.

@h

Used over a grouped table with each group ordered to speed up grouping.

@0

Discard groups on which expression x gets empty result.

@t

When empty data is obtained from the cursor, the function returns an empty table sequence having only the data structure.

@z(…;…;n)

Split the sequence according to groups during parallel computation, and the multiple threads share a same result set; in this case HASH space will not be dynamically adjusted; parameter n is HASH space size, whose value can be default.

@e

Return a table sequence consisting of results of computing function y; expression x is a field of cursor cs and y is a function on cs; the result of y must be one record of cs and y only supports maxp, minp and top@1 when it is an aggregate function.

cs

Records in a cursor.

x

Grouping expression; if omitting parameters x:F, aggregate the whole set; in this case, the semicolon “;” must not be omitted.

F

Field name in the result table sequence.

y

An aggregate function on cs, which only supports sum, count, max, min, top, avg, iterate, concat, var, conj and union; when the function works with iterate(x,a;G_i,…) function, the latter’s parameter Gi should be omitted.

G

Aggregate field name in the result table sequence.

A

1

=demo.cursor("select * from SCORES where CLASS = 'Class one'")

2

=A1.groups(;sum(SCORE):TotalScore)

As parameters x:F absent, calculate the total score of all students.

3

=demo.cursor("select * from FAMILY")

4

=A3.groups(GENDER:gender;sum(AGE):TotalAge)

Group and order data by specified fields.

5

=demo.cursor("select * from STOCKRECORDS where STOCKID<'002242'")

6

=A5.groups@n(if(STOCKID=="000062",1,2):SubGroups;sum(CLOSING):ClosingPrice)

The value of grouping expression is group number; put records whose STOCKID is “000062” to the first group and others to the second group; and meanwhile aggregate each group.

7

=demo.cursor("select * from EMPLOYEE")

8

=A7.groups@u(STATE:State;count(STATE):Total)

The result set won’t be sorted by the grouping field.

9

=demo.cursor("select * from EMPLOYEE")

10

=A9.groups@o(STATE:State;count(STATE):Total)

Compare each record with its next neighbor and won’t sort the result set.

11

=demo.cursor("select * from EMPLOYEE")

12

=A11.groups@i(STATE=="California":IsCalifornia;count(STATE):count)

Start a new group if the current record meets the condition STATE=="California".

13

=file("D:/emp10.txt").cursor@t()

For data file emp10.txt, every 10 records are ordered by DEPT

14

=A13.groups@h(DEPT:DEPT;sum(SALARY):bouns)

As A13 is grouped and ordered by DEPT, use @h option to speed up grouping.

15

=demo.query("select  * from employee")

16

=A15.cursor@m(3)

Return a multicursor.

17

=A16.groups(STATE:state;sum(SALARY):salary)

Group the multicursor with groups function.

A

1

=demo.cursor("select * from SCORES where CLASS = 'Class three'")

2

=A1.groups@t(STUDENTID:StudentID;sum(SCORE):TotalScore)

Return an empty table sequence with the original data structure.

3

=demo.cursor("select * from DEPT")

Below is content of DEPT table:

4

=A3.groups@0(FATHER)

Discard groups on which Father field expression gets empty value.

A

1

=demo.cursor("select * from SCORES")

2

=A1.groups@z(STUDENTID:StudentID;sum(SCORE):TotalScore;5)

Split A1’s sequence according to groups during parallel computation; HASH space size is 5.

A

1

=demo.cursor("select * from SCORES")

2

=A1.groups@e(SUBJECT;maxp(SCORE))

Return a table sequence consisting of result records of computing maxp(SCORE).

A

1

=connect("demo").cursor("select * from employee").groups(DEPT;conj(NAME))

Group records in the cursor by DEPT and for each group concatenate NAME values:

2

=connect("demo").cursor("select * from employee").groups(DEPT;union(STATE))

Group records in the cursor by DEPT and for each group union up STATE values:

@n

The value of grouping expression is group number used to locate the group; you can use n to specify the number of groups and generate corresponding number of zones first.

@u

Do not sort the result set by the grouping expression; it doesn’t work with @n.

@o

Compare each record only with its neighboring record to group, which is equivalent to the merge operation, and won’t sort the result set.

@i

With this option, the function only has one parameter x that is a Boolean expression; start a new group if its result is true.

@h

Used over a grouped table with each group ordered to speed up grouping.

@b

Enable returning a result set containing aggregates only without group-level data.

T

A pseudo table.

x

Grouping expression; if omitting parameters x:F, aggregate the whole set; in this case, the semicolon “;” must not be omitted

F

Field name in the result table sequence.

y

An aggregate function on T, which only supports sum, count, max, min, top, avg, iterate, concat, var, conj and union; when the function works with iterate(x,a;G_i,…) function, the latter’s parameter Gi should be omitted.

G

Aggregate field name in the result table sequence.

n

The specified maximum number of groups; stop executing the function when the number of data groups is bigger than n to prevent memory overflow; the parameter is used in scenarios when it is predicted that data will be divided into a large number of groups that are greater than n.

A

1

=create(file).record(["D:/file/pseudo/empT.ctx"])

2

=pseudo(A1)

Generate a pseudo table.

3

=A2.groups(DEPT:dept;avg(SALARY):AVG_SALARY)

Group records in A2’s pseudo table by DEPT field, calculate average of SALARY in each group – which is the aggregate method, and return result as a table sequence made up of dept field, and AVG_SALARY field and sorted by dept.

4

=A2.groups@n(if(GENDER=="F",1,2):GenderGroup;avg(SALARY):AVG_SALARY)

Devide recrods of A2’s pseudo table into two groups according to whether GENDER is F, and calculate average SALARY in each group.

5

=A2.groups@u(DEPT:dept;avg(SALARY):AVG_SALARY)

With @u option, the grouping result won’t be sorted.

6

=A2.groups@o(DEPT:dept;avg(SALARY):AVG_SALARY)

Withe @o option, compare each record with its next neighbor and won’t sort the result set.

7

=A2.groups@i(GENDER=="M":isMAN;count(EID):Count)

Start a new group if the current record meets the condition GENDER=="M".

8

=A2.groups@b(DEPT:dept;avg(SALARY):AVG_SALARY)

With @b option, return only a column of aggregates without group data.