如何找出相邻3条记录都满足同一条件(How to find out 3 continuous records all reach the same condition)

本文探讨了如何使用SQL Server 2005查询一台服务器连续三条记录中CPU使用率均超过90%的方法。提供了四种解决方案并对其优缺点进行了总结。

在SQL Server中，要想在table里找出连续3条符合同一条件的记录，可以使用LAG和LEAD函数，它们分别查看当前记录的前续和后续记录的值，我们以下面示例演示如何找出一台server连续3条记录的CPU使用率都大于90%)

Step1. 创建表并添加数据

create table CPU_Efficiency
(

ID int identity(1,1) Primary key,
           Servername varchar(10)
        ,RecordTime datetime
          ,CPU_Used float
        )

        INSERT INTO CPU_Efficiency VALUES('A','11:05',0.2);
        INSERT INTO CPU_Efficiency VALUES('A','11:10',0.9);
        INSERT INTO CPU_Efficiency VALUES('A','11:15',0.91);
        INSERT INTO CPU_Efficiency VALUES('A','11:20',0.92);
        INSERT INTO CPU_Efficiency VALUES('A','11:25',0.87);
        INSERT INTO CPU_Efficiency VALUES('B','11:05',0.3);
        INSERT INTO CPU_Efficiency VALUES('B','11:10',0.3);
        INSERT INTO CPU_Efficiency VALUES('B','11:15',0.3);
        INSERT INTO CPU_Efficiency VALUES('B','11:20',0.3);
        INSERT INTO CPU_Efficiency VALUES('B','11:25',0.3);
        INSERT INTO CPU_Efficiency VALUES('C','11:05',0.9);
        INSERT INTO CPU_Efficiency VALUES('C','11:10',0.9);
        INSERT INTO CPU_Efficiency VALUES('C','11:15',0.91);
        INSERT INTO CPU_Efficiency VALUES('C','11:20',0.3);
        INSERT INTO CPU_Efficiency VALUES('C','11:25',0.3);
        INSERT INTO CPU_Efficiency VALUES('D','11:05',0.9);
        INSERT INTO CPU_Efficiency VALUES('D','11:10',0.3);
        INSERT INTO CPU_Efficiency VALUES('D','11:15',0.9);
        INSERT INTO CPU_Efficiency VALUES('D','11:20',0.9);
        INSERT INTO CPU_Efficiency VALUES('D','11:25',0.3);

最终数据如下：

Step2. SQL语句，如下

WITH CTE AS (
    SELECT
        *,
        LAG(CPU_used, 2) OVER (ORDER BY ID) AS Pre_Pre_VALUE,
        LAG(CPU_used, 1) OVER (ORDER BY ID) AS Pre_VALUE,
        LEAD(CPU_used, 1) OVER (ORDER BY ID) AS NEXT_VALUE,
        LEAD(CPU_used, 2) OVER (ORDER BY ID) AS NEXT_NEXT_VALUE

    FROM
        CPU_Efficiency
)
SELECT
    *
FROM CTE
WHERE
    CPU_used >= 0.9 AND
	( (NEXT_VALUE >= 0.9 AND NEXT_NEXT_VALUE >= 0.9) OR --the 1st row
	  (Pre_VALUE >= 0.9 AND NEXT_VALUE >= 0.9) OR       --the 2nd row
	  (Pre_Pre_VALUE >= 0.9 AND Pre_VALUE >= 0.9)       --the 3st row
	);

上面语句中，首先得到当前记录的前后2条记录数据，然后判断当前记录是否处于连续3条符合条件的记录中，最终运行结果如下：