Snowflake SQL中JOIN无精确匹配时获取最近汇率的方法

问题分析 #

原代码仅通过INNER JOIN关联当月的最后汇率，导致无对应月份汇率的利润记录（如REGION3的GBP 2023年1/2月数据）无法获取汇率，直接被过滤。需要调整关联逻辑，优先匹配当月汇率，无匹配时自动取最接近的汇率。

修改后的SQL代码 #

版本1：含每日汇率去重（处理单日多汇率场景） #

WITH DAILY_LATEST_RATE AS (
    -- 先获取每个币种每日的最新汇率（去重单日多条记录）
    SELECT
        RELATIVE_CURRENCY,
        LOCAL_CURRENCY,
        RATE,
        DATE_RATE
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY LOCAL_CURRENCY, DATE_RATE ORDER BY DATE_RATE DESC) AS rn
        FROM RATE_TABLE
    ) t
    WHERE rn = 1
)
SELECT
    P.REGION,
    P.LOCAL_CURRENCY,
    P.LOCAL_PROFIT,
    R.RELATIVE_CURRENCY,
    R.RATE,
    P.PERIOD,
    R.DATE_RATE
FROM PROFIT_TABLE P
-- 为每条利润记录匹配最优汇率
OUTER APPLY (
    SELECT TOP 1
        RELATIVE_CURRENCY,
        RATE,
        DATE_RATE,
        -- 排序优先级标记：0=同月份，1=非当月
        CASE WHEN YEAR(DATE_RATE) = YEAR(P.PERIOD) AND MONTH(DATE_RATE) = MONTH(P.PERIOD) THEN 0 ELSE 1 END AS is_same_month
    FROM DAILY_LATEST_RATE
    WHERE LOCAL_CURRENCY = P.LOCAL_CURRENCY
    ORDER BY
        is_same_month,          -- 优先同月份
        ABS(DATEDIFF(DAY, DATE_RATE, P.PERIOD)), -- 再按日期差绝对值从小到大
        DATE_RATE DESC          -- 日期差相同时取最新汇率
) R
WHERE P.PERIOD IS NOT NULL;

版本2：简化版（无需处理单日多汇率） #

如果你的RATE_TABLE中单日不会出现同一币种的多条汇率记录，可以去掉CTE简化代码：

SELECT
    P.REGION,
    P.LOCAL_CURRENCY,
    P.LOCAL_PROFIT,
    R.RELATIVE_CURRENCY,
    R.RATE,
    P.PERIOD,
    R.DATE_RATE
FROM PROFIT_TABLE P
OUTER APPLY (
    SELECT TOP 1
        RELATIVE_CURRENCY,
        RATE,
        DATE_RATE,
        CASE WHEN YEAR(DATE_RATE) = YEAR(P.PERIOD) AND MONTH(DATE_RATE) = MONTH(P.PERIOD) THEN 0 ELSE 1 END AS is_same_month
    FROM RATE_TABLE RT
    WHERE RT.LOCAL_CURRENCY = P.LOCAL_CURRENCY
    ORDER BY
        is_same_month,
        ABS(DATEDIFF(DAY, RT.DATE_RATE, P.PERIOD)),
        RT.DATE_RATE DESC
) R
WHERE P.PERIOD IS NOT NULL;

核心逻辑说明 #

1. 关联方式：使用OUTER APPLY替代原有的INNER JOIN，确保所有利润记录都被保留，不会因无当月汇率而丢失
2. 排序优先级：
   • 第一优先级：匹配与利润周期同月份的汇率
   • 第二优先级：若无当月汇率，取与利润日期差值最小的汇率
   • 第三优先级：若存在多个汇率与利润日期差值相同，取最新的汇率
3. 去重处理：CTEDAILY_LATEST_RATE用于过滤单日同一币种的多条汇率记录，确保每日仅保留一条最新汇率

测试结果验证 #

针对你的测试数据，修改后的代码会输出：

• REGION3的GBP 2023年1/2月记录，会匹配到2022-12-10的0.9汇率
• REGION2的USD 2023年3月记录，会匹配到2023-02-03的1.1汇率
• 所有有当月汇率的记录（如REGION1的AUD 2023年2月），会优先取当月最后汇率

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