postgreSQL的历史变化

Question

我希望构建一个应用程序，跟踪某些亚马逊产品随时间的价格变化。我得出了以下表格:

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         |

这样我就可以很容易地编写查询来获取产品及其最新价格。缺点是每当价格变化时，我也需要更新这个专栏。

有什么最好的做法吗?考虑到这似乎不是一个新问题。

Answer 1

在Postgres中使用distinct on ()是非常有效的:

select distinct on (product) pp.*
from product_price pp
order by product, date desc;

可以在join中使用:

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';

如果您只存储开始日期，那么该查询的运行成本要高得多。

Answer 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

要获得最新的价格，您可以使用上面的查询