Search code examples
sqlpostgresqlpostgresql-13

Joining tables based on expanded date range

I've got two tables, orders, and currency_rates. I need to join orders using orders.received_at with currency_rates.valid_from (and also using the common column currency_id, but that's easy).

The problem I am facing is that currency_rates.valid_from is not a full table of dates, but just start and end dates of date ranges. In practice I've got three currency periods '2019-12-01' - '2020-01-03', '2020-01-03' - '2020-01-09', '2020-01-09' - onward. What is the most elegant way of accomplishing that in Postgres? Is it possible to do that using the Select statement? Thanks!

EDIT: Solutions without manually specifying ranges are welcomed.

EDIT2: Added a table orders_currency_rates with few example rows of the desired result.

    CREATE TABLE orders
(
    id                  BIGINT,
    received_at         DATE,
    shipping_cost       DOUBLE PRECISION,
    currency_id         VARCHAR,
    invoice_address_id  BIGINT,
    delivery_address_id BIGINT
);

INSERT INTO orders (id, received_at, shipping_cost, currency_id, invoice_address_id, delivery_address_id)
VALUES (385902, '2020-01-01', 0, 'CZK', 1, 11),
       (386278, '2020-01-02', 12.83, 'USD', 2, NULL),
       (386279, '2020-01-03', 49.36, 'USD', 3, 12),
       (386280, '2020-01-03', 12.83, 'USD', 4, 13),
       (386281, '2020-01-05', 12.83, 'USD', 5, 14),
       (386282, '2020-01-06', 11.43, 'GBP', 6, NULL),
       (386283, '2020-01-07', 12.83, 'USD', 7, 15),
       (386284, '2020-01-08', 44.03, 'EUR', 8, NULL),
       (386285, '2020-01-11', 12.83, 'USD', 9, NULL),
       (386286, '2020-02-12', 62.55, 'USD', 10, NULL);
CREATE TABLE currency_rates
(
    currency_id VARCHAR,
    rate        DOUBLE PRECISION,
    valid_from  DATE
);

INSERT INTO currency_rates (currency_id, rate, valid_from)
VALUES ('EUR', 24.165, '2019-12-01'),
       ('USD', 19.359, '2019-12-01'),
       ('GBP', 27.039, '2019-12-01'),
       ('PLN', 5.5, '2019-12-01'),
       ('EUR', 25.2, '2020-01-03'),
       ('USD', 20.34, '2020-01-03'),
       ('GBP', 28.4, '2020-01-03'),
       ('PLN', 5.3, '2020-01-03'),
       ('EUR', 26.165, '2020-01-09'),
       ('USD', 21.359, '2020-01-09'),
       ('GBP', 29.039, '2020-01-09'),
       ('PLN', 5.8, '2020-01-09');

Example of the desired result for the join.

     CREATE TABLE orders_currency_rates
(
    id                  BIGINT,
    received_at         DATE,
    shipping_cost       DOUBLE PRECISION,
    currency_id         VARCHAR,
    invoice_address_id  BIGINT,
    delivery_address_id BIGINT,
    rate                DOUBLE PRECISION,
    valid_from          DATE
);

INSERT INTO orders_currency_rates (id, received_at, shipping_cost, currency_id, invoice_address_id, delivery_address_id, rate, valid_from)
VALUES (386278, '2020-01-02', 12.83, 'USD', 2, NULL, 19.359, '2019-12-01'),
       (386279, '2020-01-03', 49.36, 'USD', 3, 12, 20.34, '2020-01-03'),
       (386286, '2020-02-12', 62.55, 'USD', 10, NULL, 21.359, '2020-01-09');
Solution

  • How to implement this range query?

    The key is to filter the data twice in each LEFT JOIN.

      1. Filter the part whose ask time precede rate time.
      1. Filter the nearest wanted time by max.
    SELECT * FROM orders o LEFT JOIN currency_rates c
ON c.currency_id = o.currency_id AND c.valid_from = (
  SELECT max(valid_from) FROM currency_rates
  WHERE valid_from <= o.received_at
);

/* My result:
   id   | received_at | shipping_cost | currency_id | invoice_address_id | delivery_address_id | currency_id |  rate  | valid_from 
--------+-------------+---------------+-------------+--------------------+---------------------+-------------+--------+------------
 385902 | 2020-01-01  |             0 | CZK         |                  1 |                  11 |             |        | 
 386284 | 2020-01-08  |         44.03 | EUR         |                  8 |                     | EUR         |   25.2 | 2020-01-03
 386282 | 2020-01-06  |         11.43 | GBP         |                  6 |                     | GBP         |   28.4 | 2020-01-03
 386278 | 2020-01-02  |         12.83 | USD         |                  2 |                     | USD         | 19.359 | 2019-12-01
 386281 | 2020-01-05  |         12.83 | USD         |                  5 |                  14 | USD         |  20.34 | 2020-01-03
 386279 | 2020-01-03  |         49.36 | USD         |                  3 |                  12 | USD         |  20.34 | 2020-01-03
 386280 | 2020-01-03  |         12.83 | USD         |                  4 |                  13 | USD         |  20.34 | 2020-01-03
 386283 | 2020-01-07  |         12.83 | USD         |                  7 |                  15 | USD         |  20.34 | 2020-01-03
 386285 | 2020-01-11  |         12.83 | USD         |                  9 |                     | USD         | 21.359 | 2020-01-09
 386286 | 2020-02-12  |         62.55 | USD         |                 10 |                     | USD         | 21.359 | 2020-01-09
(10 rows)
*/