P.align(A:x,y)
Description:
Align records of a record sequence to a sequence.
Syntax:
P.align(A:x,y)
Note:
Align records of record sequence P to sequence A through association between expression x and expression y.
This function is mainly for the scenario of primary-and-subtable association, where x expression is usually the foreign key field of the subtable. The computation compares if records of the subtable match the corresponding records in the primary table according to the former’s foreign key, and align them if the foreign key field match the record.
There is usually the one-to-many correspondence between the primary table and the subtable, that is, a record in A is associated with multiple records in P. The computation constructs a sequence composed of multiple associated records of P, takes it as a member and stores it in the result sequence, where the number of members is the same as that of A.
When both parameter x and parameter y are absent, align each current record of P to the corresponding member of A.
Parameter:
|
P |
A record sequence, table sequence or pure table sequence, which is usually regarded as the subtable. |
|
A |
A sequence or record sequence to which records are aligned, which is usually the primary table. |
|
x |
A field or field expression according to which association is achieved; by default, it is interpreted as ~. |
|
y |
Alignment expression in P, which is by default interpreted as P.~ . |
Option:
|
@a |
Return all records of P that match members of A in order and group the result sequence; by default, the function returns only the first matching record. |
|
@b |
Use binary search when A is an ordered sequence. |
|
@r |
When this option is present, y is an integer sequence, where each member is an alignment position, and the function groups records of P and puts them repeatedly to positions specified by n. |
|
@p |
Return a sequence of sequence numbers of members in P. |
|
@n |
Return all members of P that match members of A and put non-matching members in the result set’s last group. |
|
@s |
Sort records of P according to the order of members of A and put non-matching records in the last. |
|
@v |
Return a pure table sequence when P is a pure table sequence. |
|
@o |
Perform merge-align between P and A when both are ordered. |
Return value:
Sequence/Record sequence/Pure table sequence
Example:
Align the subtable to the primary table and perform computations:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT ") |
Treat the table as the primary table.
|
|
2 |
=demo.query("select * from EMPLOYEE ") |
Treat the table as subtable where DEPT field is associated with A1.
|
|
3 |
=A2.align@a(A1:DEPT,DEPT) |
Align EMPLOYEE table to DEPARTMENT table according to DEPT field and, as @a option works, return all matching records.
|
|
4 |
=A1.new(DEPT, A3(#).count():NUMBER) |
Perform association between A3 and A1 and list number of aligned records of the subtable EMPLOYEE in each group.
|
|
5 |
=A2.align(A1:DEPT,DEPT) |
As no option is present, only return the first eligible member.
|
For special sorting:
|
|
A |
|
|
1 |
=demo.query("select * from score") |
|
|
2 |
=A1.sort(class) |
Sort A1 by class using sort() function and display class values in the order of four, one, three, two.
|
|
3 |
=A1.group(class) |
Group A1 by class using group() function and display class values in the order of four, one, three, two; this method cannot specify the order of groups.
|
|
4 |
=["class one","class two","class three","class four"] |
|
|
5 |
=A1.align@a(A4,class) |
Use @a option to align all members, and as parameter x is absent, display A1’s records according to the order of class field values in A4, and group the result sequence.
|
Use @b option to enable binary search:
|
|
A |
|
|
1 |
=demo.query("select * from FAMILY").sort(EID) |
|
|
2 |
=demo.query("select top 11 * from EMPLOYEE") |
|
|
3 |
=A1.align@ba(A2:EID,EID) |
As A1 is ordered by EID field, we use binary search to speed up query.
|
Use @p option to return an alignment result set consisting of members’ sequence numbers in P:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT ") |
|
|
2 |
=demo.query("select EID,NAME,DEPT,SALARY from EMPLOYEE ") |
|
|
3 |
=A2.align@ap(A1:DEPT,DEPT) |
Align A2 to A1 and return a sequence consisting of sequence numbers of members of P.
|
Use @n option to align the primary table and subtable and return all members, where subtable members that do not match the primary table are stored in the last group:
|
|
A |
|
|
1 |
=demo.query("select top 15 * from SCORES") |
|
|
2 |
=demo.query("select top 3 * from STUDENTS") |
|
|
3 |
=A1.align@n(A2:ID,STUDENTID) |
The last row contains groups of members that cannot match A2. |
Use @s option to sort subtable records according to members of the primary table:
|
|
A |
|
|
1 |
=demo.query("select top 9 * from SCORES").sort(SCORE) |
|
|
2 |
=demo.query("select top 3 * from STUDENTS") |
|
|
3 |
=A1.align@s(A2:ID,STUDENTID) |
As @s option is present, sort A1 according to A2’s ID field and put non-matching members in the end.
|
