Hive中按多列分组并获取每组最频繁desc的实现问题

我来帮你搞定这个问题！你的原查询没能得到预期结果，核心原因是外层分组的逻辑不对——它把每个(date,id,desc)组合都保留了下来，并没有筛选出每个(date,id)分组里出现次数最多的desc。

正确解决方案思路 #

我们需要借助窗口函数实现“分组内取Top1”的需求，具体分三步：

1. 先统计每个(date,id,desc)组合的出现次数；
2. 对每个(date,id)分组内的记录，按计数从高到低排名；
3. 筛选出排名第一的记录，就是每个分组里出现次数最多的desc及其计数。

具体SQL代码 #

SELECT date, id, desc, count_desc
FROM (
    SELECT 
        date, 
        id, 
        desc, 
        COUNT(desc) AS count_desc,
        -- 按date和id分组，按计数降序排名；若要保留并列第一的记录，可替换为RANK()
        ROW_NUMBER() OVER(PARTITION BY date, id ORDER BY COUNT(desc) DESC) AS rn
    FROM hive_tab
    GROUP BY date, id, desc
) ranked_data
WHERE rn = 1;

补充说明 #

• 如果你需要保留某个(date,id)分组内多个并列次数最多的desc，可以把ROW_NUMBER()换成RANK()，这样所有计数相同的Top记录都会被返回；
• 原查询的外层GROUP BY id,c,date,desc没有起到筛选作用，因为子查询已经按这四个字段完成分组，每行都是唯一的，所以外层分组后仍会返回所有行。

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