本章主要展示关于常规分组的代码参考，比如找出字段的唯一值、删除重复成员、去除相邻的重复成员、将分组的结果再合并成序表、每5个成员分作一组、计算分组后汇总值、取出分组结果的子集、对分组结果再过滤或排序、对分组结果的子集再过滤或排序、对分组结果的子集再做分组、针对组内成员做跨行计算、取出每个分组子集中的某个成员、找出出现次数最多的成员等。
|
|
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) |
去掉重复的成员，不改变成员次序 |
|
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) |
不排序，仅去掉相邻的重复成员 |
|
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(EVENT) |
|
|
3 |
=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))) |
平均年龄最大的部门员工记录 |
|
|
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)) |
平均分最大的2个项目 |
|
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) |
大于40岁的员工人数超过20个的部门 |
|
|
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() |
所有项目都前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))) |
对子集再分组 |
|
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 |
|
有三天涨停 |
|
5 |
|
|
=C5|A3.STOCKID |
记录结果 |
|
|
A |
|
|
1 |
=demo.query("select NAME,TYPE,PRODUCTION from LIQUORS") |
|
|
2 |
=A1.group(TYPE).new(TYPE,~.m(-1):Last) |
直接分组聚合 |
|
3 |
=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ") |
|
|
4 |
=A3.group(DEPT) |
先分组 |
|
5 |
=A4.(~.minp(SALARY)) |
后聚合 |
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ") |
|
|
2 |
=A1.group(DEPT) |
分组 |
|
3 |
=A2.maxp(~.count()) |
找出雇员最多的组 |
|
4 |
=A3(1).DEPT |
雇员数最多的部门 |