group()

Read(331) Label: group,

Here’s how to use group() function.

A .group( x i ,… )

Description

Perform equi-grouping according one or more fields or expressions.

Syntax

A.group(xi,…)

Note

The function performs equi-grouping on a sequence or a record sequence according to a single or multiple fields/expressions. The result is a sequence consisting of groups.

Options

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

@n

x gets assigned with group numbers which can be used to define the groups. @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 bool 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. 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()

@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

Parameters

A

A sequence

xi

Grouping expression. When grouping data by multiple fields or expressions, separate grouping expressions from each other by commas

Return value

A sequence or a record sequence

Example

Ø  Group a sequence

 

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

Ø  Reuse the grouping result

 

A

 

1

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

 

2

=A1.group(GENDER)

[[Rebecca,Ashley,Rachel,…],[Matthew,Ryan,Jacob,…]], 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

Ø  Group data by multiple fields

 

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 are the same. 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

Related functions

A.id()

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

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

Description

Group a sequence and then perform aggregate operations.

Syntax

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

Note

The function groups a sequence according to a single or multiple fields/expressions x, aggregates each group of data and generates a new table sequence made up of fields F,... G,… and ordered by grouping expression x. G field gets values by computing y over each group.

 

Options

 

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

 

Parameters

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

Return value

A sequence

Example

 

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

 Related functions

A.id()

A.group(xi,)

ch. group()

Description:

Group records in a channel by comparing each one with its neighbor(s) and return a channel.

Syntax:

ch.group(x)

Note:

The function groups the records in channel ch by expression x, during which each record is compared only with its neighbor(s) and returns a channel containing a series of sequences. This is equivalent to a merge. The resulting set won’t be sorted again. This is an attached computation.

Options:

@i

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

Parameters:

ch

Channel

x

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

Return value:

Channel

Example:

 

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

cs .group()

Description:

Group cursor records by comparing only adjacent records and return the original cursor.

Syntax:

cs.group(x,…)

Note:

The function groups cursor cs containing a set of records by expression x, according to which only the adjacent records are compared, and return the original cursor of sequences. Cursor cs should be ordered. The operation is equal to a merge and supports multicursors.

Options:

@i

x is a bool 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

@q(x:F,…;x’:F’,…;…)

When the cursor is ordered by the first grouping field, just sort the grouped data by the following field(s); support memory grouping

@s

Sort only without grouping; must work with @q option

Parameters:

cs

A cursor containing a set of records

x

Grouping expression; multiple fields/expressions for grouping will be separated from each other with the comma.

Return value:

The orginal cursor

Example:

 

A

 

1

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

 

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 the first record of each group

7

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

Retrieve data of EMPLOYEE table, sort it by DEPT and return data as a cursor

8

=A7.group@q(DEPT;GENDER)

A7’s cursor is already ordered by DEPT, so the data just need to be ordred by group by GENDER

9

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

Sorted only without grouping

Related functions:

A.group(xi,…)

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

 

cs.group()

Description:

Group a cluster cursor by comparing each record with its next neighbor.

Syntax:

cs.group(x,…)

Note:

The function groups records in ordered cluster cursor cs according to expression x by comparing the value of x only with its next neighbor, and returns the cluster cursor containing a sequence of groups. The operation is equivalent to a merge and supports multicursors.

Parameters:

cs

A cluster cursor

x

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

Return value:

The grouped cluster cursor

Example:

 

A

 

1

=file@n("D:/employees.ctx","192.168.0.111:8281")

Below is employees.ctx ordered by DEPT field:

2

=A1.create()

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.group ( x:F,…;y:G,… )

Description:

Group records of a given cursor by comparing each with its neighbours, peform 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, and then performs aggregation. 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

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

 

 

A

 

1

=file@n("D:/employees.ctx","192.168.0.111:8281")

Below is employees.ctx ordered by DEPT field:

2

=A1.create()

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