Chapter 13 Group operation

Read(339) 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, 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.

13.1 Get the distinct value of a field

 

A

 

1

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

 

2

=A1.id(EVENT)

 

 

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

 

13.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]

 

13.4 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()

 

 

13.5 Set five members in each group

 

A

 

1

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

 

2

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

 

 

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

 

 

13.7 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

 

13.8 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

 

13.9 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

 

13.10 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

 

 

13.11 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

 

13.12 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

 

13.13 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

 

13.14 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

 

13.15 Find continuous array

 

A

 

1

[1,3,4,5,8,9,15,16,20]

 

2

=A1.group(~-#).select(~.len()>1)

 

 

13.16 Group by neighboring same key values

 

A

 

1

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

 

2

=A1.groups@o(CLASS,STUDENTID;sum(SCORE):SCORE)

 

 

13.17 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<CLOSING[-1])

Create a new group if condition is true

4

=A3.max(~.len())-1

Find number of continuous rising days for the stock

 

13.18 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