Finden Sie den Zeitraum der Geschwindigkeitsüberschreitung?

Question

Mir ist gerade etwas Interessantes in den Sinn gekommen. Angenommen, wir haben eine Tabelle (in SQL Server) wie diese:

• Standort
• Geschwindigkeit
• Zeit

zum Beispiel:

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

Angenommen, die Geschwindigkeitsgrenze liegt bei 40 km/h, dann sieht die Ausgabe etwa so aus

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

Was ist der beste Weg, um die Zeiträume zu bestimmen, in denen die Geschwindigkeit überschritten wird (Geschwindigkeitsgrenze ist definiert)? Meine erste Idee war, die Tabelle in ein Array zu laden und dann über das Array zu iterieren, um diese Zeiträume zu finden:

(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;
          }

}

Es funktioniert, aber irgendwie "nicht sehr effektiv". Gibt es einen "intelligenteren" Weg, z. B. eine T-SQL-Abfrage oder einen anderen Algorithmus, um dies zu tun?

Answer 1

Sie können dies erreichen, indem Sie CTE ( Allgemeine Tabellenausdrücke ).

Die nachstehende Abfrage funktioniert mit der Adventure Works-Demotabelle von SQL Server (die "Geschwindigkeitsgrenze" ist 7).

Dies ist stark inspiriert von einer anderen Frage zu SO: GROUP BY für fortlaufende Zeilen 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

Etwas in dieser Art sollte Ihnen zusagen:

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

Das kann nicht sein dass einfach, oder doch nicht?

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

Answer 3

Ich habe den folgenden Teil verwendet, um einige Daten zu erhalten (ich bin auf Kompatibilitätsmodus 80 atm, so dass ich nicht über ein Zeitfeld und bin mit einem INT für den Zeitstempel)

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

Unter der Annahme, dass Ihr Standort ein Fixpunkt ist, der überschritten werden muss, um das Folgende zu vervollständigen, ergibt sich die gewünschte Ausgabe. Ich habe es in mehrere Phasen unterteilt, damit klar wird, was jeder Teil tut.

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