news()

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

x_i

An expression, whose results will be field values; the sign ~ used in the parameter references data from X instead of A

F_i

Field name of the result sequence; when omitted, field names are x_i by default, and use the original field names when x_i is #i

@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

@q

Return a sequence of sequences when there is only one parameter x_i and parameter F_i is absent and when the result of computing x_i 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 3^rd 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.

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

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.

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 2^nd field of GenderGroup.

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

A

1

=file("emp.txt").cursor@w().fetch()

Return data as follows:

2

=A1.news@q(2;[A1.~(2),A1.~(3)*~])

With @q option, return a sequence of sequences as there is only one parameter x_i and parameter F_i is absent and since the result of computing x_i is a sequence:

ch

A channel

X

A record sequence

x_i

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 sequence number

F_i

Field name in the given channel; will be automatically identified if the parameter is omitted

@1

Enable a left join; when a record in the given record sequence is empty, create an empty record for the new table sequence

A

1

=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE")

Return a cursor.

2

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

Return a table sequence:

3

=A2.group(GENDER;~:gup)

Group table sequence A2 by GENDER field and return the following result:

4

=A1.groupx(GENDER;avg(SALARY):avg)

Group table sequence A1 by GENDER field, compute average SALARY values in each group and return a cursor. Below is the returned data:

5

=A4.join(GENDER,A3:GENDER,gup)

Attach a computation to cursor A4, which will perform a foreign-key-style join with table A3, and return the original cursor A4. Below is the returned data of A4 executing A5’s compuation:

6

=channel(A5)

Create a channel and be ready to push cursor A5’s data to the channel, but the push action needs to wait.

7

=A6.news(gup;EID,#2:Lname,GENDER,age(~.BIRTHDAY):Age,SALARY+50: Salary,avg:AvgSalary)

Attach a computation to channel A6, which will get values for gup table - #2：Lname means renaming the table’s second field Lname, and form a table sequence consisiting of fields EID, Lname,GENDER, Age, Salary and AvgSalary, and return the result to the original channel.

8

=A6.fetch()

Execute the result set function in channel A6 and keep the current data in channel.

9

=A5.fetch()

Fetch data from cursor A5 while pushing data to channel A6 to execute the attached computation and keep the result.

10

=A6.result()

Get channel A6’s result:

A

1

=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE")

Return a cursor.

2

=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE where GENDER='M' ")

Return a table sequence:

3

=A2.group(GENDER;~:gup)

Group table sequence A2 by GENDER field and return the following result:

4

=A1.groupx(GENDER;avg(SALARY):avg)

Group table sequence A1 by GENDER field, compute average SALARY values in each group and return a cursor. Below is the returned data:

5

=A4.join(GENDER,A3:GENDER,gup)

Attach a computation to cursor A4, which will perform a foreign-key-style join with table A3, and return the original cursor A4. Below is the returned data of A4 executing A5’s compuation:

6

=channel(A5)

Create a channel and be ready to push cursor A5’s data to the channel, but the push action needs to wait.

7

=A6.news@1(gup;EID,NAME,GENDER,age(~.BIRTHDAY):Age,Salary,avg:AvgSalary)

Attach a computation to channel A6, which will get values for gup table - #2：Lname means renaming the table’s second field Lname, and form a table sequence consisiting of fields EID, Lname,GENDER, Age, Salary and AvgSalary, and return the result to the original channel.

Use @1 option to perform a left join; create an empty record for the result table sequence when a record in the record sequene is empty.

8

=A6.fetch()

Execute the result set function in channel A6 and keep the current data in channel.

9

=A5.fetch()

Fetch data from cursor A5 while pushing data to channel A6 to execute the attached computation and keep the result.

10

=A6.result()

Get channel A6’s result:

cs

A cursor

X

Record sequence

x_i

An expression, whose values are uses as the new field values. It is treated as null if omitted; in that case, : F_i can’t be omitted. The sign # is used to represent a field with a sequence number

F_i

Filed name of the new cs; use the identifiers parsed from expression x_i if it is omitted

@1

Left join, which creates an empty record when record sequence X is empty

A

1

=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE")

Return a cursor.

2

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

Return a table sequence:

3

=A2.group(GENDER;~:gup)

Group table sequence A2 by GENDER field and return result set as follows:

4

=A1.groupx(GENDER;avg(SALARY):avg)

Group cursor A1 by GENDER field, compute average SALARY value in each group and return a cursor; below is data in the result cursor:

5

=A4.join(GENDER,A3:GENDER,gup)

Attach a computation to cursor A4, which performs foreign key join with A3 and return the original cursor A4; below is the data of cursor A4 where A5’s computation is executed:

6

=A4.news(gup;EID,#2:Lname,GENDER,age(~.BIRTHDAY):Age,SALARY+50: Salary,avg:AvgSalary)

Attach a computation to cursor A4 to compute gup field values, renames the 2^nd field Lname using expression #2：Lname, form a table sequence consisting of EID, Lname, GENDER, Age, Salary and AvgSalary to return cursor A4, and returns the original cursor.

7

=A4.fetch()

Fetch data from cursor A4 where A6’s computation is executed (it would be better to fetch data in batches when a huge amount of data is involved):

A

1

=demo.cursor("select  EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE")

Return a cursor.

2

=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY,SALARY from EMPLOYEE where GENDER='M' ")

Return a table sequence:

3

=A2.group(GENDER;~:gup)

Group table sequence A2 by GENDER field and return result set as follows:

4

=A1.groupx(GENDER;avg(SALARY):avg)

Group cursor A1 by GENDER field, compute average SALARY value in each group and return a cursor; below is data in the result curso

5

=A4.join(GENDER,A3:GENDER,gup)

Attach a computation to cursor A4, which performs foreign key join with A3 and returns the original cursor A4; below is data in cursor A4 where the attached computation is executed:

6

=A4.news@1(gup;EID,NAME,GENDER,age(~.BIRTHDAY):Age,Salary,avg:AvgSalary)

Attach a computation to cursor A4 to compute gup field values, form a table sequence consisting of EID, NAME, GENDER, Age, Salary and AvgSalary to return cursor A4, and returns the original cursor. @1 option enables left join to create an empty record when the corresponding record in record sequence X is empty.

7

=A4.fetch()

Fetch data from cursor A4 where A6’s computation is executed; below is the result set:

T

A composite table

A/cs

A table sequence/cursor/composite table cursor

K

 A/cs’s field name; when it is specified, use it to match with T’s first field; when it is absent, use A/cs’s key (/dimension) to perform the matching; when there are multiple Ks, use colon (:) to separate them

x

Field names or aggregate function count/sum/max/min/avg

C

Column alias

wi

Filtering condition on T; 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 usually uses expression T_i.find(K) or T_i.pfind(K), where T_i 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 T_i.find(K) and the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K; when w is expression T_i.pfind(K) and the to-be-selected fields C,... contain K, sequence numbers of K values in T_i will be assigned to K.

2．(K₁=w₁,…K_i=w_i,w)

K_i=w_i is an assignment expression. Generally, parameter w_i can use expression T_i.find(K_i) or T_i.pfind(K), where T_i is a table sequence; when w_i is expression T_i.find(K_i) and the to-be-selected fields C,... contain K_i, T_i’s referencing field will be assigned to K_i correspondingly; when w_i is expression T_i.pfind(K_i) and the to-be-selected fields C,... contain K_i, sequence numbers of K_i values in T_i will be assigned to K_i.

w is a filter expression; you can reference K_i in w.

3．K:T_i

T_i is a table sequence. Compare K_i value in the entity table with key values of T_i and discard records whose K_i value does not match; when the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K.

4．K:T_i:null

Filter away all records that satisfy K: T_i

@r

A composite table

A

1

=connect("demo").cursor("SELECT STUDENTID,CLASS,SUBJECT,SCORE FROM SCORES")

Return a cursor.

2

=file("scores-news.ctx")

3

=A2.create@y(#STUDENTID,#CLASS,SUBJECT,SCORE)

Create a composite table and set STUDENTID and CLASS as its dimension.

4

=A3.append@i(A1)

5

=connect("demo").query("SELECT ID,NAME,GENDER,AGE FROM STUDENTS")

Return a table sequence.

6

=A4.news(A5:ID,CLASS,STUDENTID,NAME,GENDER,SUBJECT,SCORE)

A4 is the subtable and A5 is the primary table; perform the matching according to table sequence A5’s ID field and T’s first key field STUDENTID and return a table sequence consisting of CLASS, STUDENTID, NAME, GENDER, SUBJECT and SCORE fields; copy records of the primary table and return the following result:

7

=A5.keys(ID)

Set ID as table sequence A5’s key.

8

=A4.news(A7,CLASS,STUDENTID,NAME,GENDER,SUBJECT,SCORE)

As parameter K is absent, perform the matching according to A7’s key and the composite table’s first key field and return same result as A6.

9

=A4.news@r(A7,NAME,GENDER,sum(SCORE):TotalScore)

Use @r option to summarize data in the subtable.

10

=A4.news(A7,CLASS,STUDENTID,NAME,GENDER,SUBJECT,SCORE;CLASS=="Class one",SUBJECT=="Math")

Perform filtering on A4’s data according to filter condition: CLASS=="Class one" and SUBJECT=="Math" during association.

A

1

=file("sco.ctx").open()

Open a composite table as follows:

2

=file("stu.txt").import@t()

Return a table sequence:

3

=A1.news(A2:Class:StudentID,NAME,SUBJECT,SCORE)

A1 is the subtable and A2 is the primary table; perform the matching between table sequence A2’s Class and StudentID fields and T’s first two fields – Class and StudentID – and return a table sequence consisting of NAME,SUBJECT,SCORE; copy records of the primary table and return the following result:

4

=A1.news@r(A2:Class:StudentID,NAME,sum(SCORE):TotalScore)

Use @r option to summarize data in the subtable.

A

1

=demo.cursor("select STUDENTID,CLASS,SUBJECT,SCORE from SCORES")

Below is content of the cursor:

2

=file("scores.ctx")

3

=A2.create@y(#STUDENTID,#CLASS,SUBJECT,SCORE )

Create a composite table.

4

=A3.append@i(A1)

Append cursor A1’s data to the composite table’s base table.

5

=demo.query("select  ID,NAME,GENDER,AGE  from STUDENTS")

Return a table sequence:

6

=A3.news(A5:ID,STUDENTID,NAME,SUBJECT,SCORE)

Match the composite table’s key values to table sequence’s fields and retrieve corresponding composite table fields and return a table sequence:

7

=create(Subject,Num).record(["PE",01,"Math",02,"History",03]).keys(Subject)

Generate a table sequence using Subject as the key:

8

=A3.news(A5:ID,NAME,SUBJECT,SCORE;SUBJECT=A7.find(SUBJECT))

Use K=w filtering mode; in this case w is T_i.find(K) and entity table records making SUBJECT=A7.find(SUBJECT) get null or false are discarded; SUBJECT is the selected field, to which table sequence A7’s referencing field is assigned.

9

=A3.news(A5:ID,NAME,SUBJECT,SCORE;SUBJECT=A7.pfind(SUBJECT))

Use K=w filtering mode; in this case w is T_i.pfind(K) and entity table records making SUBJECT=A7.pfind(SUBJECT) get null or false are discarded; SUBJECT is the selected field, to which its sequence numbers in table sequence A7 are assigned.

10

=A3.news(A5:ID,NAME,SUBJECT,SCORE;SUBJECT:A7)

Use K:T_i filtering mode; compare the entity table’s SUBJECT values with the table sequence’s key values and discard entity table records that cannot match.

11

=A3.news(A5:ID,NAME,SCORE;SUBJECT:A7)

This is a case where K isn’t selected; SUBJECT isn’t the selected field, so only filtering is performed.

12

=A3.news(A5:ID,NAME,SUBJECT,SCORE;SUBJECT:A7:null)

Use K:T_i:null filtering mode; compare the entity table’s SUBJECT values with the table sequence’s key values and discard entity table records that can match.

13

=create(Score,Level).record([90,"A",80,"B",70,"C",60,"D"]).keys(Score)

Return a table sequence using Score as the key:

14

=A3.news(A5:ID,NAME,SUBJECT,SCORE;(SCORE=A13.find(SCORE),SUBJECT=A7.find(SUBJECT),SCORE!=null&&SUBJECT!=null))

Use (K₁=w₁,…K_i=w_i,w) filtring mode; return records that meet all conditions.