如何编写SQL实现按交易发生年度汇率计算换算金额指标

核心逻辑 #

原有逻辑仅关联了一次汇率表且全局过滤了最新汇率日期，仅支持计算当前年度换算美元金额。要同时生成两个指标，需两次关联汇率表，分别匹配不同规则的汇率：

• 第一次关联：取各币种当前年度最新汇率，计算「按当前年度汇率换算的美元金额」
• 第二次关联：按交易发生年度匹配对应币种当年的最新汇率，计算「按交易发生年度汇率换算的美元金额」

实现代码 #

WITH
-- 预处理1：获取各币种当前年度最新汇率（用于第一个指标计算）
curr_year_ccy AS (
    SELECT 
        code,
        exchange_rate
    FROM currency
    WHERE exchange_rate_date IN (SELECT MAX(exchange_rate_date) FROM currency)
),
-- 预处理2：获取各币种每一年度的最新汇率（用于第二个指标按交易年度匹配）
hist_year_ccy AS (
    SELECT 
        code,
        YEAR(exchange_rate_date) AS rate_year,
        exchange_rate
    FROM currency c1
    WHERE exchange_rate_date IN (
        SELECT MAX(exchange_rate_date) 
        FROM currency c2 
        WHERE c2.code = c1.code AND YEAR(c2.exchange_rate_date) = YEAR(c1.exchange_rate_date)
    )
)
SELECT 
    ss.date,
    ss.currency_code,
    SUM(ss.local_amt) AS total_local_amt,
    -- 当前年度汇率换算金额
    SUM(ss.local_amt / cy_ccy.exchange_rate) AS loc_amt_curr_yr,
    -- 交易发生年度汇率换算金额
    SUM(ss.local_amt / hist_ccy.exchange_rate) AS loc_amt_trade_yr
FROM sales ss
-- 关联当前年度汇率
JOIN curr_year_ccy cy_ccy ON ss.currency_code = cy_ccy.code
-- 关联交易发生年度对应汇率
JOIN hist_year_ccy hist_ccy ON ss.currency_code = hist_ccy.code AND YEAR(ss.date) = hist_ccy.rate_year
GROUP BY ss.date, ss.currency_code

说明 #

• 代码中YEAR()为SQL标准日期取年函数，若使用的数据库（如Oracle、Hive等）存在差异化语法，替换为对应数据库的日期取年实现即可。
• 若currency表已直接存储年度字段，可直接使用已有年度字段关联，无需再通过YEAR()函数转换日期。

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