PostgreSQL如何存储产品状态变更记录及状态变更后天数？

优先推荐：动态计算（无需存储days字段） #

days属于衍生数据，物理存储需要额外维护更新逻辑，容易出现数据不一致问题，直接通过视图+窗口函数计算是最稳定的方案，完全匹配你的需求。
假设你的原表名为product_status，字段为product_id、change_date（即你提到的Date字段，建议避免用SQL关键字作为字段名）、status，创建视图的SQL如下：

CREATE OR REPLACE VIEW product_status_with_days AS
SELECT
    product_id,
    change_date,
    status,
    CASE
        -- delivered状态直接返回null
        WHEN status = 'delivered' THEN NULL
        -- 历史已结束状态：取下一条状态的变更日期减当前状态变更日期
        WHEN next_change_date IS NOT NULL THEN (next_change_date - change_date)
        -- 当前生效状态：取当前日期减变更日期
        ELSE (CURRENT_DATE - change_date)
    END AS days
FROM (
    SELECT
        product_id,
        change_date,
        status,
        -- 窗口函数取同产品下一条状态的变更日期
        LEAD(change_date) OVER (PARTITION BY product_id ORDER BY change_date) AS next_change_date
    FROM product_status
) t;

该方案无需任何定时任务或触发器，查询时实时计算结果，数据永远和实际一致。

方案二：物理存储days字段的实现 #

如果业务场景要求必须物理存储该字段，按以下步骤实现：

步骤1：新增days字段 #

ALTER TABLE product_status ADD COLUMN days INT;

步骤2：初始化历史数据的days值 #

WITH status_next_date AS (
    SELECT
        product_id,
        change_date,
        LEAD(change_date) OVER (PARTITION BY product_id ORDER BY change_date) AS next_change
    FROM product_status
)
UPDATE product_status ps
SET days = CASE
    WHEN ps.status = 'delivered' THEN NULL
    WHEN sn.next_change IS NOT NULL THEN sn.next_change - ps.change_date
    ELSE CURRENT_DATE - ps.change_date
END
FROM status_next_date sn
WHERE ps.product_id = sn.product_id AND ps.change_date = sn.change_date;

步骤3：新增状态时自动固定上一状态的days值 #

通过触发器实现，插入新状态前自动更新同产品上一条生效状态的最终持续天数，停止后续更新：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION fix_last_status_days()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE product_status
    SET days = NEW.change_date - change_date
    WHERE product_id = NEW.product_id
      AND change_date = (SELECT MAX(change_date) FROM product_status WHERE product_id = NEW.product_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定插入触发器
CREATE TRIGGER trigger_before_insert_status
BEFORE INSERT ON product_status
FOR EACH ROW
EXECUTE FUNCTION fix_last_status_days();

步骤4：每日自动更新当前生效状态的days值 #

使用PostgreSQL官方定时任务扩展pg_cron实现每日更新：

-- 安装扩展（需要超级用户权限）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 每天0点更新所有未结束、非delivered的当前状态的days
SELECT cron.schedule(
    'daily_update_active_status_days',
    '0 0 * * *',
    $$
    UPDATE product_status
    SET days = CURRENT_DATE - change_date
    WHERE status != 'delivered'
      AND (product_id, change_date) IN (
          SELECT product_id, MAX(change_date) 
          FROM product_status 
          GROUP BY product_id
      );
    $$
);

更优的流转追踪优化建议 #

如果后续需要频繁做状态流转分析，建议在表中新增两个字段：

• flow_id：自增主键，唯一标识每一次状态变更
• is_current：布尔类型，标记该条记录是否为产品的当前生效状态
  配合触发器在插入新状态时自动将旧的is_current设为false，新记录设为true，后续查询当前状态无需计算最大日期，性能提升明显。

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