亚马逊商品价格追踪应用的SQL表设计与最新价格查询最佳实践咨询

一、现有表结构下的最新价格查询（需求完全可行） #

基于你现有的product和product_price表结构，完全可以通过SQL查询获取所有商品的最新价格，以下是两种常用实现方式：

方式1：使用窗口函数（推荐） #

窗口函数ROW_NUMBER()可以按商品分组后，按日期倒序排序，取每组的第一条数据（即最新价格）：

SELECT p.id, p.name, pp.price AS latest_price, pp.date AS latest_price_date
FROM product p
LEFT JOIN (
    SELECT 
        product, 
        price, 
        date,
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY date DESC) AS rn
    FROM product_price
) pp ON p.id = pp.product AND pp.rn = 1;

这种方式逻辑清晰，兼容性好（支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库），且不需要额外维护数据。

方式2：使用关联子查询 #

通过子查询获取每个商品的最新价格日期，再关联查询对应价格：

SELECT p.id, p.name, pp.price AS latest_price, pp.date AS latest_price_date
FROM product p
LEFT JOIN product_price pp 
    ON p.id = pp.product
    AND pp.date = (
        SELECT MAX(date) 
        FROM product_price 
        WHERE product = p.id
    );

这种方式适合不支持窗口函数的旧版本数据库，但性能略逊于窗口函数（尤其是数据量大时）。

二、新增latest_price列方案的利弊分析 #

你提出的在product表新增latest_price列的方案：

• 优点：查询最新价格时无需关联或子查询，直接查询product表即可，性能最优
• 缺点：必须在每次新增价格记录时同步更新该列，极易出现数据不一致（比如插入价格后忘记更新、并发场景下的更新冲突），增加了业务逻辑的复杂度和维护成本

三、行业最佳实践 #

这类时间序列型的历史数据追踪场景，行业内的通用方案主要有以下几种：

• 优先使用窗口函数查询：在数据量不是特别庞大（百万级以内）的情况下，窗口函数的性能完全够用，且能保证数据的一致性，无需额外维护
• 用触发器自动维护最新价格：如果需要极致的查询性能，可以给product_price表添加插入/更新触发器，自动同步更新product表的latest_price列，避免手动维护的失误
• 使用物化视图：对于只读的查询场景，可以创建物化视图定时刷新（比如每天/每小时），预计算出所有商品的最新价格，查询时直接读取物化视图，平衡查询性能和数据一致性
• 历史数据归档：当价格历史数据量过大时，可以将超过一定时间的历史价格归档到单独的表中，只保留近期数据在主表，提升查询效率

内容的提问来源于stack exchange，提问作者Mike