I am dealing with a problem in Postgres, where I have table ORDERS with column RECEIVED_AT and table CURRENCY_RATES with column VALID_FROM. There are similar questions on StackOverflow, but unfortunately, I am not able to utilise the answers. The task is to multiply/divide a price of order in certain currency, by RATE (column from rate CURRENCY_RATES) which is valid at the date of RECEIVED_AT.

--RETURNS daily currency_id, NOT currency_rates added
select  
x,
cr.currency_id
--, cr.rate
from currency_rates cr
cross join generate_series('2019-12-01'::timestamp,
               '2020-02-12'::timestamp,'1 day') as x
               --on x.x = cr.valid_from
               group by x, cr.currency_id
               order by x;

The best way I was able to figure it out, not further, was to join time series and currency_id for each day of time series. Now, I believe it would be possible to query it with the RATE column which is equal, or max less date than the date in orders is.

Then, I will basically join it with ORDERS table on o.RECEIVED_AT = x.x and o.CURRENCY_ID = cr.CURRENCY_ID, to get cr.RATE

TABLE ORDERS

TABLE CURRENCY_RATES

If you can think of different approach which is more efficient, it will be pleasure for me to learn it. Thanks!

CodePudding user response：

No need for a row generator such as a generate_series. Your question reads like a typical use case for a lateral join with a row-limiting clause:

select o.*, cr.rate
from orders o
cross join lateral (
    select cr.*
    from currency_rates cr
    where cr.currency_id = o.currency_id and cr.valid_from <= o.received_at
    order by cr.valid_from desc 
    limit 1
) cr

For each order, the subquery searches the currency table for the latest row whose validity starts earlier than (or at) the order reception date.

For performance, consider an index on currency_rates(currency_id, valid_from) (or maybe the columns could be inverted in the index as well).