从汇率表隐式计算SQL中的汇率

Question

我有一个以多种货币表示收入的数据表(让我们称其为TRANSACTION_TABLE，列如下：

TRANSACTION_NAME
TRANSACTION_VALUE
CURRENCY

，以及另一个具有以下列的汇率表(EXCHANGE_RATE)：

FROM_CURRENCY (e.g. JPY)
TO_CURRENCY (e.g. USD)
EXCHANGE_RATE (x)

该表至少包含每种货币对美元的换算，但非美元TO_CURRENCY值的汇率并非详尽无遗。

我试图实现的是一个查询，该查询将事务转换为任何货币，即使没有在EXCHANGE_RATE表中明确规定，方法是先将货币转换为美元，然后从美元转换为目标货币。

例如，1000日元对英镑：

1. 查找日元兑美元的汇率-计算= 1000 * EXCHANGE_RATE = 9
2. 找出英镑兑美元的利率-计算= 9 \ EXCHANGE_RATE = 7

目前，我在TRANSACTION_TABLE on EXCHANGE_RATE上做了一个左加入，但是我不知道下一步该去哪里。

如能提供任何协助，将不胜感激。

到目前为止，我构建的查询(非常基本)如下，我是一个新手SQL用户。我首先构建了这个查询来转换为美元，这很好(因为我的汇率表包含所有货币对美元的值)，但是当将目标货币设置为GBP时，它显然失败了，因为它只返回null。

SELECT TRANSACTION_NAME,
             SUM (TRANSACTION_VALUE * EXCHANGE_RATE)
                 AS "REVENUE GBP"
        FROM TRANSACTION_TABLE S
             LEFT JOIN EXCHANGE_RATE C ON S.CURRENCY = C.FROM_CURRENCY AND C.TO_CURRENCY = 'GBP'
ORDER BY TRANSACTION_NAME

Answer 1

如果您的EXCHANGE_RATE表是详尽的美元，那么您将不会有超过两个“啤酒花”来进行您的转换。充其量，您将转换为美元，然后从美元到任何其他。考虑到这一点，我只为所有可能的情况编写代码，而不是尝试一些花哨的东西，比如CONNECT BY。

“所有可能的案件”，我认为是：

• 事务已经以目标货币进行处理。
• 交易使用的是可直接兑换为目标货币的货币。
• 交易必须转换为美元，然后从美元转换为目标货币。

这里有一个查询可以实现这一点。WITH子句只是为了给它一些数据--它们不会成为解决方案的一部分，因为您有实际的表。

WITH rates ( from_currency, to_currency, exchange_rate ) AS 
( SELECT 'JPY', 'USD', 0.009 FROM DUAL UNION ALL
  SELECT 'GBP', 'USD', 1.31 FROM DUAL UNION ALL
  SELECT 'CNY', 'USD', 0.15 FROM DUAL UNION ALL
  SELECT 'JPY', 'CNY', 0.06 FROM DUAL),
txns ( transaction_name, transaction_value, currency ) AS
 ( SELECT 'txn 1 in JPY', 1000, 'JPY' FROM DUAL UNION ALL
   SELECT 'txn 2 in GBP', 1000, 'GBP' FROM DUAL UNION ALL
   SELECT 'txn 3 IN CNY', 1000, 'CNY' FROM DUAL UNION ALL
   SELECT 'txn 4 IN unknown', 1000, 'XXX' FROM DUAL),
params ( target_currency ) AS 
 ( SELECT 'CNY' FROM DUAL )
SELECT t.transaction_name,
       t.transaction_value base_value,
       t.currency base_currency,
       t.transaction_value * CASE WHEN t.currency = params.target_currency THEN 1
            WHEN r1.from_currency IS NOT NULL THEN r1.exchange_rate
            ELSE r2usd.exchange_rate / r2tar.exchange_rate END converted_value,
        params.target_currency converted_currency
FROM   params CROSS JOIN 
       txns t 
       LEFT JOIN rates r1 ON r1.from_currency = t.currency AND r1.to_currency = params.target_currency
       LEFT JOIN rates r2usd ON r2usd.from_currency = t.currency AND r2usd.to_currency = 'USD'
       LEFT JOIN rates r2tar ON r2tar.from_currency = params.target_currency AND r2tar.to_currency = 'USD'

Answer 2

我建议做一个额外的步骤来扩展您的exchange表，并使用UDS作为转移货币额外定义汇率。

此查询添加通过美元计算的新利率。这是一个简单的内部连接约束，因此计算是通过‘美元’和来自和货币是不同的。WHERE子句限制已经知道的组合。

select  er1.FROM_CURRENCY, er2.TO_CURRENCY,  er1.EXCHANGE_RATE * er2.EXCHANGE_RATE EXCHANGE_RATE
from exchange_rates er1
join exchange_rates er2
on er1.TO_CURRENCY = 'USD' and er2.FROM_CURRENCY = 'USD'  and er1.FROM_CURRENCY != er2.TO_CURRENCY
where (er1.FROM_CURRENCY, er2.TO_CURRENCY)
not in (select FROM_CURRENCY,   TO_CURRENCY from exchange_rates)

您可以定义一个物理的新表或视图，甚至只作为子查询来执行它，作为原始表的UNION ALL和该查询的结果。

您的最后查询使用此扩展汇率表而不是原始汇率表。

下面是我用过的样本数据

create table exchange_rates
as
select 'GBP' FROM_CURRENCY,  'USD' TO_CURRENCY, 1.31  EXCHANGE_RATE from dual union all
select 'EUR' FROM_CURRENCY,  'USD' TO_CURRENCY, 1.16 EXCHANGE_RATE from dual union all
select 'AUD' FROM_CURRENCY,  'USD' TO_CURRENCY, .73 EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'GBP' TO_CURRENCY, .76  EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'EUR' TO_CURRENCY, .86 EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'AUD' TO_CURRENCY, 1.36 EXCHANGE_RATE from dual union all
select 'GBP' FROM_CURRENCY,  'EUR' TO_CURRENCY, 1.12 EXCHANGE_RATE from dual;