关联分组与连接

阅读(893) 标签: 关联分组, 连接,

本章主要展示关于关联分组与连接的代码参考,比如按指定的分类分组、按指定的条件范围分组、范围可能重叠的条件分组、使用等值条件进行连接、按第一个表为基准作连接(左连接) 、连接不能匹配的记录(全连接)、 将多个表按某字段值相等条件对齐、非等值条件的一般连接、无条件的连接(即完全交叉)、将外键引用转成记录型字段、将子表成员转成排列型字段、连接组成宽表等。

按指定的分类分组

 

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

按照两表的键关联并增加字段