T.groups()

Read(34) Label: pseudo table, group,

Description:

Group records in a pseudo table.

Syntax:

T. groups(x:F,…;y:G…;n)

Note:

The function groups records in a pseudo table by expression x, sorts result by the grouping field, and perform an aggregate operation on each group.

This creates a new table sequence consisting of fields F,...G,… and sorted by the grouping field x. G field gets values by computing aggregate function y on each group.

Options:

@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

@v

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

Parameters:

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; when the function works with iterate(x,a;Gi,…) 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

Return value:

A pseudo table

Example:

 

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