flink-user-zh mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ddwcg <3149768...@qq.com>
Subject 在子查询上使用row_number over返回的rn都是1
Date Tue, 27 Aug 2019 07:16:21 GMT
如果直接查询表是没问题,但是业务需求是按汇总后的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)
Mime
View raw message