如何用SQL按最近日期匹配并计算损失，筛选Top2最差交易？

现有两张表 #

1. 交易表（假设表名为transactions） #

字段包括ID、Date_time、Nominal（值>0为买入交易，<0为卖出交易）、EUR_USD，具体数据如下：

2. 实际汇率表（假设表名为actual_exchange_rates） #

字段包括Date_time、Actual exchange rate，具体数据如下：

需求说明 #

要找出Top2最差交易，具体规则：

• 给每笔交易匹配「交易日期之前最近的实际汇率」：即满足transactions.Date_time >= actual_exchange_rates.Date_time的最新汇率记录
• 计算交易的「名义等价」（用交易时的EUR_USD计算）和「实际等价」（用匹配到的实际汇率计算），通过两者的差值判断交易优劣：差值越大，交易越差

SQL实现方案 #

步骤1：匹配每笔交易对应的最近实际汇率 #

用窗口函数ROW_NUMBER()给每个交易对应的所有符合条件的汇率记录排序，只保留日期最新的那一条：

WITH transaction_with_rate AS (
    SELECT
        t.ID,
        t.Date_time AS trans_date,
        t.Nominal,
        t.EUR_USD AS trans_rate,
        ar.Actual_exchange_rate AS actual_rate,
        -- 按交易ID分组，汇率日期倒序排序，标记每条记录的排序序号
        ROW_NUMBER() OVER (
            PARTITION BY t.ID
            ORDER BY ar.Date_time DESC
        ) AS rn
    FROM transactions t
    JOIN actual_exchange_rates ar
        ON t.Date_time >= ar.Date_time
)

步骤2：计算交易损失并筛选Top2最差交易 #

基于上面的结果，筛选出每个交易对应的唯一汇率，计算名义等价与实际等价的差值，最后按差值从大到小排序取前2：

SELECT
    ID,
    trans_date,
    Nominal,
    trans_rate,
    actual_rate,
    -- 根据买入/卖出类型计算损失金额，差值越大交易越差
    CASE
        -- 买入交易：等价为 交易金额 × 汇率，计算名义与实际的差值
        WHEN Nominal > 0 THEN (Nominal * trans_rate) - (Nominal * actual_rate)
        -- 卖出交易：等价为 交易金额 ÷ 汇率，计算名义与实际的差值
        WHEN Nominal < 0 THEN (Nominal / trans_rate) - (Nominal / actual_rate)
    END AS loss_amount
FROM transaction_with_rate
WHERE rn = 1 -- 只保留每个交易对应的最新汇率记录
ORDER BY loss_amount DESC -- 按损失从大到小排序
LIMIT 2; -- 取Top2最差交易

补充说明 #

• 如果业务中对买入/卖出的等价计算逻辑有调整，直接修改CASE语句里的公式即可
• 不同SQL方言（比如MySQL、PostgreSQL）对日期格式的处理可能有差异，如果你的Date_time是字符串类型，需要先转成日期类型（比如MySQL用STR_TO_DATE()）

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