PostgreSQL查询返回重复REIT价格记录：需仅获取最新价格

问题说明 #

我正在为巴西REITs创建筛选工具，目标是获取每个REIT的最后一条价格记录作为计算数据源：

• 预期结果：每个REIT仅对应一条价格记录
• 实际结果：部分REIT（如VISC）返回多条价格记录

当前使用的PostgreSQL查询 #

select
    r.ticker,
    rp.price,
    SUM(rq.amount) as quota_amount,
    SUM(rq.amount * rq.price) / SUM(rq.amount) as average_price,
    CONCAT(ROUND((rp.price - AVG(rq.price)) / rp.price * 100,   2), '%') as variation_percentage,
    SUM(rq.amount) * rp.price as balance,
    SUM(rq.amount * rq.price) as total_invested,
    (SUM(rq.amount) * rp.price) - (SUM(rq.amount * rq.price)) as currency_variation,
    rp.registered_at as price_registration_date
from
    reit_quote rq
inner join reit r on
    r.id = rq.reit_id
inner join reit_price rp on
    rq.reit_id = rp.reit_id
group by
    r.ticker,
    rp.price,
    rp.registered_at
order by rp.registered_at desc

修复后的查询 #

通过窗口函数筛选每个REIT的最新价格记录，再关联计算：

WITH latest_reit_prices AS (
    SELECT 
        reit_id,
        price,
        registered_at,
        ROW_NUMBER() OVER (PARTITION BY reit_id ORDER BY registered_at DESC) AS rn
    FROM reit_price
)
select
    r.ticker,
    lrp.price,
    SUM(rq.amount) as quota_amount,
    SUM(rq.amount * rq.price) / SUM(rq.amount) as average_price,
    CONCAT(ROUND((lrp.price - AVG(rq.price)) / lrp.price * 100, 2), '%') as variation_percentage,
    SUM(rq.amount) * lrp.price as balance,
    SUM(rq.amount * rq.price) as total_invested,
    (SUM(rq.amount) * lrp.price) - (SUM(rq.amount * rq.price)) as currency_variation,
    lrp.registered_at as price_registration_date
from
    reit_quote rq
inner join reit r on
    r.id = rq.reit_id
inner join latest_reit_prices lrp on
    rq.reit_id = lrp.reit_id
WHERE lrp.rn = 1 -- 仅保留每个REIT的最新价格记录
group by
    r.ticker,
    lrp.price,
    lrp.registered_at
order by lrp.registered_at desc

修复逻辑 #

原查询直接关联reit_price表，会把该REIT的所有历史价格记录都关联进来，分组时只要price或registered_at不同就会生成多条结果。通过CTE结合ROW_NUMBER()窗口函数，先为每个REIT的价格记录按registered_at倒序编号，取编号为1的最新记录，再关联计算，就能确保每个REIT仅返回一条结果。

内容的提问来源于stack exchange，提问作者Diego Magalhães