This chapter lists code examples about group operation and joins, including GROUP BY, HAVING, Perform equi-join on same-level tables, Perform equi-join on main table and subtable, Non-equi-joins, and LEFT JOIN/FULL JOIN.
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.groups(DEPT; sum(SALARY): SalarySum) |
Group and aggregate together |
|
3 |
=A1.group(DEPT) |
First group |
|
4 |
=A3.new(DEPT,~.count():EmployeeNumber) |
Then aggregate |
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.groups(DEPT; sum(SALARY): SalarySum) |
Group and aggregate |
|
3 |
=A2.select(SalarySum >200000) |
Filter the aggregate result |
|
4 |
=A1.group(DEPT) |
Group |
|
5 |
=A4.select(~.count()>30) |
Filter grouped subsets |
|
|
A |
|
|
1 |
=demo.query("select * from STATENAME") |
|
|
2 |
=demo.query("select * from STATEINFO") |
|
|
3 |
=join(A1:StateName,STATEID;A2:StateInfo,STATEID) |
|
|
|
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 EMPLOYEE") |
|
|
2 |
=demo.query("select * from LIQUORS") |
|
|
3 |
=demo.query("select * from RECEIPT") |
|
|
4 |
=xjoin(A1:Employee,STATE=="New York";A2:Liquor, STOCK>500;A3:Food,QUANTITY>2) |
|
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=demo.query("select * from STATES") |
|
|
3 |
=demo.query("select * from ATTENDANCE") |
|
|
4 |
=demo.query("select * from PERFORMANCE") |
|
|
5 |
=join@1(A2:State,NAME;A1:Employee,STATE) |
Left join |
|
6 |
=join@f(A2:State,NAME;A1:Employee,STATE) |
Full join |
|
7 |
=join@1(A1:Employee,EID;A3:Attendance, EMPLOYEEID;A4:Performance, EMPLOYEEID) |
Join with the first table in alignment |