PostgreSQL：同product_id下指定条件的价格差值排名优化

表结构与需求 #

表结构 #

现有PostgreSQL表（假设表名为product_prices）：

• id：主键
• product_id + retrived_at：复合唯一键
• category_id：1~10共10种取值
• price：商品价格

数据示例：

需求 #

指定retrived_at和category_id，计算同一product_id的价格差值，对差值排名后取前20条。表总数据3亿行，单retrived_at对应约50万行。

高效实现步骤 #

1. 先建对索引（性能瓶颈核心） #

要避免全表扫描和回表查询，必须创建覆盖索引，让查询直接从索引获取所有需要的数据：

CREATE INDEX idx_cat_retrieved_product_price ON product_prices (category_id, retrived_at, product_id) INCLUDE (price);

• 索引顺序：先按category_id过滤，再按retrived_at过滤，最后定位product_id，完全匹配查询的WHERE条件逻辑
• INCLUDE (price)：把price字段塞进索引里，实现索引-only scan，不用再回表查原数据，速度能提一大截

2. 针对不同场景的查询语句 #

场景1：计算指定日期前的历史差值（当前日期与上一次记录的差值）

如果要找指定category_id和截止retrived_at下，每个商品与最近一次历史记录的价格差：

WITH product_price_history AS (
    SELECT 
        product_id,
        price,
        -- 取同商品同分类的上一条价格
        LAG(price) OVER (PARTITION BY product_id, category_id ORDER BY retrived_at) AS prev_price
    FROM product_prices
    WHERE category_id = 1 -- 替换为你的目标分类ID
      AND retrived_at <= '2023-01-02' -- 替换为你的目标日期
)
SELECT 
    product_id,
    price - prev_price AS price_diff
FROM product_price_history
WHERE prev_price IS NOT NULL -- 排除没有历史价格的商品
ORDER BY ABS(price_diff) DESC -- 按差值绝对值降序，可按需改成升序或直接按差值排序
LIMIT 20;

场景2：计算两个指定日期之间的差值

如果需求是明确对比两个日期的价格差，直接过滤这两个日期的数据，不用扫描全量历史：

WITH target_date_prices AS (
    SELECT 
        product_id,
        price,
        retrived_at
    FROM product_prices
    WHERE category_id = 1 -- 替换为目标分类ID
      AND retrived_at IN ('2023-01-01', '2023-01-02') -- 替换为要对比的两个日期
),
pivoted_prices AS (
    SELECT 
        product_id,
        MAX(CASE WHEN retrived_at = '2023-01-01' THEN price END) AS price_early,
        MAX(CASE WHEN retrived_at = '2023-01-02' THEN price END) AS price_late
    FROM target_date_prices
    GROUP BY product_id
    -- 只保留两个日期都有价格的商品
    HAVING price_early IS NOT NULL AND price_late IS NOT NULL
)
SELECT 
    product_id,
    price_late - price_early AS price_diff
FROM pivoted_prices
ORDER BY ABS(price_diff) DESC
LIMIT 20;

3. 额外优化建议 #

• 分区表：如果数据按日期增长，给表按retrived_at做分区，查询时直接扫描目标分区，性能再上一个台阶
• 清理过期数据：如果不需要太老的历史数据，定期归档或删除，减少扫描范围
• 避免过度计算：窗口函数只取需要的前一条数据（LAG(price,1)），不要扩大窗口范围

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