我正在查询一个包含ap文档列表的应付账款表其中每个字段(以及其他字段)都有我感兴趣的字段来运行聚合查询:
vendor_id、金额和日期。
我想在这个表上构建一个查询,根据年份分组,得到按总额(金额总和)排序的前10名供应商。
有人能告诉我如何使用秩函数吗。
select *
from (
select the_year, vendor_id, amount,
row_number() over(
partition by the_year
order by amount desc
) as rn
from (
select
date_trunc('year', the_date) as the_year,
vendor_id,
sum(amount) as amount
from ap
group by 1, 2
) s
) s
where rn <= 10
order by the_year, amount desc