merge()
Here’s how to use merge() function.
A .merge()
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:
CS.merge()
Description:
Merge data of the member cursors of a cursor/composite table cursor sequence.
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. 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.
When parameter CS is a sequence of composite table cursors or one of multicursors, the merge will be performed automatically by the dimension and the fields before it.
Parameter:
|
CS |
A sequence of cursors/composite table 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:
A cursor
Example:
|
|
A |
B |
C |
|
|
||
|
1 |
=demo.cursor("select * from STOCKRECORDS") |
|
|
Retrieve data and return a cursor |
|
||
|
2 |
For |
|
|
|
|
||
|
3 |
|
=A1.fetch(500) |
|
Fetch data from the cursor |
|||
|
4 |
|
if B3==null |
break |
|
|||
|
5 |
|
else |
|
|
|||
|
6 |
|
|
=B3.sort(STOCKID) |
Sort by STOCKID |
|||
|
7 |
|
|
=file("D:\\"+"a"+string(A2)+".txt").export@t(C6) |
Store data retrieved each time in a file. |
|||
|
8 |
|
|
=B1=B1|file("D:\\"+"a"+string(A2)+".txt") |
File object sequence
|
|||
|
9 |
for B1 |
|
|
|
|||
|
10 |
|
=A9.cursor@t() |
|
|
|||
|
11 |
|
=C1=C1|B10 |
|
File cursor sequence
|
|||
|
12 |
=C1.merge(STOCKID) |
|
|
Merge members of the sequence in order by STOCKID. |
|||
|
13 |
=A12.fetch() |
|
|
Get records from the merged cursor |
|||
|
14 |
=directory@p("D://*.txt") |
|
|
Below are several txt files:
|
|||
|
15 |
for A14 |
|
|
|
|||
|
16 |
|
=file(A15).cursor@t() |
=B14=B14|B16 |
|
|||
|
17 |
=B14.merge@i(STOCKID) |
=A17.fetch() |
|
|
|||
|
18 |
=directory@p("D://*.txt") |
|
|
The txt files are the same as the above |
|||
|
19 |
for A18 |
|
|
|
|||
|
20 |
|
=file(A19).cursor@t() |
=B18=B18|B20 |
|
|||
|
21 |
=B18.merge@u(STOCKID) |
=A21.fetch() |
|
Return a new cursor in which duplicate records have been removed. STOCKID is already ordered.
|
|||
|
22 |
=directory@p("D://* txt") |
|
|
The txt files are the same as above |
|||
|
23 |
for A22 |
|
|
|
|||
|
24 |
|
=file(A23).cursor@t() |
=B22=B22| 24 |
|
|||
|
25 |
=B22.merge@d(STOCKID) |
=A25.fetch() |
|
A new cursor created by removing from the first cursor the members of the other cursors; STOCKID is already ordered. |
|||
|
26 |
=directory@p("D://*.txt") |
|
|
|
|||
|
27 |
for A26 |
|
|
|
|||
|
28 |
|
=file(A27).cursor@t() |
=B26=B26|B28 |
|
|||
|
29 |
=B26.merge@x(STOCKID) |
=A29.fetch() |
|
Get dinstinct recors from the cursors to create a new cursor ordered by STOCKID |
|||
Related function: