Sorting and Locati on

Read(211) Label: sorting, location,

This chapter lists code example about data sorting and location, including Get members in odd positions, Calculate ranking, Members ranking top 10, the 3rd and the 2nd from the bottom of the sequence, and median, Calculate link relative ration for top 3, Members ranking at top 20% and middle 50%, Select 10 members randomly, Calculate max continuous interval, Sort a table sequence, Sort by specified order, and Create binary search index for a record sequence.

Get members in odd positions

 

A

 

1

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

 

3

=A1.step(2,1)

[1,3,5,7,9]

 

Get members in odd positions

 

A

 

1

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

 

3

=A1.step(2,1)

[1,3,5,7,9]

 

Calculate ranking

 

A

 

1

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

 

2

=A1.ranks@z(SCORE)

Calculate the ranking of all scores

3

=A1.rank@z(16, SCORE)

The rank of 16 points

4

=[99,98,97,96,93,87,99,95].rank@z(98)

The rank of 98 in the integer sequence

 

Calculate ranking

 

A

 

1

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

 

2

=A1.ranks@z(SCORE)

Calculate the ranking of all scores

3

=A1.rank@z(16, SCORE)

The rank of 16 points

4

=[99,98,97,96,93,87,99,95].rank@z(98)

The rank of 98 in the integer sequence

 

Members ranking top 10, the 3rd , the 2nd from the bottom of the sequence, and median

 

A

 

1

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

 

2

=A1.sort(-SCORE)

 

3

=A2(to(10))

Members with scores ranking top 10

4

=A2.m([3,-2])

The member with the score ranking the third, and the one with the score ranking the second-to-last

5

=round(A2.len()/2)

 

6

=A2(A5)

Median score

 

Members ranking top 10, the 3rd , the 2nd from the bottom of the sequence, and median

 

A

 

1

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

 

2

=A1.sort(-SCORE)

 

3

=A2(to(10))

Members with scores ranking top 10

4

=A2.m([3,-2])

The member with the score ranking the third, and the one with the score ranking the second-to-last

5

=round(A2.len()/2)

 

6

=A2(A5)

Median score

 

Calculate link relative ratio for top 3

 

A

 

1

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

 

2

=A1.sort(DATE)

Sort by DATE

3

=A2.psort(-CLOSING)

Sort by CLOSING

4

=A3(to(3))

The sequence numbers of records for the three dates with highest closing prices

5

=A4.(A2.calc(A4.~, CLOSING- CLOSING[-1]))

Calculate the increases growth rates of the three days

 

Calculate link relative ratio for top 3

 

A

 

1

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

 

2

=A1.sort(DATE)

Sort by DATE

3

=A2.psort(-CLOSING)

Sort by CLOSING

4

=A3(to(3))

The sequence numbers of records for the three dates with highest closing prices

5

=A4.(A2.calc(A4.~, CLOSING- CLOSING[-1]))

Calculate the increases growth rates of the three days

 

Members ranking at top 20% and middle 50%

 

A

 

1

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

 

2

=A1.sort(-SCORE)

 

3

=A2.len()

Total number of people

4

=round(A3*0.2)

The number of members ranking at the top 20%

5

=A2(to(A4))

Members ranking at the top 20%

6

=round(A3*0.25)

Positions of members ranking at the first 25% of the middle

7

=round(A3*0.75)

Positions of members ranking at the last 25% of the middle

8

=A2(to(A6,A7))

Records of members ranking at the middle 50%

9

=A8(1)

The highest score of middle ranking members

10

=A8.m(-1)

The lowest score of middle ranking members

 

Members ranking at top 20% and middle 50%

 

A

 

1

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

 

2

=A1.sort(-SCORE)

 

3

=A2.len()

Total number of people

4

=round(A3*0.2)

The number of members ranking at the top 20%

5

=A2(to(A4))

Members ranking at the top 20%

6

=round(A3*0.25)

Positions of members ranking at the first 25% of the middle

7

=round(A3*0.75)

Positions of members ranking at the last 25% of the middle

8

=A2(to(A6,A7))

Records of members ranking at the middle 50%

9

=A8(1)

The highest score of middle ranking members

10

=A8.m(-1)

The lowest score of middle ranking members

 

Select 10 members randomly

 

A

 

1

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

 

2

=A1.sort(rand())(to(10))

 

 

Select 10 members randomly

 

A

 

1

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

 

2

=A1.sort(rand())(to(10))

 

 

Calculate max continuous interval

 

A

 

1

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

 

2

=A1.sort(DATE)

 

3

=A2.max(a=if(CLOSING/CLOSING[-1]>=1.05, a+1,0))

The max continuous interval (days) when the increase is greater than 5%

 

Calculate max continuous interval

 

A

 

1

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

 

2

=A1.sort(DATE)

 

3

=A2.max(a=if(CLOSING/CLOSING[-1]>=1.05, a+1,0))

The max continuous interval (days) when the increase is greater than 5%

 

Sort a table sequence

 

A

 

1

=demo.query("select * from SCORES").sort(-SCORE)

 

2

=demo.query("select * from SCORES").psort(-SCORE)

 

 

Sort a table sequence

 

A

 

1

=demo.query("select * from SCORES").sort(-SCORE)

 

2

=demo.query("select * from SCORES").psort(-SCORE)

 

 

Sort by specified order

 

A

 

1

[CA,IL,KY,CO,NY]

 

2

=demo.query("select NAME,ABBR,CAPITAL,POPULATION from STATES")

 

3

=A2.align(A1,ABBR)

 

 

Sort by specified order

 

A

 

1

[CA,IL,KY,CO,NY]

 

2

=demo.query("select NAME,ABBR,CAPITAL,POPULATION from STATES")

 

3

=A2.align(A1,ABBR)

 

 

Create binary search index for a record sequence

 

A

 

1

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

 

2

=A1.sort(-SCORE)

Create a binary search index

3

=A2.select@b(SCORE:14.175)

Return matching records after the binary search is completed

4

=A1.psort(-SCORE)

Create an index

5

=A1(A4).pselect@b(SCORE:14.175)

 

6

=A4(A5)

Return the sequence number of the record matching the specified condition, after the binary search is completed

 

Create binary search index for a record sequence

 

A

 

1

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

 

2

=A1.sort(-SCORE)

Create a binary search index

3

=A2.select@b(SCORE:14.175)

Return matching records after the binary search is completed

4

=A1.psort(-SCORE)

Create an index

5

=A1(A4).pselect@b(SCORE:14.175)

 

6

=A4(A5)

Return the sequence number of the record matching the specified condition, after the binary search is completed