Group operation

Read(208) Label: group operation,

This chapter lists code examples about common group operations, including Get the distinct value of a field, Delete duplicate members, Delete duplicate adjacent members, Concatenate grouping results into a table sequence, Set five members in each group, Compute aggregate value afer grouping, Ge subset of the grouping result, Refilter or re-sort grouping result, Refilter or re-sort subsets of grouping result, Regroup subsets of grouping result, Perform intragroup cross-row calculation, Get a specified member from each grouped subset, and Find the most appeared member.

Get the distinct value of a field

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.id(EVENT)

 

 

Get the distinct value of a field

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.id(EVENT)

 

 

Delete duplicate members

 

A

 

1

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

2

=A1.id@u(TYPE)

Delete repeated members without changing the member order

3

=A1.group@1u(TYPE)

 

4

=[1,2,2,3,3,4,5,6,2,3].id@u()

[1,2,3,4,5,6]

 

Delete duplicate members

 

A

 

1

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

2

=A1.id@u(TYPE)

Delete repeated members without changing the member order

3

=A1.group@1u(TYPE)

 

4

=[1,2,2,3,3,4,5,6,2,3].id@u()

[1,2,3,4,5,6]

 

Delete duplicate adjacent members

 

A

 

1

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

2

=A1.id@o(TYPE)

Delete repeated adjacent members without sorting

3

=A1.group@1o(TYPE)

 

4

=[1,2,5,5,3,4,5,6,2,3].id@o()

[1,2,5,3,4,5,6,2,3]

 

Delete duplicate adjacent members

 

A

 

1

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

2

=A1.id@o(TYPE)

Delete repeated adjacent members without sorting

3

=A1.group@1o(TYPE)

 

4

=[1,2,5,5,3,4,5,6,2,3].id@o()

[1,2,5,3,4,5,6,2,3]

 

Concatenate grouping results into a table sequence

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

=A2.conj()

 

 

Concatenate grouping results into a table sequence

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

=A2.conj()

 

 

Set five members in each group

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(int((#-1)/5))

 

 

Set five members in each group

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(int((#-1)/5))

 

 

Compute aggregate value after grouping

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.groups(EVENT:GymEvent; sum(SCORE):TotalScore)

 

3

=A1.group(EVENT)

 

4

=A3.new(EVENT: GymEvent,~.sum(SCORE): TotalScore)

 

 

Compute aggregate value after grouping

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.groups(EVENT:GymEvent; sum(SCORE):TotalScore)

 

3

=A1.group(EVENT)

 

4

=A3.new(EVENT: GymEvent,~.sum(SCORE): TotalScore)

 

 

Get subset of the grouping result

 

A

 

1

=demo.query("select EID,NAME,STATE, GENDER, BIRTHDAY,HIREDATE, DEPT,SALARY from EMPLOYEE")

 

2

=A1.group(DEPT)

 

3

=A2.maxp(~.avg(age(BIRTHDAY)))

The records of employees in a department whose average age is the highest

 

Get subset of the grouping result

 

A

 

1

=demo.query("select EID,NAME,STATE, GENDER, BIRTHDAY,HIREDATE, DEPT,SALARY from EMPLOYEE")

 

2

=A1.group(DEPT)

 

3

=A2.maxp(~.avg(age(BIRTHDAY)))

The records of employees in a department whose average age is the highest

 

Refilter or re- sort grouping result

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

=A2.select(~.avg(SCORE)>14.3)

 

4

=A3.sort(-(~.avg(SCORE)))

 

5

=A4(to(2))

Top 2 events with the max average score

6

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

7

=A6.groups(DEPT;count(age(BIRTHDAY)>40):Number)

 

8

=A7.select(Number>=20).(DEPT)

Department with more than 20 employees over their 40s

 

Refilter or re- sort grouping result

 

A

 

1

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

=A2.select(~.avg(SCORE)>14.3)

 

4

=A3.sort(-(~.avg(SCORE)))

 

5

=A4(to(2))

Top 2 events with the max average score

6

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

7

=A6.groups(DEPT;count(age(BIRTHDAY)>40):Number)

 

8

=A7.select(Number>=20).(DEPT)

Department with more than 20 employees over their 40s

 

Refilter or re- sort subsets of grouping result

 

A

 

1

=demo.query("select NAME,EVENT, SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

>A2.(~=~.sort(-SCORE))

 

4

>A2.(~=~(to(2)))

 

5

=A2.(~.(NAME)).isect()

Athletes with all event scores at the top 2

 

Refilter or re- sort subsets of grouping result

 

A

 

1

=demo.query("select NAME,EVENT, SCORE from GYMSCORE")

 

2

=A1.group(EVENT)

 

3

>A2.(~=~.sort(-SCORE))

 

4

>A2.(~=~(to(2)))

 

5

=A2.(~.(NAME)).isect()

Athletes with all event scores at the top 2

 

Regroup subsets of grouping result

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

2

=A1.group(DEPT)

 

3

>A2.(~=~.group(month(BIRTHDAY),day(BIRTHDAY)))

Regroup the subsets

4

=A2.maxp(~.count())

 

5

=A4(1).DEPT

 

 

Regroup subsets of grouping result

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

2

=A1.group(DEPT)

 

3

>A2.(~=~.group(month(BIRTHDAY),day(BIRTHDAY)))

Regroup the subsets

4

=A2.maxp(~.count())

 

5

=A4(1).DEPT

 

 

Perform intragroup cross-row calculation

 

A

B

C

 

1

=demo.query("select * from STOCKRECORDS")

 

 

 

2

=A1.group(STOCKID).(~.sort(DATE))

 

 

 

3

for A2

=0

 

 

4

 

if A3.pselect(B3= if( CLOSING/CLOSING[-1] >=1.05,B3+1,0):4)>0

 

Limit up for three days

5

 

 

=C5|A3.STOCKID

Stores the result

 

Perform intragroup cross-row calculation

 

A

B

C

 

1

=demo.query("select * from STOCKRECORDS")

 

 

 

2

=A1.group(STOCKID).(~.sort(DATE))

 

 

 

3

for A2

=0

 

 

4

 

if A3.pselect(B3= if( CLOSING/CLOSING[-1] >=1.05,B3+1,0):4)>0

 

Limit up for three days

5

 

 

=C5|A3.STOCKID

Stores the result

 

Get a specified member from each grouped subset

 

A

 

1

=demo.query("select NAME,TYPE,PRODUCTION from LIQUORS")

 

2

=A1.group(TYPE).new(TYPE,~.m(-1):Last)

Group and aggregate directly

3

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

4

=A3.group(DEPT)

Group first

5

=A4.(~.minp(SALARY))

Then aggregate

 

Get a specified member from each grouped subset

 

A

 

1

=demo.query("select NAME,TYPE,PRODUCTION from LIQUORS")

 

2

=A1.group(TYPE).new(TYPE,~.m(-1):Last)

Group and aggregate directly

3

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

4

=A3.group(DEPT)

Group first

5

=A4.(~.minp(SALARY))

Then aggregate

 

Find the most appeared member

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

2

=A1.group(DEPT)

Group

3

=A2.maxp(~.count())

Find the group with the most employees

4

=A3(1).DEPT

The DEPT with the most employees

 

Find the most appeared member

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

2

=A1.group(DEPT)

Group

3

=A2.maxp(~.count())

Find the group with the most employees

4

=A3(1).DEPT

The DEPT with the most employees