merge()

Read(1752) Label: 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.

15

=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()

CS.merge()

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:

A.merge()