SQL实现插入新行时存储用户上一笔交易日期的技术咨询

以下给出两种常用实现方案，可根据业务场景选择：

方案1：查询时动态计算（推荐使用） #

无需修改表结构，无数据冗余，也不会出现数据一致性问题，直接通过窗口函数即可得到你需要的展示效果，SQL示例如下：

SELECT 
    customer_id AS "Customer ID",
    MIN(txn_date) OVER (PARTITION BY customer_id) AS "first txn date",
    txn_date AS "txn date",
    MAX(txn_date) OVER (PARTITION BY customer_id) AS "last txn date",
    LAG(txn_date) OVER (PARTITION BY customer_id ORDER BY txn_date ASC) AS "prev txn dt"
FROM txn_table
ORDER BY customer_id, txn_date;

方案2：插入时自动存储到表字段 #

如果业务要求必须把该字段值固化存储在交易表中，可以通过新增字段+触发器的方式实现，操作步骤如下：

• 第一步给交易表新增存储上一笔交易日期的字段

ALTER TABLE txn_table ADD COLUMN prev_txn_dt DATE;

• 第二步创建插入前触发器，插入新记录时自动填充值
  以下是MySQL版本触发器示例：

DELIMITER //
CREATE TRIGGER fill_prev_txn_dt BEFORE INSERT ON txn_table
FOR EACH ROW
BEGIN
    SET NEW.prev_txn_dt = (
        SELECT MAX(txn_date) FROM txn_table WHERE customer_id = NEW.customer_id
    );
END //
DELIMITER ;

以下是PostgreSQL版本触发器示例：

CREATE OR REPLACE FUNCTION fill_prev_txn_dt_func()
RETURNS TRIGGER AS $$
BEGIN
    NEW.prev_txn_dt := (SELECT MAX(txn_date) FROM txn_table WHERE customer_id = NEW.customer_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fill_prev_txn_dt BEFORE INSERT ON txn_table
FOR EACH ROW EXECUTE FUNCTION fill_prev_txn_dt_func();

注意事项：如果后续存在交易记录删除、更新的场景，固化存储的prev_txn_dt会出现数据不一致，需要额外编写对应触发器维护该字段值，非必要场景优先选择方案1。

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