Snowflake中基于交易日期匹配最近有效历史汇率实现货币转换的技术问询

阿华AIGC实验室

2026-4-29

解决交易货币转换的汇率匹配问题

你的问题核心是无法匹配到非当月的历史汇率，导致部分交易的转换值为空。我们需要调整关联逻辑，优先匹配当月汇率，若不存在则取交易日期之前最近的历史汇率。

解决方案思路

先关联所有符合条件的汇率：同货币(Curr)、同汇率类型(Ex_Type)，且汇率日期(Ex_Date)不晚于交易日期(Txn_Date)
对每个交易的候选汇率排序：
- 第一优先级：优先选择交易当月的汇率（判断Ex_Date是否在交易日期所在月份）
- 第二优先级：若当月无汇率，选择最近的历史汇率（按Ex_Date降序）
取每个交易的第一条排序结果，就是我们需要的目标汇率

完整SQL代码（以PostgreSQL为例）

WITH ranked_exrates AS (
    SELECT 
        PR.Txn_Id,
        PR.Net_Value,
        TX.Curr,
        TX.Ex_Type,
        TX.Txn_Date,
        EX.Ex_Date,
        EX.Ex_rate,
        -- 定义排序规则：当月汇率优先，再按汇率日期从新到旧排序
        ROW_NUMBER() OVER (
            PARTITION BY PR.Txn_Id
            ORDER BY 
                CASE 
                    WHEN date_trunc('MONTH', TX.Txn_Date) = date_trunc('MONTH', EX.Ex_Date) 
                    THEN 0 
                    ELSE 1 
                END,
                EX.Ex_Date DESC
        ) AS rn
    FROM PRC_TABLE PR
    INNER JOIN TXN_TABLE TX ON PR.Txn_Id = TX.Txn_Id
    LEFT JOIN EXRATE_TABLE EX 
        ON TX.Curr = EX.Curr 
        AND TX.Ex_Type = EX.Ex_Type 
        AND EX.Ex_Date <= TX.Txn_Date
)
SELECT 
    Txn_Id,
    Net_Value,
    Curr,
    Ex_Type,
    Txn_Date,
    Ex_Date,
    Ex_rate,
    CASE 
        WHEN Curr = 'USD' THEN Net_Value 
        ELSE ROUND(Net_Value * Ex_rate, 4)  -- 保留4位小数匹配预期结果
    END AS Conv_Net_Value
FROM ranked_exrates
WHERE rn = 1;