align()

Read(314) Label: sequence, sort, align,

P.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)

Equal 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. Usually, there is a ref field of the subtable associated with the primary table to reference the associated record of the primary table. In this occasion, the x expression usually acts as the ref field. Then, retrieve the values from the ref field in the subtable, judging if the records match the corresponding record in the primary table. 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.

Parameters:

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. If omitted, then it is interpreted as ~

y

The alignment expression of P; If omitted, then it is interpreted as P.~

n

Integer

Options:

@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 aligned to the designated position of n overlappedly.

@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.

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

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

=create(Class,Score)

 

2

=A1.record(["class four",99,"class one",89,

"class three",98,"class two",79])

3

=["class one","class two","class three","class four"]

 

4

=create(class,ID,Score)

 

5

=A4.record(["class four",1,99,"class one",1,89,

"class three",1,98,"class two",1,79, "class four",2,89,

"class one",2,99,"class three",2,96,"class two",2,78])

6

=A5.align@a(A3,class)

Use the @a option because each Class has multiple records

 

In this case, if using the sort function, the order will be "four,one,three,two" according to the alphabetical order.

7

=A5.align@a(A2:Class,class)

 

Ø  Alignment with integer sequence, which can usually speed up the computation

 

A

 

1

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

 

2

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

3

=A1.align@b(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)

The members in [5,6,7] 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)