# 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, Find the most appeared member, Get topN from each subgroup, Find continuous array, Group by neighboring same key values, Create a new group for a different condition, and Group & aggregate iteratively.

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

 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@s(EVENT) 3 =A1.group(EVENT) 4 =A2.conj()

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

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

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

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

## Get topN from each subgroup

 A 1 =demo.query("select * from EMPLOYEE ") 2 =A1.groups(DEPT;top(3;BIRTHDAY):TOP3_EMP) Get 3 eldest employees in each department

## Find continuous array

 A 1 [1,3,4,5,8,9,15,16,20] 2 =A1.group(~-#).select(~.len()>1)

## Group by neighboring same key values

 A 1 =demo.query("select* from SCORES") 2 =A1.groups@o(CLASS,STUDENTID;sum(SCORE):SCORE)

## Create new group for a different condition

 A 1 =demo.query("select* from STOCKRECORDS") Order by date 2 =A1.select(STOCKID:"000062") Select stock 000062 3 =A2.group@i(CLOSING

## Group & aggregate iteratively

 A 1 =file("E:/txt/orders_i.csv").import@t() 2 =A1.group(sellerid;(~.iterate((x=#,~~+amount),0,~~>500000),x):breach50) Find how many months when each salesperson achieves sales of over 50,000