MySQL商品价格有效期计算：生成含生效/失效日期的价格表

我有一张存储商品、价格及添加日期的表，表结构及数据如下：

CREATE TABLE item_prices (
    item_id         INT,
    item_name       VARCHAR(30),
    item_price      DECIMAL(12, 2),
    created_dttm    DATETIME
);

INSERT INTO item_prices(item_id, item_name, item_price, created_dttm) VALUES
(1, 'spoon', 10.20 , '2023-01-01 01:00:00'),
(1, 'spoon', 10.20 , '2023-01-08 01:35:00'),
(1, 'spoon', 10.35 , '2023-01-14 15:00:00'),
(2, 'table', 40.00 , '2023-01-01 01:00:00'),
(2, 'table', 40.00 , '2023-01-03 11:22:00'),
(2, 'table', 41.00 , '2023-01-10 08:28:22'),
(1, 'spoon', 10.35 , '2023-01-28 21:52:00'),
(1, 'spoon', 11.00 , '2023-02-15 16:36:00'),
(2, 'table', 41.00 , '2023-02-16 21:42:11'),
(2, 'table', 45.20 , '2023-02-19 20:25:25'),
(1, 'spoon',  9.00 , '2023-03-02 14:50:00'),
(1, 'spoon',  9.00 , '2023-03-06 16:36:00'),
(1, 'spoon',  8.50 , '2023-03-15 12:00:00'),
(2, 'table', 30    , '2023-03-05 10:10:10'),
(2, 'table', 30    , '2023-03-10 15:45:00');

需求 #

需要创建一张新表，包含以下字段：

• item_id
• item_name
• item_price
• valid_from_dt：价格生效日期（对应记录的created_dttm）
• valid_to_dt：价格失效日期（同商品下一条记录的created_dttm减1天）

我的尝试 #

我尝试通过以下查询获取首次出现新价格的日期：

SELECT item_id, item_name, item_price, 
       MIN(created_dttm) as dt 
FROM item_prices
GROUP BY item_price, item_id, item_name

该查询能返回各价格首次出现的日期，但还需要进一步处理得到完整的有效期。

预期输出示例 #

完整SQL解决方案 #

1. 查询获取有效期数据 #

使用CTE和窗口函数实现需求：

WITH price_first_dates AS (
    SELECT 
        item_id,
        item_name,
        item_price,
        MIN(created_dttm) AS valid_from_dt
    FROM item_prices
    GROUP BY item_id, item_name, item_price
),
ordered_prices AS (
    SELECT 
        *,
        -- 获取同商品下一条价格的生效日期
        LEAD(valid_from_dt) OVER (PARTITION BY item_id, item_name ORDER BY valid_from_dt) AS next_valid_from
    FROM price_first_dates
)
SELECT 
    item_id,
    item_name,
    item_price,
    DATE(valid_from_dt) AS valid_from_dt, -- 提取日期部分，匹配预期格式
    CASE 
        WHEN next_valid_from IS NOT NULL THEN DATE(DATE_SUB(next_valid_from, INTERVAL 1 DAY))
        ELSE '至今'
    END AS valid_to_dt
FROM ordered_prices
ORDER BY item_id, valid_from_dt;

2. 创建新表 #

如果需要直接生成目标表，使用CREATE TABLE ... AS SELECT：

CREATE TABLE item_price_validity AS
WITH price_first_dates AS (
    SELECT 
        item_id,
        item_name,
        item_price,
        MIN(created_dttm) AS valid_from_dt
    FROM item_prices
    GROUP BY item_id, item_name, item_price
),
ordered_prices AS (
    SELECT 
        *,
        LEAD(valid_from_dt) OVER (PARTITION BY item_id, item_name ORDER BY valid_from_dt) AS next_valid_from
    FROM price_first_dates
)
SELECT 
    item_id,
    item_name,
    item_price,
    DATE(valid_from_dt) AS valid_from_dt,
    CASE 
        WHEN next_valid_from IS NOT NULL THEN DATE(DATE_SUB(next_valid_from, INTERVAL 1 DAY))
        ELSE '至今'
    END AS valid_to_dt
FROM ordered_prices
ORDER BY item_id, valid_from_dt;

逻辑说明 #

• price_first_dates：分组获取每个商品每个价格的首次生效日期，延续原有的查询思路。
• ordered_prices：通过LEAD()窗口函数，按商品分组、生效日期排序，获取下一个价格的生效时间。
• 最终查询：将生效日期转为纯日期格式，计算失效日期（下一个生效日减1天），最后一条记录显示“至今”。

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