如何将带修改时间戳的汇率表转换为含起止日期的表

问题背景 #

我有一张仅在汇率变动时记录的汇率表，需将其转换为包含StartDate（生效起始时间）和EndDate（生效结束时间）的格式，以便将交易记录中的本地货币金额转换为USD。

原始汇率表数据 #

# Id    IsoCode ConversionRate  DecimalPlaces   IsActive    IsCorporate CreatedDate CreatedById LastModifiedDate    LastModifiedById    SystemModstamp
01L33000000V0YOEA0  AUD 1.45201 2   1   0   2017-04-05 14:21:48 00540000002oSEHAA2  2022-10-04 08:38:55 0054y000006yQSZAA2  2022-10-04 08:38:55
01L400000007MRmEAM  CAD 1.3015  2   1   0   2010-03-19 08:45:34 00540000000vr6aAAA  2022-10-04 08:38:55 0054y000006yQSZAA2  2022-10-04 08:38:55
01L400000007MRrEAM  EUR 1.00827 2   1   0   2010-03-19 08:46:01 00540000000vr6aAAA  2022-10-04 08:38:55 0054y000006yQSZAA2  2022-10-04 08:38:55
01L400000007MRwEAM  GBP 0.84991 2   1   0   2010-03-19 08:46:14 00540000000vr6aAAA  2022-10-04 08:38:55 0054y000006yQSZAA2  2022-10-04 08:38:55
01L40000000V04sEAC  USD 1   2   1   1   2010-03-18 17:06:17 005400000011UaDAAU  2010-03-18 17:06:17 005400000011UaDAAU  2010-03-18 17:06:17
01L33000000V0YOEA0  AUD 1.46488 2   1   0   2017-04-05 14:21:48 00540000002oSEHAA2  2023-04-03 08:37:46 0054y000006yQSZAA2  2023-04-03 08:37:46
01L400000007MRmEAM  CAD 1.35415 2   1   0   2010-03-19 08:45:34 00540000000vr6aAAA  2023-04-03 08:37:46 0054y000006yQSZAA2  2023-04-03 08:37:46
01L400000007MRrEAM  EUR 0.93892 2   1   0   2010-03-19 08:46:01 00540000000vr6aAAA  2023-04-03 08:37:46 0054y000006yQSZAA2  2023-04-03 08:37:46
01L400000007MRwEAM  GBP 0.82617 2   1   0   2010-03-19 08:46:14 00540000000vr6aAAA  2023-04-03 08:37:46 0054y000006yQSZAA2  2023-04-03 08:37:46

已尝试的SQL代码 #

with recursive cte_currency as
(SELECT DISTINCT *, ROW_NUMBER() OVER (PARTITION BY IsoCode ORDER BY LastModifiedDate) as Row_Num
FROM 
(SELECT DISTINCT * FROM sf_unprocessed.currencytype) as tx),
cte_currency_2 as 
(SELECT t1.Id as t1_id, t2.id as t2_id, t1.IsoCode, t1.ConversionRate, t1.DecimalPlaces, t1.IsActive, t1.CreatedDate, t1.LastModifiedDate as StartDate, t2.LastModifiedDate as EndDate, t1.Row_Num as t1_RowNumber, t2.Row_Num as t2_RowNumber
from cte_currency as t1
INNER JOIN cte_currency as t2 on t1.IsoCode = t2.IsoCode and t1.Row_Num = t2.Row_Num -1 and t1.Id = t2.Id)
SELECT DISTINCT t1_id, IsoCode, ConversionRate, DecimalPlaces, IsActive, CreatedDate, StartDate, EndDate FROM cte_currency_2

当前查询结果 #

'01L33000000V0YOEA0','AUD','1.45201','2','1','2017-04-05 14:21:48','2022-10-04 08:38:55','2023-04-03 08:37:46'
'01L400000007MRmEAM','CAD','1.3015','2','1','2010-03-19 08:45:34','2022-10-04 08:38:55','2023-04-03 08:37:46'
'01L400000007MRrEAM','EUR','1.00827','2','1','2010-03-19 08:46:01','2022-10-04 08:38:55','2023-04-03 08:37:46'
'01L400000007MRwEAM','GBP','0.84991','2','1','2010-03-19 08:46:14','2022-10-04 08:38:55','2023-04-03 08:37:46'

问题补充 #

当前结果仅覆盖了2022-10-04到2023-04-03的汇率区间，缺少每个币种从CreatedDate到2022-10-04的初始生效区间，需要补全该部分记录。

最终解决方案 #

使用ROW_NUMBER()和LAG()窗口函数，直接生成完整的时间区间，无需递归CTE：

WITH currency_ranked AS (
    SELECT 
        DISTINCT *,
        ROW_NUMBER() OVER (PARTITION BY IsoCode ORDER BY LastModifiedDate) AS Row_Num,
        LAG(LastModifiedDate) OVER (PARTITION BY IsoCode ORDER BY LastModifiedDate) AS Prev_ModifiedDate
    FROM sf_unprocessed.currencytype
)
SELECT
    Id,
    IsoCode,
    ConversionRate,
    DecimalPlaces,
    IsActive,
    -- 第一条记录用创建时间作为生效起始，后续记录用上一次修改时间作为起始
    CASE 
        WHEN Row_Num = 1 THEN CreatedDate 
        ELSE Prev_ModifiedDate 
    END AS StartDate,
    LastModifiedDate AS EndDate
FROM currency_ranked
-- 可选：如果需要让最后一条记录的生效区间延续到当前时间，可添加以下语句
UNION ALL
SELECT
    Id,
    IsoCode,
    ConversionRate,
    DecimalPlaces,
    IsActive,
    LastModifiedDate AS StartDate,
    CURRENT_TIMESTAMP AS EndDate
FROM currency_ranked
WHERE Row_Num = (SELECT MAX(Row_Num) FROM currency_ranked cr WHERE cr.IsoCode = currency_ranked.IsoCode)

逻辑说明 #

1. 用ROW_NUMBER()给每个币种的记录按修改时间排序，标记每条记录的顺序
2. 用LAG()获取上一条记录的修改时间，作为当前记录的生效起始时间
3. 第一条记录（最早的汇率版本）用CreatedDate作为生效起始，LastModifiedDate作为生效结束
4. 后续记录用上一条的修改时间作为起始，当前修改时间作为结束
5. 可选：添加最后一条记录的延续区间，让最新汇率一直生效到当前时间

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