找出超速时间段？

我脑海中浮现出一些有趣的事情。假设我们有一个表（在 SQL Server 中），如下所示：

• Location
• Velocity
• Time

例如：

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

假设速度障碍是 40kph，输出是这样的

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 查询或其他算法来执行此操作？

您可以通过使用 CTE 来实现这一点（通用表表达式 https://web.archive.org/web/20210927200924/http://www.4guysfromrolla.com/webtech/071906-1.shtml).

下面的查询适用于 SQL Server 的 Adventure Works 演示表（“速度限制”为 7）。

这是受到 SO 上另一个问题的强烈启发：SQL 中连续行的 GROUP BY https://stackoverflow.com/questions/1136597/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

这些内容应该适合您：

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