如何计算指定日期区间内各商品的价格差值

表结构 #

CREATE TABLE products 
(
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id integer NOT NULL,
    title text NOT NULL,
    price double precision NOT NULL,
    checked_at timestamp with time zone DEFAULT now()
);

数据示例 #

需求 #

传入日期范围（例如2022-07-19至2022-07-21），获取所有唯一商品的最终价格与初始价格的差值，预期结果如下：

预期结果 #

解决方案 #

以下是几种高效实现的SQL写法，核心思路是为每个商品筛选出日期范围内的最早和最晚记录，再计算价格差：

方法1：用ROW_NUMBER()筛选首尾记录 #

WITH product_prices AS (
    SELECT 
        product_id,
        title,
        price,
        checked_at,
        -- 标记每个商品的最早记录
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY checked_at ASC) AS rn_asc,
        -- 标记每个商品的最晚记录
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY checked_at DESC) AS rn_desc
    FROM products
    WHERE checked_at BETWEEN '2022-07-19'::timestamp AND '2022-07-21 23:59:59'::timestamp
)
SELECT 
    p1.product_id,
    p1.title,
    p2.price - p1.price AS price_difference
FROM product_prices p1
JOIN product_prices p2 
    ON p1.product_id = p2.product_id 
    AND p1.rn_asc = 1 
    AND p2.rn_desc = 1;

方法2：用FIRST_VALUE/LAST_VALUE直接计算 #

SELECT DISTINCT
    product_id,
    title,
    LAST_VALUE(price) OVER (
        PARTITION BY product_id 
        ORDER BY checked_at 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - FIRST_VALUE(price) OVER (
        PARTITION BY product_id 
        ORDER BY checked_at 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS price_difference
FROM products
WHERE checked_at BETWEEN '2022-07-19'::timestamp AND '2022-07-21 23:59:59'::timestamp;

方法3：子查询获取首尾价格 #

SELECT 
    p.product_id,
    p.title,
    (SELECT price FROM products 
     WHERE product_id = p.product_id 
       AND checked_at BETWEEN '2022-07-19'::timestamp AND '2022-07-21 23:59:59'::timestamp
     ORDER BY checked_at DESC LIMIT 1) 
    - 
    (SELECT price FROM products 
     WHERE product_id = p.product_id 
       AND checked_at BETWEEN '2022-07-19'::timestamp AND '2022-07-21 23:59:59'::timestamp
     ORDER BY checked_at ASC LIMIT 1) AS price_difference
FROM (
    SELECT DISTINCT product_id, title 
    FROM products
    WHERE checked_at BETWEEN '2022-07-19'::timestamp AND '2022-07-21 23:59:59'::timestamp
) p;

注意事项 #

• 日期范围边界处理：若传入的是纯日期（不带时间），建议用checked_at <= '结束日期 23:59:59'确保包含当天所有记录
• 性能对比：方法1、2基于窗口函数，适合大数据量场景；方法3逻辑直观，但子查询较多，小数据集使用更合适

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