Hive中如何补全缺失日期的货币兑换汇率查询方案

现有两张业务表：

• sales（销售表）

id, amount, currency, sales_date
1, 100, usd, 2022-05-01
2, 150, usd, 2022-06-04
3, 55, usd, 2022-06-07
4, 75, usd, 2022-07-03

• currency_conversion_usd_cad（美元兑加元汇率表）

conv_date, conversion_rate
2022-05-01, 1.28
2022-06-04, 1.26
2022-07-03, 1.27

原执行的Hive SQL语句：

select 
s.id AS id,
s.amount*cc.conversion_rate AS CAD_amount,
s.sales_date AS s_date
from sales s
left join currency_conversion_usd_cad cc
on s.sales_date = cc.conv_date

该语句会导致2022-06-07对应的CAD_amount为NULL，因为该日期无匹配的汇率数据。需要实现使用最近的有汇率数据的日期的汇率来计算缺失值，最终期望结果：

id, CAD_amount, s_date
1, 128.0, 2022-05-01
2, 189.0, 2022-06-04
3, 69.3, 2022-06-07
4, 95.25, 2022-07-03

解决方案 #

通过交叉连接+窗口函数的方式，为每个销售日期匹配最近的可用汇率，具体Hive SQL如下：

SELECT
    t.id,
    t.amount * t.conversion_rate AS CAD_amount,
    t.sales_date AS s_date
FROM (
    SELECT
        s.id,
        s.amount,
        s.sales_date,
        cc.conversion_rate,
        -- 按销售记录分组，计算并排序汇率日期与销售日期的差值
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY (s.sales_date - cc.conv_date) ASC) AS rn
    FROM sales s
    CROSS JOIN currency_conversion_usd_cad cc
    -- 只保留早于/等于销售日期的汇率数据
    WHERE cc.conv_date <= s.sales_date
) t
-- 取差值最小的那条汇率记录
WHERE rn = 1
ORDER BY s_date;

逻辑说明 #

1. 交叉连接筛选有效范围：将销售表与汇率表交叉连接，只保留汇率日期不晚于销售日期的组合，排除未来汇率；
2. 窗口函数匹配最近汇率：针对每个销售记录（按id分区），按销售日期与汇率日期的差值升序排序，差值最小的就是最近的可用汇率；
3. 提取最优计算记录：筛选出排序后rn=1的记录，用该汇率计算加元金额即可得到目标结果。

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