Блог

Я хочу объединить две таблицы, и в одной отсутствует строка для каждой валютной группы.

Таблицы:

Транзакции в местной валюте, агрегированные по месяцам. (таблица транзакций)

 Date            Currency       spend
2019-01-01       EUR             100
2019-02-01       EUR             200 
2019-03-01       EUR             500
2019-04-01       EUR             214
2019-01-01       JYP            3200
2019-01-01       JYP            1534
2019-02-01       JYP            1534
2019-03-01       JYP            1534
2019-04-01       JYP            1534
  

Обменный курс по месяцам (таблица exchange_data)

 Month            Currency       Average Monthly rate
2019-01-01       EUR            1.2
2019-02-01       EUR            1.3 
2019-03-01       EUR            1.4
2019-01-01       JYP            101
2019-02-01       JYP            102
2019-03-01       JYP            103
2019-01-01       USA            1
2019-02-01       USA            1
2019-03-01       USA            1
  

Я хочу выполнить объединение, чтобы получить все транзакции в долларах США. Проблема в том, что скорость за текущий месяц (2019-04-01) недоступна. Таким образом, все транзакции за текущий месяц возвращают NULL после объединения.

Мне удалось решить эту проблему в R, но есть ли способ решить ее с помощью SQL? Я пытался использовать функцию окна, но безуспешно

 LAG(rate,1) OVER (PARTITION BY currency ORDER BY month)
  

Решение в R: предполагая, что скорость остается постоянной.

 library(lubridate)
library(dplyr)

exchange_previous <- exchange_data[exchange_data$month == floor_date(today(),"month") %m-% months(1),]
exchange_previous$month <- exchange_previous$month %m % months(1)
exchange_data<-rbind(exchange_data,exchange_previous)

final <- transactions %>%
left_join(exchange_data, by = c("currency" = "name", "floor_date" = "month"))
Then simply multiply
  

Используйте боковое соединение, но оно должно выглядеть так:

 select t.*, ed.average_monthly_rate,
from transactions t left join lateral
     (select ed.*
      from exchange_data ed
      where ed.currency = t.currency and
            ed.month <= t.date
      order by ed.month desc
      fetch first 1 row only
     ) ed
     on 1=1;
  

Я не уверен, хотите ли вы разделить или умножить на скорость.

Вы можете использовать a LATERAL JOIN для применения самого последнего обменного курса для валюты, например:

         SELECT tr.*, rates.rate
          FROM transactions tr
          LEFT JOIN
       LATERAL (SELECT rate
                     -- order rows by date, most recent first
                     , ROW_NUMBER() OVER (PARTITION BY currency 
                                              ORDER BY d DESC) rn 
                  FROM exchange_data 
                 WHERE currency = tr.currency 
                   -- exchange dates only up until the month of a transaction
                   AND d <= tr.d
             ) rates
            ON rates.rn = 1 -- row for the most recent date
  

LATERAL JOIN аналогично APPLY SQL Server, поскольку позволяет присоединять результирующий набор с использованием предикатов из внешнего запроса.

Вот рабочий пример на dbfiddle.

Предполагая, что ваши таблицы называются Transactions и Exchange, следующий код работает для MySQL. Я думаю, Postgresql будет похож.

 select C.date, C.spend/E.Average 
   from Transactions as C, Exchange as E,
      (select A.date, A.Currency, Max(B.Month) as Month 
         from Transactions as A, Exchange as B
         where A.Currency = B.Currency and A.datum >= B.Month
         group by A.date, A.Currency
      ) as D
where C.date = D.date 
   and C.Currency = D.Currency 
   and E.Month = D.Month 
   and C.Currency = E.Currency
order by C.date;
  

Идея, лежащая в его основе, заключается в следующем: учитывая дату и валюту, вы просматриваете таблицу D, которая является ближайшей прошедшей датой для этой валюты. Как только у вас это будет, вы сможете получить обмен, который вы искали.