PostgreSQL中按最近有效日期关联订单与汇率表的问询

你不需要生成冗余的时间序列，PostgreSQL有更高效的方式直接匹配每个订单对应的生效汇率——核心是找到对应货币下，生效日期(VALID_FROM)小于等于订单接收日期(RECEIVED_AT)的最新汇率，下面两种方案都能实现，且性能远优于时间序列方法：

方案1：使用LATERAL JOIN（推荐，直观且高效） #

这种方式会为每个订单单独查询符合条件的最新汇率，适合订单量较大的场景，配合索引能大幅提升速度：

SELECT
  o.received_at,
  o.currency_id,
  o.amount, -- 假设订单表有金额字段，按需替换
  cr.rate,
  -- 按需求计算转换后金额，示例为原金额乘汇率，可自行调整乘除逻辑
  o.amount * cr.rate AS converted_amount
FROM orders o
LEFT JOIN LATERAL (
  SELECT rate
  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;

优化建议 #

给currency_rates表创建复合索引，让数据库能快速定位目标数据：

CREATE INDEX idx_currency_rates_curr_valid ON currency_rates (currency_id, valid_from DESC);

方案2：使用窗口函数ROW_NUMBER() #

如果需要一次性批量处理所有数据，可通过窗口函数给每个订单对应的汇率排序，取最新的一条：

WITH ordered_rates AS (
  SELECT
    o.received_at,
    o.currency_id,
    o.amount,
    cr.rate,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id, o.currency_id -- 按订单+货币分组，确保每个订单只取一条汇率
      ORDER BY cr.valid_from DESC
    ) AS rn
  FROM orders o
  LEFT JOIN currency_rates cr
    ON cr.currency_id = o.currency_id
    AND cr.valid_from <= o.received_at
)
SELECT
  received_at,
  currency_id,
  amount,
  rate,
  amount * rate AS converted_amount
FROM ordered_rates
WHERE rn = 1;

注意事项 #

• 两种方案均使用LEFT JOIN，确保即使订单找不到对应汇率（比如订单日期早于该货币的第一条汇率记录），订单数据也不会丢失
• 如果RECEIVED_AT是带时间戳的字段，VALID_FROM是日期类型，记得统一格式（比如用DATE(o.received_at)转换），避免类型不匹配导致的过滤错误

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