Postgres中基于扩展日期范围关联orders与currency_rates表的实现方法

可以直接用单条SELECT语句实现需求，无需手动指定日期区间，以下是两种适配不同场景的优雅实现：

方案1：LATERAL JOIN（推荐优先使用） #

PostgreSQL原生支持的语法，逻辑直观易维护，后续新增汇率区间无需调整代码，适配绝大多数业务场景：

SELECT 
    o.*,
    cr.rate,
    cr.valid_from
FROM orders o
LEFT JOIN LATERAL (
    SELECT rate, valid_from
    FROM currency_rates cr
    WHERE cr.currency_id = o.currency_id
      AND cr.valid_from <= o.received_at
    ORDER BY cr.valid_from DESC
    LIMIT 1
) cr ON true
-- 若需过滤无对应汇率的订单（例如示例中的CZK币种订单），保留以下条件，不需要可删除
WHERE cr.rate IS NOT NULL;

逻辑说明：逐行遍历每个订单，匹配同币种下所有生效日期早等于订单日期的汇率，按生效日期倒序排序后取第一条，就是该订单对应生效的汇率。

方案2：窗口函数预生成汇率有效期后关联 #

如果需要批量关联大量订单，该方案性能更优：

WITH currency_rates_with_valid_to AS (
    SELECT 
        currency_id,
        rate,
        valid_from,
        -- 取同币种下一个生效日期作为当前汇率的截止日期，最后一个汇率默认截止到9999-12-31覆盖所有后续日期
        LEAD(valid_from, 1, '9999-12-31'::DATE) OVER (PARTITION BY currency_id ORDER BY valid_from) AS valid_to
    FROM currency_rates
)
SELECT 
    o.*,
    cr.rate,
    cr.valid_from
FROM orders o
LEFT JOIN currency_rates_with_valid_to cr
    ON o.currency_id = cr.currency_id
    AND o.received_at >= cr.valid_from
    AND o.received_at < cr.valid_to
-- 同方案1，不需要过滤无汇率订单可删除
WHERE cr.rate IS NOT NULL;

逻辑说明：先通过窗口函数一次性把仅存起始日期的汇率表转换成带完整生效区间的结构，再用日期区间直接关联即可。

两种方案返回结果均完全匹配给出的orders_currency_rates示例要求，可根据业务数据量选择对应实现。

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