Find the period of over speed?

Just something interesting come in my mind. Assume that we have a table (in SQL Server) like this:

• Location
• Velocity
• Time

for example:

Location     Velocity   Time
1            40         1:20
2            35         2:00
3            45         2:05
4            50         2:30
5            60         2:45
6            48         2:55
7            40         3:00
8            35         3:15
9            50         3:20
10           70         3:30
11           50         3:35
12           40         3:40

Assume that speed barrier is 40kph, the output is something like this

Starttime         Endtime
2:05              3:00
3:20              3:35 

What is the best way to determine over speed perio开发者_如何转开发ds (speed barrier is defined) ? My first idea was loading the table into an array, and then iterate over array to find these periods:

(Pseudo C# code)

bool isOverSpeed = false;

for (int i =0;i<arr.Length;i++)
{
if (!isOverSpeed)
    if (arr[i].Velocity > speedBarrier)
        {
            #insert the first record into another array.
            isOverSpeed = true;
        }
if(isOverSpeed)

    if (arr[i].Velocity < speedBarrier)
          {
          #insert the record into that array
          isOverSpeed = false;
          }

}

It works, but somewhat "not very effectively". Is there a "smarter" way, such as a T-SQL query or another algorithm to do this?

You can achieve this by using CTE (Common Table Expressions).

The query below works against the Adventure Works demo table of SQL Server (the "speed limit" being 7).

This is strongly inspired by another question on SO: GROUP BY for continuous rows in SQL.

with CTE as (
    select
        ROW_NUMBER() over(order by SalesTaxRateID) as RowNo
        , *
    from
        Sales.SalesTaxRate
)
, MyLogGroup as (
    select
        l.*
        ,(select
              max(SalesTaxRateID)
          from
              CTE c
          where
              not exists (select * from CTE
                              where RowNo = c.RowNo-1
                              and TaxRate > 7
                              and c.TaxRate > 7)
              and c.SalesTaxRateID <= l.SalesTaxRateID) as GroupID
    from
        Sales.SalesTaxRate l)
select
    min(SalesTaxRateID) as minimum
    , max(SalesTaxRateID) as maximum
    , avg(TaxRate)
from
    MyLogGroup
group by
    GroupID
having
    min(TaxRate) > 7
order by
    minimum

Something along these lines should suit you:

with CTE as (
    select
        ROW_NUMBER() over(order by [Time]) as RowNo
        , *
    from
        <table_name>
)
, MySpeedGroup as (
    select
        s.*
        ,(select
              max([Time])
          from
              CTE c
          where
              not exists (select * from CTE
                              where RowNo = c.RowNo-1
                              and Velocity > <speed_limit>
                              and c.Velocity > <speed_limit>)
              and c.[Time] <= s.[Time]) as GroupID
    from
        <table_name> l)
select
    min([Time]) as minimum
    , max([Time]) as maximum
    , avg([Velocity]) -- don't know if you want this
from
    MySpeedGroup
group by
    GroupID
having
    min(Velocity) > <speed_limit>
order by
    minimum

It can't be that simple, or can it?

SELECT
  Location,
  Velocity,
  Time,
  CASE WHEN Velocity > @SpeedBarrier THEN 1 ELSE 0 END AS IsOverSpeed
FROM
  SpeedTable

I've used the following part to get some data ( I'm on compatibility mode 80 atm so I don't have a time field and am using an INT for the timestamp)

DECLARE @Info TABLE (Location INT IDENTITY, Velocity INT, [Time] INT);
INSERT INTO @Info (Velocity, [Time]) VALUES (40, 80);
INSERT INTO @Info (Velocity, [Time]) VALUES (35, 120);
INSERT INTO @Info (Velocity, [Time]) VALUES (45, 125);
INSERT INTO @Info (Velocity, [Time]) VALUES (50, 150);
INSERT INTO @Info (Velocity, [Time]) VALUES (60, 165);
INSERT INTO @Info (Velocity, [Time]) VALUES (48, 175);
INSERT INTO @Info (Velocity, [Time]) VALUES (40, 180);
INSERT INTO @Info (Velocity, [Time]) VALUES (35, 195);
INSERT INTO @Info (Velocity, [Time]) VALUES (50, 200);
INSERT INTO @Info (Velocity, [Time]) VALUES (70, 210);
INSERT INTO @Info (Velocity, [Time]) VALUES (50, 215);
INSERT INTO @Info (Velocity, [Time]) VALUES (40, 220);
INSERT INTO @Info (Velocity, [Time]) VALUES (45, 225);
INSERT INTO @Info (Velocity, [Time]) VALUES (45, 230);

Assuming your Location are fixed points that has to be passed in order to complete the following will produce the desired output. I've broken it out into multiple Stages so as to make it clear what each part does.

DECLARE @Limit INT;
SET @Limit = 40;

WITH Stage1 ([Location], [Velocity], [Time]) AS (
    SELECT * FROM @Info WHERE [Velocity] > @Limit
), Stage2 (Start) AS (
    SELECT [Time]
      FROM [Stage1]
     WHERE ([Location] - 1) NOT IN (SELECT [Location] FROM [Stage1])
), Stage3 ([Start], [Stop]) AS (
    SELECT [Start]
         , (SELECT MIN([Time]) FROM [Stage1] WHERE ([Location] + 1) NOT IN (SELECT [Location] FROM [Stage1]) AND [Time] > [Stage2].[Start])
      FROM Stage2
)
SELECT *
  FROM Stage3