Find the overload period?

Question

Just something interesting comes to my mind. Suppose we have a table (in SQL Server), for example:

• Location
• speed
• Time

eg:

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

Suppose the speed barrier is 40 km / h, the exit is something like this

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

What is the best way to determine speed periods (speed limit is determined)? My first idea is to load the table into an array and then iterate over the 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;
          }

}

This works, but is somewhat "not very effective." Is there a more reasonable way, for example, a T-SQL query or another algorithm?

Answer 1

, CTE ( ).

Adventure Works SQL Server ( ) 7).

SO: GROUP BY 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

- :

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

Answer 2

?

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

Answer 3

, ( 80 , , INT )

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);

, - , , , . , , .

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

Answer 4

Assuming that if it matches, it will be false by following the SQL query below

select location, speed, time, (speed> speed Limit = true OR speed <= speedLimit = false) as speeding from the table where speedLimit = yourGivenLimit

I have not tested this, but I'm sure something similar will do.