如何通过单条SQL语句获取数据集中各年份出现频次最高的名称

方案1：窗口函数实现（推荐，适配MySQL8.0+、PostgreSQL、Hive、Spark SQL等主流引擎） #

针对50万行级别的数据集，窗口函数是性能最优的实现方式，仅需要1次全表扫描即可完成全量计算，不需要逐年份单独查询。
实现逻辑：先按「年份+名称」分组统计每个名称的年度出现频次，再通过窗口函数按年份分区，对组内名称的频次倒序排名，最后筛选出排名为1的记录即可。

SELECT year, name, freq
FROM (
    SELECT 
        year,
        name,
        COUNT(1) AS freq,
        -- RANK()会将同频次的并列最高值统一标记为排名1，可返回所有并列第一的记录
        -- 如果需要每个年份仅返回1条、忽略并列，可替换为 ROW_NUMBER() OVER (PARTITION BY year ORDER BY COUNT(1) DESC)
        RANK() OVER (PARTITION BY year ORDER BY COUNT(1) DESC) AS name_rank
    FROM table_name
    GROUP BY year, name
) year_name_stats
WHERE name_rank = 1;

方案2：关联聚合实现（适配不支持窗口函数的老旧SQL版本，如MySQL5.x及更早） #

如果运行环境不支持窗口函数，可以通过多层子查询+内连接的方式实现等价逻辑，不需要依赖特殊语法：

SELECT n.year, n.name, n.freq
FROM (
    -- 统计每个年份下每个名称的出现频次
    SELECT year, name, COUNT(1) AS freq
    FROM table_name
    GROUP BY year, name
) n
INNER JOIN (
    -- 统计每个年份下所有名称的最高出现频次
    SELECT year, MAX(freq) AS max_freq
    FROM (
        SELECT year, name, COUNT(1) AS freq
        FROM table_name
        GROUP BY year, name
    ) name_year_count
    GROUP BY year
) m
-- 匹配同一年份下频次达到最大值的名称
ON n.year = m.year AND n.freq = m.max_freq;

小提醒：你原来写的基础SQL存在语法细节问题：group by name, order by count(1) desc 中的逗号属于语法错误，group by 子句仅接分组维度列，order by 是独立子句，不需要和group by用逗号连接。

性能对比 #

对比逐年份加where条件循环查询的方式，上面的单条语句最多需要2次全表扫描，对50万行的数据集来说执行速度会比逐次查询快一个数量级以上，不需要为每个年份单独跑一次统计逻辑。

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