sql - 使用 postgres rank 函数来限制前 n 个结果

Question

我正在查询一个包含 ap 文件列表的应付账款表其中每个都有(在其他领域中)我感兴趣的领域来运行聚合查询:

vendor_id, amount and a date.

我想构建此表的查询，以获取按年份分组、按总计(金额总和)订购的前 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