分组与关联计算
本章主要展示关于分组与关联计算的代码参考，比如GROUP BY、 HAVING、同级表等值连接、主子表等值连接、非等值连接、LEFT JOIN/FULL JOIN等。
GROUP BY
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.groups(DEPT; sum(SALARY): SalarySum) |
直接分组汇总 |
|
3 |
=A1.group(DEPT) |
先分组 |
|
4 |
=A3.new(DEPT,~.count():EmployeeNumber) |
后汇总 |
HAVING
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.groups(DEPT; sum(SALARY): SalarySum) |
分组汇总 |
|
3 |
=A2.select(SalarySum >200000) |
对汇总结果过滤 |
|
4 |
=A1.group(DEPT) |
先分组 |
|
5 |
=A4.select(~.count()>30) |
对分组子集过滤 |
同级表等值连接
|
|
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) |
|
LEFT JOIN/FULL JOIN
|
|
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) |
左连接 |
|
6 |
=join@f(A2:State,NAME;A1:Employee,STATE) |
全连接 |
|
7 |
=join@1(A1:Employee,EID;A3:Attendance, EMPLOYEEID;A4:Performance, EMPLOYEEID) |
与第一个表对齐连接 |