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

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

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