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

Read(421) Label: sequence, group, aggregate,

Description

Group a sequence and then perform aggregate operations.

Syntax

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

Note

First group the sequence according to a single or multiple fields/expressions and then aggregate each group of data. After the sequence is grouped according to x, a new table sequence with fields being F,... G,… will be created. Values of F field are the value of x field of the first record in each group and G field gets values by computing y with regard to 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

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

 Related functions

A.id()

A.group(xi,)