SQL Server 2008下PLC时间序列报警事件转启停对方案问询

原有逻辑问题说明 #

原有方案对开启、关闭事件分别编号配对的逻辑仅在事件完全无缺失的场景下生效，一旦出现事件丢失，两类事件的行号就会错位，直接导致关闭时间早于开启时间、配对错误的问题。

适配SQL Server 2008的解决方案 #

该方案使用OUTER APPLY特性（SQL Server 2005及以上版本支持），为每个开启事件匹配后续最近的有效关闭事件，从逻辑上避免配对错误：

SELECT 
    tOn.tagname,
    tOn.datetime AS StartTime,
    tOff.EndTime
FROM 
    (
        SELECT tagname, datetime
        FROM [brakerh].[dbo].[onchange]
        WHERE value = 1 
          AND [delete] = 0 -- 过滤已删除的无效数据
          AND quality = 192 -- 过滤质量不合格的数据
    ) tOn
OUTER APPLY
    (
        SELECT TOP 1 datetime AS EndTime
        FROM [brakerh].[dbo].[onchange]
        WHERE tagname = tOn.tagname
          AND value = 0
          AND [delete] = 0
          AND quality = 192
          AND datetime > tOn.datetime -- 强制关闭时间晚于开启时间，彻底解决时间倒错问题
        ORDER BY datetime ASC -- 取当前开启事件之后最近的关闭事件
    ) tOff
-- 若不需要展示还未结束的事件，可取消注释下面的过滤条件
-- WHERE tOff.EndTime IS NOT NULL
ORDER BY tOn.tagname, tOn.datetime

可选扩展：补充无对应开启事件的关闭记录 #

如果需要将没有匹配到开启事件的单独关闭事件也纳入输出（即StartTime为空的行），可在上述查询后追加UNION ALL逻辑：

UNION ALL
SELECT 
    tOff.tagname,
    NULL AS StartTime,
    tOff.datetime AS EndTime
FROM 
    (
        SELECT tagname, datetime
        FROM [brakerh].[dbo].[onchange]
        WHERE value = 0 
          AND [delete] = 0
          AND quality = 192
    ) tOff
WHERE NOT EXISTS
    (
        SELECT 1
        FROM [brakerh].[dbo].[onchange] tOn
        WHERE tOn.tagname = tOff.tagname
          AND tOn.value = 1
          AND tOn.[delete] = 0
          AND tOn.quality = 192
          AND tOn.datetime < tOff.datetime
          AND NOT EXISTS
              (
                  SELECT 1
                  FROM [brakerh].[dbo].[onchange] tOff2
                  WHERE tOff2.tagname = tOn.tagname
                    AND tOff2.value = 0
                    AND tOff2.[delete] = 0
                    AND tOff2.quality = 192
                    AND tOff2.datetime > tOn.datetime
                    AND tOff2.datetime < tOff.datetime
              )
    )

性能优化建议 #

针对高频写入的业务表，可创建联合覆盖索引大幅提升查询效率：

CREATE NONCLUSTERED INDEX IX_OnChange_TagValueTime ON [brakerh].[dbo].[onchange]
(
    tagname ASC,
    value ASC,
    datetime ASC
)
INCLUDE ([delete], quality)

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