如何查找指定日期前的最新记录？基于EOM表与交易表的查询问题

核心逻辑 #

我们可以通过关联两张表后按EOM日期分组取最大交易日期的方式实现需求，逻辑如下：

• 关联Product EOM表和Transaction表，关联条件优先加上产品ID匹配（如果两个表都有产品维度的话），同时限制Transaction表的交易日期 <= Product EOM表的EOM日期
• 按EOM日期（以及产品ID，如果有多产品）分组，取分组内最大的交易日期即为对应EOM日期前的最新交易日期

通用SQL实现（兼容多数SQL引擎） #

SELECT 
    e.product_id, -- 无产品维度可删除该行
    e.eom_date,
    MAX(t.transaction_date) AS latest_transaction_date_before_eom
FROM 
    product_eom e
LEFT JOIN 
    `transaction` t 
    ON e.product_id = t.product_id -- 无产品维度可删除该行
    AND t.transaction_date <= e.eom_date
GROUP BY 
    e.product_id, -- 无产品维度可删除该行
    e.eom_date
ORDER BY 
    e.eom_date

如果使用支持窗口函数的引擎（如MySQL8.0+、PostgreSQL、Spark SQL等），且需要同时取最新交易的其他业务字段，可以用以下实现：

WITH trans_rn AS (
    SELECT 
        e.product_id,
        e.eom_date,
        t.transaction_date,
        t.amount, -- 其他需要的交易字段可自行补充
        ROW_NUMBER() OVER(PARTITION BY e.product_id, e.eom_date ORDER BY t.transaction_date DESC) AS rn
    FROM product_eom e
    LEFT JOIN `transaction` t 
        ON e.product_id = t.product_id
        AND t.transaction_date <= e.eom_date
)
SELECT 
    product_id,
    eom_date,
    transaction_date AS latest_transaction_date_before_eom,
    amount -- 和CTE里补充的交易字段对应
FROM trans_rn
WHERE rn = 1
ORDER BY eom_date

注意：如果某个EOM日期之前完全没有交易，返回的latest_transaction_date_before_eom会是NULL，符合常规业务逻辑；如果需要过滤掉无交易的记录，把LEFT JOIN改成INNER JOIN即可。

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