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, traverses each record of A, assigns each Fi with value xi and returns a new table sequence consisting of the original fields and the new field(s) Fi.
Parameter:
|
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 |
Option:
|
@m |
Use parallel algorithm to handle data-intensive or computation-intensive tasks to enhance performance; and 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 |
|
@o |
When parameter A is a pure table sequence, add columns directly to it instead of generating a new table sequence |
|
@z |
Perform the inverse operation; only applies to non-pure sequences |
Return value:
Table sequence
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.
|
|
11 |
=demo.query("select NAME,BIRTHDAY,HIREDATE from EMPLOYEE").i() |
Return a pure table sequence. |
|
12 |
=A11.derive@o(age(HIREDATE):WorkAge) |
Add the new column directly to the original table sequence rather than generating a new one; here the function returns same result as A11. Following is the result:
|
Perform the inverse operation:
|
|
A |
|
|
1 |
=demo.query("select * from SCORES ") |
Return a table sequence:
|
|
2 |
=A1.derive(cum(SCORE;CLASS,STUDENTID):F1) |
Add F1 field and perform iterative operation in a loop function to cumulatively sum SCORE values in records having same CLASS and STUDENTID values, and use the result sums as F1 values:
|
|
3 |
=A1.derive@z(cum(SCORE;CLASS,STUDENTID):F1) |
Use @z option to perform the inverse operation:
|
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.
Parameter:
|
P |
A record sequence |
Option:
|
@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.The option is disabled when there are pure table sequence records in the record sequence |
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:
Attach a computation that adds one or more fields to a channel and return the original channel.
Syntax:
ch.derive(xi :Fi,…)
Note:
The function attaches a computation to channel ch, which will compute expression xi on each of its records and assign the reuslts to the new field Fi, and return the original channel containing its original fields and the new field(s) Fi,….
This is an attachment computation.
Parameter:
|
ch |
A 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 |
Option:
|
@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").cursor@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) |
Attach a computation to channel A3, which will add EntryAge field and WorkAge field to it, and return the original channel. |
|
6 |
=A3.fetch() |
Execute the result set function in channel A3 and keep the current data in channel. |
|
7 |
=A4.derive@i(SCORE:score_not_null) |
Attach a computation to channel A3, which won’t genearate the corresponding record if a SCORE value is null. |
|
8 |
=A4.fetch() |
Execute the result set function in channel A4 and keep the current data in 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) |
Be ready to push A2’s data into A4’s channel, but the action needs to wait. |
|
11 |
=A1.skip() |
Fetch data from cursor A1 while pushing data into channel A3 to execute the attached computation and keep the result. |
|
12 |
=A2.fetch() |
Fetch data from cursor A2 while pushing data into channel A4 to execute the attached computation and keep the result. |
|
13 |
=A3.result() |
Get channel A3’s result:
|
|
14 |
=A4.result() |
Get channel A4’s result:
|
cs.derive()
Description:
Attach the action of adding fields to a cursor and return the original cursor.
Syntax:
cs.derive(xi :Fi,…)
Note:
The function attaches a computation to cursor cs, which will compute expression xi on each record of cursor cs and make the results values of the new field Fi, and returns the table sequence consisting of the original fields and field Fi to the original cursor cs.
Parameter:
|
cs |
A curs |
|
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 |
Option:
|
@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") |
Return a cursor. |
|
2 |
=A1.derive(interval@y(BIRTHDAY,HIREDATE):EntryAge, age(HIREDATE):WorkAge) |
Attach an action to cursor A1 – add new fields EntryAge and WorkAge to it, and return a table sequence consisting of the original fields and the newly-added ones to the original cursor. |
|
3 |
=A1.fetch() |
Fetch data from cursor A1 on which A2’s computation is already executed:
|
|
4 |
=file("D:\\txt_files\\data1.txt").cursor@t() |
Below is the file data1.txt:
|
|
5 |
=A4.derive@i(SCORE:score_not_null) |
With @i option is present and if the SCORE value is null, the corresponding record won’t be generated.
|
|
6 |
=A4.fetch() |
Fetch data from cursor A4 on which A5’s computation is already executed:
|
Related functions:
T.derive()
Description:
Define the computation of adding fields to a pseudo table and return a new pseudo table.
Syntax:
T.derive(xi :Fi,…)
Note:
The function defines a computation on pseudo table T, which computes expression xi on each of T’s records and makes the results field values of the new fields Fi,…, and returns a new pseudo table consisting of the original fields of T and fields Fi,….
Parameter:
|
T |
A pseudo table |
|
Fi |
Field names, which should not be same as names of the existing fields in T |
|
xi |
Expression, whose results are used as field values |
Option:
|
@i |
Do not generate a corresponding record for a row when parameter xi is present and its computing result is null |
Return value:
Pseudo table
Example:
|
|
A |
|
|
1 |
=create(file).record(["emp.ctx"]) |
Below is content of composite table emp.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table object. |
|
3 |
=A2.derive(interval@y(BIRTHDAY,HIREDATE):EntryAge, age(HIREDATE):WorkAge) |
Define a computation on A2’s pseudo table, where EntryAge field and WorkAge field will be added, and return a new pseudo table. |
|
4 |
=A3.import() |
Import data from A3’s pseudo table while the computation defined on A2’s pseudo table in A3 is executed, and return the following content:
|
