postgresql 最近的较早日期的SQL联接

ljsrvy3e 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(101)

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.
| X | Currency_id |
| ------------ | ------------ |
| 2019-12-01 00:00:00 | USD |
| 2019-12-01 00:00:00 | GBP |
| 2019-12-01 00:00:00 | PLN |
| 2019-12-01 00:00:00 | EUR |
| 2019-12-02 00:00:00 | USD |
| 2019-12-02 00:00:00 | GBP |
| 2019-12-02 00:00:00 | PLN |
| 2019-12-02 00:00:00 | EUR |
| 2019-12-03 00:00:00 | USD |
| ... | ... |
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
| received_at | Currency_id |
| ------------ | ------------ |
| 2020-01-01 | EUR |
| 2020-01-01 | EUR |
| 2020-01-02 | USD |
| 2020-01-03 | USD |
| 2020-01-03 | USD |
| 2020-01-05 | USD |
| 2020-01-06 | GBP |
| ... | ... |
TABLE CURRENCY_RATES
| CURRENCY_ID | RATE | VALID_FROM |
| ------------ | ------------ | ------------ |
| EUR | 24.16 | 2019-12-01 |
| USD | 19.35 | 2019-12-01 |
| GBP | 27.039 | 2019-12-01 |
| PLN | 5.5 | 2019-12-01 |
| EUR | 25.32 | 2019-03-01 |
| USD | 20.34 | 2019-12-01 |
| GBP | 28.4 | 2019-03-01 |
| PLN | 5.3 | 2019-03-01 |
| ... | ... | ... |
If you can think of different approach which is more efficient, it will be pleasure for me to learn it. Thanks!

postgresql

来源：https://stackoverflow.com/questions/74709585/sql-join-on-the-nearest-less-date

1条答案

按热度按时间

zzwlnbp81#

不需要像generate_series这样的行生成器。您的问题看起来像是带有行限制子句的横向联接的典型用例：

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

对于每个订单，子查询在货币表中搜索其有效性开始日期早于（或等于）订单接收日期的最新行。
为了提高性能，请考虑在currency_rates(currency_id, valid_from)上建立索引（或者也可以在索引中反转列）。

赞(0）回复(0）举报 2022-12-12

我来回答

postgresql 最近的较早日期的SQL联接

1条答案

相关问题

热门标签

最新问答