假设您有一个包含以下内容的扩展汇率表:
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
请注意,NOT EXISTS子查询非常关键。如果没有它,“中间行”结果将是:
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 on 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;
IndexedExchangeRate
中的行编号,使每个货币为一行(使用PARTITION
),并将其添加到RangedExchangeRate
中的LEFT JOIN
条件中。 - Lucero