запрос, который конвертирует сумму транзакции в евро

Question

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

CREATE TABLE Bank_operation (
Customer_id INT ,
Date_id date Primary key,
Transaction_amount INT);

CREATE TABLE Rates (
Date_id date Primary key,
Ccy_code INT,
Rate decimal (5,2),
FOREIGN KEY (Date_id)  REFERENCES bank_operation (Date_id));

INSERT INTO Bank_operation VALUES (123658, '2022-03-12', 6500 );
INSERT INTO Rates VALUES ( '2022-02.21', 230, 70.65 );
INSERT INTO Rates VALUES ( '2022.05.15', 230, 72.20 );

Я хочу написать запрос, который будет конвертировать сумму транзакции в eur (ccy_code = 230), учитывая, что в таблице Rates есть данные только по будним дням, а транзакции, которые были сделаны в выходные, должны быть пересчитаны по курсу последнего рабочего дня.

Результат, который я хочу получить

Customer_id Date_id Total

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

SELECT Customer_id, Rates.Date_id,  
      Transaction_amount / ( SELECT Rate
                       FROM Rates
                       WHERE Rates.Date_id = Bank_operation.Date_id
                       ORDER BY Rates.Date_id DESC
                       LIMIT 1 ) AS total
FROM Bank_operation;

Заранее благодарю всех за помощь.

Answer 1

Сначала стоит отказаться от PRIMARY KEY и FOREIGN KEY на полях типа DATE, так как на одну и туже дату у Вас может быть несколько различных записей о платежах в таблице Bank_operation, а такая организация БД этого не позволит. Тоже касается и таблицы с курсами валют Rates, такая организация не позволит внести курсы других валют на одну и ту же дату. Более того, если оставить всё как есть, то вылетит ошибка при попытке выполнить вставки в таблицу Rates, так вторичные ключи обоих записей ('2022.02.21' и '2022.05.15') будет ссылаться на несуществующую запись в таблице Bank_operation (там только '2022.03.12'). Таким образом я бы написал так:

   CREATE TABLE Bank_operation (
   Customer_id INT ,
   Date_id DATE,
   Transaction_amount INT
   );

   CREATE TABLE Rates (
   Date_id DATE,
   Ccy_code INT,
   Rate decimal (5,2)
   );

   INSERT INTO Bank_operation VALUES (123658, '2022.03.12', 6500 );
   INSERT INTO Rates VALUES ( '2022.02.21', 230, 70.65 );
   INSERT INTO Rates VALUES ( '2022.05.15', 230, 72.20 );

Теперь рассмотрим запрос. У Вас будет ошибка в строке SELECT Customer_id, Rates.Date_id, ... таблицу Rates Вы используете только в подзапросе и на неё ссылаться в выводе результата нельзя. Ну и собственно условие, если последний известный курс записан датой ранее, то нужно поставить условие < (меньше), тогда всё заработает.

SELECT 
    b.Customer_id, 
    b.Date_id,  
    b.Transaction_amount / 
    ( SELECT r.Rate FROM Rates r
    WHERE r.Date_id < b.Date_id AND r.Ccy_code = 230
    ORDER BY r.Date_id DESC
    LIMIT 1) AS total
FROM Bank_operation b;

Надеюсь, я смог Вам помочь.

БЛОГ НА HUSL

запрос, который конвертирует сумму транзакции в евро

Ответы (1 шт):