groupx()

Read(281) Label: groupx,

Here’s how to use groupx() function.

ch. groupx()

Description:

Group records in a channel and return a channel.

Syntax:

ch.groupx(x:F,…;y:G…)

Note:

The function groups records in channel ch according to expression x, by which the records are ordered, to get a channel having F,...G,… fields. Values of F field consist of values of x field in the first record of every group, and values of G field are the results of computing expression y, which is an aggregate function executed on ch, over each group. It aims to fetch the grouping result set from the channel. 

Options:

@n

With the option the value of expression x is a group number, which points to the desired group

Parameters:

ch

Channel

x

Grouping expression

F

Field name in the resulting table sequence

y

An aggregate function which only supports sum/count/max/min/top /avg/iterate; the parameter Gi should be given up if function iterate(x,a;Gi,…) is used

G

The aggregate fields in the resulting table sequence

Return value:

Channel

Example:

 

A

 

1

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

 

2

=channel()

Create a channel

3

=channel()

Create a channel

4

=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

5

=A2.groupx(DEPT:dept;sum(SALARY):TotalSalary)

Group and sort records by DEPT field

6

=A3.groupx@n(if(GENDER=="F",1,2):SubGroups;sum(SALARY):TotalSalary)

The value of expression x is a group number; put records where GENDER is “F” into the first group and others into the second group, and then aggregate each group

7

=A1.select(month(BIRTHDAY)==2)

 

8

=A1.fetch()

Attach a fetch operation to A7’s cursor

9

=A2.result()

Return the result as a cursor

10

=A3.result()

Return the result as a cursor

cs .groupx()

Description:

Group the ordered records in a cursor and return result as a cursor.

Syntax:

cs.groupx(x:F,;y:G;n)

 

Note:

The function groups records in a cursor by grouping expression x to create a new cursor consisting of fields F,...G,… sorted by the grouping field x. The values F field are the value of x field of the first record in each group and G field gets values by computing y – the aggregate function with which records of cs is aggregated – on each group.

Options:

@n

x gets assigned with group numbers which can be used to define the groups.

@u

Won’t sort the result set by parameter x; mutually exclusive with @n.

Parameters:

cs

Records in a cursor

x

Grouping expression

F

Result field name

y

Aggregate function which only supports sum/count/max/min/top/ avg/iterate. When the function works with the iterate(x,a;Gi,…) function, the latter’s parameter Gi should be omitted

G

Aggregate field name

n

Number of buffer rows; its value is n times of the default if n is less than 1

Return value:

Cursor

Example:

 

A

 

1

=demo.cursor("select * from SCORES where CLASS='Class one'")

 

2

=A1.groupx(STUDENTID:ID;sum(SCORE):Scores).fetch()

3

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

 

4

=A3.groupx@n(if(GENDER=="Male",1,2):ID;sum(AGE):TotalAge).fetch()

The value of grouping expression is a group number. Put records whose GENDER is "Male" to the first group, and others to the second group. Meanwhile, aggregate every group of records

5

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

 

6

=A5.groupx@u(STATE:State;count(STATE):TotalScore).fetch()

The result set isn’t sorted

Related functions:

A.group(xi,…)

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

A.groups()

cs.groups()

cs.groupx()

Description:

Group records in a cluster cursor and return a synchronously segmented cursor.

Syntax:

cs.groupx(x:F,…;y:G…;n)

 

Note:

The function groups records in cluster cursor cs by grouping expression x to create a new cluster cursor consisting of fields F,...G,… sorted by the grouping field x. The values F field are the value of x field of the first record in each group and G field gets values by computing y – the aggregate function with which records of cs is aggregated – on each group.

Options:

@c

 Won’t merge result sets returned by the nodes but return a cluster cursor segmented in the same way

Parameters:

cs

A cluster cursor

x

Grouping expression

F

Resulting field name

y

Aggregate function that 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

n

Number of buffer rows

Return value:

A cluster cursor

Example:

 

 

A

 

1

[192.168.31.72:8281, 192.168.31.72:8291]

 

2

=file@0z("orderpart.ctx", A1)

Open a cluster distributed file

3

=A2.create()

Open a cluster composite table

4

=A3.cursor()

Return a cluster cursor

5

=A4.groupx(EID:ID;count(~):IdCount)

Group A7 and perform count by EID, and return a cluster cursor

6

=A5.fetch()

 

7

=A3.cursor()

 

8

=A7.groupx(if(EID==4,1,2):ID;count(~):IdCount)

Put records where EID is 4 into the first group and others into the second group, and then count the EID values

9

=A8.fetch()

 

 

 

A

 

1

[192.168.0.110:8281,192.168.18.143:8281]

 

2

=file@0("emp.ctx", A1)

emp.ctx is a distributed file on nodes 192.168.0.110 and 192.168.18.143

3

=A2.create()

Open a cluster composite table

4

=A3.cursor()

Create a cluster cursor

5

=A4.groupx@c(GENDER:gender;sum(SALARY):totalSalary)

Perform group and aggregate operations over A4’s cluster cursor by GENDER without merging result sets returned by nodes and return a cluster cursor having the same segmentation way as A4’s

6

=A5.fetch()

Fetch data from the cluster cursor