Display the Olympic gold medal table
The following is the competition data of a certain Olympic Games.
Country Table Country.txt:
The athlete table Athlete.txt records the country to which each athlete belongs:
The Match result table MatchResult.txt records all the results of the competition:
Please list the gold medal standings for this Olympic Game, including the number of gold, silver, and bronze medals from each country, sorted by the number of gold medals, and provide rankings, taking into account the tie situation.
As long as the country field in the athlete table is replaced with the record in the country table, and the athlete field in the match result table is also replaced with the corresponding record, the match results can be directly linked to the country. Then, the match results can be grouped by country, and it is easy to calculate the number of gold, silver, and bronze medals of each country. As for the ranking of the three key fields, simply sort them in a sequence composed of them, and the problem of tie can be solved using the ranks function.
| A | B | |
|---|---|---|
| 1 | =T("Country.txt").keys(ID) | =T("Athlete.txt").keys(Athlete) |
| 2 | =T("MatchResult.txt") | |
| 3 | >B1.switch(Country,A1), A2.switch(Athlete,B1) | |
| 4 | =A2.groups(Athlete.Country.Country:Country;null:Ranking, count(Ranking==1):Gold, count(Ranking==2):Silver, count(Ranking==3):Bronze) | |
| 5 | =A4.([Gold,Silver,Bronze]).ranks@z() | >A4.run(Ranking=A5(#)) |
| 6 | =A4.sort(Ranking) |
https://try.esproc.com/splx?3QB
A1 reads the country table, B1 reads the athlete table, A2 reads the match result table.
A3 uses the switch function for foreign key association to convert the country IDs in the country table into corresponding country records, and the athlete IDs in the match result table into corresponding athlete records.
A4 groups and aggregates the match result table by the athlete's country, reserve the Ranking field as the ranking, and calculate the number of gold, silver, and bronze medals of each country:
A5 takes the quantity of gold, silver, and bronze medals from each country to form a sequence, using the ranks@z function to calculate the medal table rankings of each country, and by default, it can handle tied situations when calculating rankings. B4 fills in the ranking results in A4:
A6 sorts by medal table ranking:
