用postgres秩函数限制n个顶级结果

Question

我正在查询一个帐户应付款表，该表有ap文档列表，其中每个文档都有(除其他字段外)我感兴趣的用于运行聚合查询的文件：

vendor_id，金额和日期。

我想在这个表上构造一个查询，到按年份分组，按总数(金额之和)排列的前10位供应商的位置。

有人能告诉我如何使用秩函数吗？

Answer 1

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