Oracle按交易日期源货币转美元 无匹配取最早汇率的实现
Oracle 客户账单美元金额换算实现
涉及数据表说明
- 客户账单表
cust_table:存储全量客户账单信息,数据可追溯至2000年,核心字段包含支付源货币类型、交易日期、订阅日期subs_date、原币账单金额 - 汇率表
exchange_rates:存储各货币对的汇率记录,核心字段包含源货币exc_src、目标货币exc_tgt、汇率生效日期、对应汇率值;该表仅保留近年汇率记录,无法覆盖账单表的全量时间跨度,存在大量账单日期无法精准匹配汇率的场景
业务换算规则
- 优先匹配逻辑:基于账单关联的日期(可根据业务场景选择交易日期/订阅日期),匹配同日期下源货币兑美元的精准汇率,换算公式为美元金额 = 原币账单金额 × 当日对应汇率
- 无匹配兜底逻辑:若账单关联日期在
exchange_rates表中无对应源货币兑美元的汇率记录,则取该源货币兑美元货币对下最早生效日期对应的汇率值完成换算 - 规则落地示例:
- 第一笔账单订阅日期为2020年10月1日,源货币为EUR,无对应日期的EUR兑USD汇率
- 第二笔账单订阅日期为2016年10月1日,源货币为EUR,无对应日期的EUR兑USD汇率
- 执行结果:查得EUR兑USD的最早汇率生效日期为2017年1月1日,两笔账单均使用该日期的汇率完成美元金额换算
参考实现SQL
SELECT c.*, -- 计算换算后的美元金额:优先使用精准匹配的当日汇率,无匹配时取同货币对的最早兜底汇率,保留两位小数 ROUND( c.original_amount * COALESCE(er_daily.exc_rate, er_earliest.exc_rate), 2 ) AS usd_bill_amount FROM cust_table c -- 关联精准日期匹配的兑美元汇率 LEFT JOIN exchange_rates er_daily ON er_daily.exc_src = c.source_currency AND er_daily.exc_tgt = 'USD' AND er_daily.exc_date = c.subs_date -- 需按交易日期换算时,替换为c.trans_date即可 -- 关联兜底汇率:筛选所有兑美元货币对中,生效日期最早的汇率值 LEFT JOIN ( SELECT exc_src, exc_rate FROM ( SELECT exc_src, exc_rate, -- 按源货币分组,按汇率生效日期升序排序,排第一的即为该货币对的最早汇率 ROW_NUMBER() OVER ( PARTITION BY exc_src ORDER BY exc_date ASC ) AS row_rank FROM exchange_rates WHERE exc_tgt = 'USD' ) ranked_rate WHERE row_rank = 1 ) er_earliest ON er_earliest.exc_src = c.source_currency
内容的提问来源于stack exchange,提问作者Michael
相关产品推荐
相关产品推荐

