如何在蜂巢中查找丢失日期的货币兑换率

Question

我有一个销售表和一个货币换算表

**sales**
id, amount, currency, sales_date
1, 100, usd, 2022-05-01
2, 150, usd, 2022-06-04
3, 55, usd, 2022-06-07
4, 75, usd, 2022-07-03
**currency_conversion_usd_cad**
conv_date, conversion_rate
2022-05-01, 1.28
2022-06-04, 1.26
2022-07-03, 1.27

如果我要做以下

select 
s.id AS id,
s.amount*cc.conversion_rate AS CAD_amount,
s.sales_date AS s_date
from sales s
left join currency_conversion_usd_cad cc
on s.sales_date = cc.conv_date

我的结果是

id, CAD_amount, s_date
1, 128.0, 2022-05-01
2, 189.0, 2022-06-04
3, **NULL**, 2022-06-07
4, 95.25, 2022-07-03

由于没有日期2022-06-07的转换率值，CAD中的金额默认为NULL。有没有一种方法可以绕过这个问题，让它使用有转换率的最后一个日期的转换率？因此，预期结果将是

id, CAD_amount, s_date
1, 128.0, 2022-05-01
2, 189.0, 2022-06-04
3, 69.3, 2022-06-07
4, 95.25, 2022-07-03

由于前一天的转换率为1.26的55倍，将等于69.3

Answer 1

您可以使用last_value():

select s.id, s.amount * last_value(cc.conversion_rate) over (order by s.date) as cad_amount,
s.date
from sales s left join
currency_conversion_usd_cad cc
on s.date = cc.date;