代码之家 › 专栏 › 技术社区 › Herb Caudill

SQL根据日期范围进行连接?

date-range join tsql sql-server sql

Herb Caudill · 技术社区 · 15 年前

考虑两个表:

,金额为外币:

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

兑换 ,以外币表示的主要货币(比如美元)的价值:

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

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

A.如果可能,使用最新的先前费率;因此,2009年2月4日的交易使用2009年2月1日的汇率,2009年3月15日的交易使用2009年3月1日的汇率。

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

这很有效。。。

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。

6 回复 | 直到 15 年前

Lucero 15 年前

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

比如:

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() 由于日期在遥远的将来,我在这里假设未来的利率是未知的。
通过将第一个已知汇率的日期设置为SQL Server支持的最小日期来实现规则(B) datetime ,它应该(根据定义,如果它是用于 Date 列)为可能的最小值。

Jonathan Leffler 15 年前

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

 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“BETWEEN”运算符配合使用,该运算符包括范围的结尾。通常,范围的更好格式是“开-闭”;列出的第一个日期包括在内,第二个日期不包括在内。请注意,数据行上有一个约束条件,即(a)日期范围内的覆盖范围没有差距,(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;

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

如何创建扩展汇率表?如果您的系统支持从日期值中加减1以获得下一天或前一天(并且有一个称为“Dual”的单行表),则

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(MacOS X 10.6.2上的IBM Informix Dynamic Server 11.50.FC6)上,我能够将查询转换为视图,但我必须停止对数据类型的欺骗-通过将字符串强制转换为日期:

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;

Ray 15 年前

我不能测试这个,但我认为它会起作用。它使用合并和两个子查询来根据规则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

Paul Creasey 15 年前

SELECT 
    a.tranDate, 
    a.Amount,
    a.Amount/a.Rate as convertedRate
FROM
    (

    SELECT 
        t.date tranDate,
        e.date as rateDate,
        t.Amount,
        e.rate,
        RANK() OVER (Partition BY t.date ORDER BY
                         CASE WHEN DATEDIFF(day,e.date,t.date) < 0 THEN
                                   DATEDIFF(day,e.date,t.date) * -100000
                              ELSE DATEDIFF(day,e.date,t.date)
                         END ) AS diff
    FROM 
        ExchangeRates e
    CROSS JOIN 
        Transactions t
         ) a
WHERE a.diff = 1

van 15 年前

根据您的方案,打破僵局的解决方案是:

SELECT      t.Date,
            t.Amount,
            r.Rate
            --//add your multiplication/division here

FROM        "Transactions" t

INNER JOIN  "ExchangeRates" r
        ON  r."ExchangeRateID" = (
                        SELECT TOP 1 x."ExchangeRateID"
                        FROM        "ExchangeRates" x
                        WHERE       x."SourceCurrencyISO" = t."SourceCurrencyISO" --//these are currency-related filters for your tables
                                AND x."TargetCurrencyISO" = t."TargetCurrencyISO" --//,which you should also JOIN on
                                AND x."Date" <= t."Date"
                        ORDER BY    x."Date" DESC)

您需要有正确的索引才能快速执行此查询。理想情况下,你不应该有一个 JOIN "Date" ,但是 "ID" -相似场( INTEGER

momo 12 年前

没有任何一种连接方式比 TOP 1 原始帖子中的相关子查询。但是,正如您所说,它不满足要求B。

这些查询确实有效(需要SQL Server 2005或更高版本)。看见 the SqlFiddle for these .

SELECT
   T.*,
   ExchangeRate = E.Rate
FROM
  dbo.Transactions T
  CROSS APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY
      CASE WHEN E.RateDate <= T.TranDate THEN 0 ELSE 1 END,
      E.RateDate DESC
  ) E;

请注意,具有单个列值的交叉应用在功能上等同于 SELECT 如你所示。我现在更喜欢交叉应用,因为它更灵活,允许您在多个位置重用值,有多行(用于自定义取消激活),并允许您有多个列。

SELECT
   T.*,
   ExchangeRate = Coalesce(E.Rate, E2.Rate)
FROM
  dbo.Transactions T
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY E.RateDate DESC
  ) E
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E2
    WHERE E.Rate IS NULL
    ORDER BY E2.RateDate
  ) E2;

我不知道哪一个可能会表现得更好,或者哪一个会比页面上的其他答案表现得更好。如果在日期列上有一个适当的索引,它们应该会非常活跃——肯定比任何一个都好 Row_Number() 解决方案