derive()

Read(373) Label: derive,

Here’s how to use derive() function.

A .derive()

Description:

Add one or more fields to a table sequence or a record sequence.

Syntax:

A.derive(xi:Fi,…)

Note:

The function adds Fi,… field(s) to table sequence/record sequence A to generate a table sequence consisting of the original fields and the new field(s)by traversing records of A to assign each Fi with value xi.

Parameters:

Fi

Field name, which shouldn’t have the same name as any of the original fields in A

xi

Expression, whose results are used as the values of the derived fields

A

A table sequence/record sequence

Options:

@m

Use parallel algorithm to handle data-intensive or computation-intensive tasks to enhance performance; no definite order for the records in the result set.

@i

Won’t generate a record if there is expression xi and its result is null (this won’t affect a record with null value in the original table sequence)

@x(…;n)

Unfold original fields whose values are records into n levels; default of n is 2

Return value:

A table sequence with added field(s)

Example:

 

A

 

1

=demo.query("select NAME,BIRTHDAY,HIREDATE from EMPLOYEE")

2

=A1.derive(interval@y(BIRTHDAY, HIREDATE):EntryAge,

age(HIREDATE):WorkAge)

3

=A1.derive@m(age(HIREDATE):WorkAge)

Use the @m option to increase performance of big data handling.

4

=file("D:\\txt_files\\data1.txt").import@t()

Below is the file data1.txt:

5

=A4.derive@i(SCORE:score_not_null)

If the SCORE value is null, the corresponding record won’t be generated

6

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

7

=demo.query("select NAME,GENDER,DEPT,SALARY from EMPLOYEE")

 

8

>A7.switch(DEPT,A6:DEPT)

Switch values of DEPT of A7’s table over with corresponding records

9

=A7.derive(SALARY*5:BONUS)

Add a new field:BONUS

10

=A7.derive@x(SALARY*5:BONUS)

Use @x option to unfold the DEPT field whose values are records; the default unfolding levels are 2

Note:

The difference between new() and derive(): The new() constructs a new table sequence without changing the original one. By comparison, the derive() copies the original fields and then adds new fields.

P .derive()

Description:

Generate a table sequence according to a record sequence.

Syntax:

P.derive()

Note:

The function creates a table sequence by copying the data structure of record sequence P, in order to use the table sequence functions.

Parameters:

P

A record sequence.

Options:

@o

Do not copy the record sequence’s data structure, which will change the record sequence’s structure and which will adjust the number of fields as needed. The option will achieve a better performance as well as an increased risk of error. It’s recommended to be used only when the original record sequence is no longer needed.

Return value:

A table sequence.

Example:

 

A

 

1

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

 

2

=A1.select(SCORE>90)

Return a record sequence.

3

=A2.derive()

Generate a table sequence from the record sequence.

4

=A2.derive@o()

Directly reference the records of the record sequence, instead of generating new ones.

ch. derive()

Description:

Add fields to records in a channel.

Syntax:

ch.derive(xi :Fi,…)

Note:

The function adds Fi,… field(s) to channel ch to generate a new channel consisting of the original fields and the new field(s) by traversing records of ch to assign each Fi with value xi. This is an attached computation.

Parameters:

ch

Channel

Fi

Field name, which shouldn’t be same as one of the existing ones

xi

An expression, whose results will be the values of the new fields

Options:

@i

Won’t generate a corresponding record if there is expression xi  and its result is null; won’t affect the field values of existing fields.

Return value:

Channel

Example:

 

A

 

1

=demo.cursor("select NAME,BIRTHDAY,HIREDATE from Employee")

 

2

=file("D:\\txt_files\\data1.txt").import@t()

Below is data1.txt

3

=channel()

Create a channel

4

=channel()

Create a channel

5

=A3.derive(interval@y(BIRTHDAY,HIREDATE):EntryAge, age(HIREDATE):WorkAge)

Add EntryAge field and WorkAge field in A3’s channel

6

=A3.fetch()

Attach ch.fetch() function that gets the final result set to A3’s channel to fetch and store the existing data in the channel

7

=A4.derive@i(SCORE:score_not_null)

Won’t genearate the corresponding record if a SCORE value is null

8

=A4.fetch()

Attach ch.fetch() function that gets the final result set to A4’s channel

9

=A1.push(A3)

Be ready to push A1’s data into A3’s channel, but the action needs to wait

10

=A2.push(A4)

Push data in A2’s sequence into A4’s channel

11

=A1.skip()

Data in A1’s cursor is pushed into the channel and operations are performed as the skip() operation is performed over A1

12

=A3.result()

13

=A4.result()

cs .derive()

Description:

Add one or more fields to a cursor.

Syntax:

cs.derive(xi :Fi,…)

Note:  

The function adds Fi,… field(s) to cursor cs to generate a new cursor consisting of the original fields and the new field(s) by traversing records of cs to assign each Fi with value xi. It supports multicursors.

Parameters:  

cs

Cursor

Fi

Field name. In this case, Fi cannot be of the same name as the existing fields in cs

xi

Expression, whose computed results are the field values

Options:

@i

Won’t generate a record if there is expression xi and its result is null (this won’t affect a record with null value in the original table sequence)

Return value:

The original cursor cs

Example:

 

A

 

1

=demo.cursor("select NAME,BIRTHDAY,HIREDATE from Employee")

 

2

=A1.derive(interval@y(BIRTHDAY,HIREDATE):EntryAge, age(HIREDATE):WorkAge)

Add fields EntryAge and WorkAge to the original cursor.

3

=A2.fetch()

4

=file("D:\\txt_files\\data1.txt").cursor@t()

Below is the file data1.txt:

5

=A4.derive@i(SCORE:score_not_null)

If the SCORE value is null, the corresponding record won’t be generated

 

6

=A5.fetch()

Related functions:

cs.new()

T.derive()

Description:

Add a composite table’s fields to a table sequence or a cursor according to the latter’s key values.

Syntax:

T.derive(A/cs,x:C,…;wi,...)

Note:

The function adds one or more fields of composite table T to table sequence A or cursor/multicursor cs according to key values of the latter. It returns a table sequence when adding fields to a table sequence; and returns a cursor when adding fields to an ordinary cursor or a multicursor.

Parameters:

T

A composite table

A/cs

A table sequence/cursor/multicursor

x

Field value

C

Column alias;can be absent

wi

Filtering condition; separate multiple conditions, which should be met at the same time, by comma(s).

Return value:

A table sequence/cursor

Example:

 

A

 

1

=file("D:\\emp10.ctx")

 

2

=A1.create()

Open a composite table

3

=demo.query("select  EID from EMPLOYEE").keys(EID)

 

4

=A2.derive

(A3,NAME,GENDER)

Add fields of the composite table to A3’s table sequence by the latter’s key values

5

=demo.query("select  EID from EMPLOYEE").cursor()

 

6

=A2.derive(A5,NAME:NAME,GENDER:GENDER).fetch()

Add fields of the composite table to A5’s cursor by the latter’s key values

7

=file("D:/employees.ctx").create()

Open a composite table

8

=demo.query("select EID,DEPT from employee").keys(EID)

Return a table sequence with EID as the key

9

=A7.derive(A8,NAME:name,GENDER;GENDER=="M")

Add certain fields of A7’s composite table to A8’s table sequecne according to key values; filtering condition over A7 is GENDER=="M"

T.derive( A/cs,x:C,…;wi,… )

Description:

Add fields of a cluster composite table to a cluster memory table /cluster cursor according to the latter’s key values.

Syntax:

T.derive(A/cs,x:C,…;wi,...)

Note:

The function adds one or more fields of cluster composite table T to cluster memory table A or cluster cursor/multicursor cs according to key values of the latter.

Parameters:

T

A composite table

A/cs

A cluster memory table /cluster cursor/cluster multicursor

x

Field value

C

Column alias

wi

Filtering condition; separate multiple conditions, which should be met at the same time, by comma(s)

Return value:

A cluster cursor

Example:

 

A

 

1

=file("emp1.ctx":[1],["192.168.0.118:8281"])

 

2

=A1.open()

Open a cluster table

3

=file("emp2.ctx":[1],["192.168.0.151:8281"]).open().memory()

Return a cluster memory table

4

=A2.derive(A3,DEPT,SALARY).fetch()

Add fields of the cluster composite table to A3’s cluster memory table by the latter’s key values

5

=file("emp2.ctx":[1],["192.168.0.151:8281"]).open().cursor()

 

6

=A2.derive(A5,DEPT,SALARY).fetch()

Add fields of the cluster composite table to A5’s cluster cursor by the latter’s key values

 

T.derive( T’/A/cs,x:C,…;wi,... )

Description:

Add fields of a pseudo table to another pseudo table, a table sequence or a cursor according to the latter’s key values.

Syntax:

T.derive(T’/A/cs,x:C,…;wi,...)

Note:

The function adds specified fields of pseudo table T to another pseudo table T’ or table sequence A or cursor/multicurso cs according to the latter’s key values, and returns result as a pseudo table.

Parameters:

T

A pseudo table

T’/A/cs

A pseudo table/table sequence/cursor/multicursor

x

Field values

C

Field alias; can be omitted

wi

Filtering conditions separated by comma and where the relationship between them is AND

Return value:

A pseudo table

Example:

 

A

 

1

=file("emp.ctx").open().pseudo()

Return a pseudo table as follows:

2

=file("emp6.ctx").open().pseudo()

Return a pseudo table as follows:

3

=A1.derive(A2,BIRTHDAY,SALARY:em_salary)

Add BIRTHDAY and SALARY fields of A1’s pseudo table to A2 according to the latter’s key values, rename SALARY field em_salary, and return a pseudo table as follows:

4

=A3.import()

5

=T("emp6.ctx")

Return a table sequence

6

=A1.derive(A5,BIRTHDAY,SALARY)

Add BIRTHDAY field and SALARY field of A1’s pseudo table to A5’s table sequemce according to the latter’s key values

7

=A6.import()

8

=T("emp6.ctx").cursor()

Return a cursor as follows:

9

=A1.derive(A8,BIRTHDAY,SALARY)

Add BIRTHDAY field and SALARY field of A1’s pseudo table to A8’s cursor according to the latter’s key values

10

=A9.import()

T.derive( xi : Fi ,… )

Description:

Add new fields to a pseudo table.

Syntax:

T.derive(xi :Fi,…)

Note:

The function adds Fi,… fields to pseudo table T by computing expression xi on each record of T and generates a new pseudo table consisting of all fields of T and and Fi,… fields.

Parameters:

T

A pseudo table

xi

Field expressions, whose results are used as values of new fields

F’i

Field names that should not be same as those in the original pseudo table

Options:

@i

Won’t generate a corresponding record when parameter xi is present and the result of computing it over the current record is null (fields in the original record won’t be affected)

Return value:

A pseudo table

Example:

 

A

 

1

=file("bhday.ctx").open().pseudo()

 

2

=A1.derive(interval@y(BIRTHDAY,HIREDATE):EntryAge, age(HIREDATE):WorkAge)

Add EntryAge field and WorkAge field to A1’s pseudo table

3

=A2.import()

Return a pseudo table as follows:

det()

Description:

Calculate the determinant of a square matrix.

Syntax:

det(A)

Note:

The function computes the determinant of square matrix A.

Parameters:

A

Square matrix

Return value:

Numeric value

Example:

 

A

 

1

[[0,1,2],[1,0,3],[4,-3,8]]

 

2

=det(A1)

-2