olympics ranking query
| От | David Garamond |
|---|---|
| Тема | olympics ranking query |
| Дата | |
| Msg-id | 41262968.2000507@zara.6.isreserved.com обсуждение исходный текст |
| Ответы |
Re: olympics ranking query
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: olympics ranking query (Bruno Wolff III <bruno@wolff.to>) |
| Список | pgsql-sql |
See http://www.athens2004.com/en/OlympicMedals/medals?noc=MGL . create table countrymedal ( countryid CHAR(3) PRIMARY KEY, gold INT NOT NULL, silver INT NOT NULL, bronze INT NOT NULL); COPY countrymedal (countryid, gold, silver, bronze) FROM stdin; ITA 5 6 3 FRA 5 3 5 UKR 5 1 1 RUS 4 8 10 GER 4 4 7 TUR 3 0 1 KOR 2 7 3 NED 2 5 4 HUN 2 3 1 SVK 2 2 1 ROM 2 0 2 GRE 2 0 1 POL 1 2 1 BLR 1 1 2 SUI 1 0 1 UAE 1 0 0 GBR 0 4 4 AUT 0 3 0 PRK 0 2 1 ESP 0 2 0 CUB 0 1 5 CZE 0 1 2 ZIM 0 1 1 USA 15 11 10 CHN 15 9 8 JPN 9 4 2 AUS 7 5 8 GEO 1 1 0 RSA 1 1 0 BUL 1 0 2 THA 1 0 2 IND 0 1 0 INA 0 1 0 KAZ 0 1 0 POR 0 1 0 SCG 0 1 0 AZE 0 0 2 BEL 0 0 2 BRA 0 0 2 DEN 0 0 2 ARG 0 0 1 CAN 0 0 1 COL 0 0 1 CRO 0 0 1 ISR 0 0 1 MGL 0 0 1 SLO 0 0 1 TRI 0 0 1 \. create sequence seq1; create sequence seq2; -- query #1: list of ranks by gold select setval('seq1', 1); select setval('seq2', 1); select setval('seq1', currval('seq1')+setval('seq2',count(*)))-count(*) as rank, count(*) as numranker, gold, silver,bronze from countrymedal group by gold, silver, bronze order by gold desc, silver desc, bronze desc; -- result of query #1 rank | numranker | gold | silver | bronze ------+-----------+------+--------+-------- 1 | 1 | 15 | 11 | 10 2 | 1 | 15 | 9 | 8 3 | 1 | 9 | 4 | 2 4 | 1 | 7 | 5 | 8 5 | 1 | 5 | 6 | 3 6 | 1 | 5 | 3 | 5 7 | 1 | 5 | 1 | 1 8 | 1 | 4| 8 | 10 9 | 1 | 4 | 4 | 7 10 | 1 | 3 | 0 | 1 11 | 1 | 2 | 7 | 3 12 | 1 | 2 | 5 | 4 13 | 1 | 2 | 3 | 1 14 | 1 | 2 | 2 | 1 15 | 1 | 2 | 0 | 2 16 | 1 | 2 | 0 | 1 17 | 1 | 1 | 2 | 1 18 | 1 | 1 | 1 | 2 19 | 2 | 1 | 1 | 0 21| 2 | 1 | 0 | 2 23 | 1 | 1 | 0 | 1 24 | 1 | 1 | 0 | 0 25 | 1 | 0 | 4 | 4 26 | 1 | 0 | 3 | 0 27 | 1 | 0 | 2 | 1 28 | 1 | 0 | 2 | 0 29 | 1 | 0 | 1 | 5 30 | 1 | 0 | 1 | 2 31 | 1 | 0 | 1 | 1 32 | 5 | 0 | 1 | 0 37 | 4 | 0| 0 | 2 41 | 8 | 0 | 0 | 1 (32 rows) -- query #2: list of countries ordered by their ranks select setval('seq1', 1); select setval('seq2', 1); select (case numranker when 1 then '' else '=' end) || rank as rank, countryid, cm.gold, cm.silver, cm.bronze from countrymedal cm left join (select setval('seq1', currval('seq1')+setval('seq2',count(*)) )-count(*) as rank, count(*) asnumranker, gold, silver, bronze from countrymedal group by gold, silver, bronze order by gold desc, silver desc, bronzedesc ) t1 on cm.gold=t1.gold and cm.silver=t1.silver and cm.bronze=t1.bronze order by t1.rank; -- result of query #2 rank | countryid | gold | silver | bronze ------+-----------+------+--------+-------- 1 | USA | 15 | 11 | 10 2 | CHN | 15 | 9 | 8 3 | JPN | 9 | 4 | 2 4 | AUS | 7 | 5 | 8 5 | ITA | 5 | 6 | 3 6 | FRA | 5 | 3 | 5 7 | UKR | 5 | 1 | 1 8 | RUS | 4| 8 | 10 9 | GER | 4 | 4 | 7 10 | TUR | 3 | 0 | 1 11 | KOR | 2 | 7 | 3 12 | NED | 2 | 5 | 4 13 | HUN | 2 | 3 | 1 14 | SVK | 2 | 2 | 1 15 | ROM | 2 | 0 | 2 16 | GRE | 2 | 0 | 1 17 | POL | 1 | 2 | 1 18 | BLR | 1 | 1 | 2 =19 | GEO | 1 | 1 | 0 =19 | RSA | 1 | 1 | 0 =21 | BUL | 1 | 0 | 2 =21 | THA | 1 | 0 | 2 23 | SUI | 1 | 0 | 1 24 | UAE | 1 | 0 | 0 25 | GBR | 0 | 4| 4 26 | AUT | 0 | 3 | 0 27 | PRK | 0 | 2 | 1 28 | ESP | 0 | 2 | 0 29 | CUB | 0 | 1 | 5 30 | CZE | 0 | 1 | 2 31 | ZIM | 0 | 1 | 1 =32 | IND | 0 | 1 | 0 =32 | INA | 0 | 1 | 0 =32 | KAZ | 0 | 1 | 0 =32 | POR | 0 | 1 | 0 =32 | SCG | 0 | 1 | 0 =37 | AZE | 0 | 0 | 2 =37 | BEL | 0 | 0 | 2 =37 | BRA | 0 | 0 | 2 =37 |DEN | 0 | 0 | 2 =41 | ARG | 0 | 0 | 1 =41 | CAN | 0 | 0 | 1 =41 | COL | 0 | 0 | 1 =41 | CRO | 0 | 0 | 1 =41 | ISR | 0 | 0 | 1 =41 | MGL | 0 | 0 | 1 =41 | SLO | 0 | 0 | 1 =41 | TRI | 0 | 0| 1 Challenge question: is there a simpler way to do query #1 (without any PL, and if possible without sequences too? -- dave
В списке pgsql-sql по дате отправления: