Chapter 12 Sorting and Location

Read(341) 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.

12.1 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]

 

12.2 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)

Ranking of 16 points

4

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

Ranking of 98 in the integer sequence

5

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

Find ranking for 98; won’t ignore duplicate members

6

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

 

7

=A6.pivot(CLASS,STUDENTID;SUBJECT,SCORE)

Column-to-row transposition

8

=A7.sort(CLASS,-English)

Sort by class and English in descending order

9

=A8.derive(rank(English;CLASS):RANK)

 

 

12.3 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

 

12.4 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

 

12.5 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

 

12.6 Select 10 members randomly

 

A

 

1

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

 

2

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

 

 

12.7 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%

12.8 Sort a table sequence

 

A

 

1

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

 

2

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

 

 

12.9 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)

 

 

12.10 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