postgreSQL case语句，按日期筛选，按id分组

Question

我有两个表：交易和货币。事务表包含事务：

id     date           client_id     currency     amount
2      '2017-07-18'   29            'EURO'       340
3      '2018-08-09'   34            'RUB'        5000

货币表包含欧元或美元对卢布的货币汇率-例如，第一行表示1欧元=70卢布。对于周末，由于银行关门，没有价值，为了计算，我需要使用周五的汇率：

date           currency     value
'2017-08-07'   'EURO'       70
'2018-08-07'   'USD'        60
'2018-09-09'   'USD'        NULL

所以我需要计算每个客户在RUB的花费。如果可能的话，不要使用窗口功能。

我尝试使用case when和group byclient_id，但每次他们进行交易时我都需要考虑汇率，我不知道如何提供。

Answer 1

select t.*, amount * coalesce((select value 
from currency c 
where c.currency = t.currency 
and c.date <= t.date order by c.date desc limit 1),
1)
from transactions t

假设如果没有找到货币，则为卢布，因此使用1作为汇率。

Answer 2

您可以用横向连接来表达这一点：

select t.*,
t.amount * c.value as rub
from transactions t left join lateral
(select c.*
from currency c
where c.currency = t.currency and
c.value is not null and
c.date <= t.date
order by c.date desc
fetch first 1 row only
) c;