基于月份匹配汇率并补全缺失期汇率的SQL查询问题

我有两张表：sales和currency_rate，需要根据sales表的Date列（整数格式，如20240701）和currency_rate表的CurrentPeriod列（datetime类型），为sales的每笔交易填充CurrRate列。如果currency_rate表的日期序列存在中断，就用上月的CurrRate值填充对应sales记录。

示例表 #

sales表 #

currency_rate表（模拟某一期为NULL） #

预期结果 #

可以看到，20230805对应的CurrRate是2023年7月的汇率0.887729，因为2023年8月的汇率记录缺失。

当前疑问与问题 #

1. 我用LAG函数生成currency_rate表的PreviousPeriod列，但不确定ORDER BY应该用CurrentPeriod ASC还是DESC？
2. 如何编写Spark SQL（或T-SQL，我会自行转换为Spark SQL）语句实现正确关联，同时处理日期为NULL的情况？我已经用以下语句处理数据类型转换用于关联：

date_format(rates.currentperiod, 'yyyyMM') = date_format(to_date(cast(sales.date as string), 'yyyyMMdd'), 'yyyyMM')

我的现有查询语句如下：

SELECT
    inv.*,
    IFNULL(cr.CurrRate, 0) AS CurrencyRate
FROM
    v_sales inv
LEFT JOIN (
    SELECT
        CurrentPeriod,
        LAG(CurrentPeriod) OVER (PARTITION BY curr_rate_from, curr_rate_to ORDER BY CurrentRate) AS PreviousPeriod,
        curr_rate_from,
        curr_rate_to,
        CurrRate
    FROM
        schema.currency_rate
) cr ON
    inv.curr_code = cr.curr_rate_from -- 这部分不重要
    AND (
        (date_format(to_date(cast(inv.Day AS STRING), 'yyyyMMdd'), 'yyyyMM') = date_format(cr.CurrentPeriod, 'yyyyMM'))
        OR
        (date_format(to_date(cast(inv.Day AS STRING), 'yyyyMMdd'), 'yyyyMM') < date_format(cr.CurrentPeriod, 'yyyyMM') 
        AND date_format(to_date(cast(inv.Day AS STRING), 'yyyyMMdd'), 'yyyyMM') >= date_format(cr.PreviousPeriod, 'yyyyMM'))
    )

但查询结果中CurrencyRate列仍存在一些不应出现的0值，需要解决这个问题。

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