Spark SQL关联产品与汇率表匹配最近有效汇率的错误排查

• 字段对应关系错误：你提供的汇率表表头和实际数据顺序不匹配，按示例数据来看，存储币种的列是第二列，存储汇率值的列是第一列。如果你的表实际字段名和你标注的表头一致，那么存储币种的字段为ExchangeRate、存储汇率值的字段为Currency，你原写法的关联条件没有匹配对应字段，导致币种关联错误。
• 子查询未绑定外层当前行：你在子查询中重新关联了全量产品表，没有和外层查询的单条产品记录绑定，导致子查询筛选的是全表所有符合条件的汇率，最终返回的永远是全量汇率表中最大日期对应的数据，无法匹配当前产品行的日期。

推荐使用窗口函数方案，性能更优，适合Spark大数据场景：

SELECT 
    Product,
    Currency,
    Cost,
    date AS productDate,
    c_date AS currencyDate,
    ExchangeRate AS currencyCurrency
FROM (
    SELECT 
        p.*,
        c.date AS c_date,
        -- 若汇率表实际字段名和你标注的表头一致，此处取c.Currency作为汇率值
        c.ExchangeRate,
        ROW_NUMBER() OVER(PARTITION BY p.Product, p.Currency, p.date ORDER BY c.date DESC) AS rn
    FROM product p
    LEFT JOIN currency c
        -- 若汇率表实际字段名和你标注的表头一致，此处改为c.ExchangeRate = p.Currency
        ON p.Currency = c.Currency
        AND p.date >= c.date
) t
WHERE rn = 1

如果需要用关联子查询实现，修正后写法如下：

SELECT 
    *,
    (SELECT Currency FROM currency c
     -- 若汇率表实际字段名和你标注的表头一致，此处改为c.ExchangeRate = p.Currency
     WHERE c.Currency = p.Currency
     AND c.date <= p.date
     ORDER BY c.date DESC LIMIT 1) AS currencyCurrency,
    (SELECT date FROM currency c
     -- 若汇率表实际字段名和你标注的表头一致，此处改为c.ExchangeRate = p.Currency
     WHERE c.Currency = p.Currency
     AND c.date <= p.date
     ORDER BY c.date DESC LIMIT 1) AS currencyDate
FROM product p

注：关联子查询会逐行扫描汇率表两次，数据量较大时性能远低于窗口函数方案。

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