针对日期范围的 SQL 连接?

2021-12-17 00:00:00 join sql tsql sql-server date-range

考虑两个表:

交易，外币金额:

     Date  Amount
========= =======
 1/2/2009    1500
 2/4/2009    2300
3/15/2009     300
4/17/2009    2200
etc.

汇率，主要货币(比如美元)的外币价值:

ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency:

     Date    Rate
========= =======
 2/1/2009    40.1
 3/1/2009    41.0
 4/1/2009    38.5
 5/1/2009    42.7
etc.

可以输入任意日期的汇率 - 用户可以每天、每周、每月或不定期地输入它们.

Exchange rates can be entered for arbitrary dates - the user could enter them on a daily basis, weekly basis, monthly basis, or at irregular intervals.

为了将外国金额转换为美元，我需要遵守以下规则:

In order to translate the foreign amounts to dollars, I need to respect these rules:

A.如果可能，请使用最近的先前汇率；所以 2/4/2009 的交易使用 2/1/2009 的汇率，而 3/15/2009 的交易使用 3/1/2009 的汇率.

A. If possible, use the most recent previous rate; so the transaction on 2/4/2009 uses the rate for 2/1/2009, and the transaction on 3/15/2009 uses the rate for 3/1/2009.

B.如果没有为前一个日期定义费率，请使用可用的最早费率.因此，2009 年 1 月 2 日的交易使用 2009 年 1 月的汇率，因为没有定义更早的汇率.

B. If there isn't a rate defined for a previous date, use the earliest rate available. So the transaction on 1/2/2009 uses the rate for 2/1/2009, since there isn't an earlier rate defined.

这有效...

Select 
    t.Date, 
    t.Amount,
    ConvertedAmount=(   
        Select Top 1 
            t.Amount/ex.Rate
        From ExchangeRates ex
        Where t.Date > ex.Date
        Order by ex.Date desc
    )
From Transactions t

... 但是 (1) 似乎加入会更有效 &优雅，并且 (2) 它不处理上面的规则 B.

... but (1) it seems like a join would be more efficient & elegant, and (2) it doesn't deal with Rule B above.

除了使用子查询来查找合适的费率之外，还有其他选择吗?有没有一种优雅的方法来处理规则 B，而不用束缚自己?

Is there an alternative to using the subquery to find the appropriate rate? And is there an elegant way to handle Rule B, without tying myself in knots?

推荐答案

您可以先对按日期排序的汇率进行自联接，这样您就可以知道每个汇率的开始和结束日期，而无需日期中的任何重叠或间隔(可以将其作为视图添加到您的数据库中 - 在我的情况下，我只是使用公共表表达式).

You could first do a self-join on the exchange rates which are ordered by date so that you have the start and the end date of each exchange rate, without any overlap or gap in the dates (maybe add that as view to your database - in my case I'm just using a common table expression).

现在将这些准备好的"费率与交易结合起来既简单又高效.

Now joining those "prepared" rates with the transactions is simple and efficient.

类似于:

WITH IndexedExchangeRates AS (           
            SELECT  Row_Number() OVER (ORDER BY Date) ix,
                    Date,
                    Rate 
            FROM    ExchangeRates 
        ),
        RangedExchangeRates AS (             
            SELECT  CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime) 
                    ELSE IER.Date 
                    END DateFrom,
                    COALESCE(IER2.Date, GETDATE()) DateTo,
                    IER.Rate 
            FROM    IndexedExchangeRates IER 
            LEFT JOIN IndexedExchangeRates IER2 
            ON IER.ix = IER2.ix-1 
        )
SELECT  T.Date,
        T.Amount,
        RER.Rate,
        T.Amount/RER.Rate ConvertedAmount 
FROM    Transactions T 
LEFT JOIN RangedExchangeRates RER 
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)

注意事项:

  • 您可以将 GETDATE() 替换为遥远未来的日期，我在这里假设未来的汇率未知.

  • You could replace GETDATE() with a date in the far future, I'm assuming here that no rates for the future are known.

规则 (B) 是通过将第一个已知汇率的日期设置为 SQL Server datetime 支持的最小日期来实现的，这应该(根据定义，如果它是类型您用于 Date 列)是可能的最小值.

Rule (B) is implemented by setting the date of the first known exchange rate to the minimal date supported by the SQL Server datetime, which should (by definition if it is the type you're using for the Date column) be the smallest value possible.

相关文章