Here’s how to use news() function.
Description:
Compute each member of a sequence according to the specified condition to generate multiple records and concatenate them into a new table sequence.
Syntax:
A.news(X;xi:Fi,…)
Note:
The function computes expression xi on each member of sequence A and sequence/integer X, generate multiple records and concatenate them to generate a new table sequence.
Parameter:
A |
A sequence |
X |
A sequence/integer; can be understood as to(X) when it is an integer, meaning performing X number of computations on sequence A |
xi |
An expression, whose results will be field values; the sign ~ used in the parameter references data from X instead of A |
Fi |
Field name of the result sequence; when omitted, field names are xi by default, and use the original field names when xi is #i |
Option:
@1 |
Left join, which creates an empty record when X is empty; here it is number 1 instead of letter l |
@m |
Use parallel processing to increase performance |
Return value:
A table sequence
Example:
When A is a sequence
|
A |
|
1 |
[1,2,3,4,5] |
|
2 |
=A1.news([10,20]; A1.~:a,~:b,a*b) |
Parameter X is a sequence and compute members of A and X one by one, that is, use A1’s members as values of field a, members of [10,20] as values of field b and result values of a*b as values of the 3rd field; as parameter Fi is absent, use parameter xi as field names |
3 |
=A1.news(2; A1.~:a, ~:b,a*b) |
Parameter X is an integer, which is equivalent to to(2), and compute each of A1’s member two times |
When A is a table sequence
|
A |
|
1 |
=demo.query("select top 5 ID,NAME,BIRTHDAY,DEPT,SALARY from EMPLOYEE") |
|
2 |
=A1.news(~;NAME,age(BIRTHDAY):AGE) |
Parameter X is a table sequence and symbol ~ is understood as member of A1; compute A1 and return records consisting of NAME field and AGE field, where AGE field is computed from A1’s BIRTHDAY field |
3 |
=A1.news(3;EID,NAME,SALARY*~:Salary) |
Parameter X is integer 3 and compute each member of A1 three times; ~ is understood as the current number of computations |
A为排列
|
A |
|
1 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER,SALARY from EMPLOYEE") |
|
2 |
=A1.group(DEPT).(~.group(GENDER)) |
Group table sequence A1 by DEPT and then group the result by GENDER |
3 |
=A2.news(~;A2.~.DEPT,A2.~.~.GENDER,A2.~.~.avg(SALARY):AvgSalary) |
Get DEPT and GENDER values from A2’s group and calculate average salary of each GENDER in each DEPT |
When parameter xi uses #i
|
A |
|
1 |
=demo.query("select top 10 EID,NAME,BIRTHDAY,GENDER,SALARY from EMPLOYEE") |
|
2 |
=A1.group(GENDER;~:GenderGroup,~.avg(SALARY):Avg) |
Group A1 by GENDER and calculate average salary in each group |
3 |
=A2.news(GenderGroup;EID,#2,GENDER, age(~.BIRTHDAY):age,SALARY+1000:Nsalary,A2.Avg:AvgSalary) |
Compute the expression on each group of A2 and generate records to form a new table sequence, during which #2 represents NAME, the 2nd field of GenderGroup |
Use @1 option to perform left join
|
A |
|
1 |
=demo.query("select top 5 EID,NAME,BIRTHDAY,GENDER,SALARY from EMPLOYEE") |
|
2 |
=A1.group(GENDER;~:GenderGroup) |
Group A1’s records by GENDER |
3 |
=demo.query("select top 10 EID,NAME,BIRTHDAY,GENDER,SALARY from EMPLOYEE") |
|
4 |
=A3.group(GENDER;avg(SALARY):avg) |
Group A3 by GENDER and calculate average salary in each group |
5 |
=A4.join(GENDER,A2:GENDER,GenderGroup) |
Perform association between A2 and A4 according to GENDER field; since A2 does not have records where GENDER is M, the corresponding GenderGroup field values are represented by nulls after table association |
6 |
=A5.news(GenderGroup;EID,NAME,GENDER,age(~.BIRTHDAY):age,avg:AvgSalary) |
Compute the expression on record sequence A5 to generate new records, from which those whose GenderGroup is empty are by default discarded |
7 |
=A5.news@1(GenderGroup;EID,NAME,GENDER,age(~.BIRTHDAY):age,avg:AvgSalary) |
Use @1 option to create an empty record when record sequence X is empty |
Related function:
Description:
Get new values for the fields of records in a channel and update them into the channel.
Syntax:
ch.news(X;xi:Fi,…)
Note:
With channel ch, the function computes values of fields in record sequence X and updates the new field values into the channel. Fi is the new field name that will be automatically identified if the parameter is omitted; xi represents the new field values. This is an attached computation.
Parameter:
ch |
Channel |
X |
Record sequence |
xi |
Expression, whose results will be field values; the sign ~ used in the parameter references data from X instead of A. The sign # is used to represent a field with a ordinal number |
Fi |
Field name in the given channel; will be automatically identified if the parameter is omitted |
Return value:
The original channel with new fields
Example:
|
A |
|
1 |
=demo.cursor("select * from EMPLOYEE") |
|
2 |
=demo.query("select * from EMPLOYEE") |
|
3 |
=channel() |
Create a channel |
4 |
=A2.group(GENDER;~:group) |
Group records by GENDER |
5 |
=A1.groupx(GENDER;avg(SALARY):avg) |
Group records by GENDER |
6 |
=A5.join(GENDER,A4:GENDER,group) |
|
7 |
=A3.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary) |
A6 is one of the groups; compute field values based on each group and concatenate the results into A3’s channel |
8 |
=A3.fetch() |
Fetch and store the existing data in the channel |
9 |
=A6.push(A3) |
Be ready to push data in A6’s cursor into A3’s channel, but the action needs to wait |
10 |
=A6.fetch() |
Data in A6’s cursor is pushed into the channel and operations are performed as the fetch() operation is performed over A6 |
11 |
=A3.result() |
|
Description:
Compute new cursor field values to update them into the original cursor.
Syntax:
cs.news(X;xi:Fi,…)
Note:
The function computes field values based on cursor cs, updates the newly-computed fields into the original cursor where xi is field value of each record and Fi is the new field name, which will be automatically identified if Fi is omitted.
Parameter:
cs |
Cursor |
X |
Record sequence |
xi |
An expression, whose values are uses as the new field values. It is treated as null if omitted; in that case, : Fi can’t be omitted. The sign # is used to represent a field with a ordinal number |
Fi |
Filed name of the new cs; use the identifiers parsed from expression xi if it is omitted |
Option:
@1 Left join, which creates an empty record when record sequence X is empty
Return value:
The original cursor with the new field values
Example:
|
A |
|
1 |
=demo.cursor("select * from EMPLOYEE") |
|
2 |
=demo.query("select * from EMPLOYEE") |
|
3 |
=A2.group(GENDER;~:group) |
Perform group by GENDER |
4 |
=A1.groupx(GENDER;avg(SALARY):avg) |
Perform group by GENDER |
5 |
=A4.join(GENDER,A3:GENDER,group) |
|
6 |
=A5.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary) |
A4 is composed of data groups; based on each group field values are computed and a cursor is generated; and then cursors are concatenated into a new one. |
7 |
=A6.fetch() |
|
8 |
=MySQL1.query("select * from EMPLOYEE") |
|
9 |
=A8.group(GENDER;~:group) |
Group GENDER field |
10 |
=A4.join(GENDER,A9:GENDER,group) |
|
11 |
=A10.news@1(group;EID,NAME,GENDER,age(~.BIRTHDAY):age,salary,avg:AvgSalary) |
Compute each GENDER value for each group, generate an empty table for the group where GENDER is M and then concatenate result records into a new table sequence |
12 |
=A11.fetch() |
|
Related functions:
A.news(X;xi:Fi,…)
Description:
Get values of specified field(s) in a composite table according to key values in a table sequence or a cursor, concatenate them and return result as a table sequence or a cursor.
Syntax:
T.news(A/cs:K,x:C,…;wi,...)
Note:
Composite table T and table sequence A or cursor cs have a relationship of many-to-one. The function retrieves specified field values x,... from T and concatenate them to form a new table sequence according to the correspondence between table sequence A or cursor cs’s key/dimension values and T’s fields (begin form the first field). It is required that A/cs be ordered by the key and their key has same order as that of T’s first field keys.
It is used to join a table sequence or a cursor to a composite in a one-to-many relationship and return a result set containing all records in the composite table pointing to the table sequence or cursor’s primary key. While T.new() function performs a join in a one-to-one or one-to-many relationship.
Parameter:
T |
A composite table |
A/cs |
A table sequence/cursor/composite table cursor |
K |
Field names; when there are K… parameters after A/cs, correspond them with theses specified fields |
x |
Field names or aggregate function count/sum/max/min/avg |
C |
Column alias |
wi |
Filtering condition; retrieve the whole set when this parameter is absent; separate multiple conditions by comma(s) and their relationships are AND. Besides regular filtering expressions, you can also use the following five types of syntax in a filtering condition, where K is a non-key field in entity table T: 1.K=w w is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of w is null or false, the corresponding record in the entity table will be filtered away; when w is expression Ti.find(K) and the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. 2.(K1=w1,…Kn=wn,w) wi is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of wi is null or false, the corresponding record in the entity table will be filtered away; when wi is expression Ti.find(K) and the to-be-selected fields C,... contain K1,...Ki, T1,... Ti’s referencing field will be assigned to K1,...Ki correspondingly; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. w is an expression that returns a Boolean value; each of the multiple Ki=wi and w has a relationship of AND; you can reference Ki in w. 3.K:Ti Ti is a table sequence. Compare Ki value in the entity table with key values of Ti and discard records whose Ki value does not match; when the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K. 4.K:Ti:null Filter away all records that satisfy K: Ti. |
Option:
@r Copy records of the primary table and return result that aligned by A/cs and where aggregation can be performed
Return value:
A table sequence/cursor
Example:
|
A |
|
1 |
=file("score.ctx").open() |
Open a composite table file
|
2 |
=demo.query("select * from students").keys(ID) |
Return a table sequence with ID as the key |
3 |
=A1.news(A2:ID,STUDENTID,SUBJECT,SCORE;SUBJECT=="Math",SCORE>70) |
Join A1’s composite table and A2’s table sequence where the filteing condition is SUBJECT=Math & SCORE>70 and return eligible records in the composite table |
4 |
=A1.news@r(A2:ID,NAME,sum(SCORE):TotalScore) |
Sum scores of all subjects for each student and return result: |
Use special types of filtering conditions: