You need to enable JavaScript to run this app. Oracle按交易日期源货币转美元 无匹配取最早汇率的实现
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.22 19:36:07