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, Convert members of a subtable into table sequence fields, and Form a wide table.
|
|
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 |
|
|
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) |
|
|
|
|
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) |
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) |
|
|
|
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 |
|
|
A |
|
|
1 |
=demo.query("select * from STATES") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=A1.fname()\"NAME" |
Disable A1’s key |
|
4 |
=A2.join(STATE,A1:NAME,${A3.concat@c()}) |
Join two tables by keys and add a new field |