group()

@o

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

@1

Get the first record of each group to form a record sequence and return it (Please note that 1 is a number instead of a letter); can work with @v option

@n

x gets assigned with group numbers which can be used to define the groups; discard groups that make x<1. @n and @0 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. This option is equivalent to A.group@o(a+=if(x,1,0)), in which a=0 and there is only one x

@0

Discard the group over which the result of grouping expression x is null; discard empty groups when @n is also present. Use it when there’s only one expression x

@s

Perform a concatenation of sequences/records sequences after the grouping. It is equivalent to A.group(xi,…).conj(); can work with @v option

@p

Return a sequence of integer sequences, each of which contains the positions of members in each group in sequence A

@h

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

@v

When parameter A is a pure table sequence, return a set of pure table sequences

A

A sequence

x_i

Grouping expression

A

1

[6,9,12,15,16,5,1,7,8]

[[6,12,16,8],[ 9,15,5,1,7]] The series is divided into two groups. Divide the number of members in both groups by 2, one of the remainders is 0 and the other is 1

2

=A1.group(~%2)

3

=A1.group(~%2,~%3)

[[6,12],[16],[8],[9,15],[1,7],[5]]

Group the series according to multiple expressions.

4

=[6,9,16,5,1,7,8].group@s(~%2)

Divide the sequence into a group of odd numbers and a group of even numbers, and then concatenate them

5

=A1.group((#-1)\3)

Group sequence A1 every 3 members

A

1

=demo.query("select NAME,BIRTHDAY,GENDER from EMPLOYEE")

2

=A1.group(GENDER)

Group A1’s table sequence by GENDER:

[Each group is a sequence:

3

=A2.new(GENDER:Gender,~.count():Number)

Count members of each group

4

=A2.new(GENDER:Gender,~.avg(age(BIRTHDAY)):Average)

Get different statistical results using  the same grouping result

A

1

=demo.query("select NAME,GENDER,DEPT,BIRTHDAY from EMPLOYEE")

2

=A1.group(GENDER,DEPT)

Group records by multiple fields

3

=A1.group@o(GENDER)

Records won’t be sorted; only the adjacent ones will be compared and group them together if they match. Same records that are not adjacent may be put into different groups, so there may be overlapped groups and the returned result is a set of sequences.

4

=A1.group@1(GENDER)

Return the first record of each group

5

=A1.group@n(if(GENDER=="F",1,2))

x gets assigned with group numbers which can be used to define the groups directly.

[[Rebecca,Ashley,Rachel,…],[Matthew,Ryan,Jacob,…]]

6

=A1.group@u(GENDER,DEPT)

Do not sort result set by the sorting field

7

=A1.group@i(GENDER=="F")

Start a new group when GENDER=="F"

8

=A1.group@p(GENDER,DEPT)

Return a sequence of integer sequences, each of which contains the positions of records in a group (grouped by GENDER field and DEPT field) in the original table sequence

9

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

10

=A9.group@0(Gender)

Group the table sequence by GENDER field, while discarding groups with null values

11

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

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

12

=A11.group@h(DEPT)

As A11 is segmented and ordered by DEPT, @h option is used to speed up grouping

13

=A1.group@n(if(DEPT=="HR":1,DEPT=="Sales":2;0))

Use @n option to discard groups of records that make the grouping expression less than 1. That is, give up those where DEPT is neither HR nor Sales

A

1

=demo.query@v("select * from EMPLOYEE order by GENDER,DEPT ")

Return a pure table sequence

2

=A1.group@v(GENDER,DEPT)

@v option enables returning a set of pure table sequences

@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.

@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

@s

Summarize data cumulatively

A

A sequence

x

Grouping expression. If omitting x:F, aggregate the whole set without grouping; in this case “;” must not be omitted

F

Field name of the result table sequence

G

Names of summary fields in the result table sequence

y

Aggregate expression in which ~ is used to reference a group

A

1

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

2

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

3

=A1.group@o(STUDENTID:StudentID;~.sum(SCORE):TotalScore)

Only the adjacent ones will be compared and group them together if they are the same. The result set won’t be sorted.

4

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

5

=A4.group@n(if(STOCKID=="000062",1,2):StockID;~.sum(CLOSING):TotalPrice)

x gets assigned with group numbers

6

=A1.group(;~.sum(SCORE):TotalScore)

x:F is omitted, so compute the total score of all the students

7

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

8

=A7.group@u(STATE:State;~.count(STATE):TotalScore)

Do not sort result set by the sorting field

9

=A7.group@i((GENDER=="F"):IsF;~.count():Number)

Start a new group when GENDER=="F"

10

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

11

=A10.group@0(Gender:Gender;~.sum(Age):Total)

Discard the groups where GENDER values are null

12

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

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

13

=A12.group@h(DEPT:dept;~.sum(SALARY):bouns)

As A12 are segmented and ordered by DEPT, @h option is used to speed up grouping

14

=demo.query("select * from EMPLOYEE where EID > 500")

Return an empty table sequence

15

=A14.group@t(STATE:State;~.count(STATE):TotalScore)

Return an empty table sequence with the data structure

16

=demo.query("select * from employee").group@s(DEPT;sum(SALARY):TotalSalary)

Perform aggregation cumulatively

@i

With this option and with grouping expression x being a boolean expression, start a new group if the result of x is true. Be sure there’s only one x in this case.

ch

Channel

x

Grouping expression. Use the comma to separate multiple grouping fields/expressions.

A

1

=demo.cursor("select EID,NAME,GENDER,DEPT,SALARY from EMPLOYEE")

2

=channel()

Create a channel

3

=channel()

Create a channel

4

=A2.group(GENDER,DEPT)

Group records by GENDER field and DEPT field

5

=A2.fetch()

Attach ch.fetch() function that gets the final result to A2’s channel to fetch and store the existing data in the channel

6

=A3.group@i(GENDER=="F")

Start a new group whenever GENDER=="F"

7

=A3.fetch()

Attach ch.fetch() function that gets the final result to A3’s channel

8

=A1.push(A2,A3)

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

9

=A1.skip()

Data in A1’s cursor is pushed into the channel and operations are performed as the skip() operation is performed over A1

10

=A2.result()

11

=A3.result()

@i

x is a boolean expression. Begin a new group when a record makes it return true. In this case there should be only one x.

@1

Get the first record of every group to form a record sequence and return it to the original cursor; here it is number 1 instead of letter l

@v

When parameter cs is a cursor based on pure table sequence, copy each grouped subset as a new pure table sequence

cs

A cursor or multicursor containing a set of records

x

Grouping expression

A

1

=demo.cursor("select * from EMPLOYEE").sortx(GENDER,DEPT)

Data in the cursor returned is ordered by GENDER and DEPT

2

=A1.group(GENDER,DEPT)

Put adjacent records whose GENDER and DEPT values are equal into same group, and return the original cursor

3

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

4

=A3.group@i(GENDER=="F").fetch()

Begin a new group as long as the record satisfies GENDER=="F"

5

=demo.cursor("select * from EMPLOYEE order by GENDER  ")

6

=A5.group@1(GENDER).fetch()

Return a record sequence consisting of the first records in every group

A

1

=demo.cursor@v("select * from EMPLOYEE order by GENDER,DEPT")

Return a cursor of pure table sequence

2

=A1.group@v(GENDER,DEPT)

As @v option is present, copy each grouped subset as new pure table sequence

3

=A2.fetch()

cs

A cluster cursor

x

Grouping expression; use comma to separate multiple grouping fields or expressions

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)

Group the cluster cursor by DEPT and return the grouped cluster cursor

5

=A4.fetch()

cs

Cursor

x

Grouping expression

F

Field name

G

Aggregation field name

y

Aggregate expression

@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

@e

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

When @sev options work together, the function returns a pure table sequence

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  where STUDENTID <5  order by  STUDENTID ")

5

=A4.group@s(STUDENTID:StudentID;sum(SCORE):TotalScore)

Cumulative aggregation

6

=A5.fetch()

7

=demo.cursor("select * from EMPLOYEE order by DEPT ")

Query EMPLOYEE table, sort it by DEPT, and return result as a cursor

8

=A7.group@q(DEPT;GENDER).fetch()

Sort only by GENER as A7’s cursor is already ordered by DEPT

9

=demo.cursor("select * from EMPLOYEE order by DEPT ")

10

=A9.group@qs(DEPT:DEPT;GENDER:GENDER).fetch()

Only sort without grouping

11

=demo.cursor("select * from EMPLOYEE order by DEPT ")

12

=A11.group@qs(DEPT:DEPT;GENDER:GENDER;count(GENDER):count).fetch()

Cumulative aggregation

A

1

=demo.cursor("select * from EMPLOYEE order by DEPT ")

2

=A1.group@e(DEPT;~.minp(SALARY)).fetch()

Return a cursor consisting of result records of minp(SALARY)

cs

A cluster cursor

x

Grouping expression

F

Field name

G

Field to be aggregated

y

Aggregation expression

A

1

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

employees.ctx is ordered by DEPT. Its content is as follows:

2

=A1.open()

Open the cluster composite table

3

=A2.cursor()

Return a cluster cursor

4

=A3.group(DEPT:dept;count(NAME):count)

Group records by DEPT and perform aggregation on each group and return result to the original cluster cursor

5

=A4.fetch()

T

A pseudo table

x_i

Grouping expression; use comma to separate multiple grouping fields or expressions

@i

x is a boolean expression. Begin a new group when a record makes it return true. In this case there should be only one x

@1

Get the first record of every group to form a record sequence and return it; here it is number 1, instead of letter l

@t

Return result as a table sequence on which an index can be directly created; by default, the function sorts the result

@v

Store the composite table in the column-wise format when loading it the first time, which helps to increase performance

A

1

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

2

=pseudo(A1)

Generate a pseudo table object

3

=A2.group(GENDER,DEPT)

Put records where both GENDER and DEPT values are same into the same group and return a new pseudo table

4

=A3.import()

Get data from A3’s pseudo table

5

=A2.group@i(GENDER=="F")

Create a new group whenever the record meets the condition GENDER=="F"

6

=A5.import()

Get data from A5’s pseudo table

7

=A2.group@1t(GENDER)

Retrun the first record of each group