Соединение SQL против диапазонов даты?

Многие решения будут работать. Вы действительно должны найти тот, который лучше всего (самый быстрый) для вашей рабочей нагрузки: вы обычно ищете одну транзакцию, их список, все?

Решение для разрешения конфликтов с учетом вашей схемы:

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 на «Дата» , а на «ID» -подобное поле ( INTEGER ). Дайте мне больше информации о схеме, я создам для вас пример.

Предположим, у вас есть расширенная таблица обменных курсов, содержащая:

 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, который включает концы диапазонов. Часто лучшим форматом для диапазонов является «открытый-закрытый»; первая указанная дата включена, а вторая исключена. Обратите внимание, что для строк данных существует ограничение: (а) нет пробелов в охвате диапазона дат и (б) нет перекрытий в охвате. Обеспечение этих ограничений не совсем тривиально (вежливое преуменьшение - мейоз).

Теперь основной запрос тривиален, и случай 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;

В тестовой СУБД (IBM Informix Dynamic Server 11.50.FC6 в MacOS X 10.6.2) мне удалось преобразовать запрос в view, но мне пришлось прекратить обманывать типы данных - преобразовывая строки в даты:

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;

Я не могу проверить это, но думаю, что это будет работать. Он использует coalesce с двумя подзапросами для выбора ставки по правилу 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

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

Вычисляется разница между датой перехода и даты курса, затем отрицательные значения (условие b) умножаются на -10000, чтобы их можно было ранжировать, но положительные значения (условие a всегда имеет приоритет. Затем мы выбираем минимальную разницу дат для каждый переход с использованием предложения «ранг выше».