ShadowFlame ShadowFlame - 2 months ago 5
MySQL Question

select data where date is max(date) less than x

I have 2 tables
The first one has exchange rates:

| date | ratio | currency |
------------------------------
1| 9/09 | 1.0 | EUR |
2| 9/09 | 1.1 | USD | -- no weekend
3| 12/09 | 1.0 | EUR | -- goes from 9 to 12
4| 12/09 | 120.0 | JPY |


The second one has transactions

| date | amount | currency |
------------------------------
1| 9/09 | 20.0 | EUR |
2| 9/09 | 101.0 | USD | -- weekend
3| 10/09 | 1.0 | USD | -- has 10/09 which is a saturday
4| 10/09 | 10.0 | USD |


Both contain the date and the currency.
As it stands my exchange rates are not updated during the weekend, and that won't change.

I'm looking for a performant way to select the last available data to be put into the exchange_rate table. In other words, the last day before the missing day.(10/09 in the example)

I'm using the transaction table to get a list of days that need the exchange-rate information, so that I can convert everything to EUR.

the full result wanted should be something like

| date | amount | currency | ratio |
----------------------------------------
1| 9/09 | 20.0 | EUR | 1.0 |
2| 9/09 | 101.0 | USD | 1.1 | -- already exists in exchange_rate
3| 10/09 | 1.0 | USD | 1.1 | -- selected because 9/09 is last available line
4| 10/09 | 10.0 | USD | 1.1 |


Alternatively I am fine with a query that updates the exchange_rate table with the needed data as well, because the final query would be cleaner and easier to maintain later on

Answer

You can do this using a correlated subquery:

select t.*,
       (select er.ratio
        from exchangerates er
        where ec.date <= e.date and ec.currency = t.currency
        order by ec.date desc
        limit 1
       ) as ratio
from transactions t;

For performance, you want an index on exchangerates(currency, date, ratio).

I would start with this and see if it meets your needs.

Comments