Сделать JOIN с подстановкой данных по времени

Question

Есть две таблицы orders и prices

Orders

CREATE TABLE orders (
    count        integer                  not null,
    created_at   timestamp with time zone not null,
    product_id   integer                  not null
);

Prices

CREATE TABLE prices (
    retail     integer                  not null,
    discount   integer                  not null,
    created_at timestamp with time zone not null,
    product_id integer                  not null
);

Необходимо сделать выборку количества заказов по определенному товару product_id и в определенный период времени, но еще сделать JOIN с таблицей цен prices так, чтобы цены соответствовали по времени этих заказов.

Пример данных:

orders

count	created_at	product_id
1	2023-01-10 10:00:00	100
3	2023-01-10 12:00:00	100
2	2023-01-10 14:00:00	100
4	2023-01-10 16:00:00	100
1	2023-01-10 18:00:00	100

prices

retail	discount	created_at	product_id
1050	5	2023-01-10 8:00:00	100
990	10	2023-01-10 11:00:00	100
1100	0	2023-01-10 13:00:00	100
1200	15	2023-01-10 14:35:00	100
990	3	2023-01-10 17:40:00	100

В результате хочу получить список заказов с ценой:

count	retail	discount	created_at	product_id
1	1050	5	2023-01-10 10:00:00	100
3	990	10	2023-01-10 12:00:00	100
2	1100	0	2023-01-10 14:00:00	100
4	1200	15	2023-01-10 16:00:00	100
1	990	3	2023-01-10 18:00:00	100

Пытаюсь подобрать правильное условие для JOIN, но пока неудачно. Наверное JOIN тут и не нужен. Надеюсь на вашу помощь.

Answer 1

Чисто из описания:

SELECT *
FROM orders
JOIN LATERAL ( 
    SELECT *
    FROM prices
    WHERE orders.product_id = prices.product_id
      AND orders.created_at >= prices.created_at
    ORDER BY prices.created_at DESC LIMIT 1 ) price ON 1=1

Ну а с умножениями и прочей арифметикой сам разберёшься...

БЛОГ НА HUSL

Сделать JOIN с подстановкой данных по времени

Orders

Prices

Ответы (1 шт):