Tabulate Olympic Games Medal Standings
l Problem
The table below records the data of one of the Olympic Games.
The Country table is as given below:
The Athlete table records the country that each athlete represents, as given below:
The MatchResult table records the results of matches, as given below:
Please make the medal table for the current Olympics Games, including the numbers of gold medals, silver medals, and bronze medals won by each country/district and sorted and ranked by total gold medals. Please note that countries may tied for the same place.
l Tip
General steps: First, associate Ranking with Country by replacing the values of the Country field of the Athlete table with the corresponding records in the Country table and then replacing the values of the Athlete field of the MatchResult table with corresponding records in the Athlete table; then, group the MatchResult table by Country; third, count the gold medals, silver medals, and bronze medals won by countries respectively; fourth, sort the table sequence according to the three variables (keywords): the number of gold medals, the number of silver medals, and the number of bronze medals. To solve the problem that countries may tied for the same place, use the rank function.
1. Replace the values of the Country field of the Athlete table with the corresponding records in the Country table, and the values of the Athlete field of the MatchResult table with corresponding records in the Athlete table;
2. Group the MatchResult table by Country;
3. Create a new table sequence based on the sequence formed after grouping. The table sequence shall include country names, and the numbers of gold medals, silver medals, and bronze medals won by countries respectively, and reserve a “Ranking” field. Because countries will probably get the same place, the values of the Ranking field will be filled later.
4. Sort the table sequence according to t he three variables (keywords): the number of gold medals, the number of silver medals, and the number of bronze medals; run the rank function to fill the Ranking field.
5. Sort the table by Ranking.
l Code
|
|
A |
|
|
1 |
=file("C:\\txt\\Country.txt").import@t().keys(ID) |
The Country table |
|
2 |
=file("C:\\txt\\Athlete.txt").import@t().keys(Athlete) |
The Athlete table |
|
3 |
=file("C:\\txt\\MatchResult.txt").import@t() |
The MatchResult table |
|
4 |
>A2.switch(Country,A1), A3.switch(Athlete,A2) |
Replace the specified fields in the Athlete table and the MatchResult table with the corresponding records |
|
5 |
=A3.group(Athlete.Country) |
Group athletes by Country |
|
6 |
=A5.new(:Ranking, Athlete.Country.Country:Country, ~.count(Ranking==1):Gold,~.count(Ranking==2): Silver, ~.count(Ranking==3):Bronze) |
Create a new table sequence, calculate the numbers of golden medals, silver medals, and bronze medals won by each country, pick out the country names, and preserve a ranking field with the value Null. |
|
7 |
=A6.([Gold,Silver,Bronze]) |
List the sequence composed of numbers of gold medals, silver medals, and bronze medals won by countries respectively |
|
8 |
>A6.run(Ranking=A7.rank@z([Gold,Silver,Bronze])) |
Rank countries according to the above sequence |
|
9 |
=A6.sort(Ranking) |
Sort the table by Ranking |
l Result
