Group & Join over associative tables

This chapter lists code examples about group & join operations over associative tables, including Group by specified category, Group by specified ranges, Conditional grouping with possible overlapped ranges, Join table on equivalence conditions, Join tables based on the first one (left join), Join records even if specified conditions are not matched (full join), Align tables on condition that specified fields in them are equal, Perform a join under non-equal conditions, Perform unconditional join (full cross join), Convert foreign key references into record fields, and Convert members of a subtable into table sequence fields.

Group by specified category

 

A

 

1

[America,Jamaica,France,Scotland,England]

 

2

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

3

=A2.align@a(A1,PRODUCTION)

Group by PRODUCTION

 

Group by specified category

 

A

 

1

[America,Jamaica,France,Scotland,England]

 

2

=demo.query("select NAME,TYPE, PRODUCTION from LIQUORS")

 

3

=A2.align@a(A1,PRODUCTION)

Group by PRODUCTION

 

Group data by specified ranges

 

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)

 

Group by AGE

9

=A8.new(A6(#).AgeGroup:AgeGroup, ~.count():Number, ~.avg(AGE):AverageAge)

 

 

 

Group data by specified ranges

 

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)

 

Group by AGE

9

=A8.new(A6(#).AgeGroup:AgeGroup, ~.count():Number, ~.avg(AGE):AverageAge)

 

 

 

Conditional group ing with possible overlapped range s

 

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)

 

 

Conditional group ing with possible overlapped range s

 

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)

 

 

Join tables on equivalence conditions

 

A

 

1

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

 

2

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

 

3

=join(A1:State,NAME;A2:Employee,STATE)

 

 

Join tables on equivalence conditions

 

A

 

1

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

 

2

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

 

3

=join(A1:State,NAME;A2:Employee,STATE)

 

 

Join tables based on the first one (left join)

 

A

 

1

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

 

2

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

 

3

=join@1(A1:State,NAME;A2:Employee,STATE)

 

 

Join tables based on the first one (left join)

 

A

 

1

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

 

2

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

 

3

=join@1(A1:State,NAME;A2:Employee,STATE)

 

 

Join records even if specified conditions are not matched (full join)

 

A

 

1

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

 

2

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

 

3

=join@f(A1:State,NAME;A2:Employee,STATE)

 

 

Join records even if specified conditions are not matched (full join)

 

A

 

1

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

 

2

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

 

3

=join@f(A1:State,NAME;A2:Employee,STATE)

 

 

Align tables on condition that specified fields in them are equal

 

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)

 

 

Align tables on condition that specified fields in them are equal

 

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)

 

 

Perform a join under non-equal conditions

 

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

 

 

Perform a join under non-equal conditions

 

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

 

 

Perform unconditional join (full cross join )

 

A

 

1

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

 

2

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

 

3

=xjoin(A1:State;A2:Student)

 

 

Perform unconditional join (full cross join )

 

A

 

1

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

 

2

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

 

3

=xjoin(A1:State;A2:Student)

 

 

Convert foreign key references into record fields

 

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)

Create a reference between the main table and a subtable

4

=A1.group(STATEID.REGIONID)

Directly access the main table via reference fields

5

=A2.run(CAPITAL=A1.select@1(NAME==CAPITAL))

 

6

=A1.new(NAME,STATEID.CAPITAL.NAME:StateCapital)

 

7

=A1.select(STATEID.CAPITAL.POPULATION>1000000)

 

 

Convert foreign key references into record fields

 

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)

Create a reference between the main table and a subtable

4

=A1.group(STATEID.REGIONID)

Directly access the main table via reference fields

5

=A2.run(CAPITAL=A1.select@1(NAME==CAPITAL))

 

6

=A1.new(NAME,STATEID.CAPITAL.NAME:StateCapital)

 

7

=A1.select(STATEID.CAPITAL.POPULATION>1000000)

 

 

Convert members of a subtable into table sequence fields

 

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

Create a reference between the main table and a subtable

5

>A3.(EID=EID.sort(-AGE))

Sort a record sequence field again

6

=A3.new(NAME,EID(1).GENDER:GenderOfFirstChild, age(BIRTHDAY)-EID(1).AGE:ReproductiveAge)

Directly aggregate a record sequence field

 

Convert members of a subtable into table sequence fields

 

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

Create a reference between the main table and a subtable

5

>A3.(EID=EID.sort(-AGE))

Sort a record sequence field again

6

=A3.new(NAME,EID(1).GENDER:GenderOfFirstChild, age(BIRTHDAY)-EID(1).AGE:ReproductiveAge)

Directly aggregate a record sequence field