PostgreSQL中获取最新sub_date记录并关联多表的查询问题

看起来你遇到的核心问题是：要针对每个EIN获取其最新sub_date的filing_filing记录，但原查询的子查询没法直接拿到对应最大sub_date的object_id——因为GROUP BY EIN后，不能直接SELECT非聚合的object_id。同时你还需要支持批量EIN的筛选，我给你两种实用的解决方案：

方法一：使用窗口函数ROW_NUMBER()（推荐） #

窗口函数是处理这类“分组取最新/最早记录”场景的最优方案之一，它能精准标记出每个EIN下最新的那条记录：

SELECT 
    r0.ein,
    "TtlRvAndExpnssAmt" AS "Revenue",
    "TtlAsstsEOYFMVAmt" AS "Assets",
    "CmpOfcrDrTrstRvAndExpnssAmt" AS "Compensation & Benefits Expense"
FROM (
    SELECT 
        object_id,
        ein,
        -- 按EIN分组，每组内按sub_date倒序排序，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY ein ORDER BY sub_date DESC) AS rn
    FROM filing_filing
    -- 提前筛选目标EIN，减少后续计算量
    WHERE ein IN ('456829368', '123456789', '987654321')
) AS latest_ff
-- 用最新记录的object_id关联其他表
JOIN return_pf_part_0 r0 ON latest_ff.object_id = r0.object_id
JOIN return_pf_part_i r1 ON latest_ff.object_id = r1.object_id
JOIN return_pf_part_ii r2 ON latest_ff.object_id = r2.object_id
-- 只保留每个EIN的最新记录
WHERE latest_ff.rn = 1;

为什么这个方法可行？ #

• PARTITION BY ein会把数据按EIN分成独立的组，ORDER BY sub_date DESC让每组内最新的记录排在最前面
• ROW_NUMBER()给每组内的记录按顺序编号，最新的那条编号就是1，筛选rn=1就能拿到我们需要的记录
• 子查询里提前加WHERE ein IN (...)可以缩小数据范围，提升查询效率，非常适合你后续要处理长串EIN的场景

方法二：使用LATERAL JOIN #

如果你更习惯用关联的方式处理，LATERAL JOIN也能实现同样的效果，它可以针对每个EIN单独查询最新记录：

SELECT 
    r0.ein,
    "TtlRvAndExpnssAmt" AS "Revenue",
    "TtlAsstsEOYFMVAmt" AS "Assets",
    "CmpOfcrDrTrstRvAndExpnssAmt" AS "Compensation & Benefits Expense"
FROM (
    -- 先拿到所有要查询的EIN（去重避免重复计算）
    SELECT DISTINCT ein 
    FROM filing_filing 
    WHERE ein IN ('456829368', '123456789')
) AS ein_list
-- 对每个EIN，查询其最新的filing_filing记录的object_id
LATERAL (
    SELECT object_id 
    FROM filing_filing ff
    WHERE ff.ein = ein_list.ein
    ORDER BY ff.sub_date DESC
    LIMIT 1
) AS latest_ff
-- 关联其他表获取字段
JOIN return_pf_part_0 r0 ON latest_ff.object_id = r0.object_id
JOIN return_pf_part_i r1 ON latest_ff.object_id = r1.object_id
JOIN return_pf_part_ii r2 ON latest_ff.object_id = r2.object_id;

这个方法的特点： #

• 逻辑更直观：先确定要查的EIN列表，再逐个获取每个EIN的最新记录
• 适合EIN数量不多的场景，性能表现也不错

这两种方法都能完美解决你原查询中无法获取对应max(sub_date)的object_id的问题，而且都支持批量EIN筛选，你可以根据自己的习惯和数据规模选择使用。

内容的提问来源于stack exchange，提问作者extensionhelp