PostgreSQL关联查询日期缺失时如何获取最近的历史可用汇率数据

解决方法 #

你原始SQL的核心问题是两点：一是左连接主表写反了，应该以交易表t2为主表才能保留所有交易日期；二是缺少缺失汇率的向前填充逻辑，以下是不同兼容性的实现方案：

方案1：子查询实现（兼容性最高，支持所有主流数据库）

直接对每条交易记录找小于等于交易日期的最大汇率日期对应的汇率：

SELECT
    t2.trx_date,
    t2.trx_date AS curr_date,
    (
        SELECT rate 
        FROM t1 
        WHERE t1.curr_date <= t2.trx_date 
        ORDER BY t1.curr_date DESC 
        LIMIT 1
    ) AS rate,
    t2.amount,
    (
        SELECT rate 
        FROM t1 
        WHERE t1.curr_date <= t2.trx_date 
        ORDER BY t1.curr_date DESC 
        LIMIT 1
    ) * t2.amount AS net_amt
FROM t2

方案2：窗口函数实现（性能更优，支持PostgreSQL、Hive、Spark SQL、MySQL 8.0+等支持IGNORE NULLS语法的数据库）

先关联出匹配的汇率，再用窗口函数向前填充空值：

WITH raw_join_result AS (
    SELECT
        t2.trx_date,
        t2.trx_date AS curr_date,
        t1.rate,
        t2.amount
    FROM t2
    LEFT JOIN t1 ON t2.trx_date = t1.curr_date
)
SELECT
    trx_date,
    curr_date,
    LAST_VALUE(rate) IGNORE NULLS OVER(
        ORDER BY trx_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rate,
    amount,
    LAST_VALUE(rate) IGNORE NULLS OVER(
        ORDER BY trx_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * amount AS net_amt
FROM raw_join_result

方案3：兼容低版本MySQL的实现（不支持IGNORE NULLS语法场景）

用分组标记给每个缺失汇率的交易匹配最近的有效汇率组：

SELECT
    trx_date,
    trx_date AS curr_date,
    rate,
    amount,
    rate * amount AS net_amt
FROM (
    SELECT
        t.*,
        -- 取当前分组对应的有效汇率
        MAX(IF(curr_date = trx_date, rate, NULL)) OVER(PARTITION BY rate_group) AS rate
    FROM (
        SELECT
            t2.trx_date,
            t1.curr_date,
            t1.rate,
            t2.amount,
            -- 给每条交易分配最近有汇率的日期分组标记
            COUNT(t1.curr_date) OVER(ORDER BY t2.trx_date) AS rate_group
        FROM t2
        LEFT JOIN t1 ON t2.trx_date = t1.curr_date
    ) t
) final

注意事项 #

如果存在交易日期早于汇率表最早记录的情况，上述查询返回的汇率会为NULL，你可以根据业务需求用COALESCE(rate, 默认汇率)指定兜底值。

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