sql - 如何连接上个月缺少行的两个表？

Question

我想连接两个表，其中一个表缺少每个货币组的一行。

表格:

按月汇总的本地货币交易。(交易表)

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

Answer 1

使用横向连接，但它应该如下所示:

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;

我不确定你是想除以比率还是乘以比率。