如何按最近早于订单日期的汇率关联两张表计算订单总价

表结构与数据 #

T1（订单明细） #

T2（汇率表） #

需求说明 #

计算T1中每个订单的总价，将总价乘以T2中对应币种且日期匹配的汇率；若T2无对应日期，则匹配最近的早于该订单日期的汇率。

原查询问题 #

原SQL使用内连接（INNER JOIN）仅匹配日期完全一致的汇率，导致订单1（2024-04-15）因T2无对应日期而返回NULL，且分组逻辑错误（不应将t1.Price加入分组）：

Select 
 t1.Ordernr, 
 t1.Date,
 Sum(t1.Price * t2.ExchangeRate) as Orderprice,
 t2.ExchangeRate

from t1 
inner join t2
  on t1.Currency = t2.Currency
  and t1.Date = t2.Date

Group by t1.Date, t1.Ordernr, t1.Price, t2.ExchangeRate

原查询结果：

正确SQL查询语句 #

WITH OrderTotal AS (
    -- 先计算每个订单的原始总价
    SELECT 
        Ordernr,
        Date,
        Currency,
        SUM(Price) AS TotalPrice
    FROM T1
    GROUP BY Ordernr, Date, Currency
),
RateWithRank AS (
    -- 为每个订单匹配所有符合条件的汇率，并按日期倒序排名
    SELECT 
        ot.Ordernr,
        ot.Date,
        ot.TotalPrice,
        t2.ExchangeRate,
        ROW_NUMBER() OVER (
            PARTITION BY ot.Ordernr 
            ORDER BY t2.Date DESC
        ) AS RateRank
    FROM OrderTotal ot
    LEFT JOIN T2 t2 
        ON ot.Currency = t2.Currency
        AND t2.Date <= ot.Date
)
-- 筛选每个订单对应的最近历史汇率，计算最终订单价
SELECT 
    Ordernr,
    Date,
    TotalPrice * ExchangeRate AS Orderprice,
    ExchangeRate
FROM RateWithRank
WHERE RateRank = 1;

逻辑解释 #

1. OrderTotal CTE：先聚合T1数据，按订单号、日期、币种分组计算原始总价，避免后续重复计算。
2. RateWithRank CTE：用LEFT JOIN关联所有同币种且日期早于等于订单日期的汇率，通过ROW_NUMBER()窗口函数按汇率日期倒序排名，确保最近的历史汇率排名为1。
3. 最终筛选：只保留排名为1的记录，计算总价乘以汇率后的最终订单价。

期望结果 #

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