SQL按分组取占比最高的城市及对应占比（含并列场景实现）

现有SQL的错误核心来自两处：

• 概率计算逻辑错误：外层查询直接用分组的最大频次top计算占比，而非当前城市对应的实际频次freq，导致所有返回行的概率都错误显示为分组最高占比数值。
• 保留字未转义：group是SQL保留关键字，未做转义处理会引发语法解析异常，可能导致WHERE条件判断逻辑出错，返回不符合过滤规则的行。

SELECT `group`, city AS top_city, freq / total AS prob
FROM (
     SELECT 
         `group`, 
         city, 
         freq,
         MAX(freq) OVER(PARTITION BY `group`) AS max_freq,
         SUM(freq) OVER(PARTITION BY `group`) AS total
     FROM
         (SELECT `group`, city, COUNT(*) AS freq
          FROM mytable
          GROUP BY `group`, city) inner_query
     ) outer_query
WHERE outer_query.freq = outer_query.max_freq

• 最内层聚合先统计每个分组下每个城市的对应总用户数freq
• 中层通过窗口函数分别计算每个分组的最高城市用户数max_freq、分组总用户数total
• 外层过滤仅保留城市用户数等于分组最高值的行，用当前城市的实际频次freq除以分组总用户数得到正确占比
• 天然适配边界要求：分组存在唯一最高城市时仅返回1行，多城市并列最高时返回所有并列行

内容的提问来源于stack exchange，提问作者jbuddy_13