寻求最优PostgreSQL查询：获取指定日期适用的货币兑换汇率

假设货币兑换的数据库表结构及数据如下：

需求说明：每组(fromCurrency, toCurrency)对应多条汇率记录，查询时需根据指定日期，获取该日期下适用的唯一记录（即生效日期不晚于查询日期，且是该组合下最新的生效记录）。

查询示例 #

• 2024年2月1日查询fromCurrency='USD'的兑换汇率，返回结果：

• 2024年4月15日执行同一查询，返回结果：

最优PostgreSQL查询语句 #

使用窗口函数ROW_NUMBER()可高效实现该需求，语句如下：

WITH ranked_rates AS (
    SELECT 
        fromCurrency,
        toCurrency,
        "effective date",
        "conversion rate",
        ROW_NUMBER() OVER (
            PARTITION BY fromCurrency, toCurrency 
            ORDER BY "effective date" DESC
        ) AS rn
    FROM currency_exchange
    WHERE fromCurrency = 'USD'
        AND "effective date" <= '2024-02-01' -- 替换为实际查询日期
)
SELECT 
    fromCurrency,
    toCurrency,
    "effective date",
    "conversion rate"
FROM ranked_rates
WHERE rn = 1;

逻辑说明 #

1. 通过ROW_NUMBER()按fromCurrency和toCurrency分组，每组内按effective date降序排序，最新生效的记录会被标记为rn=1。
2. 先过滤出生效日期不晚于查询日期的记录，再在每组中取排序第一的记录，即为当前查询日期适用的汇率。

性能优化建议 #

为提升查询效率，建议创建复合索引：

CREATE INDEX idx_currency_exchange_rates ON currency_exchange 
    (fromCurrency, toCurrency, "effective date" DESC);

该索引可让数据库快速定位到目标分组的最新生效记录，避免全表扫描。

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