针对日期范围的SQL联接？

Question

考虑两个表：

Transaction，其金额为外币：

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

以外币表示主货币(例如美元)价值的ExchangeRates，：

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

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

为了将外国数额换算成美元，我需要遵守以下规则：

如果可能的话，使用最近的汇率；因此，2009年4月2日的交易使用2009年2月1日的汇率，2009年3月15日的交易使用3/1/2009的汇率。

如果没有为以前的日期定义费率，请使用可用的最早比率。因此，2009年1/2的交易使用了2/1/2009的汇率，因为没有定义更早的汇率。

这很管用..。

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。

除了使用子查询来找到合适的速率之外，还有其他选择吗？有没有一种优雅的方法来处理规则B，而不把自己绑在绳结上呢？

Answer 1

您可以首先对按日期排序的汇率进行自联接，这样就可以得到每个汇率的开始日期和结束日期，而不会出现任何重叠或空白(可能会将其添加到数据库中--在我的例子中，我只是使用了一个通用的表表达式)。

现在，将这些“准备好的”利率与交易结合起来是简单而有效的。

类似于：

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()，我在这里假设未来的利率是未知的。
• 规则(B)是通过将第一个已知汇率的日期设置为Server datetime支持的最小日期来实现的，根据定义，该日期应该是您为Date列使用的类型的最小值。

Answer 2

假设您有一个扩展的汇率表，其中包含：

 Start Date   End Date    Rate
 ========== ========== =======
 0001-01-01 2009-01-31    40.1
 2009-02-01 2009-02-28    40.1
 2009-03-01 2009-03-31    41.0
 2009-04-01 2009-04-30    38.5
 2009-05-01 9999-12-31    42.7

我们可以讨论前两行是否应该合并的细节，但是一般的想法是，要找到给定日期的汇率是很简单的。此结构适用于包含范围末端的SQL“includes”运算符。通常，范围的一种更好的格式是“开放-关闭”；列出的第一个日期包括在内，而第二个日期被排除在外。注意，对数据行有一个限制- (a)在日期范围的覆盖范围方面没有空白，(b)覆盖范围没有重叠。强制执行这些约束并不是完全微不足道的(礼貌的轻描淡写-减数分裂)。

现在，基本的查询是微不足道的，而案例B不再是特例：

SELECT T.Date, T.Amount, X.Rate
  FROM Transactions AS T JOIN ExtendedExchangeRates AS X
       ON T.Date BETWEEN X.StartDate AND X.EndDate;

棘手的部分是动态地从给定的ExtendedExchangeRate表创建ExchangeRate表。如果这是一个选项，那么修改基本ExchangeRate表的结构以匹配ExtendedExchangeRate表将是一个好主意；当输入数据时(每月一次)，而不是每次需要确定汇率(每天多次)，您就可以解决这些杂乱无章的问题。

如何创建扩展汇率表？如果您的系统支持从日期值中添加或减去1以获得第二天或前一天(并且有一个名为“Dual”的单行表)，则对此的更改将起作用(不使用任何OLAP函数)：

CREATE TABLE ExchangeRate
(
    Date    DATE NOT NULL,
    Rate    DECIMAL(10,5) NOT NULL
);
INSERT INTO ExchangeRate VALUES('2009-02-01', 40.1);
INSERT INTO ExchangeRate VALUES('2009-03-01', 41.0);
INSERT INTO ExchangeRate VALUES('2009-04-01', 38.5);
INSERT INTO ExchangeRate VALUES('2009-05-01', 42.7);

第一行：

SELECT '0001-01-01' AS StartDate,
       (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

结果：

0001-01-01  2009-01-31      40.10000

最后一行：

SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
       '9999-12-31' AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

结果：

2009-05-01  9999-12-31      42.70000

中间行：

SELECT X1.Date     AS StartDate,
       X2.Date - 1 AS EndDate,
       X1.Rate     AS Rate
  FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
       ON X1.Date < X2.Date
 WHERE NOT EXISTS
       (SELECT *
          FROM ExchangeRate AS X3
         WHERE X3.Date > X1.Date AND X3.Date < X2.Date
        );

结果：

2009-02-01  2009-02-28      40.10000
2009-03-01  2009-03-31      41.00000
2009-04-01  2009-04-30      38.50000

请注意，不存在子查询非常关键。没有它，“中间行”的结果是：

2009-02-01  2009-02-28      40.10000
2009-02-01  2009-03-31      40.10000    # Unwanted
2009-02-01  2009-04-30      40.10000    # Unwanted
2009-03-01  2009-03-31      41.00000
2009-03-01  2009-04-30      41.00000    # Unwanted
2009-04-01  2009-04-30      38.50000

当表的大小增加时，不想要的行数会急剧增加(对于N>2行，我认为有(N-2) *(N-3)/2个不需要的行)。

ExtendedExchangeRate的结果是三个查询的(不相交)联合：

SELECT DATE '0001-01-01' AS StartDate,
       (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
FROM Dual
UNION
SELECT X1.Date     AS StartDate,
       X2.Date - 1 AS EndDate,
       X1.Rate     AS Rate
  FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
       ON X1.Date < X2.Date
 WHERE NOT EXISTS
       (SELECT *
          FROM ExchangeRate AS X3
         WHERE X3.Date > X1.Date AND X3.Date < X2.Date
        )
UNION
SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
       DATE '9999-12-31' AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

在测试DBMS (IBMInformixDynamicServer11.50.FC6在MacOS X 10.6.2上)上，我能够将查询转换为视图，但必须停止对数据类型的欺骗--方法是将字符串强制转换为日期：

CREATE VIEW ExtendedExchangeRate(StartDate, EndDate, Rate) AS
    SELECT DATE('0001-01-01')  AS StartDate,
           (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
           (SELECT Rate FROM ExchangeRate WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
    FROM Dual
    UNION
    SELECT X1.Date     AS StartDate,
           X2.Date - 1 AS EndDate,
           X1.Rate     AS Rate
      FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
           ON X1.Date < X2.Date
     WHERE NOT EXISTS
           (SELECT *
              FROM ExchangeRate AS X3
             WHERE X3.Date > X1.Date AND X3.Date < X2.Date
            )
    UNION 
    SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
           DATE('9999-12-31') AS EndDate,
           (SELECT Rate FROM ExchangeRate WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
    FROM Dual;

Answer 3

我不能测试这个，但我认为它会成功的。它使用合并两个子查询来根据规则A或规则B选择费率。

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