Блог

У меня есть две таблицы

Таблица заказов

 id       | total_price (EUR)| order_date |
---------|------------------|------------|
1        | 400              | 2021-05-01 |
2        | 1350             | 2021-10-04 |
3        | 760              | 2021-10-05 |
 

Таблица обменных курсов (cron-job выполняет эту работу)

 id       | day              | base_currency | currency | exchange |
---------|------------------|---------------|----------|----------|
1        | 2021-10-03       | EUR           | USD      | 1.19     |
2        | 2021-10-04       | EUR           | USD      | 1.17     |
3        | 2021-10-05       | EUR           | USD      | 1.16     |
 

Я должен суммировать общую сумму заказов в долларах США и с правильным обменным курсом!

Как я мог этого добиться? Существует ли обычная практика?

То, что у меня есть до сих пор, является базовой суммой:

 sum(total_price) as salesRevenue,
 

Нужная вам ссылка основана на дате. Для этой цели я бы сначала проверил, есть ли у меня вся необходимая информация.

Поэтому я спрошу, учитывая все даты заказа, какие ставки доступны на этот день; требуя, чтобы ставка была нулевой, что означает, что ставки нет. Желаемый результат заключается в том, что запрос ничего не возвращает (ни у одного дня нет ставок, поэтому у всех дней есть хотя бы одна ставка).

 SELECT o.order_date 
   FROM orders AS o 
   LEFT JOIN rates AS r 
       ON (o.order_date = r.day AND base_currency='EUR' AND currency='USD')
   WHERE r.exchange IS NULL;
 

Еще один приемочный тест, который я бы провел, — убедиться, что для обменного курса нет двойных записей (каждый день имеет ровно один курс):

 SELECT day, COUNT(*) AS n FROM rates
    GROUP BY day
    HAVING COUNT(*) > 1;
 

Опять же, желаемый результат заключается в том, что запрос ничего не возвращает.

Если тесты что-то сообщат, вы не сможете выполнить приведенные ниже запросы, потому что получите неправильные результаты.

Если оба теста ничего не сообщают, то вы можете повторить почти одно и то же объединение, и у каждого кортежа будет exchange столбец, доступный для использования:

 SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD
   FROM orders AS o 
   -- Now we know a link is always available, we can use a JOIN
   JOIN rates AS usd
       ON (o.order_date = usd.day AND usd.base_currency='EUR' AND usd.currency='USD')
   ;
 

Если вы хотите добавить дополнительный столбец валюты, вам нужно будет повторить объединение с другим псевдонимом:

 SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD,
       SUM(o.order_amount * sek.exchange) AS TotalSEK,
   FROM orders AS o 
   JOIN rates AS usd
       ON (o.order_date = usd.day AND base_currency='EUR' AND currency='USD')
   JOIN rates AS sek
       ON (o.order_date = sek.day AND sek.base_currency='EUR' AND sek.currency='SEK')
   ;
 

С MySQL > = 8.0

Я предполагаю, что у вас нет ежедневного обменного курса, поэтому я использую предыдущий последний доступный обменный курс. Если в некоторые дни у вас более одного обменного курса, вы также должны сэкономить часы и минуты.

Сначала вам нужно получить предыдущий последний обменный курс для каждого заказа, затем вам нужно перевести в доллары и, наконец, суммировать все заказы в долларах.

Будьте осторожны: в вашем примере данных первого заказа нет обменного курса, потому что он должен быть предшествующим дате заказа (этот заказ будет опущен в моем запросе).

 WITH cte AS (SELECT o.id, o.order_date, o.total_price, 
                    FIRST_VALUE(er.exchange) OVER (PARTITION BY o.id ORDER BY er.day DESC) AS exchange
             FROM Orders o
             INNER JOIN ExchangeRates er ON o.order_date >= er.day
             WHERE base_currency = 'EUR' AND currency = 'USD'),
             
     cte1 AS (SELECT id, order_date, total_price * MIN(exchange) AS total_price_dolar
              FROM cte
              GROUP BY id, order_date, total_price)
              
SELECT SUM(total_price_dolar) AS total
FROM cte1;