SQL Server检测特定ID的EventCode从N变更为其他值的实现方法

适配环境 #

• SQL Server 2016
• SQL Server 2019

场景1：单时间序列Events表查询 #

表结构说明 #

字段：Date（日期）、Time（时间）、EventCode（事件编码）

查询规则 #

支持自定义起止日期过滤，区间内无EventCode = 'N'的记录时无返回，存在时返回N所在行及紧随其后的第一条记录。

查询代码 #

-- 定义查询起止日期参数
DECLARE @StartDate DATE = '2020-01-02', @EndDate DATE = '2020-01-03';

WITH OrderedEvents AS (
    -- 合并日期时间字段并按时间排序
    SELECT 
        *,
        CONVERT(DATETIME, Date + ' ' + Time) AS FullDateTime,
        ROW_NUMBER() OVER (ORDER BY CONVERT(DATETIME, Date + ' ' + Time)) AS rn
    FROM Events
    WHERE Date BETWEEN @StartDate AND @EndDate
),
NPosition AS (
    -- 找到区间内第一个N的行号
    SELECT MIN(rn) AS N_rn FROM OrderedEvents WHERE EventCode = 'N'
)
-- 返回N行及紧随其后的一行
SELECT oe.Date, oe.Time, oe.EventCode
FROM OrderedEvents oe
CROSS JOIN NPosition np
WHERE np.N_rn IS NOT NULL AND oe.rn IN (np.N_rn, np.N_rn + 1)
ORDER BY oe.rn;

场景2：按ID分组的多序列事件表查询 #

表结构说明 #

字段：ID（主体标识）、EventType（事件类型）、EventDateTime（事件时间）

查询规则 #

查询指定ID的事件序列中，EventType从N变为其他值的变更记录，返回变更相关的ID、EventType、EventDateTime字段。

查询代码 #

-- 定义要查询的目标ID参数
DECLARE @TargetID INT = 103917;

WITH IDOrderedEvents AS (
    -- 按ID分组后按事件时间排序，标记相邻行的事件类型
    SELECT 
        *,
        LAG(EventType) OVER (PARTITION BY ID ORDER BY EventDateTime) AS PrevEventType,
        LEAD(EventType) OVER (PARTITION BY ID ORDER BY EventDateTime) AS NextEventType
    FROM EventTable -- 此处替换为实际表名
    WHERE ID = @TargetID
)
-- 筛选N序列的最后一行和变更后的第一行
SELECT ID, EventType, EventDateTime
FROM IDOrderedEvents
WHERE 
    (EventType = 'N' AND NextEventType <> 'N')
    OR (EventType <> 'N' AND PrevEventType = 'N')
ORDER BY EventDateTime;

结果示例 #

查询ID=103921时返回结果：

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