Set, aggreg te and loop operations

This chapter lists code examples of handling sets, aggregate operations and loop operation, including Judge if it is an interger sequence, Judge if it is an ascending integer sequence, Judge if it is a member or a subset of an integer sequence, Judge if members from two sequences are equal, Perform union, intersection and difference on sorted sequences by merge function, Calculate quadratic sum and variance, Calculate weighted average, Calculate average of an integer sequence after removing the max and the min, Calculate link relative ratio over adjacent rows, Calculate moving average over adjacent sets, Aggregate loop, and Union record sequences with different data structures.

Judge if it is an integer sequence

 

A

 

1

[1,2,3,4]

 

2

=A1.pselect(~!=int(~))==null

Check if any member is not integer

 

Judge if it is an integer sequence

 

A

 

1

[1,2,3,4]

 

2

=A1.pselect(~!=int(~))==null

Check if any member is not integer

 

Judge if it is an ascending integer sequence

 

A

 

1

[1,2,3,4,5]

 

2

=A1.pselect(~!=int(~) || ~<=~[-1] )==null

 

 

Judge if it is an ascending integer sequence

 

A

 

1

[1,2,3,4,5]

 

2

=A1.pselect(~!=int(~) || ~<=~[-1] )==null

 

 

Judge if it is a member or a subset of an integer sequence

 

A

 

1

[1,2,3,4,5,6,7]

 

2

=A1.pos(2)!=null

true for member

3

=A1.pos@c([2,3])!=null

true for continuous subset

4

=A1.pos@c([3,2])!=null

false

5

=A1.pos@c([2,5])!=null

false

6

=A1.pos@i([2,3])!=null

true for subset

7

=A1.pos@i([3,2])!=null

false

8

=A1.pos@i([2,5])!=null

true

 

Judge if it is a member or a subset of an integer sequence

 

A

 

1

[1,2,3,4,5,6,7]

 

2

=A1.pos(2)!=null

true for member

3

=A1.pos@c([2,3])!=null

true for continuous subset

4

=A1.pos@c([3,2])!=null

false

5

=A1.pos@c([2,5])!=null

false

6

=A1.pos@i([2,3])!=null

true for subset

7

=A1.pos@i([3,2])!=null

false

8

=A1.pos@i([2,5])!=null

true

 

Judge if members from two sequences are equal

 

A

 

1

[1,2,3]

 

2

[3,2,1]

 

3

=A1.eq(A2)

true

Judge if members from two sequences are equal

 

A

 

1

[1,2,3]

 

2

[3,2,1]

 

3

=A1.eq(A2)

true

Perform union, intersection, and difference on sorted sequences by merge function

 

A

 

1

=demo.query("select CLASS,STUDENTID, SUBJECT, SCORE from SCORES where CLASS=? and SUBJECT=? and STUDENTID<?","Class one", "Math",10)

 

2

=demo.query("select CLASS,STUDENTID, SUBJECT,SCORE from SCORES where CLASS=? and SUBJECT=? and STUDENTID>?","Class two", "Math",5)

 

3

=A1.sort(STUDENTID)

 

4

=A2.sort(STUDENTID)

 

5

=[A3:A4].merge(STUDENTID)

Concatenation

6

=[A3:A4].merge@u(STUDENTID)

Union

7

=[A3:A4].merge@i(STUDENTID)

Intersection

8

=[A3:A4].merge@d(STUDENTID)

Difference

 

Perform union, intersection, and difference on sorted sequences by merge function

 

A

 

1

=demo.query("select CLASS,STUDENTID, SUBJECT, SCORE from SCORES where CLASS=? and SUBJECT=? and STUDENTID<?","Class one", "Math",10)

 

2

=demo.query("select CLASS,STUDENTID, SUBJECT,SCORE from SCORES where CLASS=? and SUBJECT=? and STUDENTID>?","Class two", "Math",5)

 

3

=A1.sort(STUDENTID)

 

4

=A2.sort(STUDENTID)

 

5

=[A3:A4].merge(STUDENTID)

Concatenation

6

=[A3:A4].merge@u(STUDENTID)

Union

7

=[A3:A4].merge@i(STUDENTID)

Intersection

8

=[A3:A4].merge@d(STUDENTID)

Difference

 

Calculate quadratic sum and variance

 

A

 

1

[1,2,3,4,5,6,7,8]

 

2

=A1.sum(~*~)

Quadratic sum

3

=A1.variance()

Variance

4

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

5

=A4.variance(SCORE)

 

 

Calculate quadratic sum and variance

 

A

 

1

[1,2,3,4,5,6,7,8]

 

2

=A1.sum(~*~)

Quadratic sum

3

=A1.variance()

Variance

4

=demo.query("select NAME,EVENT,SCORE from GYMSCORE")

 

5

=A4.variance(SCORE)

 

 

Calculate weighted average

 

A

 

1

[9,9.1,8.5,9.8,9.4]

 

2

[0.9,0.8,1.0,0.95,1.0]

 

3

=(A1**A2).sum()/A2.sum()

 

 

Calculate weighted average

 

A

 

1

[9,9.1,8.5,9.8,9.4]

 

2

[0.9,0.8,1.0,0.95,1.0]

 

3

=(A1**A2).sum()/A2.sum()

 

 

Calculate average of an integer sequence after removing the max and the min

 

A

 

1

[99,98,95,93,87,89,90,96,94]

 

2

=(A1.sum()-A1.max()-A1.min())/(A1.len()-2)

 

3

=(A1\A1.min()\A1.max()).avg()

 

 

Calculate average of an integer sequence after removing the max and the min

 

A

 

1

[99,98,95,93,87,89,90,96,94]

 

2

=(A1.sum()-A1.max()-A1.min())/(A1.len()-2)

 

3

=(A1\A1.min()\A1.max()).avg()

 

 

Calculate link relative ratio over adjacent rows

 

A

 

1

[1,2,3,4,5,6]

 

2

=A1.(~/~[-1]-1)

 

3

=demo.query("select DATE,sum(CLOSING) AMOUNT from STOCKRECORDS GROUP BY DATE")

 

4

=A3.derive(AMOUNT/AMOUNT[-1]-1: Period-over-period)

 

 

Calculate link relative ratio over adjacent rows

 

A

 

1

[1,2,3,4,5,6]

 

2

=A1.(~/~[-1]-1)

 

3

=demo.query("select DATE,sum(CLOSING) AMOUNT from STOCKRECORDS GROUP BY DATE")

 

4

=A3.derive(AMOUNT/AMOUNT[-1]-1: Period-over-period)

 

 

Calculate moving average over adjacent sets

 

A

 

1

[1,2,3,4,5,6]

 

2

=A1.(~[-1,1].avg())

 

3

=demo.query("select STOCKID, DATE,CLOSING from STOCKRECORDS where STOCKID=?","000062")

 

4

=A3.(CLOSING[-3,3].avg())

 

 

Calculate moving average over adjacent sets

 

A

 

1

[1,2,3,4,5,6]

 

2

=A1.(~[-1,1].avg())

 

3

=demo.query("select STOCKID, DATE,CLOSING from STOCKRECORDS where STOCKID=?","000062")

 

4

=A3.(CLOSING[-3,3].avg())

 

 

Aggregate loop

 

A

 

1

[1,2,3,4,5,6,7]

 

2

=A1.iterate(~*~~;1)

Return the product

 

Aggregate loop

 

A

 

1

[1,2,3,4,5,6,7]

 

2

=A1.iterate(~*~~;1)

Return the product

 

Union record sequences with different data structures

 

A

 

1

=demo.query("select * from STUDENTS")

 

2

=demo.query("select * from EMPLOYEE ").derive(interval@y(BIRTHDAY,now()):AGE)

 

3

=A1|A2

 

4

=A3.select(GENDER:"F").avg(AGE)

 

 

Union record sequences with different data structures

 

A

 

1

=demo.query("select * from STUDENTS")

 

2

=demo.query("select * from EMPLOYEE ").derive(interval@y(BIRTHDAY,now()):AGE)

 

3

=A1|A2

 

4

=A3.select(GENDER:"F").avg(AGE)