Исторические изменения SQL
Я хочу создать приложение, которое отслеживает изменение цен на определенные продукты Amazon с течением времени. У меня получились следующие таблицы:
product
| ID | Name |
| 1 | iPhone case |
| 2 | Wallet |
product_price
| ID | product | price | date |
| 1 | 1 | 12.99 | 2023-03-04 |
| 1 | 2 | 10.99 | 2023-03-02 |
| 1 | 1 | 9.99 | 2023-03-01 |
Но как мне теперь написать SQL-запрос, который извлекает все мои продукты и их последние цены? Возможно ли это вообще, учитывая эту структуру таблицы?
Еще одно решение, которое я мог бы придумать, — добавить еще один столбец в мою таблицу product:
| ID | name | latest_price |
| 1 | iPhone case | 1 ]
| 2 | Wallet | NULL |
Таким образом, я могу легко написать запрос, чтобы получить продукты и их последнюю цену. Недостатком является то, что всякий раз, когда цена меняется, мне также нужно обновлять этот столбец.
Существуют ли какие-либо передовые методы для этого? Учитывая, что это не похоже на новую проблему.
Ответы 2
SELECT X.PRODUCT,X.PRICE,X.DATE
FROM
(
SELECT P.PRODUCT,P.PRICE,P.DATE,
ROW_NUMBER()OVER(PARTITION BY P.PRODUCT ORDER BY P.DATE DESC,P.ID DESC)AS XCOL
FROM product_price AS P
) AS X WHERE X.XCOL=1
Чтобы получить последнюю цену, вы можете использовать запрос, как указано выше.
Использование distinct on () довольно эффективно в Postgres:
select distinct on (product) pp.*
from product_price pp
order by product, date desc;
Это можно использовать в соединении:
select p.*, pp.price as latest_price
from product p
join (
select distinct on (product) *
from product_price pp
order by product, date desc
) pp on pp.product = p.id
Другой вариант — использовать боковое соединение, которое иногда работает быстрее:
select p.*, pp.price as latest_price
from product p
join lateral (
select *
from product_price pp
where pp.product = p.id
order by pp.date desc
limit 1
) pp on true
В любом случае индекс на product_price (product, date desc) ускорит эти запросы.
Другой способ смоделировать это — использовать диапазон дат для определения времени, когда цена действительна. Это немного сложнее обновить, так как «последний» диапазон должен быть закрыт перед добавлением нового. Но это немного более гибко для запроса (и может быть даже быстрее):
create table product_price
(
product int not null references product,
price numeric,
valid_during daterange,
constraint unique_price_range
exclude using gist(product with =, valid_during with &&)
);
Ограничение исключения предотвратит перекрытие диапазонов для одного продукта.
Затем вы можете запросить «последнюю цену» (= цена «сегодня»), используя:
select *
from product_price
where valid_during @> current_date;
Но это также упрощает поиск цены на любую нужную вам дату (например, дату заказа товара):
select *
from product_price
where valid_during @> date '2023-03-18';
Выполнение этого запроса обходится намного дороже, если вы сохраняете только дату начала.
Второй вариант определенно НЕ является хорошей практикой. С первым все в порядке, и да, можно получить все продукты и их последнюю цену. Вы пытались написать этот запрос?