基于关联表最新记录批量插入Event表的PostgreSQL实现问题

我来帮你搞定这个需求！先明确下你的表结构和需求：

表结构 #

你的两张表创建语句如下：

CREATE TABLE PRODUCT ( 
    ID BIGSERIAL, 
    TYPE VARCHAR(24), 
    TITLE VARCHAR(128), 
    PRIMARY KEY (ID) 
); 

CREATE TABLE EVENT ( 
    ID BIGSERIAL, 
    STATE VARCHAR(64) NOT NULL, 
    DATETIME TIMESTAMP NOT NULL, 
    PRODUCT_ID BIGINT, 
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(ID), 
    PRIMARY KEY (ID) 
);

其中EVENT表的STATE字段对应Java枚举，可选值包括INITIALIZED、AVAILABLE、PROCESSED等。

需求 #

对所有产品，若其对应的最新Event记录的STATE为PROCESSED，则在EVENT表中插入一条STATE为AVAILABLE的新记录。

解决方案 #

这里可以用PostgreSQL的窗口函数结合INSERT...SELECT语句来实现，具体SQL如下：

INSERT INTO EVENT (STATE, DATETIME, PRODUCT_ID)
SELECT 
    'AVAILABLE' AS STATE,
    NOW() AS DATETIME, -- 这里用当前时间，你可以根据需求调整
    latest_events.PRODUCT_ID
FROM (
    SELECT 
        PRODUCT_ID,
        STATE,
        ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY DATETIME DESC) AS rn
    FROM EVENT
) AS latest_events
WHERE latest_events.rn = 1 -- 筛选出每个产品的最新事件
  AND latest_events.STATE = 'PROCESSED'; -- 只保留最新事件状态为PROCESSED的产品

代码解释 #

• 子查询latest_events：通过PARTITION BY PRODUCT_ID将事件按产品分组，再用ORDER BY DATETIME DESC对每个组内的事件按时间倒序排序，ROW_NUMBER()会给每个组的事件编号，最新的事件编号为1。
• 外层查询：筛选出编号为1（即最新）且状态为PROCESSED的产品ID，然后插入对应的新事件，状态设为AVAILABLE，时间用当前时间（你可以替换成业务需要的时间值）。

如果你需要确保即使产品没有任何事件也不影响（因为需求是针对“所有产品”中符合条件的，没有事件的产品自然不满足最新事件为PROCESSED的条件），这段语句已经可以覆盖这种情况。

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