使用SQL定位方波特征点的技术实现问询

嘿，这个问题在时序状态监测场景里确实挺常见的，咱们用SQL的窗口函数就能优雅解决，完全不用复杂的数学技巧。核心思路就是定位状态变化的边界点——也就是那些和前一个或后一个值不一样的记录，这就是你要找的拐点。

具体实现思路 #

我们可以用LAG()和LEAD()这两个窗口函数，分别获取每条记录的前一个值和后一个值，然后筛选出满足以下任意条件的记录：

• 当前值和前一个值不同（状态变化的起始点，比如连续0后的第一个1）
• 当前值和后一个值不同（状态变化的结束点，比如连续0的最后一个0）
• 同时要处理首尾两条记录的边界情况：第一条记录只需判断和后一个值是否不同，最后一条记录只需判断和前一个值是否不同

存储过程代码 #

CREATE PROCEDURE GetInflectionPoints
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        timeStampUtc,
        value
    FROM (
        SELECT 
            timeStampUtc,
            value,
            -- 获取当前记录的前一个值
            LAG(value) OVER (ORDER BY timeStampUtc) AS PreviousValue,
            -- 获取当前记录的后一个值
            LEAD(value) OVER (ORDER BY timeStampUtc) AS NextValue
        FROM DataPoints
    ) AS SubQuery
    WHERE 
        -- 中间记录：和前一个值不同（状态开始变化）
        (PreviousValue IS NOT NULL AND value != PreviousValue)
        -- 中间记录：和后一个值不同（状态结束变化）
        OR (NextValue IS NOT NULL AND value != NextValue)
        -- 第一条记录：如果和后一个值不同，也算拐点
        OR (PreviousValue IS NULL AND NextValue IS NOT NULL AND value != NextValue)
        -- 最后一条记录：如果和前一个值不同，也算拐点
        OR (NextValue IS NULL AND PreviousValue IS NOT NULL AND value != PreviousValue)
    ORDER BY timeStampUtc;
END;

效果说明 #

拿你举的例子{0,0,0,0,0,0,0,1,1,1,1,0,0,0}来说，这个存储过程会返回：

• 第7个0（最后一个连续0的终点）
• 第8个1（第一个连续1的起点）
• 第11个1（最后一个连续1的终点）
• 第12个0（第一个连续0的起点）

完全符合你要的“拐点”定义。

优化小贴士 #

• 如果你的DataPoints表数据量很大，记得给timeStampUtc字段建索引——窗口函数需要按这个字段排序，索引能大幅提升查询性能
• 要是你只需要“状态切换的触发点”（比如只保留从0变1的那个1，或者从1变0的那个0），可以把WHERE条件简化成(PreviousValue IS NOT NULL AND value != PreviousValue)，这样就只返回每个状态变化的起始点

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