实现自动更新的Open_Time计算列：DOOR CLOSE触发的时长统计

要实现新增DOOR CLOSE事件行时自动计算最近一次DOOR OPEN与该CLOSE事件的时间差并更新Open_Time列，最直接的方式是使用AFTER INSERT触发器，以下是具体步骤：

1. 先为#Events表新增Open_Time列 #

假设时间差以秒为单位存储，可根据需求调整数据类型（如INT存秒数、TIME存时间间隔等）：

ALTER TABLE #Events 
ADD Open_Time INT; -- 若需存储时间间隔类型，可改用 TIME 或 DATETIME2

2. 创建AFTER INSERT触发器 #

触发器会在每次向表中插入行后触发，仅处理DOOR CLOSE事件，并自动匹配最近的同门DOOR OPEN事件计算时间差：

方案一：关联子查询匹配最近OPEN事件（兼容性强） #

CREATE TRIGGER trg_Events_UpdateOpenTime
ON #Events
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅对插入的DOOR CLOSE事件行更新Open_Time
    UPDATE target
    SET target.Open_Time = DATEDIFF(SECOND, latest_open.EventTime, target.EventTime)
    FROM #Events target
    INNER JOIN inserted ins ON target.EventID = ins.EventID -- 假设EventID是表的主键
    -- 找到当前CLOSE事件前最近的同门DOOR OPEN事件
    INNER JOIN (
        SELECT 
            DoorID,
            EventTime,
            -- 按时间倒序取最近的OPEN事件
            ROW_NUMBER() OVER (PARTITION BY DoorID ORDER BY EventTime DESC) AS rn
        FROM #Events
        WHERE EventType = 'DOOR OPEN'
          AND EventTime < ins.EventTime
    ) latest_open ON latest_open.DoorID = ins.DoorID AND latest_open.rn = 1
    WHERE ins.EventType = 'DOOR CLOSE';
END;

方案二：使用LAG窗口函数（代码更简洁） #

如果你的SQL版本支持窗口函数（如SQL Server 2012+），可以用LAG直接获取前一个事件的时间，前提是最近的前一个事件确实是DOOR OPEN：

CREATE TRIGGER trg_Events_UpdateOpenTime
ON #Events
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE target
    SET target.Open_Time = DATEDIFF(SECOND, 
        LAG(target.EventTime) OVER (PARTITION BY target.DoorID ORDER BY target.EventTime),
        target.EventTime)
    FROM #Events target
    INNER JOIN inserted ins ON target.EventID = ins.EventID
    WHERE ins.EventType = 'DOOR CLOSE'
      -- 确保前一个事件是DOOR OPEN，避免中间有其他事件导致计算错误
      AND LAG(target.EventType) OVER (PARTITION BY target.DoorID ORDER BY target.EventTime) = 'DOOR OPEN';
END;

关键注意事项 #

• 主键依赖：示例中假设表有EventID作为主键来关联插入的行，若你的表用其他主键（如复合主键），需调整JOIN条件。
• 门标识字段：示例中用DoorID区分不同的门，若你的表用其他字段（如DeviceID），需替换PARTITION BY后的字段。
• 时间差单位：DATEDIFF的第一个参数可按需修改（如MINUTE、HOUR），对应调整Open_Time的数据类型。
• 临时表限制：#Events是临时表的话，触发器仅在当前会话有效，会话结束后表和触发器都会被销毁；如果是永久表，触发器会持续生效。
• 异常处理：若某DOOR CLOSE事件找不到对应的DOOR OPEN事件，Open_Time会被设为NULL，可根据需求添加默认值或额外逻辑处理。

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