Read(34) Label: pseudo table, group,


Group records in a pseudo table.


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


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.



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


Do not sort the result set by the grouping expression; it doesn’t work with @n


Compare each record only with its neighboring record to group, which is equivalent to the merge operation, and won’t sort the result set


With this option, the function only has one parameter x that is a Boolean expression; start a new group if its result is true


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


Enable returning a result set containing aggregates only without group-level data


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



A pseudo table


Grouping expression; if omitting parameters x:F, aggregate the whole set; in this case, the semicolon “;” must not be omitted


Field name in the result table sequence


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


Aggregate field name in the result table sequence


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










Generate a pseudo table



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



Devide recrods of A2’s pseudo table into two groups according to whether GENDER is F, and calculate average SALARY in each group



With @u option, the grouping result won’t be sorted



Withe @o option, compare each record with its next neighbor and won’t sort the result set



Start a new group if the current record meets the condition GENDER=="M"



With @b option, return only a column of aggregates without group data