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

    We got a question from Ingrid today, that is one table has cpu using status information, how to find out a serial continuous three records which the cpu using rate more than 90% for one server, it means for one server, the 3 continuous records all have a more than 90% CPU using.(一张表里记录了cpu使用情况，如何找出一台server连续3条记录的CPU使用率都大于90%)

    Many guys joined the discussing, and several methods were given, I list all below.

 

    Environment: SQL Server 2005

 

    Step1. create table.

        create table CPU_Efficiency
        (
           Servername varchar(10)
          ,RecordTime datetime
          ,CPU_Used float
        )

    Step2. Initial data

        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);
    Step3. Solutions

 

    Solution 1.

 

        SELECT *
        FROM (
            SELECT A.* , COUNT(servername) OVER(PARTITION BY servername) V1
            FROM( SELECT T.*,ROW_NUMBER() OVER(PARTITION BY servername ORDER BY Recordtime) as RowNumber
                  FROM CPU_Efficiency T
                ) A
            WHERE CPU_used>=0.9
        ) B
        WHERE V1>=3;

 

    Solution 2.

 

        SELECT servername
        FROM(
            SELECT *,new_number=(rownumber-(ROW_NUMBER() OVER(ORDER BY servername,Recordtime))) 
            FROM  ( SELECT *,rownumber = ROW_NUMBER() OVER(ORDER BY servername,Recordtime)
                    FROM CPU_Efficiency) A
            WHERE cpu_used>=0.9) B
        GROUP BY servername,new_number
        HAVING COUNT(*) >=3

 

    Solution 3.

 

        SELECT *
        FROM CPU_Efficiency a
        WHERE a.CPU_used >= 0.9
        AND EXISTS ( -- FIND THE PRE-RECORD
          SELECT *
          FROM ( SELECT TOP 1 * FROM CPU_Efficiency b WHERE a.servername = b.servername AND b.RecordTime < a.RecordTime ORDER BY RecordTime DESC) c
          WHERE c.CPU_used >= 0.9
        )
        AND EXISTS ( -- FIND THE POST-RECORD
          SELECT *
          FROM ( SELECT TOP 1 * FROM CPU_Efficiency d WHERE a.servername = d.servername AND d.RecordTime > a.RecordTime ORDER BY RecordTime ) e
          WHERE e.CPU_used >= 0.9
        )

    Solution 4.

 

        SELECT b.*, a.*, c.*
        FROM CPU_Efficiency a
        CROSS APPLY ( -- find the pre-record
          SELECT TOP 1 * FROM CPU_Efficiency b WHERE a.servername = b.servername AND b.RecordTime < a.RecordTime ORDER BY RecordTime Desc
        ) b
        CROSS APPLY ( -- find the post-record
          SELECT TOP 1 * FROM CPU_Efficiency d where a.servername = d.servername AND d.RecordTime > a.RecordTime ORDER BY RecordTime
        ) c
        WHERE a.CPU_used >= 0.9 AND b.CPU_used >= 0.9 AND c.CPU_used >= 0.9

 

 

    Summary:

      All these 4 solutions is not the best, if you want more information, u can update one solution to get what u want.

 

      Solution 1, the data can be returned but not the wanted. So it's a wrong solution.

      Solution 2, it can return the correct server name, but no time and cpu using rate information.

      Solution 3, it can return the mid record of the series 3 records. if u want more information, additional sql must need.

      Solution 4, it can return all information for the 3 records, but all 3 records in the same line. It can be used for special requirement.