关联分组与连接
本章主要展示关于关联分组与连接的代码参考，比如按指定的分类分组、按指定的条件范围分组、范围可能重叠的条件分组、使用等值条件进行连接、按第一个表为基准作连接（左连接） 、连接不能匹配的记录（全连接）、 将多个表按某字段值相等条件对齐、非等值条件的一般连接、无条件的连接（即完全交叉）、将外键引用转成记录型字段、将子表成员转成排列型字段、连接组成宽表等。
按指定的分类分组
|
|
A |
|
|
1 |
[America,Jamaica,France,Scotland,England] |
|
|
2 |
=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS") |
|
|
3 |
=A2.align@a(A1,PRODUCTION) |
按PRODUCTION分组 |
按指定的条件范围分组
|
|
A |
B |
|
|
1 |
?<25 |
Below 25 |
|
|
2 |
?>=25 && ?<=30 |
25 to 30 |
|
|
3 |
?>30 && ?<=40 |
30 to 40 |
|
|
4 |
?>40 && ?<=50 |
40 to 50 |
|
|
5 |
?>50 |
over 50 |
|
|
6 |
=create(Section,AgeGroup).record([A1:B5]) |
|
|
|
7 |
=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT, SALARY from EMPLOYEE").derive(age(BIRTHDAY):AGE) |
|
|
|
8 |
=A7.enum@r(A6.(Section),AGE) |
|
按年龄段分组 |
|
9 |
=A8.new(A6(#).AgeGroup:AgeGroup, ~.count():Number, ~.avg(AGE):AverageAge) |
|
|
范围可能重叠的条件分组
|
|
A |
|
|
1 |
[?<5000,?>=7000,?>10000] |
|
|
2 |
=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT, SALARY from EMPLOYEE") |
|
|
3 |
=A2.enum@r(A1,SALARY) |
|
使用等值条件进行连接
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=join(A1:State,NAME;A2:Employee,STATE) |
|
按第一个表为基准作连接（左连接）
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=join@1(A1:State,NAME;A2:Employee,STATE) |
|
连接不能匹配的记录（全连接）
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=join@f(A1:State,NAME;A2:Employee,STATE) |
|
将多个表按某字段值相等条件对齐
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=demo.query("select * from ATTENDANCE") |
|
|
3 |
=demo.query("select * from PERFORMANCE") |
|
|
4 |
=join@1(A1:Employee,EID;A2:Attencance,EMPLOYEEID; A3:Performance,EMPLOYEEID) |
|
非等值条件的一般连接
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from CITIES") |
|
|
3 |
=demo.query("select * from GYMSCORE") |
|
|
4 |
=xjoin(A1:State,left(NAME,1)=="A";A2:City,POPULATION> 1000000;A3:Score,EVENT=="Floor") |
|
无条件的连接（即完全交叉）
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from STUDENTS") |
|
|
3 |
=xjoin(A1:State;A2:Student) |
|
将外键引用转成记录型字段
|
|
A |
|
|
1 |
=demo.query("select * from CITIES").keys(CID) |
|
|
2 |
=demo.query("select * from STATES where STATEID<?",51).keys(STATEID) |
|
|
3 |
=A1.switch(STATEID,A2) |
主子表建立引用关系 |
|
4 |
=A1.group(STATEID.REGIONID) |
通过引用字段直接访问主表 |
|
5 |
=A2.run(CAPITAL=A1.select@1(NAME==CAPITAL)) |
|
|
6 |
=A1.new(NAME,STATEID.CAPITAL.NAME:StateCapital) |
|
将子表成员转成排列型字段
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT,SALARY from EMPLOYEE") |
|
|
2 |
=demo.query("select * from FAMILY where RELATION=?","child") |
|
|
3 |
=A1.select(GENDER=="F" && A2.id(EID).pos(EID)>0) |
|
|
4 |
=A3.run(EID=A2.select(EID==A3.EID)) |
主子表建立引用关系 |
|
5 |
>A3.(EID=EID.sort(-AGE)) |
对排列型字段再排序 |
|
6 |
=A3.new(NAME,EID(1).GENDER:GenderOfFirstChild,age(BIRTHDAY)-EID(1).AGE:ReproductiveAge) |
直接对排列型字段统计 |
连接组成宽表
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=A1.fname()\"NAME" |
去掉A1的键 |
|
4 |
=A2.join(STATE,A1:NAME,${A3.concat@c()}) |
按照两表的键关联并增加字段 |