Oracle 查询，用于从按类型分组的每个计数值中查找最大数据

Question

我在查询中查找每个计数数据中的最大值并按类型分组时遇到问题

是我的数据

type|category|ratio|
1001    1       10
1001    1       10
1001    1        8
1001    1        6
1002    2        5
1002    2        5
1002    2        15
1003    3        2

我有查询来计算所有这样的数据

select type, category, ratio, count(ratio) from age_ratio group by type, category, ratio

然后结果是

type|category|ratio|count
1001    1       10   2
1001    1        8   1
1001    1        6   1
1002    2        5   2
1002    2        15  1
1003    3        2   1

然后我想显示数据只有 max(count( 按类型、类别和比率分组，如下所示：

type|category|ratio|count
1001    1       10   2
1002    2        5   2
1003    3        2   1

我的假设查询是这样的：

select r.type, r.category, r.ratio, 
max(r.num) as jum
from (select type, category, ratio, count(ratio) as num from age_ratio 
group by type, cat, ratio) r inner join ratio_age ra on
ra.type=r.type and ra.category=r.category and r.ratio=ra.ratio
group by r.type, r.category, r.ratio 

Answer 1

使用row_number()：

select t.*
from (select type, category, ratio, count(*) as cnt,
row_number() over (partition by type order by count(*) desc) as seqnum
from age_ratio
group by type, category, ratio
) t
where seqnum = 1;