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.
|
|
A |
|
|
1 |
=demo.query("select NAME,EVENT,SCORE from GYMSCORE") |
|
|
2 |
=A1.id(EVENT) |
|
|
|
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,EVENT,SCORE from GYMSCORE") |
|
|
2 |
=A1.group@s(EVENT) |
|
|
3 |
=A1.group(EVENT) |
|
|
4 |
=A2.conj() |
|
|
|
A |
|
|
1 |
=demo.query("select NAME,EVENT,SCORE from GYMSCORE") |
|
|
2 |
=A1.group(int((#-1)/5)) |
|
|
|
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) |
|
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
|
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 |
|
|
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 |
|
|
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 |
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE ") |
|
|
2 |
=A1.groups(DEPT;top(3;BIRTHDAY):TOP3_EMP) |
Get 3 eldest employees in each department |
|
|
A |
|
|
1 |
[1,3,4,5,8,9,15,16,20] |
|
|
2 |
=A1.group(~-#).select(~.len()>1) |
|
|
|
A |
|
|
1 |
=demo.query("select* from SCORES") |
|
|
2 |
=A1.groups@o(CLASS,STUDENTID;sum(SCORE):SCORE) |
|
|
|
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 |
|
|
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 |