由网友(有种愤怒叫担心)分享简介：只是一些有趣的事情来在我的脑海。假设我们有一个表（在SQL Server）是这样的：Just something interesting come in my mind. Assume that we have a table (in SQL Server) like this:位置速度时间例如：Locatio...

只是一些有趣的事情来在我的脑海。假设我们有一个表（在SQL Server）是这样的：

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

位置 速度 时间

例如：

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，输出是这样的

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 periods (speed barrier is defined) ? My first idea was loading the table into an array, and then iterate over array to find these periods:

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

}

它的工作原理，但有些不是很有效。是否有一个聪明的方式，如T-SQL查询或另一种算法来做到这一点？

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

推荐答案

您可以通过使用CTE（的公共表前pressions 的）。

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

查询下面的工作对SQL Server（以下简称限速是7）的Adventure Works的演示表。

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

这是强烈的灵感来自于另一个问题上这样： GROUP BY连续行在SQL 。

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