Oracle 11g：实现多币种转USD后计算交易总金额并筛选符合条件的交易

没问题，我来帮你调整查询逻辑，实现先将非USD币种转换为USD，再统计首次交易7天内总金额超1000的用户交易的需求。

核心思路 #

1. 先确定每个用户的首次交易日期，以此为基准划定7天的时间范围；
2. 关联currencyRate表，将每个交易的金额按对应币种的sellingRate转换为USD（USD直接保留原金额）；
3. 计算每个用户在7天时间范围内的总USD金额；
4. 筛选出总金额超过1000的用户，返回他们所有符合时间范围的交易记录。

完整SQL代码 #

WITH user_first_trans AS (
    -- 第一步：获取每个用户的首次交易日期
    SELECT sender_name, MIN(transaction_date) AS first_trans_date
    FROM transactions
    GROUP BY sender_name
),
transactions_with_usd AS (
    -- 第二步：将每个交易金额转换为USD，并限定在首次交易7天内
    SELECT 
        t.sender_name,
        t.transaction_date,
        t.amount,
        t.currency,
        -- USD直接用原金额，其他币种乘以对应汇率
        CASE 
            WHEN t.currency = 'USD' THEN t.amount
            ELSE t.amount * cr.sellingRate
        END AS amount_usd,
        uft.first_trans_date
    FROM transactions t
    -- 关联用户首次交易日期表
    JOIN user_first_trans uft ON t.sender_name = uft.sender_name
    -- 关联汇率表，匹配对应币种的汇率
    LEFT JOIN currencyRate cr ON t.currency = cr.currencyCode
    -- 筛选交易日期在首次交易7天内的记录
    WHERE t.transaction_date BETWEEN uft.first_trans_date 
          AND uft.first_trans_date + INTERVAL '7' DAY
),
user_total_usd AS (
    -- 第三步：计算每个用户在时间范围内的总USD金额
    SELECT 
        *,
        SUM(amount_usd) OVER (PARTITION BY sender_name) AS total_usd
    FROM transactions_with_usd
)
-- 第四步：筛选总金额超过1000的用户交易记录
SELECT sender_name, transaction_date, amount, currency, amount_usd
FROM user_total_usd
WHERE total_usd > 1000
ORDER BY sender_name, transaction_date;

代码说明 #

• user_first_trans：通过MIN(transaction_date)获取每个用户的首次交易时间，作为后续时间范围的起点。
• 币种转换逻辑：用CASE判断币种，USD直接保留原金额，其他币种乘以currencyRate表中的sellingRate；用LEFT JOIN是为了避免某个币种没有汇率记录导致数据丢失（如果你的数据中所有币种都有对应汇率，也可以换成INNER JOIN）。
• 时间范围筛选：用BETWEEN ... AND ...结合INTERVAL '7' DAY，确保只统计首次交易后7天内的交易。
• 总金额计算：用窗口函数SUM(amount_usd) OVER (PARTITION BY sender_name)，让每条交易记录都带上所属用户的总USD金额，方便后续筛选。

按照这个逻辑，Alice和Carol的交易转成USD后总金额超过1000，会被显示；Betty的总金额不足1000，不会出现在结果中，完全符合你的预期。

备注：内容来源于stack exchange，提问作者Saidvali