Неявное вычисление курсов валют в 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 JPY в GBP:

1. Найти курс JPY к USD - расчет = 1000 * EXCHANGE_RATE = 9
2. Найти курс GBP к USD - расчет = 9 \ EXCHANGE_RATE = 7

На данный момент я сделал левое соединение для TRANSACTION_TABLE на EXCHANGE_RATE но я заблудился, куда идти дальше.

Будем очень благодарны любой помощи.

Запрос (очень простой), который я создал до сих пор, выглядит следующим образом, и я начинающий пользователь SQL. Сначала я построил этот запрос для преобразования в доллары США, что прекрасно работает (поскольку моя таблица обменных курсов содержит значения для всех валют по отношению к доллару США), но, очевидно, он не работает при установке валюты назначения в фунтах стерлингов, поскольку он просто возвращает нули.

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

Я бы предложил сделать дополнительный шаг, чтобы расширить вашу таблицу обмена с обменными курсами, дополнительно определенными с использованием 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;