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.
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 |
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
|
|
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:
|
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:
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.
Parameter:
|
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 |
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) |
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 cursor 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 |
=A2.skip() |
|
|
13 |
=A3.result() |
|
|
14 |
=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.
Parameter:
|
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 |
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") |
|
|
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:
T.derive()
Description:
Add new fields to a pseudo table.
Syntax:
T.derive(xi :Fi,…)
Note:
The function adds Fi,…fields to pseudo table T, computes expression xi on each of T’s records, and generates a pseudo table consisting of the original fields and the newly-added fields.
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) |
Add EntryAge field and WrordAge field to the pseudo table |
|
4 |
=A3.import() |
Now the pseudo table has the following content:
|
