pjoin()
Here are how to use pjoin() functions.
P.pjoin ()
Description:
Create association between multiple table sequences/record sequences through the join key.
Syntax:
P.pjoin(K:..,x:F,…;Ai:z,Ki:…,xi:Fi,…; …)
Note:
The function associates table sequence/record sequence A and table sequence/record sequence Ai through the join key and returns a sequence consisting of x:F,… and xi:Fi,…; by default, x:F,… involves all fields of P.
When relationship between P and Ai is one-to-many, xi is an aggregate expression.
When relationship between P and Ai is many-to-one, records of Ai will appear repeatedly in the result set.
Parameter z specifies the join type. It can be absent or null. Perform an inner join when z is absent, and a left join when z is null; and only retain records of P that cannot find matches when z is null and both parameters xi:Fi are absent.
Parameter:
|
P |
A table sequence/record sequence |
|
K |
P’s join key |
|
x |
P’s expression |
|
F |
Field name corresponding to expression x |
|
Ai |
A table sequence/record sequence |
|
z |
Join type |
|
Ki |
Join key of Ai |
|
xi |
Expression of Ai |
|
Fi |
Field name corresponding to expression xi |
Return value:
Table sequence
Option:
|
@o |
Work when data is ordered to enable MERGE |
|
@f |
Enable full join while ignoring parameter z |
Example:
Multi-homo-dimension table join:
|
|
A |
|
|
1 |
=connect("demo").query("select STATEID,NAME from STATENAME") |
Return a table sequence:
|
|
2 |
=connect("demo").query("select STATEID,POPULATION from STATEINFO") |
Return a table sequence:
|
|
3 |
=connect("demo").query("select STATEID,CAPITAL from STATECAPITAL") |
Return a table sequence:
|
|
4 |
=A1.pjoin(STATEID,STATEID:ID,NAME;A2,STATEID,POPULATION;A3,STATEID,CAPITAL) |
Associate A1, A2 and A3 through join key STATEID and rename STATEID ID; below is the result:
|
When A and Ai has a one-to- many relationship:
|
|
A |
|
|
1 |
=demo.query("select top 6 DEPT,MANAGER from DEPARTMENT") |
Below is content of the returned table sequence:
|
|
2 |
=demo.query("select EID,NAME,DEPT from EMPLOYEE") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(DEPT;A2,DEPT,count(EID):Num) |
A1 and A2 have a one-to-many relationship; associate them through DEPT, find the number of EID values under each DEPT value, and make the computing results a new field Num; below is the returned data:
|
When A and Ai has a many-to-one relationship:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
A1 and A2 have a many-to-one relationship; associate them through STATEID, during which A2’s field values appear in the result set repeatedly; below is the result:
|
When using @o option:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Return a sequence ordered by STATEID; below is the content:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Return a sequence ordered by STATEID; below is the content:
|
|
3 |
=A1.pjoin@o(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
As sequences are ordered by the join key, use @o option to perform MERGE.
|
Use @f option to enable full join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin@f(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
Use @f option to perform full join, during which field values are displayed as null for non-matching records; below is the result:
|
Method 1 for left join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2:null,STATEID,CAPITAL ) |
As parameter z is null, perform left join to list all records of A1 display field values of A2’s non-matching records as nulls; below is the result:
|
Method 2 for left join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID;A2:null,STATEID) |
Parameter z is null and parameters xi:Fi are absent, so only retain A1’s non-matching records; below is the result:
|
cs.pjoin ()
Description:
Attach the join-key-based association with another cursor or a record sequence to a cursor and return the original cursor.
cs.pjoin(K:..,x:F,…; csi/Ai:z,Ki:…,xi:Fi,…; …)
Note:
The function attaches a computation to cursor cs, which associates cursor/record sequence cs and cursor/record sequence csi through the join key and forms a sequence consisting of x:F,… and xi:Fi,… to return to the original cursor.
cs and csi can be cursors or record sequences, and both should be ordered by the join key. By default, x:F,… involves all fields of cs.
When relationship between cs and csi is one-to-many, xi is an aggregate expression.
When relationship between cs and csi is many-to-one, records of csi/Ai will appear repeatedly in the result set.
Parameter z specifies the join type. It can be absent or null. Perform an inner join when z is absent, and a left join when z is null; and only retain records of cs that cannot find matches when z is null and both parameters xi:Fi are absent.
When both cs and csi are composite table cursors, the latter will by default follows the former to filter blocks. The process is this: retrieve data from cs and then retrieve data from csi according to the value range of the retrieved cs data. The method filters csi and helps to increase computing efficiency.
This is a delayed function.
Parameter:
|
cs |
A cursor /record sequence |
|
K |
cs’s join key |
|
x |
cs’s expression |
|
F |
Field name corresponding to expression x |
|
csi |
A cursor/composite table cursor/record sequence |
|
z |
Join type |
|
Ki |
Join key of csc:/Ai |
|
xi |
Expression of csc:/Ai |
|
Fi |
Field name corresponding to expression xi |
Return value:
Cursor
Option:
|
@r |
Use this option to perform block filtering on cs1 according to cs and increase efficiency when both cs and cs1, cs2,...csi are table cursors and cs1 has a relatively small amount of data |
|
@f |
Enable full join while ignoring parameter z; do not work with @r option |
|
@t |
Use this option to make the time key the join key when both cs and csi are composite table-based cursors and csi has the time key; do not work with @f option |
Example:
Homo-dimension table join:
|
|
A |
|
|
1 |
=connect("demo").cursor("select STATEID,NAME from STATENAME").sortx(STATEID) |
Return a cursor ordered by STATEID; below is the data:
|
|
2 |
=connect("demo").query("select STATEID,POPULATION from STATEINFO").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
3 |
=connect("demo").query("select STATEID,CAPITAL from STATECAPITAL").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
4 |
=A1.pjoin(STATEID,STATEID:ID,NAME;A2,STATEID,POPULATION;A3,STATEID,CAPITAL) |
Attach a computation to cursor A1, which associates cursor A1 and record sequences A2 and A3 through join key STATEID, renames STATEID field ID and returns the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
When A and Ai has a one-to-many relationship:
|
|
A |
|
|
1 |
=demo.cursor("select top 6 DEPT,MANAGER from DEPARTMENT") |
Return a cursor; below is the content:
|
|
2 |
=demo.cursor("select EID,NAME,DEPT from EMPLOYEE").sortx(DEPT) |
Return a cursor; below is the content:
|
|
3 |
=A1.pjoin(DEPT;A2,DEPT,count(EID):Num) |
Relationship between A1 and A2 is one-to-many; attach a computation to cursor A1, which associates cursors A1 and A2 through DEPT, finds the number of EID values under each DEPT value and makes the computing result a new filed v and returns the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
When A and Ai has a many-to-one relationship:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sortx(STATEID) |
Return a cursor; below is the content:
|
|
2 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; below is the content:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
Relationship between A1 and A2 is many-to-one; attach a computation to cursor A1, which associates them through STATEID, during which A2’s field values appear repeatedly in the result set and returns the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
Full join:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sortx(STATEID) |
Return a cursor; below is the content:
|
|
2 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; below is the content:
|
|
3 |
=A1.pjoin@f(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
Attach a computation to cursor A1, which, uses @f to perform full join, during which non-matching field values of records are displayed as nulls and returns the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
Method 1 for left join:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sortx(STATEID) |
Return a cursor; below is the content:
|
|
2 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; below is the content:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2:null,STATEID,CAPITAL ) |
Attach a computation to cursor A1. As parameter z is null, perform left join to list all records of A1 while displaying non-matching field values in A2 as nulls and return the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
Method 2 for left join:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sortx(STATEID) |
Return a cursor; below is the content:
|
|
2 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; below is the content:
|
|
3 |
=A1.pjoin(STATEID;A2:null,STATEID) |
Attach a computation to cursor A1. Parameter z is null and parameters xi:Fi are absent, so only the non-matching records of A1 are retained and return the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
Join between composite table cursor:
|
|
A |
|
|
1 |
=file("DEPARTMENT.ctx").open().cursor() |
Return a composite table cursor; below is the content:
|
|
2 |
=file("EMPLOYEE.ctx").open().cursor() |
Return a composite table cursor; below is the content:
|
|
3 |
=A1.pjoin@r(DEPT;A2,DEPT,count(EID):Num) |
The relationship between A1 and A2 is one-to-many; Attach a computation to associate them according to DEPT, find the number of EID values under each DEPT and use the results to form Num field while using @r option and return the original cursor A1; below is data in cursor A1 after the attached computation is executed on the cursor A1:
|
When csi has the time key:
|
|
A |
|
|
1 |
=file("transaction.btx").cursor@b() |
Return a cursor ordered by UID and Time. |
|
2 |
=file("trap.ctx") |
|
|
3 |
=A2.create@ty(#UID,#Time,Change,Amount) |
Create a composite table where UID is the basic key and Time is the time key. |
|
4 |
=A3.append@i(A1) |
Append cursor A1’s records to composite table file trap.ctx. |
|
5 |
=A4.cursor() |
Return a composite table-based cursor, whose content is as follows:
… …
|
|
6 |
=demo.cursor("select top 5 EID, NAME, DEPT from EMPLOYEE ") |
Return a cursor. |
|
7 |
=file("ep.ctx") |
|
|
8 |
=A7.create@y(#EID, NAME, DEPT) |
Generate a composite table and set EID as the key. |
|
9 |
=A8.append@i(A6) |
Append cursor A6’s records to composite table file ep.ctx. |
|
10 |
=A9.cursor() |
Return a composite table-based cursor, whose content is as follows:
|
|
11 |
=A10.pjoin@t(EID:date(2021,5,1),NAME,DEPT;A5,UID:Time,UID,Time,Change,Amount) |
Attach a computation to cusor A10. Join two composite table-based cursors: A5 and A10, as composite table trap.ctx has the time key, use @t option to return the record where UID is equal to ID and that has the largest Time value before 2021-05-01, and return the original cursor A10: |
|
12 |
=A10.fetch() |
Fetch data from cursor A10:
|
T.pjoin ()
Description:
Define an association computation between a pseudo table and a cursor/record sequence through the join key on a pseudo table.
Syntax:
T.pjoin(K:..,x:F,…; csi:z,Ki:…,xi:Fi,…; …)
Note:
The function defines a computation on pseudo table T, which associates T and cursor/record sequence csi through the join key, by which they need to be ordered, and returns a sequence consisting of x:F,… and xi:Fi,….
csi can be a cursor or a records sequence. by default, x:F,… involves all fields of T; both T and csi should be ordered by the join key.
When relationship between T and csi is one-to-many, xi is an aggregate expression.
When relationship between T and csi is many-to-one, records of csi will appear repeatedly in the result set.
Parameter z specifies the join type. It can be absent or null. Perform an inner join when z is absent, and a left join when z is null; and only retain records of T that cannot find matches when z is null and both parameters xi:Fi are absent.
Parameter:
|
T |
A pseudo table |
|
K |
T’s join key |
|
x |
T’s expression |
|
F |
Field name corresponding to expression x |
|
csi |
A cursor/record sequence |
|
z |
Join type |
|
Ki |
Join key of csi |
|
xi |
Expression of csi |
|
Fi |
Field name corresponding to expression xi |
Return value:
Pseudo table
Option:
|
@f |
Enable full join while ignoring parameter z; do not work with @r |
Example:
Multi-homo-dimension table join:
|
|
A |
|
|
1 |
=create(file).record(["statename-pj.ctx"]) |
Below is content of statename-pj.ctx, which is ordered by STATEID:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").query("select STATEID,POPULATION from STATEINFO").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
4 |
=connect("demo").query("select STATEID,CAPITAL from STATECAPITAL").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
5 |
=A2.pjoin(STATEID,STATEID:ID,NAME;A3,STATEID,POPULATION;A4,STATEID,CAPITAL) |
Define a computation on A2’s pseudo table, which will create association between the pseudo table and record sequences A3 and A4 through join key STATEID, and rename STATEID ID, and return a new pseudo table.
|
|
6 |
=A5.cursor().fetch() |
Fetch data from A5’s pseudo table while executing the computation defined in A5 on A2’s pseudo table, and return the following table:
|
When T and csi has a one-to-many relationship:
|
|
A |
|
|
1 |
=create(file).record(["dep-pj.ctx"]) |
Below is content of composite table dep-pj.ctx, which is ordered by DEPT:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=demo.cursor("select EID,NAME,DEPT from EMPLOYEE").sortx(DEPT) |
Below is content of returned cursor:
|
|
4 |
=A2.pjoin(DEPT;A3,DEPT,count(EID):Num) |
Pseudo table A2 and cursor A3 have a one-to-many relationship; define a computation on A2’s pseudo table, which will associate them through DEPT, find the number of EID values under each DEPT value and make the computing results a new field Num, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
When T and csi has a many-to-one relationship:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Below is content of composite table cities-pj.ctx, which is ordered by STATEID:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor and below is the content:
|
|
4 |
=A2.pjoin(STATEID,CID,NAME,POPULATION;A3,STATEID,CAPITAL ) |
Pseudo table A2 and cursor A3 have a many-to-one relationship; define a computation on A2’s pseudo table, which will associate them through STATEID, during which A2’s field values appear in the result set repeatedly, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Full join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; content is as follows:
|
|
4 |
=A2.pjoin@f(STATEID,CID,NAME,POPULATION;A3,STATEID,CAPITAL ) |
Define a computation on A2’s pseudo table – with @f option, perform a full join to display record field values that do not have matches as nulls, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Method 1 for left join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor whose data is as follows:
|
|
4 |
=A2.pjoin(STATEID,CID,NAME,POPULATION;A3:null,STATEID,CAPITAL ) |
Define a computation on A2’s pseudo table – as parameter z is null, perform a left join to list all records of the pseudo table and display field values that do not match A3 as nulls – and return a new pseudo table. |
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Method 2 for left join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; its content is as follows:
|
|
4 |
=A2.pjoin(STATEID;A3:null,STATEID) |
Define a computation on A2’s pseudo table – as parameter z is null and parameters xi:Fi are absent, only retain pseudo table records that do not have matches, and return a new pseudo table.
|
|
5 |
=A3.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
