用货币填充缺失的行

Question

我有下表(使用不同的货币(：

ex_rate

日期		货币
2020年11月30日00.00美元	0.8347245409015025
2020年11月27日00.00美元	0.8387854386847845
2020年11月26日00.00美元	0.84033613445378152

您可以使用generate_series根据表中最早和最晚的可用值构建一系列日期，然后通过横向连接引入相应的行：

select d.dt, 'USD', t.ex_rate
from (
select generate_series(min(date), max(date), interval '1 day') as dt
from mytable
where currency = 'USD'
) d
cross join lateral (
select t.*
from mytable t 
where currency = 'USD' and t.date <= d.dt
order by t.date desc limit 1
) t

我想知道left join日期相等，然后使用一些窗口函数技术来构建记录组是否更有效：

select dt, 'USD', max(ex_rate) over(partition by grp) as ex_rate
from (
select d.*, t.*,
count(t.date) over(order by d.dt) as grp
from (
select generate_series(min(date), max(date), interval '1 day') as dt
from mytable
where currency = 'USD'
) d
left join mytable t on currency = 'USD' and t.date = d.dt
) t

请注意，这可以很容易地概括为同时处理所有货币：

select dt, currency, max(ex_rate) over(partition by currency, grp) as ex_rate
from (
select d.dt, c.currency, t.ex_rate,
count(t.date) over(partition by c.currency order by d.dt) as grp
from (select distinct currency from mytable) c
cross join (
select generate_series(min(date), max(date), interval '1 day') as dt
from mytable
) d
left join mytable t on t.currency = c.currency and t.date = d.dt
) t