derive()

Read(299) 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 re-structure it as a table sequence with "original fields in P plus Fi,…", and then traverses 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 P

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 the result of expression xi 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,… fields to channel ch, making a new channel having “orginal fields of channel ch plus Fi,…”, and then traverse the records of ch to assign xi to each Fi. 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 appears null in the results of computing expression xi; 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,… fields to cursor cs, making a new cursor having “orginal fields of channel ch plus Fi,…”, and then traverses each of the records of cs to compute expression 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 the result of expression xi 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 table sequence/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

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@0("emp10.ctx",["192.168.0.118:8281"])

 

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 cluster 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 cluster composite table to A5’s table sequence by the latter’s key values

 

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