# 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]

 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]

 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