SQL货币转换：如何筛选首次成功的USD等值达标交易

嘿，这个货币转换的问题其实不难，我们可以一步步把转换逻辑整合到你现有的SQL里，我给你拆解清楚：

首先，核心是先把每笔交易的金额转换成USD等值，再用这个转换后的值来筛选。结合你给的两张表结构，我们需要处理汇率的匹配逻辑——毕竟汇率表可能有两种存储方式：要么是交易货币→USD的汇率，要么是USD→交易货币的汇率，所以得用CASE语句来适配这两种情况。

完整的SQL方案 #

我用CTE（公共表表达式）来拆分逻辑，这样可读性更强：

WITH converted_transactions AS (
    SELECT 
        t.*,
        -- 计算USD等值金额，根据汇率方向灵活处理
        CASE
            -- 情况1：汇率表直接给出「1单位交易货币 = X USD」（Base是交易货币，目标是USD）
            WHEN fx.base_currency = t.currency AND fx.currency = 'USD' THEN t.amount * fx.rate
            -- 情况2：汇率表给出「1 USD = X单位交易货币」（Base是USD，目标是交易货币），所以用除法转换
            WHEN fx.base_currency = 'USD' AND fx.currency = t.currency THEN t.amount / fx.rate
            ELSE NULL -- 没有对应汇率的交易，后续可以过滤掉
        END AS usd_amount
    FROM transactions t
    -- 关联汇率表，匹配两种可能的汇率方向
    LEFT JOIN fx_rates fx 
        ON (fx.base_currency = t.currency AND fx.currency = 'USD') 
        OR (fx.base_currency = 'USD' AND fx.currency = t.currency)
    -- 先过滤成功的交易，减少后续计算量
    WHERE t.state = 'COMPLETED'
)
-- 现在基于转换后的结果，筛选用户首次成功且USD金额超10的交易
SELECT *
FROM converted_transactions ct
WHERE (ct.user_id, ct.created_date) IN (
    SELECT user_id, MIN(created_date)
    FROM converted_transactions
    WHERE usd_amount > 10 -- 这里用转换后的USD金额判断
    GROUP BY user_id
)
-- 可选：过滤掉没有有效汇率的交易
AND ct.usd_amount IS NOT NULL;

关键逻辑说明 #

• 货币转换部分：通过LEFT JOIN关联汇率表，用CASE语句处理两种汇率存储场景，确保每笔交易都能算出准确的USD等值金额。
• 筛选逻辑复用：把你原来的子查询逻辑直接复用，只是把原来的amount >10替换成了转换后的usd_amount >10，保证筛选的是USD金额达标的交易。
• 性能优化：先在CTE里过滤state='COMPLETED'的交易，避免后续处理无效数据。

额外提示 #

• 如果你的汇率表中只有交易货币→USD这一种存储方式，可以简化CASE语句，只保留第一种情况即可。
• 如果不需要保留无汇率的交易，可以把LEFT JOIN改成INNER JOIN，这样就不用最后加usd_amount IS NOT NULL的条件了。

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