Here’s how to use merge() function.
Description:
Merge multiple table sequences/record sequences.
Syntax:
A.merge (xi,…)
Note:
The function merges multiple table sequences/record sequences A(i)s (which can be represented as A(i)|…) according to all fields when parameter xi is omitted and when primary keys are not set for A, a sequence of table sequences/record sequences. For records having same xi, first retrieve those in A(i) and then those in A(i+1).
Parameter:
A |
Multiple table sequences/record sequences of the same structure |
xi |
A field of A(i); if performing merge by multiple fields, use the comma to separate them, for example, x1,x2... |
Option:
@u |
Remove the duplicates from the table sequence/record sequence generated from unioning members of A(i)s in certain order; records with same xi have same corresponding members of A(i) |
@i |
Return a table sequence/record sequence composed of the common members of A(i)s |
@d |
Generate a new table sequence/record sequence by removing members of A(2)&…A(n) from A(1). |
@o |
Do not assume that A(i) is already sorted by [xi,…] |
@0 |
Put records with null values at the end |
@x |
Remove common members of A(i) and union the other members to generate a new table sequence/sequence |
Return value:
Table sequence/record sequence
Example:
|
A |
|
1 |
=demo.query("select EID,NAME,GENDER,SALARY from EMPLOYEE where EID<6") |
EID field is ordered.
|
2 |
=demo.query("select EID,NAME,GENDER,SALARY from EMPLOYEE where EID>3") |
EID field is ordered.
|
3 |
=[A1,A2].merge(EID) |
Merge A1 and A2 in order by EID field:
|
4 |
=[A1,A2].merge@u(EID) |
Merge A1 and A2 in order by EID field and remove duplicates from the result.
|
5 |
=[A1,A2].merge@i(EID) |
Merge A1 and A2 in order by EID field but only keep records that have duplicates. |
6 |
=[A1,A2].merge@d(EID) |
Delete A2’s members from A1. |
7 |
=[A1,A2].merge@o(SALARY) |
|
8 |
=[A1,A2].merge@x(EID) |
Merge A1 and A2 to get a new sequence by removing their common members. |
9 |
=demo.query("select * from EMPLOYEE where GENDER = 'M'").keys(EID) |
|
10 |
=demo.query("select * from EMPLOYEE where GENDER = 'F'").keys(EID) |
|
11 |
=[ A9,A10].merge() |
With xi omitted, this table sequence is the result of merge by the order of the primary keys. |
12 |
=demo.query("select * from EMPLOYEE where GENDER = 'M' and EID<15") |
|
13 |
=demo.query("select * from EMPLOYEE where GENDER = 'M' and EID>=15") |
|
14 |
=[A12,A13].merge(EID,GENDER) |
Merge by the EID field and GENDER field. |
=A1.run(EID=null) |
|
|
16 |
=[A2,A15].merge(EID) |
|
17 |
=[A2,A15].merge@0(EID) |
Records with null values are put in the end. |
Related functions:
Description:
Merge data of the member cursors of a sequence of cursors and return a multicursor.
Syntax:
CS.merge(xi,…)
Note:
CS is a sequence of cursors ordered by [xi,…], and from each cursor a sequence of records can be output. The function merges the records of these cursors by the expression xi and returns a multicursor.
Members of the cursor sequence must be of the same structure. If members are multicursors, they must have the same number of parallel cursors and be segmented synchronically.
This is a delayed function.
Parameter:
CS |
A sequence of cursors. |
xi |
An expression. If performing merge by multiple fields, use comma to separate them, for example, x1,x2... |
Option:
@u |
Union operation. Remove the duplicate records from the resulting cursor obtained by unioning the cursor members of CS in certain order. By default, the duplicate records are included. |
@i |
Intersection operation. Return a cursor composed of common members of members of CS, the sequence of cursors. |
@d |
Difference operation. Create a new cursor by removing members of CS2&…CSn from CS1. |
@0 |
Put records with null values at the end. |
@x |
Only merge distinct records in member cursors. |
Return value:
Multicursor
Example:
|
A |
|
1 |
=connect("demo").cursor("SELECT top 10 EID,NAME,DEPT,GENDER FROM employee ") |
Return a cursor whose data is as follows:
|
2 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where GENDER='M' ") |
Return a cursor whose data is as follows:
|
3 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where DEPT='Sales' ") |
Return a cursor whose data is as follows:
|
4 |
=[A1,A2,A3] |
Return a sequence of cursors. |
5 |
=A4.merge(EID) |
Merge records in the cursor members of the sequence in order according to EID field. |
6 |
=A5.fetch() |
Fetch data from cursor A5 (it would be better to fetch data in batches when a huge amount of data is involved):
|
Use @u option to perform union operation:
|
A |
|
1 |
=connect("demo").cursor("SELECT top 10 EID,NAME,DEPT,GENDER FROM employee ") |
Return a cursor whose data is as follows:
|
2 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where GENDER='M' ") |
Return a cursor whose data is as follows:
|
3 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where DEPT='Sales' ") |
Return a cursor whose data is as follows:
|
4 |
=[A1,A2,A3] |
Return a sequence of cursors. |
5 |
=A4.merge@u(EID) |
Use @u option to merge records of member cursors in the sequence in order according to EID field, during which dulicate members are discarded. |
6 |
=A5.fetch() |
Fetch data from cursor A5:
|
Use @i option to perform intersection operation:
|
A |
|
1 |
=connect("demo").cursor("SELECT top 10 EID,NAME,DEPT,GENDER FROM employee ") |
Return a cursor whose data is as follows:
|
2 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where GENDER='M' ") |
Return a cursor whose data is as follows:
|
3 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where DEPT='Sales' ") |
Return a cursor whose data is as follows:
|
4 |
=[A1,A2,A3] |
Return a sequence of cursors. |
5 |
=A4.merge@i(EID) |
Use @i option to get the intersection and returna cursor containing common records of the member cursors in the sequence. |
6 |
=A5.fetch() |
Fetch data from cursor A5:
|
Use @d option to perform difference operation:
|
A |
|
1 |
=connect("demo").cursor("SELECT top 10 EID,NAME,DEPT,GENDER FROM employee ") |
Return a cursor whose data is as follows:
|
2 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where GENDER='M' ") |
Return a cursor whose data is as follows:
|
3 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where DEPT='Sales' ") |
Return a cursor whose data is as follows:
|
4 |
=[A1,A2,A3] |
Return a sequence of cursors. |
5 |
=A4.merge@d(EID) |
Use @d option to perform the difference operation that removes records of cursor A2 and cursor A3 from cursor A1 and forms a new cursor. |
6 |
=A5.fetch() |
Fetch data from cursor A5:
|
Use @x option to get non-duplicate members from the sequence of cursors to form a new sequence:
|
A |
|
1 |
=connect("demo").cursor("SELECT top 10 EID,NAME,DEPT,GENDER FROM employee ") |
Return a cursor whose data is as follows:
|
2 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where GENDER='M' ") |
Return a cursor whose data is as follows:
|
3 |
=connect("demo").cursor("SELECT top 5 EID,NAME,DEPT,GENDER FROM employee where DEPT='Sales' ") |
Return a cursor whose data is as follows:
|
4 |
=[A1,A2,A3] |
Return a sequence of cursors. |
5 |
=A4.merge@x(EID) |
Use @x option to perform the difference operation that gets non-duplicate records from members cursors of the sequence to form a new sequence. |
6 |
=A5.fetch() |
Fetch data from cursor A5:
|
Related function: