SQL联接日期范围? [英] SQL join against date ranges?

问题描述

考虑两个表:

交易，其中包含外币金额:

Transactions, with amounts in a foreign currency:

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

ExchangeRates ，其中包含主要货币(例如美元)的外币价值:

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.如果可能，请使用最近的先前汇率；因此，2009年2月4日的交易使用2009年2月1日的汇率，2009年3月15日的交易使用2009年3月1日的汇率.

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年2月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.

这篇关于SQL联接日期范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！