Spark SQL按年份分组获取cnt列最大值对应记录的实现方法

看起来你是想按年份分组，提取每组中cnt值最大的那条完整记录对吧？你的原SQL写法有两个核心问题：一是max(count(cnt))完全不符合需求（count(cnt)是统计分组内非空cnt的行数，不是取数值的最大值）；二是按yr, char分组会把每个年份下的不同字符拆成单独分组，没法拿到年份维度的最大值记录。

下面给你两种可行的解决方案：

方法一：使用窗口函数（推荐） #

窗口函数是处理这类「分组取TopN」场景最直观的方式，这里用ROW_NUMBER()给每个年份组内的记录按cnt降序编号，再筛选编号为1的记录即可：

WITH ranked_records AS (
    SELECT 
        yr,
        char,
        cnt AS count,
        -- 按年份分区，每个分区内按cnt从大到小排序生成行号
        ROW_NUMBER() OVER (PARTITION BY yr ORDER BY cnt DESC) AS row_num
    FROM view
)
SELECT yr, char, count
FROM ranked_records
WHERE row_num = 1;

补充说明： #

• 如果某个年份存在多条cnt值相同的最大值记录，ROW_NUMBER()会随机保留其中一条；如果想保留所有最大值记录，可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()。

方法二：先聚合最大值再关联原表 #

先通过子查询算出每个年份的最大cnt值，再和原表关联匹配出对应的完整记录：

SELECT 
    v.yr,
    v.char,
    v.cnt AS count
FROM view v
INNER JOIN (
    -- 先计算每个年份的最大cnt
    SELECT yr, MAX(cnt) AS max_cnt
    FROM view
    GROUP BY yr
) max_vals 
ON v.yr = max_vals.yr AND v.cnt = max_vals.max_cnt;

补充说明： #

• 这种方法会保留所有cnt等于年份最大值的记录，适合存在多个最大值的场景。

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