Group and Join Operations

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.

GROUP BY

 

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

 

GROUP BY

 

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

 

HAVING

 

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

 

HAVING

 

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

 

Perform equi-join on same-level tables

 

A

 

1

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

 

2

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

 

3

=join(A1:StateName,STATEID;A2:StateInfo,STATEID)

 

 

Perform equi-join on same-level tables

 

A

 

1

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

 

2

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

 

3

=join(A1:StateName,STATEID;A2:StateInfo,STATEID)

 

 

Perform equi-join on main table and subtable

 

A

 

1

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

 

2

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

 

3

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

 

 

Perform equi-join on main table and subtable

 

A

 

1

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

 

2

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

 

3

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

 

 

Non-equi-join s

 

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)

 

Non-equi-join s

 

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)

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

 

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)

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