mysql - 查询计算性别百分比

Question

在下表中，我需要根据计数排名前 2 位的城市。还需要表中给出的 2 个城市的纬度、经度。男性百分比、女性百分比单独计算。

+-------+---------+--------+-----+------+
| name  | city    | gender | lat | long |
+-------+---------+--------+-----+------+
| jon   | nyc     | m      | 40  | 74   |
| kat   | dallas  | f      | 32  | 96   |
| kay   | la      | m      | 34  | 118  |
| jim   | chicago | f      | 41  | 87   |
| joy   | nyc     | f      | 40  | 74   |
| kelly | dallas  | f      | 32  | 96   |
| lind  | chicago | m      | 41  | 87   |
| tyson | nyc     | m      | 40  | 74   |
| bush  | dallas  | f      | 32  | 96   |
| will  | chicago | m      | 41  | 87   |
| sony  | la      | f      | 34  | 118  |
| graph | la      | f      | 34  | 118  |
| mike  | la      | m      | 34  | 118  |
| pele  | chicago | m      | 41  | 87   |
| mony  | la      | f      | 34  | 118  |
+-------+---------+--------+-----+------+

期望的输出:

+---------+-----+------+-----------+----------+
| city    | lat | long | male_perc | fem_perc |
+---------+-----+------+-----------+----------+
| la      | 34  | 118  | 40        | 60       |
| chicago | 41  | 87   | 75        | 25       |
+---------+-----+------+-----------+----------+

Answer 1

  select city, 
         lat, 
         long, 
         100*sum(case when gender = 'm' then 1 else 0 end)/count(*) male_perc,
         100*sum(case when gender = 'f' then 1 else 0 end)/count(*) fem_perc,
    from cities
group by city, lat, long
order by count(*) desc
limit 2