T.join()
Description:
Define a computation of foreign-key-style join on a pseudo table and return a new pseudo table.
Syntax:
T.join(C:.,A:K,x:F,…; …;…)
Note:
The function defines a computation on pseudo table T, which will match its field C with the key of table sequence/record sequence A, compute expression x on each of the corresponding records, make the computing results values of field F to join with T, and return a new pseudo table.
K can be omitted or represented by #. When omitted, K is A’s key by default; when written as #, K is the ordinal number of a record of table A, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and further increase performance.
If there is an F field in T, just modify the existing field of A. Use the latest time calculated through now() when time key value is not specified.
Option:
|
@i |
Delete a record with a non-matching foreign key value; by default, a non-matching record will be represented by null |
|
@o(Fi; C:.,T:K,x:F,…; …;…) |
Use the record as the value of F field to generate a new record |
|
@d |
When parameters x:F are absent, delete whole records matching the foreign key and perform the filtering operation only over pseudo table T |
|
@m |
Enable a merge join when T is ordered by C and A is ordered by K |
Parameter:
|
T |
A pseudo table |
|
C |
T’s foreign key; separate multiple fields in a composite key with the colon |
|
A |
Table sequence/record sequence |
|
K |
A’s key |
|
x |
A’s field expression |
|
F |
Field name in expression x |
Return value:
Pseudo table
Example:
|
|
A |
B |
|
|
1 |
=create(file).record(["cities.ctx"]) |
|
Below is content of composite table cities.ctx:
|
|
2 |
=pseudo(A1) |
|
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").query("SELECT * FROM STATECAPITAL where STATEID<30").keys(STATEID) |
|
Below is content of STATECAPITAL:
|
|
4 |
=A2.join(STATEID,A3,CAPITAL) |
=A4.cursor().fetch() |
Execute expression in A4. Define a computation on A2’s pseudo table, which will associate A2’s pseudo table CITIES and A3’s STATECAPITAL table through the foreign key, during which default parameter K is STATEID, the key of STATECAPTITAL, and add STATECAPITAL’s CAPITAL field to CITIES to generate a new pseudo table. Execute expression in B4: fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
|
5 |
=A2.join(STATEID,A3:#,CAPITAL) |
=A5.import() |
Execute expression in A5. Define a computation on A2’s pseudo table; as STATEID field values are natural numbers starting from 1, which correspond to ordinarl numbers of records in STATECPATITAL table, parameter K is written as # to use those ordinal numbers in order to increae efficiency; Execute expression in B5 and return same result as B4. |
|
6 |
=A2.join@i(STATEID,A3,CAPITAL) |
=A6.import() |
@i option enables deleting records with non-matching foreign key values – write them as nulls if there isn’t the option – and execute B6 and return the following table:
|
|
7 |
=A2.join@i(STATEID,A3) |
=A7.import() |
@i option enables filtering CITIES table only when parameters x:F are absent; execute B7 and return the following table:
|
|
8 |
=A2.join@d(STATEID,A3) |
=A8.import() |
With @d option and when parameters x:F are absent, delete records where foreign key values are matching and perform filtering only on pseduo table CITIES; execute B8 and return the following table:
|
|
9 |
=A2.join(STATEID,A3,abc) |
=A9.import() |
Write records where values of parameter x cannot be found in A2 as nulls; execute B9 and return the following table:
|
|
10 |
=A2.join@o(cities;STATEID,A3,CAPITAL) |
=A10.import() |
@o option enables to take the whole orignal record as a cities value
to generate a new record; this is equivalent to an expression where parameter
x is ~; execute B10 and return the following table: |
|
11 |
=A2.join(STATEID,A3,CAPITAL:NAME) |
=A11.import() |
Modify the existing fields when NAME field already exists in CITIES table; execute B11 and return the following table:
|
