|
|
如果直接查询表是没问题，但是业务需求是按汇总后的amount排序，所以有一个from子查询，请问有没有什么方法汇总后求topN
select id,province,amount,rn from(
select
id,province,amount,
row_number() over(partition by province order by amount desc ) as rn
from (
select id,province,sum(amount) amount from mytable group by id,province
)m
)a where rn<=5
;
返回结果：
1> (true,id001,浙江,1505.66,1)
2> (true,id001,其他,3384.91,1)
7> (true,id001,北京,365.87,1)
3> (true,id001,天津,310.38,1)
7> (false,id001,北京,365.87,1)
7> (true,id001,北京,676.25,1)
7> (false,id001,北京,676.25,1)
7> (true,id001,北京,978.14,1)
7> (true,id001,广东,329.25,1)
7> (false,id001,广东,329.25,1)
如果直接查询表是没问题：
select id,province,amount,rn from(
select
id,province,amount,
row_number() over(partition by province order by amount desc ) as rn
from mytable
)a where rn<=5
;
7> (true,id001,北京,310.38,2)
6> (true,id001,湖北,344.34,1)
8> (true,id001,山东,348.11,1)
3> (true,id001,四川,7283.02,2)
7> (true,id001,北京,301.89,3)
3> (false,id001,四川,1128.3,2)
8> (true,id001,重庆,310.38,3)
3> (true,id001,四川,1128.3,3)
6> (true,id001,上海,647.55,1)
3> (false,id001,四川,310.38,3)
6> (false,id001,上海,310.38,1)
7> (true,id001,广东,329.25,1)
3> (true,id001,四川,310.38,4)
8> (true,id001,重庆,1618.87,1)
6> (true,id001,上海,310.38,2)
|
Locked
