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.
|
|
A |
|
|
1 |
[1,2,3,4,5,6,7,8,9,10] |
|
|
3 |
=A1.step(2,1) |
[1,3,5,7,9] |
|
|
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) |
|
|
|
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 |
|
|
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 |
|
|
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 |
|
|
A |
|
|
1 |
=demo.query("select NAME,EVENT,SCORE from GYMSCORE") |
|
|
2 |
=A1.sort(rand())(to(10)) |
|
|
|
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% |
|
|
A |
|
|
1 |
=demo.query("select * from SCORES").sort(-SCORE) |
|
|
2 |
=demo.query("select * from SCORES").psort(-SCORE) |
|
|
|
A |
|
|
1 |
[CA,IL,KY,CO,NY] |
|
|
2 |
=demo.query("select NAME,ABBR,CAPITAL,POPULATION from STATES") |
|
|
3 |
=A2.align(A1,ABBR) |
|
|
|
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 |