align()
Description:
Align the records of a record sequence to a sequence, so as to group the record sequence.
Syntax:
|
P.align(A:x,y) |
If omitting x,y, then align the current records of P with members of A. |
|
P.align(n,y) |
Equivalent to P.align (to(n),y), and support @r. |
Note:
Align the records of P to A by the associated fields x and y, which means that, compute y against each record of P and compute x against each record of A, and then compare each value of x and y, if two of them are equal, then the two records are aligned.
This function is mainly for the scenario of primary table and subtable. In this occasion, the x expression is usually the foreign key field of the subtable. compare if records of the subtable match the corresponding records in the primary table according to the former’s foreign key. If they match, then it indicates that the two tables are aligned.
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. Therefore, construct a sequence composed of multiple associated records of P, take and store this sequence as a member of the result sequence, then the number of members in the result sequence is the same as that of the A.
Parameter:
|
P |
Sub record sequence/table sequence |
|
A |
Primary record sequence/ primary table sequence, according to which another record sequence is aligned |
|
x |
The field or field expression of A for relational operation; it is interpreted as ~ if omitted |
|
y |
The alignment expression of P; it is interpreted as P if omitted |
|
n |
Integer |
Option:
|
@a |
Of P records, return all members which are aligned according to the members of A and group the resulting sequence. By default, only the first member will be returned. |
|
@b |
If A is an ordered sequence, the binary search will be used |
|
@r |
y is an integer sequence, and every member of y is taken as the alignment position for aligning P to A; P will be repeatedly aligned to the designated position of n |
|
@p |
The return value is composed of the sequence numbers of members in the P |
|
@n |
Return all members of P whose records can be aligned to members of A. Of the result sets, there is an extra group to hold those members whose records fail to be aligned. |
|
@s |
In P, sort members by the same order as members of A, and put non-corresponding member(s) to A at the tail. |
|
@v |
Return a pure table sequence when P is a pure table sequence; by default return a pure sequence |
Return value:
Aligned table sequence/sub record sequence
Example:
Ø Computation after the subtable has been aligned to the primary table
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=A2.align@a(A1:DEPT,DEPT) |
Align the EMPLOYEE table to the DEPARTMENT table and return all records |
|
4 |
=A1.new(DEPT, A3(#).count():NUMBER) |
Perform the relational operation on A3 and A1 |
|
5 |
=A2.align(A1:DEPT,DEPT) |
Return the first satisfying member by default |
Ø For special sorting
|
|
A |
|
|
1 |
=demo.query("select * from score") |
|
|
2 |
=A1.sort(class) |
Use sort() function to sort records by class; values in the class field are displayed in the order of four/one/three/two
|
|
3 |
=A1.group(class) |
Use group() function to sort records by class; values in the class field are displayed in the order of four/one/three/two
|
|
4 |
=["class one","class two","class three","class four"] |
|
|
5 |
=A1.align@a(A4,class) |
Use @a option with align() to align class field of all records in A1 according to A4 and group the result sequence
|
Ø Alignment with integer sequence, which can usually speed up the computation
|
|
A |
|
|
1 |
=demo.query("select * from FAMILY ") |
|
|
2 |
=demo.query("select * from EMPLOYEE where EID<12") |
|
|
3 |
=A1.align@ba(A2:EID,EID) |
A2 is sorted and you can use the binary search to speed up the computation
|
Ø @r option is to align the primary table to the subtable overlappedly by the position designated by the integer sequence
|
|
A |
|
|
1 |
=demo.query("select * from FAMILY") |
|
|
2 |
=demo.query("select * from EMPLOYEE") |
|
|
3 |
=A1.derive(A2.pselect@a(EID==A1.EID): SubtableNo) |
First, find the sequence numbers of EMPLOYEE records corresponding to the FAMILY table, second, store these sequence numbers in the SubtableNo field as the integer sequence |
|
4 |
=A33.align@r(11,SubtableNo) |
Align to n directly by the SubtableNo |
|
5 |
=A3.align@rp(11,SubtableNo) |
Add the @p option, return the sequence number of the record instead of the record itself |
Ø @n option is to align the primary table to the subtable and return all members. The result set’s last group will be used to store the unaligned members.
|
|
A |
|
|
1 |
=demo.query("select * from STUDENTS") |
|
|
2 |
=demo.query("select * from STUDENTS1") |
|
|
3 |
=A1.align@n(A2:ID,ID) |
Members of the sequence in the last row are unaligned |
Ø @s option is to sort the records in the subtable by the members of primary table.
|
|
A |
|
|
1 |
=demo.query("select * from STUDENTS") |
|
|
2 |
=demo.query("select * from STUDENTS1 order by ID desc") |
|
|
3 |
=A1.align@s(A2:ID,ID) |
Use @s option to sort A1 by A2 |
