寻找超速的周期？

Question

有一些有趣的想法。假设我们有一个类似于以下表格的表格（在SQL Server中）：

• 位置
• 速度
• 时间

例如：

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

假设速度限制为40公里每小时，输出结果如下：

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

什么是确定超速期间（定义了速度障碍）的最佳方法？我的第一个想法是将表加载到数组中，然后迭代数组以查找这些时间段：

（伪C#代码）

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

}

它能够工作，但效果不是很理想。是否有更“智能”的方法，例如使用T-SQL查询或其他算法来完成这个任务？

- Quan Mai

---

问题是想要聚合 Velocity > speedBarrier 的时间，作为 TimeSpan 吗？这不太清楚。你期望的输出是什么？ - Kobi

我期望的输出是汽车从“何时”到“何时”超速，而不仅仅是TimeSpan。 - Quan Mai

你对车辆速度随时间变化的模型是什么？ - Donal Fellows

车辆的速度按间隔记录。我忽略了加速度，所以我们只依赖表格上的数据 :) - Quan Mai

从技术上讲，第二列应该是“速度”，而不是“速率”，因为没有方向性成分。 </pedant> - Phil Gan

Answer 1

您可以使用CTE（公共表达式）来实现此目的。

下面的查询针对SQL Server的Adventure Works演示表运行（“ 速度限制”为7）。

这是受到SO上另一个问题的强烈启发：SQL中连续行的GROUP BY。

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

- scherand

Answer 2

这么简单？不会吧！

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

- Tomalak

---

似乎您的查询列出了所有超速记录，而不仅仅是超速期间的记录。现在没有真实的表格进行测试 :) - Quan Mai

@Vimvq1987：是的，这就是为什么我的回答如此犹豫的原因。;-) - Tomalak

刚刚测试了一下，你的查询返回了所有记录，我刚刚添加了一个新字段（IsOverSpeed 1/0） :) - Quan Mai

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

- Don